add a new categorical column to an existing table python
d = df.groupby('Item_Identifier')['Sales'].mean().to_dict()
print (d)
{'Beef': 3030.0, 'Milk': 1233.3333333333333, 'Tea': 150.0}
print (df['Item_Identifier'].map(d))
0 1233.333333
1 1233.333333
2 1233.333333
3 3030.000000
4 3030.000000
5 150.000000
6 150.000000
7 150.000000
Name: Item_Identifier, dtype: float64
bins = [df['Sales'].min(),500, 1500, df['Sales].max()]
labels=['low','medium','high']
df['Price Category'] = pd.cut(df['Item_Identifier'].map(d), bins=bins, labels=labels)
print (df)
Item_Identifier Sales Price Category
0 Milk 500 medium
1 Milk 1200 medium
2 Milk 2000 medium
3 Beef 60 high
4 Beef 6000 high
5 Tea 150 low
6 Tea 100 low
7 Tea 200 low