pandas 对每个组使用不同的列来计数组内的出现次数

xvw2m8pv 于 2023-01-07 发布在其他

关注(0)|答案(1)|浏览(106)

在下面的df中，变量“group”中有三个组-“A”、“AB”、“C”。df中的其他列通过后缀- var1_A关联到组A等分配给特定组。

data = pd.DataFrame({'group':['A', 'AB', 'A', 'AB', 'AB', 'C', 'C', 'A', 'A', 'AB'],
                     'var1_A':['pass', 'fail', 'pass','fail', 'pass']*2,
                     'var2_A':['pass', 'pass', 'pass','fail', 'pass']*2,
                     'var1_AB':['pass', 'pass', 'pass','fail', 'pass']*2,
                     'var2_AB':['pass', 'pass', 'fail','fail', 'pass']*2,
                     'var1_C':['pass', 'pass', 'pass','fail', 'pass']*2,
                     'var2_C': ['fail', 'fail', 'fail','fail', 'pass']*2
                    })

我想为每一行计数'通过'发生的次数。对于属于组A的示例，我只想计数连接到组A的变量。我想在一个新的列中得到结果。这几乎可以完成工作。

data['new_col'] = data[data['group']=='A']['var1_A, var2_A].isin(['pass']).sum(1)
data['new_col'] = data[data['group']=='AB']['var1_AB, var2_AB].isin(['pass']).sum(1)
data['new_col'] = data[data['group']=='C']['var1_C, var2_C].isin(['pass']).sum(1)

然而，我希望所有组的结果都在同一列中。这个操作也许可以使用groupby和transform来完成。但是，我被困在了计算中。
目标 Dataframe ：

pd.DataFrame({'group':['A', 'AB', 'A', 'AB', 'AB', 'C', 'C', 'A', 'A', 'AB'],
                     'var1_A':['pass', 'fail', 'pass','fail', 'pass']*2,
                     'var2_A':['pass', 'pass', 'pass','fail', 'pass']*2,
                     'var1_AB':['pass', 'pass', 'pass','fail', 'pass']*2,
                     'var2_AB':['pass', 'pass', 'fail','fail', 'pass']*2,
                     'var1_C':['pass', 'pass', 'pass','fail', 'pass']*2,
                     'var2_C': ['fail', 'fail', 'fail','fail', 'pass']*2,
                     'result':[2,2,2,0,2,1,1,2,0,2]
                    })
'''

pandas

来源：https://stackoverflow.com/questions/75030573/count-occurrence-within-a-group-using-different-columns-for-each-group

1条答案

按热度按时间

t30tvxxf1#

您可以melt、过滤器和groupby.count：

data['result'] = (data
  .rename(columns=lambda x: x.split('_')[-1]) # get only part after "_"
  .reset_index().melt(['index', 'group'])
  # keep only identical groups and "pass" values
  .loc[lambda d: d['group'].eq(d['variable']) & d['value'].eq('pass')]
  .groupby('index')['value'].count()
  .reindex(data.index, fill_value=0)
)

print(data)

或者使用矩阵和字符串比较的另一种方法：

df2 = data.set_index('group').eq('pass')
data['result'] = (df2.mul(df2.columns.str.extract('_(\w+)', expand=False))
                     .eq(df2.index, axis=0).sum(axis=1)
                     .to_numpy()
                 )

输出：

group var1_A var2_A var1_AB var2_AB var1_C var2_C  result
0     A   pass   pass    pass    pass   pass   fail       2
1    AB   fail   pass    pass    pass   pass   fail       2
2     A   pass   pass    pass    fail   pass   fail       2
3    AB   fail   fail    fail    fail   fail   fail       0
4    AB   pass   pass    pass    pass   pass   pass       2
5     C   pass   pass    pass    pass   pass   fail       1
6     C   fail   pass    pass    pass   pass   fail       1
7     A   pass   pass    pass    fail   pass   fail       2
8     A   fail   fail    fail    fail   fail   fail       0
9    AB   pass   pass    pass    pass   pass   pass       2

赞(0）回复(0）举报 2023-01-07

我来回答

pandas 对每个组使用不同的列来计数组内的出现次数

1条答案

相关问题

热门标签

最新问答