Sum one dataframe based on value of other dataframe in same index/row-CodePudding

I would like to sum values of a dataframe conditionally, based on the values of a different dataframe. Say for example I have two dataframes:

df1 = pd.DataFrame(data = [[1,-1,5],[2,1,1],[3,0,0]],index=[0,1,2],columns = [0,1,2])

index   0   1   2        
-----------------
0       1   -1   5    
1       2   1   1  
2       3   0   0

df2 = pd.DataFrame(data = [[1,1,3],[1,1,2],[0,2,1]],index=[0,1,2],columns = [0,1,2])

index   0   1   2    
-----------------
0       1   1   3   
1       1   1   2  
2       0   2   1

Now what I would like is that for example, if the row/index value of df1 equals 1, to sum the location of those values in df2.

In this example, if the condition is 1, then the sum of df2 would be 4. If the condition was 0, the result would be 3.

CodePudding user response：

You can use a mask with where:

df2.where(df1.eq(1)).to_numpy().sum()
# or
# df2.where(df1.eq(1)).sum().sum()

output: 4.0

intermediate:

df2.where(df1.eq(1))
     0    1    2
0  1.0  NaN  NaN
1  NaN  1.0  2.0
2  NaN  NaN  NaN

CodePudding user response：

Another option with Pandas' query:

df2.query("@df1==1").sum().sum()
# 4