时间戳

首页 > 解决方案 > Pandas: Find N largest values on each group Then create N columns

python - Pandas: Find N largest values on each group Then create N columns

问题描述

I want to find N largest values from each group then create N columns with ITEM and VAL.

df = pd.DataFrame()
df['DATE'] = ['2018-01-01', '2018-01-01', '2018-01-01', '2018-01-01',
              '2018-01-02', '2018-01-02', '2018-01-02', '2018-01-02']

df['ITEM'] = ['A', 'B', 'C', 'D', 'A', 'B', 'C', 'E']
df['VAL'] = [1, 4, 5, 3, 5, 4, 4, 6]

df

         DATE ITEM  VAL
0  2018-01-01    A    1
1  2018-01-01    B    4
2  2018-01-01    C    5
3  2018-01-01    D    3
4  2018-01-02    A    5
5  2018-01-02    B    4
6  2018-01-02    C    4
7  2018-01-02    E    6

I tried this following code, and I'm stuck here. I can't find an efficient way to get my expected output. Any ideas?

N = 3
df.groupby(['DATE']).apply(lambda x: x.set_index('ITEM').VAL.nlargest(N)).unstack()

ITEM          A    B    C    D    E
DATE                               
2018-01-01  NaN  4.0  5.0  3.0  NaN
2018-01-02  5.0  4.0  NaN  NaN  6.0

Expected Output:

         DATE TOP_1  VAL_1 TOP_2  VAL_2 TOP_3  VAL_3
0  2018-01-01     C      5     B      4     D      3
1  2019-01-02     E      6     A      5     B      4

标签： pythonpandas

解决方案

用于GroupBy.cumcount计数器列，使用DataFrame.set_indexwith重塑形状，使用 sDataFrame.unstack展平MultiIndex使用列表理解f-string：

df1 = df.groupby(['DATE']).apply(lambda x: x.set_index('ITEM').VAL.nlargest(N)).reset_index()

或者：

df1 = df.sort_values(['DATE','VAL'], ascending=[True, False]).groupby('DATE').head(N)

g = df1.groupby('DATE').cumcount().add(1)
df1 = df1.set_index(['DATE',g]).unstack().sort_index(level=1, axis=1)
df1.columns = [f'{x}_{y}' for x, y in df1.columns]
df1 = df1.reset_index()
print (df1)
         DATE ITEM_1  VAL_1 ITEM_2  VAL_2 ITEM_3  VAL_3
0  2018-01-01      C      5      B      4      D      3
1  2018-01-02      E      6      A      5      B      4

推荐阅读