0

I have a df such as...

     log_ratio   city   type   year
0   2.892095   Detroit  Pos_A  2016
1   2.176814   Detroit  Pos_B  2016
2   3.218273   Detroit  Pos_A  2017
3   2.922206   Detroit  Pos_B  2017
4   2.776701  Columbus  Pos_A  2016
5   2.615424  Columbus  Pos_B  2016
6   2.781899  Columbus  Pos_A  2017
7   2.499343  Columbus  Pos_B  2017
...

I want to reshape this table so that the city is the index and the year and type become a hierarchical columns and the log_ratio are the values, such as...

 mr             2016                2017

           Pos_A    Pos_B      Pos_A   Pos_B 

Detroit  2.892095 2.176814   3.218273 2.922206
Columbus 2.776701 2.615424   2.781899 2.499343
...

When I run the command...

df3 = df2.pivot(index='mr',columns=['year','type'],values='log_ratio')

I get an error...

 'Cannot find level year'.

Any help would be much appreciated. Thanks!

nick
  • 1,090
  • 1
  • 11
  • 24
jtam
  • 814
  • 1
  • 8
  • 24
  • 1
    You need to use `pd.pivot_table` for this one: `pd.pivot_table(df2, index='city', columns=['year','type'], values='log_ratio')` – ALollz Aug 15 '18 at 18:45
  • Possible duplicate of [How to pivot a dataframe](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe). See Question 7 of that link. – ALollz Aug 15 '18 at 18:45

1 Answers1

1

I think all you need is pivot_table instead of pivot:

df.pivot_table(index='city', columns=['year','type'], values='log_ratio')
year          2016                2017          
type         Pos_A     Pos_B     Pos_A     Pos_B
city                                            
Columbus  2.776701  2.615424  2.781899  2.499343
Detroit   2.892095  2.176814  3.218273  2.922206

For more details, check out this awesome canonical answer: How to pivot a dataframe

Peter Leimbigler
  • 10,775
  • 1
  • 23
  • 37