CODEWITHSUNDEEP
pythonpandasdataframepivot-tablepandas-groupby
CHRD
CHRD

Reputation: 1957

Pandas combine multiple pivot tables

Suppose I have df1, piv1, and, piv2 below:

df1 = pd.DataFrame({'R': [1, 2], 'S': ['s1', 's2'], 'G1': ['g1a', 'g1b'], 'G2': ['g2a', 'g2b']})

df1
    R   S   G1  G2
0   1   s1  g1a g2a
1   2   s2  g1b g2b

piv1 = df1.pivot_table(index=(['S']), columns=(['G1']), aggfunc=({'R': 'mean'}))

piv1
        R
G1  g1a g1b
S       
s1  1.0 NaN
s2  NaN 2.0

piv2 = df1.pivot_table(index=(['S']), columns=(['G2']), aggfunc=({'R': 'mean'}))

piv2
        R
G2  g2a g2b
S       
s1  1.0 NaN
s2  NaN 2.0

Instead of piv1 and piv2, I am trying to make piv3 which would look like below. Any ideas? Ideally I'd like to create piv3 directly from df1 (i.e. not needing to create piv1 and piv2 and then combine them).

piv3
S   g1a  g1b  g2a  g2b
s1  1.0  NaN  1.0  NaN
s2  NaN  2.0  NaN  2.0

Upvotes: 2

Views: 443

Answers (3)

anky
anky

Reputation: 75100

df.melt and pivot_table:

df1.melt(['S','R']).pivot_table(index='S',columns='value',values='R').rename_axis(None)

value  g1a  g1b  g2a  g2b
s1     1.0  NaN  1.0  NaN
s2     NaN  2.0  NaN  2.0

Upvotes: 4

BENY
BENY

Reputation: 323316

IIUC

s=df1.melt(['R','S']).groupby(['S','value']).R.mean().unstack()
Out[63]: 
value  g1a  g1b  g2a  g2b
S                        
s1     1.0  NaN  1.0  NaN
s2     NaN  2.0  NaN  2.0

Upvotes: 4

Jim Eisenberg
Jim Eisenberg

Reputation: 1500

Probably not the most elegant answer:

piv3 = piv1.copy()
piv3[piv2.columns] = piv2

Out[17]: 
      R               
G1  g1a  g1b  g2a  g2b
S                     
s1  1.0  NaN  1.0  NaN
s2  NaN  2.0  NaN  2.0

Upvotes: 0

Related Questions