CODEWITHSUNDEEP
pythonpandasdataframemelt
Matteo
Matteo

Reputation: 165

python pandas dataframe Melt multiindex multi-levels

I have a DF with the following structure:

                    | Level           | Rate      |
Indicator           | AAA | BBB | CCC | XXX | YYY |
location   variable |
One        2017       0.69  0.22  0.71  0.02  0.98
           2018       0.31  0.15  0.78  0.03  0.96
           2019       0.55  0.19  0.82  0.04  0.83
Two        2017       0.31  0.33  0.93  0.11  0.21
           2018       0.24  0.35  0.01  0.12  0.14
           2019       0.16  0.25  0.12  0.14  0.17
Three      2017       0.58  0.11  0.55  0.21  0.27
           2018       0.75  0.10  0.68  0.22  0.25
           2019       0.42  0.08  0.71  0.23  0.41

I need to get a DF the following structure (with only one level):


location | variable | Indicator | Level | Rate |
------------------------------------------------
One      | 2017     | AAA       | 0.69  | NaN  |
...
Three    | 2019     | YYY       | NaN   | 0.41 |

I've made several attempts like this below but they don't work:

df.melt(col_level=0, id_vars = ['Location','Indicator','variable'] , value_vars = ['Level', 'Rate'])

Any help would be highly appreciated

Upvotes: 1

Views: 147

Answers (1)

jezrael
jezrael

Reputation: 862591

Use DataFrame.stack with DataFrame.rename_axis and DataFrame.reset_index:

df = df.stack().rename_axis(('location','variable','indicator')).reset_index()
print (df.head(10))
  location  variable indicator  Level  Rate
0      One      2017       AAA   0.69   NaN
1      One      2017       BBB   0.22   NaN
2      One      2017       CCC   0.71   NaN
3      One      2017       XXX    NaN  0.02
4      One      2017       YYY    NaN  0.98
5      One      2018       AAA   0.31   NaN
6      One      2018       BBB   0.15   NaN
7      One      2018       CCC   0.78   NaN
8      One      2018       XXX    NaN  0.03
9      One      2018       YYY    NaN  0.96

Upvotes: 1

Related Questions