CODEWITHSUNDEEP
pythonpandasdataframestackmulti-index
BEAst
BEAst

Reputation: 219

Stack the header, not the two columns

Tried to stack my table, however it works well if I didn't have the "country column". How do I remain first to columns "unstacked, and just stack the rows of the date. The Picture below demonstrates what I want.

enter image description here

The left Picture is how the table looks like, the right is the format I want to. So the question is how do I stack after rows, usually you stack after column levels.

All best,

Upvotes: 2

Views: 45

Answers (1)

jezrael
jezrael

Reputation: 862661

You need:

cols = ['GEO','INDIC',1990,1991,1992]
df = pd.DataFrame({'GEO':['Austria']*3, 'INDIC':['dis','fin1','fin2'],
                   1990:[2,42,17],1991:[3,44,18],1992:[2,44,17]}, columns=cols)
print (df)
       GEO INDIC  1990  1991  1992
0  Austria   dis     2     3     2
1  Austria  fin1    42    44    44
2  Austria  fin2    17    18    17

1.

Create index by set_index of all columns for not reshape and then add stack, rename_axis and reset_index is for new column names:

df1 = df.set_index(['GEO','INDIC'])
        .stack()
        .rename_axis(['GEO','INDIC', 'year'])
        .reset_index(name='quantity')
print (df1)
       GEO INDIC  year  quantity
0  Austria   dis  1990         2
1  Austria   dis  1991         3
2  Austria   dis  1992         2
3  Austria  fin1  1990        42
4  Austria  fin1  1991        44
5  Austria  fin1  1992        44
6  Austria  fin2  1990        17
7  Austria  fin2  1991        18
8  Austria  fin2  1992        17

2.

Reshape by melt, there is different sorting of columns:

df1 = df.melt(id_vars=['GEO','INDIC'], var_name='year', value_name='quantity')
print (df1)
       GEO INDIC  year  quantity
0  Austria   dis  1990         2
1  Austria  fin1  1990        42
2  Austria  fin2  1990        17
3  Austria   dis  1991         3
4  Austria  fin1  1991        44
5  Austria  fin2  1991        18
6  Austria   dis  1992         2
7  Austria  fin1  1992        44
8  Austria  fin2  1992        17

Upvotes: 2

Related Questions