DougKruger DougKruger - 3 months ago 16
Python Question

Pandas dataframe pivot table and grouping

I have a DataFrame which I made into a pivot table, but now I want to order the pivot table so that common values based on a particular column are aligned beside each other. For e.g. order DataFrame so that all common countries align to same row:

data = {'dt': ['2016-08-22', '2016-08-21', '2016-08-22', '2016-08-21', '2016-08-21'],
'country':['uk', 'usa', 'fr','fr','uk'],
'number': [10, 21, 20, 10,12]
}

df = pd.DataFrame(data)
print df

country dt number
0 uk 2016-08-22 10
1 usa 2016-08-21 21
2 fr 2016-08-22 20
3 fr 2016-08-21 10
4 uk 2016-08-21 12


#pivot table by dt:

df['idx'] = df.groupby('dt')['dt'].cumcount()
df_pivot = df.set_index(['idx','dt']).stack().unstack([1,2])
print df_pivot
dt 2016-08-22 2016-08-21
country number country number
idx
0 uk 10 usa 21
1 fr 20 fr 10
2 NaN NaN uk 12


#what I really want:

dt 2016-08-22 2016-08-21
country number country number

0 uk 10 uk 12
1 fr 20 fr 10
2 NaN NaN usa 21


or even better:

2016-08-22 2016-08-21
country number number

0 uk 10 12
1 fr 20 10
2 usa NaN 21


i.e.
uk
values from both
2016-08-22
and
2016-08-21
are aligned on same row

Answer

You can use:

df_pivot = df.set_index(['dt','country']).stack().unstack([0,2]).reset_index()
print (df_pivot)
dt country 2016-08-22 2016-08-21
               number     number
0       fr       20.0       10.0
1       uk       10.0       12.0
2      usa        NaN       21.0  

#change first value of Multiindex from first to second level
cols  = [col for col in df_pivot.columns]
df_pivot.columns = pd.MultiIndex.from_tuples([('','country')] + cols[1:])
print (df_pivot)
          2016-08-22 2016-08-21
  country     number     number
0      fr       20.0       10.0
1      uk       10.0       12.0
2     usa        NaN       21.0

Another simplier solution is with pivot:

df_pivot = df.pivot(index='country', columns='dt', values='number')
print (df_pivot)
dt       2016-08-21  2016-08-22
country                        
fr             10.0        20.0
uk             12.0        10.0
usa            21.0         NaN