Pivot Table with multi column from Groupby Python

Question

After the groupby operation over (id, gender and job) I have this:

id        geneder      job           salary
22         male       engineer         100
                      doctor           120
                       ...
           female     engineer          90
                       doctor          100
                       ...

23         male       engineer         200
                      doctor           150
                       ...
           female     engineer          80
                       doctor          100

From this I want to create the something like that:

id       m_engineer    m_doctor ... f_engineer    f_doctor
22          100          120           90           100
23          200          150           80           100

I tried with pivot tables but I continue to have errors. Any ideas?

Answer 1

You need to create a new column that you can pivot, something like

(grouped_df
 .reset_index()
 .assign(gender_job = lambda df: df['gender'].str[0] + '_' + df['job'])
 .pivot('id', 'gender_job', 'salary'))