substracting date times in pandas

Question

i've data frame is this

In[1]: df1
Out[1]
   Loan Date Negotiation
   2019-03-31
   2019-03-31
   2019-03-31

as Loan Date Negotiation           datetime64[ns]

so i wanna make function to subtract 2 days from it. if last the day of the month is sunday, i will subtract 2 days from it.

From dataframe above, 2019-03-31 Is sunday,

i've tried but it's fail, this is

 def subtractingDate(dateTime):
     dateTimestamp = pd.Timestamp(dateTime)
     newDate = dateTimestamp - pd.Timedelta("2 days")
     return newDate

 dfMARET.loc[dfMARET["Loan Date Negotiation"].dt.dayofweek == 6, "New Date"] = subtractingDate(dfMARET["Loan Date Negotiation"])

*note: 6 is for sunday

So the error is


TypeError                                 Traceback (most recent call last)
<ipython-input-9-cc2a3348e6ce> in <module>
     16 # a = subtractingDate(dfMARET["Loan Date Negotiation"])
     17 # a
---> 18 dfMARET.loc[dfMARET["Loan Date Negotiation"].dt.dayofweek == 6, "New Date"] = subtractingDate(dfMARET["Loan Date Negotiation"])
     19 dfMARET
     20 

<ipython-input-9-cc2a3348e6ce> in subtractingDate(dateTime)
     10 
     11 def subtractingDate(dateTime):
---> 12     dateTimestamp = pd.Timestamp(dateTime)
     13     newDate = dateTimestamp - pd.Timedelta(days = 2)
     14     return newDate

pandas\_libs\tslibs\timestamps.pyx in pandas._libs.tslibs.timestamps.Timestamp.__new__()

pandas\_libs\tslibs\conversion.pyx in pandas._libs.tslibs.conversion.convert_to_tsobject()```

so my expectation would be

 Loan Date Negotiation
 2019-03-29
 2019-03-29
 2019-03-29

Ther's solution in pandas?

thank's

Can you edit your post to include `df1["Loan Date Negotiation"].dtype` and the exact error that you get? — Mike, May 29 '19 at 14:55

ilja · Answer 1 · 2019-05-30T06:34:58.120

If you need to convert datetime64 to Timestamp first, you can use:

df['Date'] = [pd.Timestamp(x) for x in df['Date']]

and use timedelta():

from datetime import datetime, timedelta
dt = pd.Timestamp(2019,3,31)
new_dt = dt-timedelta(days=2)

new_dt
> datetime.datetime(2019, 3, 29, 0, 0)

new_dt.strftime('%Y-%m-%d')
> '2019-03-29'

Or on whole column Date with:

df['New_Date'] = df['Date']-timedelta(days=2)

EDIT: Full example:

import numpy as np
from datetime import datetime, timedelta

df1 = pd.DataFrame([[np.datetime64(datetime(2019, 3, 31))],
                    [np.datetime64(datetime(2019, 3, 27))],
                    [np.datetime64(datetime(2019, 3, 24))]], 
                   columns=['Loan Date Negotiation'])
df1

df1['Loan Date Negotiation'].dtype
> dtype('<M8[ns]')

M8[ns] is a specific type of datetime64[ns], so there should be no difference in further processing.

If you want to subtract two days only if the day is a sunday, you can use np.where():

df1['New_Date'] = np.where(df1['Loan Date Negotiation'].dt.dayofweek==6, 
         df1['Loan Date Negotiation']-timedelta(days=2), 
         df1['Loan Date Negotiation'])

Dates at index 0 and 2 were sundays and got 2 days subtracted. Date at index 1 was not touched.

Alternative via list comprehension over every single Timestamp

df1['New_Date'] = [x-timedelta(days=2) if x.weekday()==6 else x for x in df1['Loan Date Negotiation']]

@charismabathara what exactly does not work? I added a full example with two alternatives. Could you try them? — ilja, May 30 '19 at 06:36

score 0 · Answer 2 · answered May 29 '19 at 15:00

0

You're close! You just need to change what you pass into Timedelta().

Here's an example:

import pandas as pd

ts = pd.Timestamp(2017, 1, 1, 12)
days = pd.Timedelta(days=2)
print(days)
print(ts)
print(ts - days)

Output:

2 days 00:00:00
2017-01-01 12:00:00
2016-12-30 12:00:00

answered May 29 '19 at 15:00

rkor

63
7

how can i convert my datetime64[ns] types into timestamp sir? i've already tried with pd.Timestamp(df["Date"]). but it doesnt give a correct results. it giving so many nan – charisma bathara May 29 '19 at 15:06
I'm not sure about that, but maybe this will help: https://stackoverflow.com/questions/13703720/converting-between-datetime-timestamp-and-datetime64 – rkor May 29 '19 at 15:15
Also, do you actually need that kind of time precision for what you're doing? Maybe it would be easier if you didn't need to use datetime64. – rkor May 29 '19 at 15:20
@charisma bathara, you can use list comprehension to convert datetime64 to Timestamp, see my answer. :-) – ilja May 29 '19 at 15:28
@rkor Datatime64 is from my excel import – charisma bathara May 29 '19 at 15:30
@charismabathara can you post the code that does the import? – rkor May 29 '19 at 15:40

substracting date times in pandas

2 Answers2