python:pandas:如何基于groupby另一列在列中查找最大值 [英] python: pandas: how to find max value in a column based on groupby another column
本文介绍了python:pandas:如何基于groupby另一列在列中查找最大值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想根据一列 SERVER 对我的数据框进行分组，然后在另一列 JOB_ID 中找到最大值.DF:
I want to group my dataframe based on one column SERVER and than find max value in other column JOB_ID. DF:
SERVER JOB_ID LOG_FILE TIME
0 abc_123 1 1/abc_123/dep2/1/123.log 2019-12-05T05:06:16.346Z
1 abc_123 10 1/abc_123/dep2/10/123.log 2019-12-04T17:05:28.335Z
2 abc_123 11 1/abc_123/dep2/11/123.log 2019-12-04T20:27:03.988Z
3 abc_123 12 1/abc_123/dep2/12/123.log 2019-12-04T20:35:49.039Z
4 abc_123 13 1/abc_123/dep2/13/123.log 2019-12-04T20:42:36.890Z
5 abc_123 14 1/abc_123/dep2/14/123.log 2019-12-04T20:52:01.295Z
6 abc_123 15 1/abc_123/dep2/15/123.log 2019-12-04T20:58:07.132Z
7 abc_123 16 1/abc_123/dep2/16/123.log 2019-12-04T20:59:51.877Z
8 abc_123 17 1/abc_123/dep2/17/123.log 2019-12-04T21:00:23.458Z
9 abc_123 18 1/abc_123/dep2/18/123.log 2019-12-04T21:05:48.047Z
10 abc_123 19 1/abc_123/dep2/19/123.log 2019-12-05T03:10:39.325Z
11 abc_123 2 1/abc_123/dep2/2/123.log 2019-12-04T15:37:41.540Z
12 abc_123 20 1/abc_123/dep2/20/123.log 2019-12-05T04:09:39.221Z
13 abc_123 21 1/abc_123/dep2/21/123.log 2019-12-05T04:14:54.228Z
14 abc_123 3 1/abc_123/dep2/3/123.log 2019-12-04T15:41:38.340Z
15 abc_123 4 1/abc_123/dep2/4/123.log 2019-12-04T15:43:34.277Z
16 abc_123 5 1/abc_123/dep2/5/123.log 2019-12-04T15:56:18.647Z
17 abc_123 6 1/abc_123/dep2/6/123.log 2019-12-04T16:14:23.323Z
18 abc_123 7 1/abc_123/dep2/7/123.log 2019-12-04T16:19:22.126Z
19 abc_123 8 1/abc_123/dep2/8/123.log 2019-12-04T16:32:30.121Z
20 abc_123 9 1/abc_123/dep2/9/123.log 2019-12-04T16:53:54.236Z
21 abc_123 1 1/abc_123/dep_1/1/123.log 2019-11-30T06:20:16.528Z
22 abc_123 10 1/abc_123/dep_1/10/123.log 2019-12-03T07:10:38.320Z
23 abc_123 11 1/abc_123/dep_1/11/123.log 2019-12-03T09:19:33.350Z
24 abc_123 12 1/abc_123/dep_1/12/123.log 2019-12-03T09:51:49.835Z
25 abc_123 13 1/abc_123/dep_1/13/123.log 2019-12-03T10:43:19.727Z
26 abc_123 14 1/abc_123/dep_1/14/123.log 2019-12-04T06:11:52.125Z
27 abc_123 15 1/abc_123/dep_1/15/123.log 2019-12-04T06:33:58.416Z
28 abc_123 16 1/abc_123/dep_1/16/123.log 2019-12-04T06:48:18.057Z
29 abc_123 2 1/abc_123/dep_1/2/123.log 2019-11-30T16:45:13.983Z
30 abc_123 3 1/abc_123/dep_1/3/123.log 2019-11-30T18:19:14.364Z
31 abc_123 4 1/abc_123/dep_1/4/123.log 2019-12-02T08:38:01.766Z
32 abc_123 5 1/abc_123/dep_1/5/123.log 2019-12-02T10:12:45.500Z
33 abc_123 6 1/abc_123/dep_1/6/123.log 2019-12-02T12:04:03.326Z
34 abc_123 7 1/abc_123/dep_1/7/123.log 2019-12-02T15:13:11.312Z
35 abc_123 8 1/abc_123/dep_1/8/123.log 2019-12-03T05:44:47.436Z
36 abc_123 9 1/abc_123/dep_1/9/123.log 2019-12-03T06:16:05.041Z
当我在代码下面运行时
DF_FINAL = DF.groupby(['SERVER']).agg({'JOB_ID':'max'})
低于输出
SERVER JOB_ID LOG_FILE TIME
20 abc_123 9 1/abc_123/dep2/9/123.log 2019-12-04T16:53:54.236Z
预期输出
13 abc_123 21 1/abc_123/dep2/21/123.log 2019-12-05T04:14:54.228Z
我参考了这个链接.但它没有给我正确的答案.
I refered this link. But its not giving me correct answer.
推荐答案
Column JOB_ID 不是数字，而是字符串(dtype是object)，所以需要转换为解决方案之前的数字:
Column JOB_ID is not numeric, but strings (dtype is object), so need convert it to numeric before your solution:
DF.JOB_ID = DF.JOB_ID.astype(int)
如果上面的解决方案不起作用，因为一些非数值使用:
If not working solution above, because some non numeric values use:
DF.JOB_ID = pd.to_numeric(DF.JOB_ID, errors='coerce')
最后使用 DataFrameGroupBy.idxmax 用于带有 DataFrame.loc:
DF_FINAL = DF.loc[DF.groupby('SERVER')['JOB_ID'].idxmax()]
这篇关于python:pandas:如何基于groupby另一列在列中查找最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
