Reshape DataFrame by pivoting multiple columns

Question

Hi how can I pivot a table like this

import pandas as pd

d = {'name' : ['A','A','B','B'],'year': ['2018','2019','2018','2019'],'col1':[1,4,7,10],'col2':[2,5,8,11],'col3':[3,6,9,12]}

pd.DataFrame(data=d)

name    year    col1    col2    col3
   A    2018       1       2       3
   A    2019       4       5       6
   B    2018       7       8       9
   B    2019      10      11      12

Into another like this:

name    cols    2018    2019
   A    col1    1       4
   A    col2    2       5
   A    col3    3       6
   B    col1    7       10
   B    col2    8       11
   B    col3    9       12

year will become columns and each column should iterate with name to form pairs that will be used as a composed primary key.

Answer 1

You can use melt and pivot_table:

(df.melt(['name','year'], var_name='cols')
   .pivot_table(index=['name', 'cols'], 
                columns='year', 
                values='value', 
                aggfunc='sum')
   .reset_index()
   .rename_axis(None, 1))

  name  cols  2018  2019
0    A  col1     1     4
1    A  col2     2     5
2    A  col3     3     6
3    B  col1     7    10
4    B  col2     8    11
5    B  col3     9    12

---

An alternative stack, unstack based solution:

(df.set_index(['name','year'])
   .stack()
   .unstack(1)
   .rename_axis(['name', 'cols'], 0)
   .rename_axis(None, 1)
   .reset_index())

  name  cols  2018  2019
0    A  col1     1     4
1    A  col2     2     5
2    A  col3     3     6
3    B  col1     7    10
4    B  col2     8    11
5    B  col3     9    12

Answer 2

Using wide_to_long

pd.wide_to_long(df,['col'],i=['name','year'],j='cols').col.unstack(level=1).reset_index()
Out[573]: 
year name  cols  2018  2019
0       A     1     1     4
1       A     2     2     5
2       A     3     3     6
3       B     1     7    10
4       B     2     8    11
5       B     3     9    12