python - 如何在 pandas 中通过分组然后聚合创建自定义列

Question

我有以下格式的DataFrame。

| User          | CodeID        | Language |
| ------------- |---------------| -------- |
| foo           | 1             | C        |
| foo           | 2             | C        |
| foo           | 3             | CPP      |
| bar           | 4             | C        |
| bar           | 5             | CPP      |
| bar           | 6             | Java     |
| bar           | 7             | CPP      |

现在我想要的是计算每个用户每种语言的代码数量。也就是说，我想要一个以下格式的 DataFrame 。

| User | C  | CPP | Java | Total |
| ---- | -- | --- | ---- | ----- |
| foo  | 2  | 1   | 0    | 3     |
| bar  | 1  | 2   | 1    | 4     |

Point to be noted that the number of languages is dynamic. However, it is okay if someone can provide a solution with a fixed set of languages. Thanks in advance.

Answer 1

您可以使用交叉表并计算总计

In [223]: pd.crosstab(df.User, df.Language).assign(Total=lambda x: x.sum(axis=1))
Out[223]:
Language  C  CPP  Java  Total
User
bar       1    2     1      4
foo       2    1     0      3

或者

In [247]: df.pivot_table(index='User', columns='Language', values='CodeID', 
                         aggfunc=len).assign(Total=lambda x: x.sum(1))
Out[247]:
Language    C  CPP  Java  Total
User
bar       1.0  2.0   1.0    4.0
foo       2.0  1.0   NaN    3.0

或者

In [250]: df.groupby(['User', 'Language']).size().unstack(fill_value=0)
Out[250]:
Language  C  CPP  Java
User
bar       1    2     1
foo       2    1     0