Reputation: 97
I have an .xlsx file that I import into Python and create a Pandas dataframe. One of the columns in the .xlsx file is formatted as a date, mm-dd-yyyy and gets imported like that. I then delete some unneeded columns from that dataframe and export that dataframe using the xlsxwriter engine to create another Excel file.
writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter', date_format='mm/dd/yyyy')
When I do that, the date column's format changes and time automatically gets added to the date column. The cells in the column then follow this format, yyyy-mm-dd hh:mm:ss.
I found a potential solution but when I tried the code below,
df.set_index(df.index.map(lambda x: x.strftime('%m/%d/%Y'))).to_excel()
I get the error
AttributeError: 'int' object has no attribute 'strftime'
I also read that it isn't possible to format any cells that already have a default format applied.
Is this a lost cause? How can I preserve the pre-existing date format upon export as an .xlsx file?
Upvotes: 1
Views: 2084
Reputation: 210842
Try this:
writer = pd.ExcelWriter('Sample_Master_Data_edited.xlsx', engine='xlsxwriter',
date_format='mm/dd/yyyy', datetime_format='mm/dd/yyyy')
Upvotes: 6