[ad_1]

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()
```

Thank you!

[ad_2]