pandas dataframe:如何基于列的值聚合行的子集 [英] pandas dataframe: how to aggregate a subset of rows based on value of a column

问题描述

我有一个结构如下的pandas数据框:

I have a pandas dataframe structured like this:

      value
lab        
A        50
B        35
C         8
D         5
E         1
F         1

这只是一个示例，实际的数据帧较大，但是遵循相同的结构.
使用以下两行创建了示例数据框:

This is just an example, the actual dataframe is bigger, but follows the same structure.
The sample dataframe has been created with this two lines:

df = pd.DataFrame({'lab':['A', 'B', 'C', 'D', 'E', 'F'], 'value':[50, 35, 8, 5, 1, 1]})
df = df.set_index('lab')

我想汇总其值小于给定阈值的行:所有这些行都应由单个行替换，其值是被替换行的总和.

I would like to aggregate the rows whose value is smaller that a given threshold: all these rows should be substituted by a single row whose value is the sum of the substituted rows.

例如，如果我选择阈值= 6，则预期结果应为:

For example, if I choose a threshold = 6, the expected result should be the following:

      value
lab        
A        50
B        35
C         8
X         7 #sum of D, E, F

我该怎么做?

我想使用groupby()，但是我看到的所有示例都涉及使用单独的列进行分组，因此在这种情况下我不知道如何使用它.
通过执行df.loc[df['value'] < threshold]，可以使用loc选择小于阈值的行，但是我不知道如何只对这些行求和，而其余数据框保持不变.

I thought to use groupby(), but all the examples I've seen involved the use of a separate column for grouping, so I do not know how to use it in this case.
I can select the rows smaller than my threshold with loc, by doing df.loc[df['value'] < threshold] but I do not know how to sum only these rows and leave the rest of the dataframe unaltered.

推荐答案

使用

Use setting with enlargement with filtered DataFrame:

threshold = 6
m = df['value'] < threshold
df1 = df[~m].copy()
df1.loc['Z'] = df.loc[m, 'value'].sum()

print (df1)
     value
lab       
A       50
B       35
C        8
Z        7

另一种解决方案:

m = df['value'] < threshold
df1 = df[~m].append(df.loc[m, ['value']].sum().rename('Z'))
print (df1)
     value
lab       
A       50
B       35
C        8
Z        7

这篇关于pandas dataframe:如何基于列的值聚合行的子集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！