Can I combine this tables via panas.crosstab?-CodePudding

I have three data frames (as result from .mean()) like this:

I want to concatenate them to three columns like this

     all      X      Y
A  533.9  558.0  509.8
B  691.9  613.2  770.6
C  611.5  618.4  604.6
D  557.8  591.0  524.6

My MWE below does work. But I wonder if I can use .crosstab() or another fancy and more easy pandas function for that.

The initial data frame:

  group    A    B    C    D
0     X  844  908  310  477
1     X  757  504  729  865
2     X  420  281  898  260
3     X  258  755  683  805
4     X  511  618  472  548
5     Y  404  250  100   14
6     Y  783  909  434  719
7     Y  303  982  610  398
8     Y  476  810  913  824
9     Y  583  902  966  668

And this is the MWE using dict and pandas.concat() to solve the problem.

#!/usr/bin/env python3
import random as rd
import pandas as pd
import statistics
rd.seed(0)

df = pd.DataFrame({
    'group': ['X'] * 5   ['Y'] * 5,
    'A': rd.choices(range(1000), k=10),
    'B': rd.choices(range(1000), k=10),
    'C': rd.choices(range(1000), k=10),
    'D': rd.choices(range(1000), k=10),
})

cols = list('ABCD')

result = {
    'all': df.loc[:, cols].mean(),
    'X': df.loc[df.group.eq('X'), cols].mean(),
    'Y': df.loc[df.group.eq('Y'), cols].mean()
}

tab = pd.concat(result, axis=1)

print(tab)

CodePudding user response：

You can do with melt then pivot_table

out = df.melt('group').pivot_table(
    index = 'variable',
    columns = 'group',
    values = 'value',
    aggfunc = 'mean',
    margins = True).drop(['All'])

Out[207]: 
group         X      Y    All
variable                     
A         558.0  509.8  533.9
B         613.2  770.6  691.9
C         618.4  604.6  611.5
D         591.0  524.6  557.8

CodePudding user response：

Solution :

res = df.groupby('group').mean().T
res['all'] = (res.X   res.Y) / 2
print(res)

Output

group      X      Y    all
A      558.0  509.8  533.9
B      613.2  770.6  691.9
C      618.4  604.6  611.5
D      591.0  524.6  557.8