How to convert a dataframe with MultiIndex indices and columns that has string values to a pivot table?

user_12276279 0
Viewed 34

I have a pandas dataframe df1 which looks as follows:

module  wind_on_share   wind_off_share  wind_total_share
variable    Wind-onshore power generation   Wind-offshore power generation  Wind power generation
model   scenario            
AIM/CGE 2.0 ADVANCE_2030_Price1.5C  high    NaN medium
SSP1-19 NaN NaN high
GCAM 4.2    SSP1-19 NaN NaN medium
IMAGE 3.0.1 SSP1-19 NaN NaN low
SSP1-26 NaN NaN low

The dataframe has MultiIndex indices containing model and scenario. Similarly, it has MultiIndex columns containing module and variable which looks like follow:

[(   'wind_on_share',  'Wind-onshore power generation'),
            (  'wind_off_share', 'Wind-offshore power generation'),
            ('wind_total_share',          'Wind power generation')]

I'd like to convert this dataframe into a pivot table such that the module or variable in the existing columns are added to the indices. And the values should be shown in a single column as values. It should looks something as in the screenshot of Excel below: enter image description here

I have tried different things with df1.pivot_table(index = "", values = "") But it has not worked out so far. And I am not sure which parameters should I pass to index and values. I also tried df1.unstack()and df1.reset_index(), but I am not again sure which parameters to pass as it has not worked out so far. What would be the appropriate way to create a pivot table in this case?

The data frame looks like following in dictionary format.

{('wind_on_share',
  'Wind-onshore power generation'): {('AIM/CGE 2.0',
   'ADVANCE_2030_Price1.5C'): 'high', ('AIM/CGE 2.0',
   'SSP1-19'): nan, ('GCAM 4.2', 'SSP1-19'): nan, ('IMAGE 3.0.1',
   'SSP1-19'): nan, ('IMAGE 3.0.1', 'SSP1-26'): nan, ('MESSAGE-GLOBIOM 1.0',
   'ADVANCE_2030_Price1.5C'): 'low', ('MESSAGE-GLOBIOM 1.0',
   'SSP1-19'): nan, },
 ('wind_off_share',
  'Wind-offshore power generation'): {('AIM/CGE 2.0',
   'ADVANCE_2030_Price1.5C'): nan, ('AIM/CGE 2.0',
   'SSP1-19'): nan, ('GCAM 4.2', 'SSP1-19'): nan, ('IMAGE 3.0.1',
   'SSP1-19'): nan, ('IMAGE 3.0.1', 'SSP1-26'): nan, ('MESSAGE-GLOBIOM 1.0',
   'ADVANCE_2030_Price1.5C'): 'low', ('MESSAGE-GLOBIOM 1.0',
   'SSP1-19'): nan},
 ('wind_total_share',
  'Wind power generation'): {('AIM/CGE 2.0',
   'ADVANCE_2030_Price1.5C'): 'medium', ('AIM/CGE 2.0',
   'SSP1-19'): 'high', ('GCAM 4.2', 'SSP1-19'): 'medium', ('IMAGE 3.0.1',
   'SSP1-19'): 'low', ('IMAGE 3.0.1',
   'SSP1-26'): 'low', ('MESSAGE-GLOBIOM 1.0', 'ADVANCE_2030_Price1.5C'): 'medium', ('MESSAGE-GLOBIOM 1.0',
   'SSP1-19'): 'low'}}
pandas pivot-table dataframe python
1 Answers

You can drop level -1 from column (since you don't want it in the resulting dataframe), then stack the dataframe with dropna=False to keep the NaN values for the indices, then finally call to_frame passing the column name as value to convert the stacked series to a dataframe.

>>> df.droplevel(-1,1).stack(dropna=False).to_frame('value')

OUTPUT:

                                                              value
AIM/CGE 2.0         ADVANCE_2030_Price1.5C wind_on_share       high
                                           wind_off_share       NaN
                                           wind_total_share  medium
                    SSP1-19                wind_on_share        NaN
                                           wind_off_share       NaN
                                           wind_total_share    high
GCAM 4.2            SSP1-19                wind_on_share        NaN
                                           wind_off_share       NaN
                                           wind_total_share  medium
IMAGE 3.0.1         SSP1-19                wind_on_share        NaN
                                           wind_off_share       NaN
                                           wind_total_share     low
                    SSP1-26                wind_on_share        NaN
                                           wind_off_share       NaN
                                           wind_total_share     low
MESSAGE-GLOBIOM 1.0 ADVANCE_2030_Price1.5C wind_on_share        low
                                           wind_off_share       low
                                           wind_total_share  medium
                    SSP1-19                wind_on_share        NaN
                                           wind_off_share       NaN
                                           wind_total_share     low
user_91363480
Related