Thursday, 5 September 2013

how to perform calculations across specific rows and columns of a crosstabulation in pandas?

how to perform calculations across specific rows and columns of a
crosstabulation in pandas?

import pandas as pd
import numpy as np
c1 = np.repeat(['a','b'], [50, 50], axis=0)
c2 = list('xy'*50)
c3 = np.repeat(['G1','G2'], [50, 50], axis=0)
np.random.shuffle(c3)
c4=np.repeat([1,2], [50,50],axis=0)
np.random.shuffle(c4)
val = np.random.rand(100)
df = pd.DataFrame({'c1':c1, 'c2':c2, 'c3':c3, 'c4':c4, 'val':val})
table = pd.crosstab([df.c1,df.c2],[df.c3,df.c4])
c3 G1 G2
c4 1 2 1 2
c1 c2
a x 3 11 5 6
y 9 5 7 4
b x 5 7 11 2
y 5 5 5 10
for each group (G1, G2), is it possible to compute ax - bx and ay - by
only for c4==2 and have the result in a data frame?:
x G1 3
y G1 4
x G2 4
y G2 -6

No comments:

Post a Comment