Subtract date from datetime columns

Question

I have a dataframe where one of the column ('ProcessingDATE') is datetime format. I want to create another column ('Report Date') where if the processing date is a Monday, subtract 3 days from it, which will end to be a Friday; else subtract 1 day from it.

I've been using python for a short amount of time, so doesn't have a lot of idea about how to write it. My thoughts was to write a for loop with if the cell = Monday, then = datetime.datetime.today() – datetime.timedelta(days=3); else = datetime.datetime.today() – datetime.timedelta(days=1)

for j in range(len(DDA_compamy['ProcessingDATE'])):
    if pd.to_datetime(datetime(DDA_company.ProcessingDATE[j])).weekday() == 2

Please [provide a reproducible copy of the existing DataFrame](https://stackoverflow.com/questions/52413246/how-do-i-provide-a-reproducible-copy-of-my-existing-dataframe) — Trenton McKinney, Oct 02 '19 at 21:42

Sudharsana Rajasekaran · Accepted Answer · 2019-10-02T22:03:46.730

Hope this helps,

from datetime import timedelta

if DDA_compamy['ProcessingDATE'].weekday() == 4:  #Condition to check if it is friday
    DDA_compamy['Report Date']=DDA_compamy['ProcessingDATE'] - timedelta(days=3) # if friday subtracting 3 days
else:
    DDA_compamy['Report Date']=DDA_compamy['ProcessingDATE'] - timedelta(days=1) #Else one day from the date is subtracted

the above can also be written as,

DDA_compamy['Report Date'] = (DDA_compamy['ProcessingDATE'] - timedelta(days=3)) if (DDA_compamy['ProcessingDATE'].weekday() == 4) else (DDA_compamy['Report Date']=DDA_compamy['ProcessingDATE'] - timedelta(days=1))

the if statement should be: if DDA_compamy['ProcessingDATE'].weekday() == 0: — Alice Xu, Oct 10 '19 at 15:09

score 0 · Answer 2 · answered Oct 02 '19 at 21:59

Use pandas.Series.dt.weekday and some logic:

import pandas as pd
df = pd.DataFrame({'ProcessingDATE':pd.date_range('2019-04-01', '2019-04-27')})
df1 = df.copy()
mask = df1['ProcessingDATE'].dt.weekday == 0
df.loc[mask, 'ProcessingDATE'] = df1['ProcessingDATE'] - pd.to_timedelta('3 days')
df.loc[~mask, 'ProcessingDATE'] = df1['ProcessingDATE'] - pd.to_timedelta('1 days')

Output:

   ProcessingDATE
0      2019-03-29
1      2019-04-01
2      2019-04-02
3      2019-04-03
4      2019-04-04
5      2019-04-05
6      2019-04-06
7      2019-04-05
8      2019-04-08
9      2019-04-09
10     2019-04-10
11     2019-04-11
12     2019-04-12
13     2019-04-13
14     2019-04-12
15     2019-04-15
16     2019-04-16
17     2019-04-17
18     2019-04-18
19     2019-04-19
20     2019-04-20
21     2019-04-19
22     2019-04-22
23     2019-04-23
24     2019-04-24
25     2019-04-25
26     2019-04-26

Subtract date from datetime columns

2 Answers2