CODEWITHSUNDEEP
pythonpandasdataframe
BD12
BD12

Reputation: 107

Loop through dataframe and set a date using pd.DateOffset

I am stumped as to why this isn't working and I'm sure its very simple!

df = pd.DataFrame([[1,'2021-03-15','Monday'],[2,'2021-03-16','Tuesday'],[3,'2021-03-17','Wednesday'],[4,'2021-03-18','Thursday'],
             [5,'2021-03-19','Friday'],[6,'2021-03-20','Saturday'],[7,'2021-03-21','Sunday']], 
              columns=['id','cob_date','day'])

I need to create a new column recon_date which equals the next business day based on my existing column cob_date. My logic is that this should be cob+1 for Mon-Thu and cob+3 for Fri.

I can do this using pd.DatetimeIndex(df['cob_date']) + pd.DateOffset(1) however when I use this within the below for loop all my results are cob+3.

for index, row in df.iterrows():
if row['day'] in ['Monday','Tuesday','Wednesday','Thursday']:
    df['recon_date'] = pd.DatetimeIndex(df['cob_date']) + pd.DateOffset(1) 
elif row['day'] == 'Friday':
    df['recon_date'] = pd.DatetimeIndex(df['cob_date']) + pd.DateOffset(3)

Upvotes: 0

Views: 126

Answers (2)

SeaBean
SeaBean

Reputation: 23217

If you want the next business day, you can also consider using pd.offsets.BDay() as follows:

df['recon_date'] = pd.DatetimeIndex(df['cob_date']) + pd.offsets.BDay(1)

print(df)

   id    cob_date        day recon_date
0   1  2021-03-15     Monday 2021-03-16
1   2  2021-03-16    Tuesday 2021-03-17
2   3  2021-03-17  Wednesday 2021-03-18
3   4  2021-03-18   Thursday 2021-03-19
4   5  2021-03-19     Friday 2021-03-22
5   6  2021-03-20   Saturday 2021-03-22
6   7  2021-03-21     Sunday 2021-03-22

Note that this not only change Friday to next Monday, it also changes Saturday and Sunday to next Monday. See whether this is what you want.

Upvotes: 2

jezrael
jezrael

Reputation: 862701

Use numpy.select with Series.isin for test membership by multiple values:

m1 = df['day'].isin(['Monday','Tuesday','Wednesday','Thursday'])
m2 = df['day'] == 'Friday'

s = pd.to_datetime(df['cob_date'])
df['recon_date'] = np.select([m1, m2], [s + pd.DateOffset(1), s + pd.DateOffset(3)], s)
print (df)
   id    cob_date        day recon_date
0   1  2021-03-15     Monday 2021-03-16
1   2  2021-03-16    Tuesday 2021-03-17
2   3  2021-03-17  Wednesday 2021-03-18
3   4  2021-03-18   Thursday 2021-03-19
4   5  2021-03-19     Friday 2021-03-22
5   6  2021-03-20   Saturday 2021-03-20
6   7  2021-03-21     Sunday 2021-03-21

Upvotes: 2

Related Questions