pandas - Make new dataframe from if condition across 2 dataframes

Question

If I have a monthly "points" dataframe, in which the values are from cumsum():

ID   month1  month2  month3  month4
000  0       10      45      55
111  40      60      100     100

And I have a "buy" dataframe, which is basically whether there'll be a purchase in that month or not:

ID   month1  month2  month3  month4
000  NO      NO      YES     NO
111  NO      YES     NO      YES     

How do I make a new dataframe whose values satisfies the condition:

IF points > 40 AND buy == "YES" 
THEN returns MAX(40, 0.8*points)
ELSE returns 0

the resulting dataframe should be:

ID   month1  month2  month3  month4
000  0       0       40      0
111  0       48      0       41.6

ID 111's month4 value is 41.6 because it still got 12 points remaining from the previous months and added by another 40 from current month, so it's 52*0.8 = 41.6

Answer 1

The easiest would be to merge the two datasets by 'ID':

df = df1.merge(df2, on='ID')

And then use np.where:

df['month1_x'] = np.where((df['month1_x'] > 40) & (df['month1_y'] == 'YES'), MAX(40, 0.8*df['month1_x']), 0)