pandas subtract values in two dataframes with identical columns create new dataframe to store result

user_16182652 0
Viewed 201

I am trying to create a new dataframe new_df with a new column containing the difference in values from subtracting identical columns in 2 separate dataframes: df1 df2

I have tried to use the code new_df.loc['difference'] = df1.loc['s_values'] - df2.loc['s_values'] but I cannot achieve my result.

where df1 =

                     stats  s_values
gender year               
women  2007         height   40
       2007  cigarette use   31

and df2 =

                     stats  s_values
gender year               
Men    2007         height   10
       2007  cigarette use   11

desired output achieved (I do not want to include the gender index)

new_df =

                stats  difference
   year               
   2007         height  30
   2007  cigarette use  20
pandas dataframe python
2 Answers
new_df = pd.DataFrame()
new_df["year"] = df1["year"]
new_df["stats"] = df1["stats"]

for i, (val1, val2) in enumerate(zip(df1["s_values"],df2["s_values"])):
        new_df.at[i,"difference"] = val1-val2
user_160384590

You can try this (full example):

Input:

import pandas as pd

df1 = pd.DataFrame({'gender': {0: 'woman', 1: 'woman'},
 'year': {0: 2007, 1: 2007},
 'stats': {0: 'height', 1: 'cigarette use'},
 's_values': {0: 40, 1: 31}})

df2 = pd.DataFrame({'gender': {0: 'men', 1: 'men'},
 'year': {0: 2007, 1: 2007},
 'stats': {0: 'height', 1: 'cigarette use'},
 's_values': {0: 10, 1: 11}})

Code:

df = pd.concat([df1,df2], ignore_index=True)
df['s_values'] = df.groupby(['year', 'stats'])['s_values'].diff().abs()
df.dropna(subset=['s_values']).drop('gender', axis=1)

Output:

   year          stats  s_values
2  2007         height      30.0
3  2007  cigarette use      20.0

Note:

If both dataframes are completely identicaly structured, its even shorter:

df1.drop('gender', axis=1).assign(s_values=df1['s_values'] - df2['s_values'])
user_119717850
Related