在pandas python中按两列和第三个最大值分组

本文介绍了在pandas python中按两列和第三个最大值分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带有 PERIOD_START_TIME、ID、更多列和列 VALUE 的数据框.我需要的是按 PERIOD_START_TIME 和 ID 分组(因为按时间和 ID 存在重复行)并取列 VALUE 的最大值.df:

I have a dataframe with PERIOD_START_TIME, ID, a few more columns and column VALUE. What I need is group by PERIOD_START_TIME and ID(cause there are duplicate rows by time and ID) and take max value of column VALUE. df:

PERIOD_START_TIME ID VALUE 06.01.2017 02:00:00 55 ... 35 06.01.2017 02:00:00 55 ... 22 06.01.2017 03:00:00 55 ... 63 06.01.2017 03:00:00 55 ... 33 06.01.2017 04:00:00 55 ... 63 06.01.2017 04:00:00 55 ... 45 06.01.2017 02:00:00 65 ... 10 06.01.2017 02:00:00 65 ... 5 06.01.2017 03:00:00 65 ... 22 06.01.2017 03:00:00 65 ... 5 06.01.2017 04:00:00 65 ... 12 06.01.2017 04:00:00 65 ... 15

所需的输出:

PERIOD_START_TIME ID ... VALUE 06.01.2017 02:00:00 55 ... 35 06.01.2017 03:00:00 55 ... 63 06.01.2017 04:00:00 55 ... 63 06.01.2017 02:00:00 65 ... 10 06.01.2017 03:00:00 65 ... 22 06.01.2017 04:00:00 65 ... 15

推荐答案

使用 groupby 并聚合 max:

print (df) PERIOD_START_TIME ID A VALUE 0 06.01.2017 02:00:00 55 8 35 1 06.01.2017 02:00:00 55 8 22 2 06.01.2017 03:00:00 55 8 63 3 06.01.2017 03:00:00 55 8 33 4 06.01.2017 04:00:00 55 8 63 5 06.01.2017 04:00:00 55 8 45 6 06.01.2017 02:00:00 65 8 10 7 06.01.2017 02:00:00 65 8 5 8 06.01.2017 03:00:00 65 8 22 9 06.01.2017 03:00:00 65 8 5 10 06.01.2017 04:00:00 65 8 12 11 06.01.2017 04:00:00 65 8 15 df = df.groupby(['PERIOD_START_TIME','ID'], as_index=False)['VALUE'].max()

或者:

df = df.groupby(['PERIOD_START_TIME','ID'])['VALUE'].max().reset_index()

print (df) PERIOD_START_TIME ID VALUE 0 06.01.2017 02:00:00 55 35 1 06.01.2017 02:00:00 65 10 2 06.01.2017 03:00:00 55 63 3 06.01.2017 03:00:00 65 22 4 06.01.2017 04:00:00 55 63 5 06.01.2017 04:00:00 65 15

更多栏目需要idxmax 并通过 选择loc:

For more columns need idxmax and select by loc:

df = df.loc[df.groupby(['PERIOD_START_TIME','ID'])['VALUE'].idxmax()] print (df) PERIOD_START_TIME ID A VALUE 0 06.01.2017 02:00:00 55 8 35 6 06.01.2017 02:00:00 65 8 10 2 06.01.2017 03:00:00 55 8 63 8 06.01.2017 03:00:00 65 8 22 4 06.01.2017 04:00:00 55 8 63 11 06.01.2017 04:00:00 65 8 15

替代方案:

cols = ['PERIOD_START_TIME','ID'] df = df.sort_values(cols).groupby(cols, as_index=False).first() print (df) PERIOD_START_TIME ID A VALUE 0 06.01.2017 02:00:00 55 8 35 1 06.01.2017 02:00:00 65 8 10 2 06.01.2017 03:00:00 55 8 63 3 06.01.2017 03:00:00 65 8 22 4 06.01.2017 04:00:00 55 8 63 5 06.01.2017 04:00:00 65 8 12