pandas 按每列分组，并为每组添加新列 [英] Pandas groupby each column and add new column for each group

问题描述

我有一个这样的数据框

lvl1=['l1A','l1A','l1B','l1C','l1D']
lvl2=['l2A','l2A','l2A','l26','l27']
wgt=[.2,.3,.15,.05,.3]
lvls=[lvl1,lvl2]
df=pd.DataFrame(wgt, lvls).reset_index()
df.columns = ['lvl' + str(i) for i in range(1,3)] + ['wgt']
df
  lvl1 lvl2   wgt
0  l1A  l2A  0.20
1  l1A  l2A  0.30
2  l1B  l2A  0.15
3  l1C  l26  0.05
4  l1D  l27  0.30

我想获取每个级别的平均权重，并将它们作为单独的列添加到此数据框.

I want to get the average weight at each level and add them as a separate column to this data frame.

pd.concat([df, df.groupby('lvl1').transform('mean').add_suffix('_l1avg'), df.groupby('lvl2').transform('mean').add_suffix('_l2avg')], axis=1)
  lvl1 lvl2   wgt  wgt_l1avg  wgt_l2avg
0  l1A  l2A  0.20       0.25   0.216667
1  l1A  l2A  0.30       0.25   0.216667
2  l1B  l2A  0.15       0.15   0.216667
3  l1C  l26  0.05       0.05   0.050000
4  l1D  l27  0.30       0.30   0.300000

级别可以超过两个，因此我想使用变量来代替.随着数据集变得非常大，什么是最好，最有效的方法.我不一定需要将它们放在同一数据框中.在这种情况下，它可以只是一个单独的n x m矩阵(2 x 5)中的平均权重矩阵.

The levels can be more than two so I would like to do this using variable instead. What is the best and efficient way to do this as the dataset get to grow very large. I don't necessarily need these to be in the same data frame. It can be just a matrix of average weights in a separate n x m matrix (2 x 5) in this case.

推荐答案

使用list comprehension:

cols = ['lvl1','lvl2']
k = ['{}_avg'.format(x) for x in cols]
df = df.join(pd.concat([df.groupby(c)['wgt'].transform('mean') for c in cols], 1, keys=k))
print (df)
  lvl1 lvl2   wgt  lvl1_avg  lvl2_avg
0  l1A  l2A  0.20      0.25  0.216667
1  l1A  l2A  0.30      0.25  0.216667
2  l1B  l2A  0.15      0.15  0.216667
3  l1C  l26  0.05      0.05  0.050000
4  l1D  l27  0.30      0.30  0.300000

这篇关于 pandas 按每列分组，并为每组添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！