0

I create a pivot table with a MultiIndex.

import pandas as pd

treatcriteria_daily_data = pd.read_csv(r"treatcriteria_evolution.csv", sep=';')

variations_data = treatcriteria_daily_data.groupby(['startdate_dayweek','startdate_weekyear'],as_index = False).sum().pivot('startdate_dayweek','startdate_weekyear').fillna(0)

print(variations_data)
print(variations_data.columns)

The result:

                     mc_cpu_hours                 ...                              
startdate_weekyear             27             28  ...             30             31
startdate_dayweek                                 ...                              
1                   527644.000731  349896.850976  ...  176007.786269  177517.591864
2                   468053.338183  481313.693908  ...  364379.872622       0.000000
3                   517548.838022  372385.568095  ...  366155.953075       0.000000
4                   573669.325129  378069.713821  ...  375948.240935       0.000000
5                   515710.534260  385937.231788  ...  366151.336263       0.000000
6                   511711.421986  385856.666340  ...  395790.387672       0.000000
7                   841073.028107  842468.209151  ...  700936.336812  686023.780120

[7 rows x 5 columns]
MultiIndex(levels=[['mc_cpu_hours'], [27, 28, 29, 30, 31]],
           codes=[[0, 0, 0, 0, 0], [0, 1, 2, 3, 4]],
           names=[None, 'startdate_weekyear'])

How can I proceed to obtain this table?

                               27             28  ...             30             31
1                   527644.000731  349896.850976  ...  176007.786269  177517.591864
2                   468053.338183  481313.693908  ...  364379.872622       0.000000
3                   517548.838022  372385.568095  ...  366155.953075       0.000000
4                   573669.325129  378069.713821  ...  375948.240935       0.000000
5                   515710.534260  385937.231788  ...  366151.336263       0.000000
6                   511711.421986  385856.666340  ...  395790.387672       0.000000
7                   841073.028107  842468.209151  ...  700936.336812  686023.780120

I tried a map/join ( concise way of flattening multiindex columns ) but I had this error message:

TypeError: sequence item 1: expected str instance, int found

Thanks to you.

Rinnosuke
  • 123
  • 8

1 Answers1

1

can you try to convert the startdate_weekyear into string before your pivot and retry the map solution ? I hope it works for you

R10
  • 74
  • 2
  • Thanks, I tried this solution earlier but I didn't know how to drop 'mc_cpu_hours' level, I did "treatcriteria_daily_data =treatcriteria_daily_data.droplevel(0,1)" and it works now. – Rinnosuke Aug 11 '21 at 13:52