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