Overwriting column data basis multiple condition-CodePudding

Existing Dataframe :

Id      last_dt_of_payment      Group     payer_status
A1            22/08/2022          x            1
A2            21/05/2022          x            1
A3            01/09/2022          y            1
A4            22/01/2022          y            1
A5            26/02/2022          p            1
A6            30/09/2022          s            1

Expected Dataframe :

Id      last_dt_of_payment      Group     payer_status
A1            22/08/2022          x            1
A2            21/05/2022          x            0
A3            01/09/2022          y            1
A4            22/01/2022          y            0
A5            26/02/2022          p            1
A6            30/09/2022          s            1

I am trying to Overwrite the payer_status basis the Group and last_dt_of_payment. if payer_status is either x or y and the last_dt_of_payment is done within last 3 months , the payer_status to be tagged as 1 else 0

stuck with applying logic for checking last three months payment.

CodePudding user response：

EDIT:

groups = ['x','y']

#convert to datetimes
df['last_dt_of_payment'] = pd.to_datetime(df['last_dt_of_payment'], dayfirst=True)

#create testing Period
td = pd.Period('2022-09', freq='m')
#get column to months periods
per = df['last_dt_of_payment'].dt.to_period('m')

#chain both mask
m = df['Group'].isin(groups) & per.lt(td - 3)

#set 0
df.loc[m, 'payer_status'] = 0

print (df)
   Id last_dt_of_payment Group  payer_status
0  A1         2022-08-22     x             1
1  A2         2022-05-21     x             0
2  A3         2022-09-01     y             1
3  A4         2022-01-22     y             0
4  A5         2022-02-26     p             1
5  A6         2022-09-30     s             1