Calculating the share of each code, by ID

user_11974225 0
Viewed 42

I have this data-frame:

ID  code   X  X_total
 A   456  40       40
 A   789   0       40
 B   123  75      100
 B   987  25      100
 C   789  13       91
 C   987   0       91
 C   123  35       91
 C   456  43       91

I want the calculate the share of each code (from [123, 465, 789, 987]), by dividing X by X_total, for each ID.

Expected result:

ID  share_123  share_456  share_789  share_987
 A       0.00       1.00       0.00       0.00
 B       0.75       0.00       0.00       0.25
 C       0.38       0.47       0.14       0.00
pandas numpy python
3 Answers

Let us do crosstab

s = pd.crosstab(df.ID, df.code, df.X ,aggfunc='sum', normalize='index').add_prefix("share_")
Out[70]: 
code       123       456       789   987
ID                                      
A     0.000000  1.000000  0.000000  0.00
B     0.750000  0.000000  0.000000  0.25
C     0.384615  0.472527  0.142857  0.00
user_79645270

Or with df.pivot with your logic:

df.assign(k=df['X'].div(df['X_total'])).pivot("ID","code","k").fillna(0)

code       123       456       789   987
ID                                      
A     0.000000  1.000000  0.000000  0.00
B     0.750000  0.000000  0.000000  0.25
C     0.384615  0.472527  0.142857  0.00

Adding formatting:

(df.assign(k=df['X'].div(df['X_total'])).pivot("ID","code","k").fillna(0)
   .add_prefix("share_").round(2).rename_axis(None,axis=1).reset_index())

  ID  share_123  share_456  share_789  share_987
0  A       0.00       1.00       0.00       0.00
1  B       0.75       0.00       0.00       0.25
2  C       0.38       0.47       0.14       0.00
user_98406370

Another approach with groupby + unstack

df['X'].div(df['X_total']).groupby([df['ID'], df['code']]).sum().unstack(fill_value=0)

code       123       456       789   987
ID                                      
A     0.000000  1.000000  0.000000  0.00
B     0.750000  0.000000  0.000000  0.25
C     0.384615  0.472527  0.142857  0.00
user_118842370
Related