pandas groupby，然后按列值(例如，最小值，最大值)选择一行 [英] pandas groupby and then select a row by value of column (min,max, for example)

问题描述

假设我有一个数据框

Category Data1 column1
A 'SOMEDATA' 10
A 'SOMEDATA' 2
A 'SOMEDATA' -10
B 'SOMEDATA' 10
B 'SOMEDATA' 1
B 'SOMEDATA' -10

以此类推

我想按列值在每个组中选择一行.例如，ABS(column1)

I'd like to select a one row in each group by column value. For example, ABS(column1)

因此得出的数据是

Category Data1 column1
A 'SOMEDATA' 2
B 'SOMEDATA'  1

如何在python中做到这一点?

How can I do this in python?

我不知道如何返回整行.例如，

I couldn't figure out how to return entire row. For example,

df.groupby('Category')['column1'].min();

这只会返回"Category" min(column1).

this would only return 'Category' min(column1) only.

推荐答案

sort然后.drop_duplicates.

(df.assign(to_sort = df.column1.abs()).sort_values('to_sort')
     .drop_duplicates('Category').drop(columns='to_sort'))

  Category       Data1  column1
4        B  'SOMEDATA'        1
1        A  'SOMEDATA'        2

排序只能对现有列进行排序，因此我们需要创建具有绝对值的列(使用.assign).然后进行排序，以确保最小绝对值首先出现，并且删除重复项将保留每个类别的第一行，现在是最小绝对值行.

Sort can only sort on existing columns, so we need to create the column of absolute values (with .assign). Sorting then ensures the minumum absolute value appears first, and dropping duplicates keeps the first row for each category, which is now the minumum absolute value row.

groupby也可以，如果每个组需要返回多个行，则更好:

Also possible with groupby, which is better if you need to return more than one row per group:

df.assign(to_sort = df.column1.abs()).sort_values('to_sort').groupby(df.Category).head(1)

---

或者，您可以使用groupby的结果进行切片.如果要返回与最小值匹配的所有所有行，这很有用:

---

Alternatively, you can slice with the result of a groupby. This is useful in cases where you want to return all rows that match the minimum:

df[df.groupby(df.Category, group_keys=False).apply(lambda x: x.column1 == x.column1.abs().min())]

  Category       Data1  column1
1        A  'SOMEDATA'        2
4        B  'SOMEDATA'        1

这篇关于pandas groupby，然后按列值(例如，最小值，最大值)选择一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！