Dataframe：如何将列中的值分组以创建pi的索引

Question

Hi everyone, I'm super new at this so I'm looking for some help. Please consider the following dataframe:

      fruit     sales     price    
0     lemon      ..         .. 
1     orange     ..         ..
2     carrot     ..         .. 
3     potato     ..         .. 
4    pineapple   ..         .. 
5     mango      ..         .. 

Lets say that fruit column can be categorized in the following way: lemon + orange = citrus; carrot + potato = tuber; pineapple + mango = tropical.

After I would like to use this new grouping as an index for a pivot table. , in order to see average price or sales in a "citrus/tuber/tropical" segmentation.

In the dataframe I'm trying to apply this logic on there are too many values to make a ditionary.

Any help would be greatly appreciated :)

Answer 1

您可以为^{}创建dict，并使用^{}witt aggregate ^{}：

#sample data
df = pd.DataFrame({
'price': [4, 7, 3, 4, 1, 4], 
'sales': [1, 5, 1, 2, 6, 3], 
'model': ['lemon', 'orange', 'carrot', 'potato', 'pineapple', 'mango']})
print (df)
       model  price  sales
0      lemon      4      1
1     orange      7      5
2     carrot      3      1
3     potato      4      2
4  pineapple      1      6
5      mango      4      3

#dict of mapping
d1 = {'citrus': ['lemon', 'orange'],
      'tuber':['carrot', 'potato'],
       'tropical':['pineapple', 'mango']}
#is necessary swap values with keys and expand them to new dict
d = {k: oldk for oldk, oldv in d1.items() for k in oldv}
print (d)
{'pineapple': 'tropical', 'potato': 'tuber', 'mango': 'tropical', 
'lemon': 'citrus', 'orange': 'citrus', 'carrot': 'tuber'}

s = df['model'].map(d)
df1 = df.groupby(s)['sales'].mean().reset_index()
print (df1)
      model  sales
0    citrus    3.0
1  tropical    4.5
2     tuber    1.5

与^{}类似的解决方案，但随后需要更改列名：

df1 = df.set_index('model').groupby(d)['sales'].mean().reset_index()
df1.columns= ['model','sales']
print (df1)
      model  sales
0    citrus    3.0
1  tropical    4.5
2     tuber    1.5