dataframe:
| cater | rating |
|---|---|
| a | 2.5 |
| b | 4 |
| c | 3 |
| a | 4 |
| a | 2.5 |
| b | 4 |
what i want:
| cater | rating | average |
|---|---|---|
| a | 2.5 | 3.25 |
| b | 4 | 3.5 |
| c | 3 | 3 |
| a | 4 | 3.25 |
| b | 2.5 | 3.5 |
| b | 4 | 3.5 |
how I convert cater index to integer index?
2 Answers
You need to self join your dataframe with a grouped version of itself. This is achievable by using pd.merge() with your dataframe as the left table variable and groupby dataframe as your right table variable.
>>> df
cater rating
0 1 2.5
1 2 4.0
2 3 3.0
3 1 4.0
4 2 2.5
5 2 4.0
>>> df2 = df.groupby('cater', as_index=False).mean()
>>> df2
cater rating
0 1 3.25
1 2 3.50
2 3 3.00
>>> df3 = pd.merge(df, df2, on='cater', suffixes=('', '_average'))
>>> df3.assign(cater = df3['cater'].map({1:'a',2:'b',3:'c'}))
cater rating rating_average
0 a 2.5 3.25
1 a 4.0 3.25
2 b 4.0 3.50
3 b 2.5 3.50
4 b 4.0 3.50
5 c 3.0 3.00
2 Comments
Leo
i changed the question, would you mind edit it again?
Gusti Adli
sure, please check on the updated answer @Leo
You can use this,
import pandas as pd
Input = "yourdataframe.xlsx"
df1 = pd.read_excel(Input, sheet_name = ' ')
df2 = df1.groupby('cater', as_index=False).mean().reset_index(drop = True)
df3 = pd.merge(df1, df2, on = 'cater', how = 'left')
df3.columns = ['cater', 'rating', 'average']
machine-learningquestion, kindly do not spam irrelevant tags (removed).