python - Average Pandas Dataframe with condition other Dataframe

Question

I have two dataframes. One only contains binary values, the other floats between 0 and 1. Eg.

df1:
         col 1        col 2        col 3        col 4        col 5        col 6        col 7
0          0.0          1.0          0.0          0.0          0.0          0.0          0.0  
1          0.0          0.0          0.0          0.0          1.0          0.0          0.0  
2          0.0          0.0          0.0          0.0          1.0          0.0          1.0  
3          0.0          0.0          0.0          0.0          0.0          0.0          1.0  
4          0.0          0.0          0.0          0.0          1.0          0.0          0.0  
df2:
         col 1        col 2        col 3        col 4        col 5        col 6        col 7  
0     0.068467     0.099870     0.090778     0.087500     0.612955     0.081495     0.570557  
1     0.091651     0.084946     0.082704     0.103070     0.517317     0.092595     0.603526  
2     0.070380     0.104353     0.103062     0.086780     0.598848     0.101543     0.570064  
3     0.052239     0.123760     0.215329     0.087608     0.581883     0.080650     0.574241  
4     0.087564     0.104460     0.125887     0.079945     0.646284     0.081015     0.609308

What I need is to compute the average of df1 where df2 >= 0.5 (or any other number) All I could find on this topic is for columns only and I could not get it to work on the entire dataframe. Any help is appreciated.

question from:https://stackoverflow.com/questions/65880429/average-pandas-dataframe-with-condition-other-dataframe

Answer 1

First is necessary same index and same columns names in both DataFrames.

Then use DataFrame.where for set missing values to False values by mask and then get mean:

df = df1.where(df2 >= 0.5).mean() 

If need mean of all values use numpy.nanmean for exclude missing values:

mean = np.nanmean(df1.where(df2 >= 0.5))

Another idea is convert all values to Series with DataFrame.stack and then get mean:

mean = df1.where(df2 >= 0.5).stack().mean()