Using timedelta and strftime on column converted to datetime

user_7221994 0
Viewed 219

GOAL: After reading a *.csv to a dataframe, I want to convert a column of birth dates to this format: %m-%d-%Y. And then write them to excel. The *.csv has 600K+ records.

Summary: I have dates for both the 20th and 21st centuries, and the years sometime overlap. For example, I can have four-digit years from 1901 and 2001. Because of this, I had to use a code snippet borrowed from this answer.

What I've tried and what I expected: I originally converted the column to datetime, and used strftime('%m-%d-%Y'). But, then the year 1970 and before would be written as 20--. For example, the years 2070 and 2068 here should be 1970 and 1968, respectively. And the birthdate column is dtype object, if it matters.

import pandas as pd
from datetime import timedelta
from datetime import datetime

#birthdate column
birthdate = 'PATIENT_BIRTH_DATE'
#after reading the *.csv to a dataframe, convert the birthdate column to %m-%d-%Y format
df[birthdate] = pd.to_datetime(df[birthdate]).dt.strftime("%m-%d-%Y")

#prints
0    08-24-1996
1    10-16-1971
2    02-19-2070
3    09-25-2068

So, then I used the above-mentioned code snippet to fix that. However, now I can't seem to remove the time from the date that gets written to Excel. If I print to the terminal I don't get the time, but when I write to Excel I do. It looks like this: 1996-08-24 00:00:00

df[birthdate] = pd.to_datetime(df[birthdate])

future = df[birthdate] > datetime.today()
df.loc[future, birthdate] -= timedelta(days=365.25*100)

If I use df[birthdate] = pd.to_datetime(df[birthdate]).dt.strftime("%m-%d-%Y") here, I get an error: TypeError: '>' not supported between instances of 'str' and 'datetime.datetime'

pandas excel datetime python
2 Answers

Excel is formatting it, but you can update that selection while writing to the workbook.

You can use the the xlsxwriter engine to specify the format of a given column.

https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

Here is a guide on formatting dates with xlsxwriter as well, but I provided a solution from what I could infer below

https://xlsxwriter.readthedocs.io/working_with_dates_and_time.html?highlight=date%20format

This will only work if the column is in datetime format, so it may be necessary to validate that using this snippet

df['date1'] = df['date1'].dt.strftime('%m-%d-%Y')

Then run the following, being aware of the column where the formatting is applied will change based on which column in the dataframe has the datetimes.

df.to_excel(writer, sheet_name='Sheet1')

writer = pd.ExcelWriter("output.xlsx", engine='xlsxwriter')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
# %m-%d-%Y
format = workbook.add_format({'num_format': 'mm-dd-yyyy'})

# Set the column width and format.
worksheet.set_column('B:B', 18, format)

writer.save()
user_43523170

A minor addition, to account for the 100 years offset, I'd suggest

import pandas as pd

birthdate = 'PATIENT_BIRTH_DATE'
df = pd.DataFrame({birthdate: pd.to_datetime(["08-24-1996", "10-16-1971", "02-19-2070", "09-25-2068"])})

df.loc[df[birthdate] > pd.Timestamp('now'), birthdate] -= pd.tseries.offsets.DateOffset(years=100)

df
  PATIENT_BIRTH_DATE
0         1996-08-24
1         1971-10-16
2         1970-02-19
3         1968-09-25

instead of using average number of days per year.

+1 for the "never-use-two-digit-years" bucket.

user_101974180
Related