按天和名称对列进行分组，并使用python pandas获取其开始和结束的最小值 [英] group the columns by day and name and get the min value with their start and end using python pandas

问题描述

需要按天和名称对列进行分组，并获得其开始和结束的最小值

need to group the columns by day and name and get the min value with their start and end

dataframe

day name value start end duration
Wednesday AAA 1 10/23/2019 2:46  10/23/2019 3:09 00:23
Wednesday AAA 1 10/23/2019 5:20  10/23/2019 5:44 00:24
Wednesday AAA 1 10/23/2019 6:51  10/23/2019 8:14 01:23
Wednesday AAA 17602 10/23/2019 12:35 10/23/2019 12:38 00:03
Wednesday AAA 1155 10/23/2019 15:50 10/23/2019 15:54 00:04

逻辑

df.groupby(['day','name']).agg({'duration':[np.min,np.max],'start':[np.min,np.max],'end':[np.min,np.max],'value':[np.min,np.max]})

我会得到什么

day name duration_min duration_max duration_max_start duration_max_end duration_min_start duration_min_end value_min value_max
Wednesday AAA 00:03 01:23 10/23/2019 6:51  10/23/2019 3:09 10/23/2019 12:35 10/23/2019 15:54 1  17602

但是我应该得到什么

day name duration_min duration_max duration_max_start duration_max_end value_max duration_min_start duration_min_end value_min
Wednesday AAA 00:03 01:23 10/23/2019 6:51  10/23/2019 8:14 1 10/23/2019 12:35 10/23/2019 12:38 17602

我需要通过将其开始值和结束值分组来获得最小值和最大值

what i want is need to get min value and max value by grouping with their start and end values

推荐答案

您想要的是发生持续时间min和max的同一行上的属性.您所写的是每一列的最小值和最大值，无论它们是否在同一行中.

What you want is the attributes on the same row where duration min and max occur. What you wrote is the min and max of each individual column, whether they are on the same row or not.

使用idxmin& idxmax查找出现最大值和最小值的行，然后与原始帧合并:

Use idxmin & idxmax to find the row where min and max values occur, then merge with the original frame:

idx = df.groupby(['day','name'])['duration'].agg(['idxmin','idxmax'])
idx.merge(df.add_suffix('_min'), left_on='idxmin', right_index=True) \
    .merge(df.add_suffix('_max'), left_on='idxmax', right_index=True) \
    [['duration_min', 'duration_max', 'start_min', 'end_min', 'start_max', 'end_max', 'value_min', 'value_max']]

结果:

day       | name | duration_min | duration_max | start_min           | end_min             | start_max           | end_max             | value_min | value_max
Wednesday | AAA  | 00:03        | 01:23        | 2019-10-23 12:35:00 | 2019-10-23 12:38:00 | 2019-10-23 06:51:00 | 2019-10-23 08:14:00 | 17602     | 1

根据需要重命名列.

这篇关于按天和名称对列进行分组，并使用python pandas获取其开始和结束的最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！