Replace pandas column with sorted index

Is this what you are looking for in column d1? You could apply some similar technique to d2 as well. Its not the most elegant solution though.

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randint(0,10,size=(7,3)),columns=["a","b","c"])
df["d1"]=["Apple","Mango","Apple","Mango","Mango","Mango","Apple"]
df["d2"]=["Orange","lemon","lemon","Orange","lemon","Orange","lemon"]
df["date"] = ["2002-01-01","2002-01-01","2002-01-01","2002-01-01","2002-02-01","2002-02-01","2002-02-01"]
df["date"] = pd.to_datetime(df["date"])

df['mean_value'] = df.groupby(['date', 'd1'])['c'].transform(lambda x: np.mean(x))
df['rank_value'] = (df.groupby(['date'])['mean_value'].rank(ascending=True, method='dense') - 1).astype(int)
df['d1'] = df['rank_value']
df.drop(labels=['rank_value', 'mean_value'], axis=1, inplace=True)

df

   a  b  c  d1      d2       date
0  3  1  4   1  Orange 2002-01-01
1  9  7  5   0   lemon 2002-01-01
2  9  9  5   1   lemon 2002-01-01
3  8  1  2   0  Orange 2002-01-01
4  8  0  1   0   lemon 2002-02-01
5  1  8  3   0  Orange 2002-02-01
6  8  0  4   1   lemon 2002-02-01