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!