Overwriting column data basis multiple condition

Question

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.

Does this answer your question? [How do I assign values based on multiple conditions for existing columns?](https://stackoverflow.com/questions/30631841/how-do-i-assign-values-based-on-multiple-conditions-for-existing-columns) — Tzane, Nov 25 '22 at 07:54

jezrael · Accepted Answer · 2022-11-25T10:07:57.467

1

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

edited Nov 25 '22 at 10:07

answered Nov 25 '22 at 07:56

jezrael

822,522
95
1,334
1,252

thanks, how does it check for Group since i need this status to be updated for x and y – Roshankumar Nov 25 '22 at 09:02
1

df['Group'].isin(['x','y']) – Roshankumar Nov 25 '22 at 09:12
thanks. just one doubt when it check for the past three months , it is current month(i.e. Nov2022) - 3month. is my understanding correct.? – Roshankumar Nov 25 '22 at 09:43
@Roshankumar - No, it is not clear from question. So I use maximal datetime. Solution will be changed – jezrael Nov 25 '22 at 09:46
can we set a month , i mean consider sept 2022 as a threshold and check whether the last date of payment. i.e. checking past 3month from sept 2022. this would really solve the purpose – Roshankumar Nov 25 '22 at 09:52
@Roshankumar - answer was edited. – jezrael Nov 25 '22 at 10:01
kudos..!! one last can we consider sept 2022 as a threshold and check whether the last date of payment. i.e. checking past 3month from sept 2022. – Roshankumar Nov 25 '22 at 10:04
@Roshankumar - answer was editd. – jezrael Nov 25 '22 at 10:08

Overwriting column data basis multiple condition

1 Answers1