I have this table with a Multiindex at columns, it is read from an Excel
Unnamed: 0_level_0 Unnamed: 1_level_0 Unnamed: 2_level_0 2017 2018
Person Material Country DEMAND HITS MISSES FILLRATE DEMAND HITS MISSES FILLRATE
0 Person1 Material1 Spain 0 0 0 0 0 0 0 0
1 Person1 Material1 France 0 0 0 0 0 0 0 0
2 Person1 Material1 India 0 0 0 0 5 5 0 1
3 Person1 Material1 China 0 0 0 0 0 0 0 0
4 Person2 Material2 Spain 0 0 0 0 0 0 0 0
5 Person2 Material2 France 0 0 0 0 0 0 0 0
6 Person2 Material2 India 0 0 0 0 5 5 0 1
7 Person2 Material2 China 0 0 0 0 0 0 0 0
I have replicated the structure below, in case it helps for the possible solutions
# Column Multi-Index
col_idx_arr = list(zip(['', '', '', '2017', '2017', '2017', '2017', '2018', '2018', '2018', '2018'],
['Person', 'Material', 'Country', 'DEMAND', 'HITS', 'MISSES', 'FILLRATE', 'DEMAND', 'HITS', 'MISSES', 'FILLRATE']))
col_idx = pd.MultiIndex.from_tuples(col_idx_arr)
# Create the DataFrame
df = pd.DataFrame('-', index=range(10), columns=col_idx)
And I am trying to transform it to this, "partially" stacking some columns
Date Person Material Country DEMAND HITS MISSES FILLRATE
0 2017.0 Person1 Material1 Spain 0 0 0 0
1 NaN Person1 Material1 France 0 0 0 0
2 NaN Person1 Material1 India 0 0 0 0
3 NaN Person1 Material1 China 0 0 0 0
4 NaN Person2 Material2 Spain 0 0 0 0
5 NaN Person2 Material2 France 0 0 0 0
6 NaN Person2 Material2 India 0 0 0 0
7 NaN Person2 Material2 China 0 0 0 0
8 2018.0 Person1 Material1 Spain 0 0 0 0
9 NaN Person1 Material1 France 0 0 0 0
10 NaN Person1 Material1 India 5 5 0 1
11 NaN Person1 Material1 China 0 0 0 0
12 NaN Person2 Material2 Spain 0 0 0 0
13 NaN Person2 Material2 France 0 0 0 0
14 NaN Person2 Material2 India 5 5 0 1
15 NaN Person2 Material2 China 0 0 0 0
IIUC, this may work
(df.set_index(list(df.columns[:3]))
.stack(level=0)
.reset_index()
.rename(columns=lambda x: x[1] if x[0]=='' else x)
.sort_values('level_3')
)
Output:
Person Material Country level_3 DEMAND FILLRATE HITS MISSES
0 Person1 Material1 Spain 2017 0 0 0 0
2 Person1 Material1 France 2017 0 0 0 0
4 Person1 Material1 India 2017 0 0 0 0
6 Person1 Material1 China 2017 0 0 0 0
8 Person2 Material2 Spain 2017 0 0 0 0
10 Person2 Material2 France 2017 0 0 0 0
12 Person2 Material2 India 2017 0 0 0 0
14 Person2 Material2 China 2017 0 0 0 0
1 Person1 Material1 Spain 2018 0 0 0 0
3 Person1 Material1 France 2018 0 0 0 0
5 Person1 Material1 India 2018 5 1 5 0
7 Person1 Material1 China 2018 0 0 0 0
9 Person2 Material2 Spain 2018 0 0 0 0
11 Person2 Material2 France 2018 0 0 0 0
13 Person2 Material2 India 2018 5 1 5 0
15 Person2 Material2 China 2018 0 0 0 0