Numpy: Conditional Np.where Replace

I have the following dataframe: 'customer_id','transaction_dt','product','price','units' 1,2004-01-02 00:00:00,thing1,25,47 1,2004-01-17 00:00:00,thing2,150,8 2,2004-01-29 00:00:00

Solution 1:

I think you can use:

tra=df['transaction_dt'].values[:,None]idx=np.argmax(end_date_range.values>tra,axis=1)sdr=start_date_range[idx]m=df['transaction_dt']<sdr#change value by condition with previousdf["window_start_dt"]=np.where(m,start_date_range[idx-1],sdr)df['window_end_dt']=end_date_range[idx]print(df)customer_idtransaction_dtproductpriceunitswindow_start_dt\012004-01-02  thing125472004-01-01112004-01-17  thing215082004-01-01222004-01-29  thing2150252004-01-01332017-07-15  thing355172017-06-21432016-05-12  thing355472016-04-27542012-02-23  thing2150222012-02-18642009-10-10  thing125122009-10-01742014-04-04  thing215022014-03-09852008-07-09  thing2150432008-07-08952004-01-30  thing125402004-01-011052004-01-31  thing125222004-01-011152004-02-01  thing12522004-01-31

Solution 2:

You can use numpy.where() like :

numpy.where(df['transaction_dt'] <= df['window_start_dt'], *operation whenTrue*, *operation whenFalse*)

Solution 3:

What about something like this?

# get argmax indices
idx = df.transaction_dt.apply(lambda x: np.argmax(end_date_range > x)).values
# define window_start_dt
df = df.assign(window_start_dt = start_date_range[idx])

# identify exceptions
mask = df.transaction_dt.le(df.window_start_dt)
# replace with shifted start_date_rage
df.loc[mask, "window_start_dt"] = start_date_range[idx - 1][mask]

Output:

customer_idtransaction_dtproductpriceunitswindow_start_dt012004-01-02  thing125472004-01-01112004-01-17  thing215082004-01-01222004-01-29  thing2150252004-01-01332017-07-15  thing355172017-06-21432016-05-12  thing355472016-04-27542012-02-23  thing2150222012-02-18642009-10-10  thing125122009-10-01742014-04-04  thing215022014-03-09852008-07-09  thing2150432008-07-08952004-01-30  thing125402004-01-011052004-01-31  thing125222004-01-011152004-02-01  thing12522004-01-31