1

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

since the original data were like this.

enter image description here

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

enter image description here

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.

enter image description here

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.

bido_Boy
  • 35
  • 6
  • Your call ('OcP', 'CoC', 'tolerance') means level1, level2, level3. If you do df["OcP"] = 0 it will create a column with OcP on level1 of MultiIndex. – Borut Flis Nov 10 '21 at 08:12
  • Can you specify more about where you want these values to be in the index hierarchy? – Borut Flis Nov 10 '21 at 08:12
  • yes i mean the levels, but i want to insert the new columns to level3, – bido_Boy Nov 10 '21 at 08:14
  • I want to add them at level3 so they are similar to (B, ENS, or G) – bido_Boy Nov 10 '21 at 08:16
  • Ok, but you need need to specify the higher levels as well. – Borut Flis Nov 10 '21 at 08:20
  • I did specify the higher two levels by that table[('OcP', 'CoC', 'tolerance')] = 0 since the 'OcP' and 'CoC' already exist and the new part is 'tolerance', instead of adding it he created a new two higher levels with same names and added the 'tolerance' column to them at the end of the table like shown at the last image – bido_Boy Nov 10 '21 at 08:30
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/239067/discussion-between-bido-boy-and-borut-flis). – bido_Boy Nov 10 '21 at 08:35
  • Than I think you script already works. True it looks like it is added at the end, but if you call "Ocp" you will see "tolerance" is added to the hierarchy. – Borut Flis Nov 10 '21 at 08:35

2 Answers2

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]]
Borut Flis
  • 15,715
  • 30
  • 92
  • 119
0

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.

bido_Boy
  • 35
  • 6