0

So, Basically, I got this 2 df columns with data content. The initial content is in the dd/mm/YYYY format, and I want to subtract them. But I can't really subtract string, so I converted it to datetime, but when I do such thing for some reason the format changes to YYYY-dd-mm, so when I try to subtract them, I got a wrong result. For example:

Initial Content:

a: 05/09/2022
b: 30/09/2021 
result expected: 25 days.

Converted to DateTime:

a: 2022-05-09
b: 2021-09-30 (For some reason this date stills the same)
result: 144 days.

I'm using pandas and datetime to make this project. So, I wanted to know a way I can subtract this 2 columns with the proper result.

--- Answer

When I used

pd.to_datetime(date, format="%d/%m/%Y")

It worked. Thank you all for your time. This is my first project in pandas. :)

Luiz p
  • 1
  • 1
  • could you make this a reproducible example? so your input is date/time in format dd/mm/YYYY; how did you convert to datetime (remember to set `dayfirst=True`?)? – FObersteiner Dec 03 '21 at 19:34
  • Did you use [`pd.to_datetime(date, format="%d/%m/%Y")`](https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html)? – mosc9575 Dec 03 '21 at 19:37
  • `a = datetime.strptime(, format="%d/%m%Y")`, `b = datetime.strptime(, format="%d/%m%Y")`, `days = (a - b).days`. See – Larry the Llama Dec 03 '21 at 19:38
  • Actually, I used pandas to read an excel file with these content, they were already in data format on excel (with the dd/mm/YYYY format). Then, I used pandas to_datetime in the columns I wanted to subtract. – Luiz p Dec 03 '21 at 19:39
  • @Luizp Please update your question and show us what you do? If you parse [`excel`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html?highlight=read_excel#pandas.read_excel) the there is an option to pares dates by dateformat, too. – mosc9575 Dec 03 '21 at 19:41
  • @mosc9575 I tried, but got this error: time data '30/09/2021' does not match format '%d%m%Y' (match) – Luiz p Dec 03 '21 at 19:43
  • @Luizp You have to do it right. It is `%d/%m/%Y`. Please recognize the `/`-sign. – mosc9575 Dec 03 '21 at 19:44
  • @mosc9575 Oh now it worked. Ty for your time. This is my first project in pandas and I did not have any clue. – Luiz p Dec 03 '21 at 20:06

2 Answers2

0
df = pd.DataFrame({'Date1': ['05/09/2021'], 'Date2': ['30/09/2021']})
df = df.apply(lambda x:pd.to_datetime(x,format=r'%d/%m/%Y')).assign(Delta=lambda x: (x.Date2-x.Date1).dt.days)
print(df)

       Date1      Date2  Delta
0 2021-09-05 2021-09-30     25
Алексей Р
  • 7,507
  • 2
  • 7
  • 18
0

I just answered a similar query here subtracting dates in python

import datetime
from datetime import date
from datetime import datetime
import pandas as pd

date_format_str = '%Y-%m-%d %H:%M:%S.%f'

date_1 = '2016-09-24 17:42:27.839496'
date_2 = '2017-01-18 10:24:08.629327'

start = datetime.strptime(date_1, date_format_str)
end =   datetime.strptime(date_2, date_format_str)

diff = end - start

# Get interval between two timstamps as timedelta object
diff_in_hours = diff.total_seconds() / 3600
print(diff_in_hours)

# get the difference between two dates as timedelta object
diff = end.date() - start.date()
print(diff.days)

Pandas

import datetime
from datetime import date
from datetime import datetime
import pandas as pd

date_1 = '2016-09-24 17:42:27.839496'
date_2 = '2017-01-18 10:24:08.629327'

start = pd.to_datetime(date_1, format='%Y-%m-%d %H:%M:%S.%f')
end = pd.to_datetime(date_2, format='%Y-%m-%d %H:%M:%S.%f')

# get the difference between two datetimes as timedelta object
diff = end - start

print(diff.days)
NeoTheNerd
  • 566
  • 3
  • 11
  • please don't copy-paste [your answers](https://stackoverflow.com/a/70219596/10197418) to similar questions. that's what the duplicate flag is for. – FObersteiner Dec 03 '21 at 20:02
  • @MrFuppes I left a link to my answer to the alternative albeit similar question at the start of this post. If I'm replicating my own answer thats my choice. I've read up on duplicate flag. Its similar question, but not duplicate! – NeoTheNerd Dec 03 '21 at 20:50