3

I have the following dataframe df

           Datum    HH  DayPrecipitation
9377    2016-01-26  18  3
9378    2016-01-26  19  4
9379    2016-01-26  20  11
9380    2016-01-26  21  23
9381    2016-01-26  22  12

Which I converted to wide format using 

df.pivot_table(index = 'Datum', columns='HH' ,values = 'DayPrecipitation')

This leaves me with a double column

     HH     18  19  20  21  22
  Datum                 
2016-01-26  3   4   11  23  12

I want to make the column look like this and rename the columns:

   Datum  col1 col2  col3 col4 col5 col6            
2016-01-26  1    3    4    11   23   12

However when I use reset_index it just adds another index column and does not remove the multi_index. Does anyone know how to achieve said table? Help would be much appreciated!

borisvanax
  • 670
  • 1
  • 6
  • 15

2 Answers2

2

You can remove [] in ['DayPrecipitation'] for avoid MultiIndex in columns, then set new columns names by DataFrame.set_axis and last convert index to column by DataFrame.reset_index:

L = [f'col{x+1}' for x in range(df['HH'].nunique())]
df1 = (df.pivot_table(index = 'Datum', columns='HH' ,values = 'DayPrecipitation')
         .rename_axis(None,axis=1)
         .set_axis(L, inplace=False, axis=1)
         .reset_index())
print (df1)
        Datum  col1  col2  col3  col4  col5
0  2016-01-26     3     4    11    23    12
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
0

try using 

df.T.reset_index(drop=True).T
David
  • 871
  • 1
  • 5
  • 13