python - Python - groupby sum map to another dataframe

Question

My dataframe A:

id   groupA    groupB    groupC    groupD   groupE    ...
001    0         0         0         0        0
002    0         0         0         0        0
003    0         0         0         0        0
  ...

Dataframe B:

id     value     count
001    groupA      2
002    groupB      1
001    groupB      3
001    groupC      1
003    groupC      2
002    groupA      1


dfB.groupby(['id', 'value'])['count'].sum()

By running this groupby I get:

id  value 
1   groupA    2
    groupB    3
    groupC    1
2   groupA    1
    groupB    1
3   groupC    2
Name: count, dtype: int64

I was trying to transform this result to a dataframe and map it back to dataframe A, but it didn't work out.

My ideal dataframe Aoutput is:

id   groupA    groupB    groupC    groupD   groupE    ...
001    2         3         1         0        0
002    1         1         0         0        0
003    0         0         2         0        0
  ...

标签： pythonpandasgroup-bypandas-groupbyseries

Answer 1

用单线链做reindex

dfb.groupby(['id', 'value'])['count'].sum().\
     unstack(fill_value=0).\
       reindex(columns=dfa.columns,index=dfa.id,fill_value=0).\
        drop('id',1)
    groupA  groupB  groupC  groupD  groupE
id                                        
1        2       3       1       0       0
2        1       1       0       0       0
3        0       0       2       0       0