Long/wide data to wide/long

Question

I have a data frame that look as follow:

import pandas as pd
d = {'decil': ['1. decil','1. decil','2. decil','2. decil','3. decil','3. decil'],
    'kommune': ['AA','BB','AA','BB','AA','BB'],'2010':[44,25,242,423,845,962],
    '2011':[64,26,239,620,862,862]}    
df = pd.DataFrame(data=d)

Printing

decil      kommune  2010  2011
1. decil   AA       44    64
1. decil   BB       25    26
2. decil   AA      242   239
2. decil   BB      423   620
3. decil   AA      845   862
3. decil   BB      962   862

My desired output is something like this

 kommune  year  1. decil  2. decil  3. decil
 AA       2010        44       242       845
 AA       2011        64       239       862
 BB       2010        25       423       962
 BB       2011        25       620       862

That is, I'm searching for a way to change the 'decil' column from long to wide format while at the same time changing the year columns from wide to long format. I have tried pd.pivot_table, loops and unstack without any luck. Is there any smart way around this? In advance, thanks for the help.

Hornbeck

Answer 1

Use set_index with stack and unstack:

df = (df.set_index(['decil','kommune'])
        .stack()
        .unstack(0)
        .reset_index()
        .rename_axis(None, axis=1))

print (df)
  kommune level_1  1. decil  2. decil  3. decil
0      AA    2010        44       242       845
1      AA    2011        64       239       862
2      BB    2010        25       423       962
3      BB    2011        26       620       862

Greenockite

Exactly what I was searching for! Thanks a lot. – Hornbeck

If this works then mark it as a answer so other can see its answered! – Heavyhearted

I will. But it seems that I only can accept it as an answer 10 minutes after it was written. – Hornbeck

@AsbjørnKampJohannesen, thnx :-) – Heavyhearted