python - Pandas 在 group_by 和 value_counts 之后将列添加到 df

Question

import pandas as pd
group = ['A']*5 + ['B']*4 + ['C']*3
color = ['red', 'red', 'green', 'blue', 'green', 'red'] * 2
df = pd.DataFrame({'group':group, 'color':color})
df

    group   color
0   A   red
1   A   red
2   A   green
3   A   blue
4   A   green
5   B   red
6   B   red
7   B   red
8   B   green
9   C   blue
10  C   green
11  C   red

我想要的是向此数据框添加新列，该列将显示组中每种颜色的频率(作为整体的一部分)。可以使用以下代码获取此列的值:

df.groupby('group').color.value_counts(normalize=True)

group  color
A      green    0.400000
       red      0.400000
       blue     0.200000
B      red      0.750000
       green    0.250000
C      blue     0.333333
       green    0.333333
       red      0.333333
Name: color, dtype: float64

但是我不知道如何将它变成与原始数据框相对应的列。我试过了

df['freq'] = df.groupby('group').color.transform('value_counts(normalize=True)') # error

这是行不通的。这是我正在寻找的输出:

    group   color   freq
0   A   red         0.4
1   A   red         0.4
2   A   green       0.4
3   A   blue        0.2
4   A   green       0.4
5   B   red         0.75
6   B   red         0.75
7   B   red         0.75
8   B   green       0.25
9   C   blue        0.33
10  C   green       0.33
11  C   red         0.33

Answer 1

或者 join依赖于 group 和 color:

counts = df.groupby('group')['color'].value_counts(normalize=True)
df = df.join(counts.rename('freq'), on=['group', 'color'])

   group  color      freq
0      A    red  0.400000
1      A    red  0.400000
2      A  green  0.400000
3      A   blue  0.200000
4      A  green  0.400000
5      B    red  0.750000
6      B    red  0.750000
7      B    red  0.750000
8      B  green  0.250000
9      C   blue  0.333333
10     C  green  0.333333
11     C    red  0.333333

---

或者通过计算group + color counts vs group counts 通过 groupby transform 手动计算归一化值计数:

df['freq'] = (
        df.groupby(['group', 'color'])['color'].transform('count') /
        df.groupby('group')['group'].transform('count')
)

   group  color      freq
0      A    red  0.400000
1      A    red  0.400000
2      A  green  0.400000
3      A   blue  0.200000
4      A  green  0.400000
5      B    red  0.750000
6      B    red  0.750000
7      B    red  0.750000
8      B  green  0.250000
9      C   blue  0.333333
10     C  green  0.333333
11     C    red  0.333333