Pandas pivot table with aggregates from two separate dataframes

user_16182652 0
Viewed 33

I am trying to build a pivot_table that contains the difference in values from two dataframes (df1 and df2)along with the agg mean and standard deviation.

contains df1 =

gender year     statistics    s_values
male   1999  cigarette use       100
male   1999  cellphone use       310
male   1999   internet use       101
male   1999    alcohol use       100

contains df2 =

gender   year     statistics    s_values
female   1999  cigarette use       156
female   1999  cellphone use       198
female   1999   internet use       232
female   1999    alcohol use       243

desired output is my_pivot_table =

year    statistics       male   female  difference   mean    std
1999  cigarette use     100.0    156.0       56.0    128.0   28.0
1999  cellphone use     310.0    198.0      112.0    254.0   56.0
1999   internet use     101.0    232.0       78.0    166.5   65.5
1999    alcohol use     100.0    243.0      143.0    171.5   50.0

I merged df1 and df2 into one dataframe called merged_df I am not sure if this would be the right step towards building the desired pivot table... I am not sure where to go from here the male column contains s_values from df1 and the female column contains s_values from df2 contains merged_df =

gender   year     statistics    s_values
  male   1999  cigarette use       100
  male   1999  cellphone use       310
  male   1999   internet use       101
  male   1999    alcohol use       100
female   1999  cigarette use       156
female   1999  cellphone use       198
female   1999   internet use       232
female   1999    alcohol use       243
pandas dataframe pivot python
1 Answers

Try:

x = pd.concat([df1, df2]).pivot(
    index=["year", "statistics"], columns="gender", values="s_values"
)

x["difference"] = (x["male"] - x["female"]).abs()
x["mean"] = x[["male", "female"]].mean(axis=1)
x["std"] = x[["female", "male"]].std(axis=1, ddof=0)
print(x.reset_index().rename_axis("", axis=1))

Prints:

   year     statistics  female  male  difference   mean   std
0  1999    alcohol use     243   100         143  171.5  71.5
1  1999  cellphone use     198   310         112  254.0  56.0
2  1999  cigarette use     156   100          56  128.0  28.0
3  1999   internet use     232   101         131  166.5  65.5
user_100359850
Related