switching rows and columns in dataframe

Question

I have a dataframe that looks like the following:

import pandas as pd
datelisttemp = pd.date_range('1/1/2014', periods=3, freq='D')
s = list(datelisttemp)*3
s.sort()
df = pd.DataFrame({'day':s,'stats':['mean','var','std','mean','var','std','mean','var','std'], 's1':[21 ,25 ,27 ,42 ,4 ,74 ,63 ,67, 6],'s2':[34 ,32 ,3, 53 ,75 ,5, 32, 75, 7], 's3':[8 ,82 ,8, 12 ,43 ,12, 99, 99, 95]})

I would like to get a dataframe like this:

df = pd.DataFrame({'sensors':  ['s1','s2','s3','s1','s2','s3','s1','s2','s3'],'day':s,'mean':[21,34,8,42,53,12,  63,32,99],'var':[25,32,82,4,75,43,67,75,99],'std':[27,3,8,74,5,12,74,5,12]})

Basically, I need the dataframe to have sensors in rows and stats in columns. Can please someone help me?

Answer 1

You can use pivot_table with stack. Last reset_index, rename columns and remove column names by rename_axis (new in pandas 0.18.0):

print (df.pivot_table(index='day', columns='stats')
         .stack(0)
         .reset_index()
         .rename(columns={'level_1':'sensors'})
         .rename_axis(None, axis=1))

         day sensors  mean  std  var
0 2014-01-01      s1    21   27   25
1 2014-01-01      s2    34    3   32
2 2014-01-01      s3     8    8   82
3 2014-01-02      s1    42   74    4
4 2014-01-02      s2    53    5   75
5 2014-01-02      s3    12   12   43
6 2014-01-03      s1    63    6   67
7 2014-01-03      s2    32    7   75
8 2014-01-03      s3    99   95   99