How would I do date time math on a DF column using today's date?-CodePudding

Essentially I want to create a new column that has the number of days remaining until maturity from today. The code below doesn't work, kind of stuck what to do next as nearly all examples showcase doing math on 2 DF columns.

today = date.today()    
today = today.strftime("%m/%d/%y")    
df['Maturity Date'] = df['Maturity Date'].apply(pd.to_datetime)
df['Remaining Days til Maturity] = (df['Maturity Date'] - today)

CodePudding user response：

You're mixing types, it's like subtracting apples from pears. In your example, today is a string representing - to us humans - a date (in some format, looks like the one used in the USA). Your pandas Series (the column of interest in your DataFrame) has a datetime64[ns] type, after you did the apply(pd.to_datetime) (which, you could do more efficiently without the apply as that will run an operation in a non-vectorized way over every element of the Series - have a look below, where I'm converting those strings into datetime64[ns] type in a vectorized way).

The main idea is that whenever you do operations with multiple objects, they should be of the same type. Sometimes frameworks will automatically convert types for you, but don't rely on it.

import pandas as pd
df = pd.DataFrame({"date": ["2000-01-01"]})
df["date"] = pd.to_datetime(df["date"])
today = pd.Timestamp.today().floor("D")  # That's one way to do it
today
# Timestamp('2021-11-02 00:00:00')
today - df["date"]
# 0   7976 days
# Name: date, dtype: timedelta64[ns]

CodePudding user response：

parse the Maturity Date as a datetime and format it as month/day/year then subtract the Maturity Date as a date type and store the difference in days as Remaining Days til Maturity

from datetime import date
today = date.today()    
df=pd.DataFrame({'Maturity Date':'11/04/2021'},index=[0])
df['Maturity Date'] = pd.to_datetime(df['Maturity Date'], format='%m/%d/%Y')
df['Remaining Days til Maturity'] = (df['Maturity Date'].dt.date - today).dt.days
print(df)

output:

   Maturity Date  Remaining Days til Maturity
0    2021-11-04                            2