Skip to main content
 首页 » 编程设计

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

2024年12月31日13Leo_wl

我有一个数据框如下- 文件名 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

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

请您参考如下方法:

使用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