Problem With Python Pandas Data Output To Excel In Date Format

Need guidance on how I can format a value to date format in Pandas before it prints out the value to an Excel sheet. I am new to Pandas and had to edit an existing code when the v

Solution 1:

For making sure you have column in dateformat, use following

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

Once that is done, you can use Pandas ExcelWriter's xlsxwriter engine.

Please see more details about that in this article: https://xlsxwriter.readthedocs.io/example_pandas_column_formats.html

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter("pandas_column_formats.xlsx", engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Get the xlsxwriter workbook and worksheet objects.
workbook  = writer.book
worksheet = writer.sheets['Sheet1']

format = workbook.add_format({'num_format': 'dd/mm/yy'})

# Set the column width and format. # Provide proper column where you have date info.
worksheet.set_column('A:A', 18, format)

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Solution 2:

Convert date format in pandas dataframe itself using below:

date['date1'] = pd.to_datetime(df['date1'])

Example:

I have a dataframe

PRODUCTPRICEPURCHSEdate0ABC5000    True2020/06/011ABB2500    False2020/06/01

apply above given formulae on date in dataframe

df['date'] = pd.to_datetime(df['date'])

Output will like:

PRODUCTPRICEPURCHSEdate0ABC5000    True2020-06-011ABB2500    False2020-06-01