add columns to specific level pivot tables in pandas

Question

I am trying to achieve this multi-indexing form with a pandas pivot table.

since the original data were like this.

I used this code table = pd.pivot_table(df, index=str(df.columns[0]), columns =list(df.columns[1:4]), values='Value') to get this result

but now I need to add these three columns (Forcast, Tolerance, Baseline Forcast) to the most detailed level of the pivot table for each subproduct like adding them under the ECo, I tried this table[('OcP', 'CoC', 'tolerance')] = 0 it worked but added the column to the end of the pivot table like this.

so how can add them and make them fall under the same sub-category that is already existed not at the end of the pivot like shown above? Note: I know there are similar questions but they didn't solve my case.

Answer 1

table[('OcP', 'CoC', 'tolerance')] = 0

Sets 'OcP' as level1 'CoC' as level2 and 'tolerance' as level3.

You said you want them on level3 than you have to set them like this:

table[(level1, level2, "CoC")]

You need to specify the index up in the hierarchy as well.

The new column is than added to the end of the data-frame and it appears as if it is not part of the multi-index hierarchy, but it is, you can check this by calling table[(level1, level2)] you will see it is included.

If you want to display them ordered by the multi-index hierarchy you should sort the columns:

df.iloc[:,df.columns.sortlevel(level=[0,1])[1]]

Answer 2

I finally achieved the solution thanks to borut he lighted the way to it and this question , it was using table[('OcP', 'CoC', 'tolerance')] = 0 and then apply pivot.sort_index(axis=1) to the pivot.