Slick pivot and mapping in Python-CodePudding

I wish to create column headers from the type values. Then map the appropriate en value under the newly formed headers.

Data

country start       type    en
US      8/1/2022    bb      10  
Japan   8/1/2022    aa      25  
Japan   9/1/2022    cc      1

Desired

country start       aa  bb  cc
US      8/1/2022    0   10  0
Japan   8/1/2022    25  0   0
Japan   9/1/2022    1   0   0

Doing

# pip install pyjanitor
import pandas as pd
import janitor

df.pivot(
    index = 'type', 
    names_to = ('start', '.value'), 
    names_pattern = r"(Q\d )_?(. )",
    sort_by_appearance = True)

Any suggestion is appreciated

CodePudding user response：

Try:

df.pivot(index=['country', 'start'], columns='type', values='en').fillna(0, downcast='int').reset_index()

#Thanks, @Sammywemmy.

Output:

type country     start  aa  bb  cc
0      Japan  8/1/2022  25   0   0
1      Japan  9/1/2022   0   0   1
2         US  8/1/2022   0  10   0

Or,

df.groupby(['country', 'start', 'type'])['en'].sum().unstack().fillna(0).astype(int).reset_index()