Pandas pivot table with aggregates from two separate dataframes

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


Read more here: https://stackoverflow.com/questions/67942439/pandas-pivot-table-with-aggregates-from-two-separate-dataframes

Content Attribution

This content was originally published by Morello at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: