python - 应用 groupby() 后计算最大行数

Question

我有一个数据框如下-
文件名 PageNo LineNo 名称 Class_par_ratio
17973375 - 1 TM000010 82 奶粉
17973375 - 1 TM000015 49 牛奶 MILK
17973375 - 1 TM000015 49 乳制品 其他食品
17973375 - 1 TM000016 11 动植物脂肪油
17973375 - 1 TM000006 79 奶粉
17973375 - 1 TM000016 9 牛奶 MILK

我想按 FileName 和 Class_par_ratio 对输出进行分组，我还想找到 Class_par_ratio 的频率并将其放在列 - frequency 中，然后我想在另一个名为“max freq”的列中找到最大频率。

输出有点像-

FileName      Class_par_ratio           Frequency    Max_Class     Max Freq.
17743633 - 1  OTHER FOODS               2            OTHER GOODS    4
              OTHER GOODS               4                  
17743634 - 1  MEAT                      12           MEAT           12
17743634 - 2  MEAT                       1           MEAT            1
17743635 - 1  MEAT                      83           MEAT           83
              OTHER GOODS               2      
17743642 - 1  MEAT                      43           MEAT           43
              OTHER GOODS               2                  
17743739 - 1  OTHER GOODS               3            OTHER GOODS     3

到目前为止，我尝试过的代码片段是 -

1) df.groupby(['FileName'])['Class_par_ratio'].value_counts()

我得到的输出是:-

FileName      Class_par_ratio
17743633 - 1  OTHER GOODS         8
17743634 - 1  MEAT AND LIVESTOCK 15
17743634 - 2  PETROLEUM           1
17743635 - 1  MEAT AND LIVESTOCK 87

另一个是——

2) coll_g = coll.groupby(['FileName', 'Class_par_ratio']).size().groupby(              
['FileName', 'Class_par_ratio']).agg({'count': max})
coll_g = coll_g['count'].groupby(level=0, group_keys=False)
coll_g = coll_g.nlargest(1)
coll_g

在这里，我得到了出现次数最多的类，但我没有得到最大值。频率没有。我得到的输出是 -

17743754 - 1  MEAT & LIVESTOCK            1
17743759 - 1  ANIMAL AND VEGETABLE OIL    1
17743970 - 1  IRON ORE                    1
17743996 - 1  OTHER GOODS                 1

我正在使用 Pandas .20 和 python 3.6.3

你们能告诉我哪里出错了以及我的代码应该是什么吗。

Answer 1

使用agg通过 idxmax , 什么返回最大类别因为之前 set_index和 max 到新的 DataFrame 然后 join 到原始的 DataFrame:

df = df.groupby(['FileName'])['Class_par_ratio'].value_counts().reset_index(name='Freq')

df1 = df.set_index('Class_par_ratio').groupby(['FileName'])['Freq'].agg(['idxmax','max'])

d = {'idxmax':'Max_Class','max':'Max Freq.'}
df = df.join(df1, on='FileName').rename(columns=d)

或者使用双transform :

df = df.groupby(['FileName'])['Class_par_ratio'].value_counts().reset_index(name='Freq')

g = df.set_index('Class_par_ratio').groupby(['FileName'])['Freq']
df['Max_Class'] = g.transform('idxmax').values
df['Max Freq.'] = g.transform('max').values
print (df)
       FileName           Class_par_ratio  Freq Max_Class  Max Freq.
0  17973375 - 1                      MILK     4      MILK          4
1  17973375 - 1  ANIMAL AND VEGETABLE OIL     1      MILK          4
2  17973375 - 1               OTHER FOODS     1      MILK          4

第二个样本数据验证方案:

df1 = df.set_index('Class_par_ratio').groupby(['FileName'])['Frequency'].agg(['idxmax','max'])
d = {'idxmax':'Max_Class','max':'Max Freq.'}
df = df.join(df1, on='FileName').rename(columns=d)
print (df)
       FileName Class_par_ratio  Frequency    Max_Class  Max Freq.
0  17743633 - 1      OTHE FOODS          2  OTHER GOODS          4
1  17743633 - 1     OTHER GOODS          4  OTHER GOODS          4
2  17743634 - 1            MEAT         12         MEAT         12
3  17743634 - 2            MEAT          1         MEAT          1
4  17743635 - 1            MEAT         83         MEAT         83
5  17743635 - 1     OTHER GOODS          2         MEAT         83
6  17743642 - 1            MEAT         43         MEAT         43
7  17743642 - 1     OTHER GOODS          2         MEAT         43
8  17743739 - 1     OTHER GOODS          3  OTHER GOODS          3

如果需要删除重复值，请添加 duplicated与 mask :

cols = ['Max_Class','Max Freq.']
df[cols] = df[cols].mask(df['FileName'].duplicated())
print (df)
       FileName Class_par_ratio  Frequency    Max_Class  Max Freq.
0  17743633 - 1      OTHE FOODS          2  OTHER GOODS        4.0
1  17743633 - 1     OTHER GOODS          4          NaN        NaN
2  17743634 - 1            MEAT         12         MEAT       12.0
3  17743634 - 2            MEAT          1         MEAT        1.0
4  17743635 - 1            MEAT         83         MEAT       83.0
5  17743635 - 1     OTHER GOODS          2          NaN        NaN
6  17743642 - 1            MEAT         43         MEAT       43.0
7  17743642 - 1     OTHER GOODS          2          NaN        NaN
8  17743739 - 1     OTHER GOODS          3  OTHER GOODS        3.0