python - 使用 pandas 创建汇总表

Question

如何使用 pandas 从我的以下数据中获取汇总表:

ID  Condition   Confirmed
D0119   Bad Yes
D0119   Good    No
D0117   Bad Yes
D0110   Bad Undefined
D1011   Bad Yes
D1011   Good    Yes
D1001   Bad Yes
D1001   Bad Yes

要求的输出:

ID  Condition   Confirmed   %Bad
D0119   Bad,Good    Yes, No 50
D0117   Bad,Yes 100
D0110   Bad,Undefined   0
D1011   Bad,Good    Yes, Yes
D1001   Bad,Bad Yes, Yes    100

有人可以帮忙吗？谢谢

Answer 1

你可以这样做:

In [123]: (df.assign(Bad=df.Condition=='Bad')
     ...:    .groupby('ID')
     ...:    .agg({'Condition':pd.Series.tolist,
     ...:          'Confirmed':pd.Series.tolist,
     ...:          'Bad':'mean'})
     ...: )
     ...:
Out[123]:
       Bad    Condition    Confirmed
ID
D0110  1.0        [Bad]  [Undefined]
D0117  1.0        [Bad]        [Yes]
D0119  0.5  [Bad, Good]    [Yes, No]
D1001  1.0   [Bad, Bad]   [Yes, Yes]
D1011  0.5  [Bad, Good]   [Yes, Yes]

垂直变体:

In [113]: df
Out[113]:
      ID Condition  Confirmed
0  D0119       Bad        Yes
1  D0119      Good         No
2  D0117       Bad        Yes
3  D0110       Bad  Undefined
4  D1011       Bad        Yes
5  D1011      Good        Yes
6  D1001       Bad        Yes
7  D1001       Bad        Yes

In [114]: g = df.assign(Bad=df.Condition=='Bad').groupby('ID')

In [115]: df['Bad'] = df['ID'].map((g.sum().div(g.size(), 0)*100).Bad)

In [116]: df
Out[116]:
      ID Condition  Confirmed    Bad
0  D0119       Bad        Yes   50.0
1  D0119      Good         No   50.0
2  D0117       Bad        Yes  100.0
3  D0110       Bad  Undefined  100.0
4  D1011       Bad        Yes   50.0
5  D1011      Good        Yes   50.0
6  D1001       Bad        Yes  100.0
7  D1001       Bad        Yes  100.0