CODEWITHSUNDEEP
pandasdataframepivot-tablemultiple-columnsmulti-index
piyo_lune
piyo_lune

Reputation: 37

how to add columns on pandas pivot table( multi-column)

Please tell me how to add columns? This DataFrame to pivot.

|date      |country|type|qty|
|----------|-------|----|---|
|2021/03/01|jp     |A   |10 |
|2021/03/01|en     |C   |20 |
|2021/03/01|jp     |C   |15 |
|2021/03/02|jp     |A   |10 |
|2021/03/02|en     |A   |20 |
|2021/03/02|en     |C   |15 |

(to pivot)

|     |2021/03/01|2021/03/02|
|-----|----------|----------|
|     |jp   |en  |jp   |en  |
|-----|----------|----------|
| A   |10   | 0  |50   |30  |
| C   |15   | 15 |0    |75  |

I would like to add "rate column"

|     |2021/03/01           |2021/03/02            |
|-----|---------------------|----------------------|
|     |    jp    |    en    |    jp    |    en     |
|-----|---------------------|----------------------|
|     |cnt | rate|cnt  |rate|cnt | rate|cnt  |rate |
|-----|----------|----------|----------|-----------|
| A   |10  | 0.4 | 0   | 0  |50  | 1   | 30  | 0.26|
| C   |15  | 0.6 | 15  | 1  |0   | 0   | 85  | 0.74|

Upvotes: 1

Views: 95

Answers (1)

jezrael
jezrael

Reputation: 863281

You can use concat with keys parameter with divide values by sums, then add DataFrame.reorder_levels and sort MultiIndex:

#change to your function if necessary
df1 = df.pivot_table(index='type', columns=['date','country'], values='qty', fill_value=0)
print (df1)
date    2021/03/01     2021/03/02    
country         en  jp         en  jp
type                                 
A                0  10         20  10
C               20  15         15   0

df = (pd.concat([df1, df1.div(df1.sum())], axis=1, keys=('cnt','rate'))
        .reorder_levels([1,2,0], axis=1)
        .sort_index(axis=1))
print (df)
date    2021/03/01               2021/03/02                   
country         en       jp              en            jp     
               cnt rate cnt rate        cnt      rate cnt rate
type                                                          
A                0  0.0  10  0.4         20  0.571429  10  1.0
C               20  1.0  15  0.6         15  0.428571   0  0.0

Upvotes: 3

Related Questions