Reordering of pivot table and removing multi-indexing

Question

Dataframe

    TYPE  WEEK  VALUE1  VALUE2
0  Type1     1       1       1
1  Type2     2       2       2
2  Type3     3       3       3
3  Type4     4       4       4

import pandas as pd
import numpy as np

df = {'TYPE' : pd.Series(['Type1','Type2','Type3','Type4']),
    'WEEK' : pd.Series([1, 2, 3, 4]),
    'VALUE1' : pd.Series([1, 2, 3, 4]),
    'VALUE2' : pd.Series([1, 2, 3, 4])
}
df = pd.DataFrame(df)
df = pd.pivot_table(df,index="TYPE",columns="WEEK", values=['VALUE1','VALUE2']).reset_index()
df2 = df.swaplevel(0,1,axis=1).reset_index()

Output

WEEK index             1      2      3      4      1      2      3      4
             TYPE VALUE1 VALUE1 VALUE1 VALUE1 VALUE2 VALUE2 VALUE2 VALUE2
0        0  Type1    1.0    NaN    NaN    NaN    1.0    NaN    NaN    NaN
1        1  Type2    NaN    2.0    NaN    NaN    NaN    2.0    NaN    NaN
2        2  Type3    NaN    NaN    3.0    NaN    NaN    NaN    3.0    NaN
3        3  Type4    NaN    NaN    NaN    4.0    NaN    NaN    NaN    4.0

Expected structure output

WEEK  TYPE  | VALUE11  VALUE21 | VALUE12  VALUE22 | VALUE13  VALUE23 | VALUE14  VALUE24  
0     Type1 |                  |                  |                  |
1     Type2 |                  |                  |                  | 
2     Type3 |                  |                  |                  |
3     Type4 |                  |                  |                  |

Approaches in thought:

Reorder the structure. (I have tried swaplevel() as above but cannot attain the expected output)
Join the columns name eg. "Value11" by "Value1" + "1" I have looked through several examples from the internet but cannot come up with anything.

is value column a concatenation fo week value and type – Golden Lion Jan 17 '22 at 15:31 — Golden Lion, Jan 17 '22 at 15:31

Corralien · Accepted Answer · 2022-01-17T15:38:35.187

Try:

# Pivot without resetting index
df = pd.pivot_table(df,index="TYPE",columns="WEEK", values=['VALUE1','VALUE2']).sort_index(level=1, axis=1)

# Rename columns
df.columns = [f"{l1}{l2}" for l1, l2 in df.columns.tolist()]

Output:

>>> df.reset_index()
       VALUE11  VALUE21  VALUE12  VALUE22  VALUE13  VALUE23  VALUE14  VALUE24
TYPE                                                                         
Type1      1.0      1.0      NaN      NaN      NaN      NaN      NaN      NaN
Type2      NaN      NaN      2.0      2.0      NaN      NaN      NaN      NaN
Type3      NaN      NaN      NaN      NaN      3.0      3.0      NaN      NaN
Type4      NaN      NaN      NaN      NaN      NaN      NaN      4.0      4.0

Thanks. My answer is now fixed. – Corralien Jan 17 '22 at 15:43 — Corralien, Jan 17 '22 at 15:43

score 2 · Answer 2 · answered Jan 17 '22 at 15:34

IIUC, you can use:

df2 = (df.pivot_table(index="TYPE",columns="WEEK", values=['VALUE1','VALUE2'])
         .sort_index(level=1, axis=1)
      )
df2.columns = df2.columns.map(lambda x: x[0]+str(x[1]))
df2.reset_index()

output:

    TYPE  VALUE11  VALUE21  VALUE12  VALUE22  VALUE13  VALUE23  VALUE14  VALUE24
0  Type1      1.0      1.0      NaN      NaN      NaN      NaN      NaN      NaN
1  Type2      NaN      NaN      2.0      2.0      NaN      NaN      NaN      NaN
2  Type3      NaN      NaN      NaN      NaN      3.0      3.0      NaN      NaN
3  Type4      NaN      NaN      NaN      NaN      NaN      NaN      4.0      4.0

Reordering of pivot table and removing multi-indexing

2 Answers2