I have a pandas multi index that looks like this:
I would like to transform the Raw_Score into a percentile, where the comparison is against the day. So the top score of the day gets a 100, and the bottom score of the day gets at zero, with the other scores proportionately aligned. I would like to do this at the “all data”, “Sector”, or “Region” level. The dataframe would have a new column called “Adjusted_Score”. I think I have to use groupby and quantile, but I am lost. Can someone point me in the right direction?
Here is my code to set up the df:
import pandas as pd import numpy as np from datetime import datetime from numpy import random def create_df(num): # create empty data frame in pandas df = pd.DataFrame() # add a range of dates dates = pd.date_range(start="2022-04-01",end="2022-06-05").to_pydatetime().tolist() df['Dates'] = dates # generate a random 3 digit Value #for i in range(len(dates)): df['Raw_Score'] = np.random.randint(-999, 999, size=len(df)) # generate a random 2 digit Sector between 10 and 15 #for i in range(len(dates)): df['Sector'] = np.random.randint(10, 20, size=len(df)) # give it a unique value df['Region'] = num return df # make a big df big_df =  for num in range(10): df = create_df(num) df = pd.DataFrame(df) while num == 0: big_df = df.copy() num = num + 1 else: big_df = pd.concat((big_df, df), axis=0) big_df.set_index(["Region", "Sector", "Dates"]) big_df.groupby(level=[0,1]).quantile()
Leave an answer