Transposing Specific Data in CSV

Question

I have a csv file which looks like this:

Month     Day   Year  Tmax
   4       1    1912    56
   4       2    1912    56
   4       3    1912    74
   4       4    1912    82
   4       5    1912    79
   4       1    1913    73
   4       2    1913    60
   4       3    1913    67
   4       4    1913    81
   4       5    1913    77

and I want it to look like this:

Year  Month   1     2     3      4     5 
 1912   4     56     56    74     82    79
 1913   4     73     60    67     81    77

So each Day is now a column header and Tmax are displayed as columns instead of rows. I have played around with unstacking and transposing but can't seem to quite get it. Any help would be appreciated.

Answer 1

You could use pd.pivot_table() with index as ['Year', 'Month'] and columns as 'Day' on 'Tmax' values.

In [10]: pd.pivot_table(df, values='Tmax',
                        index=['Year', 'Month'],
                        columns='Day')
Out[10]:
Day          1   2   3   4   5
Year Month
1912 4      56  56  74  82  79
1913 4      73  60  67  81  77