CODEWITHSUNDEEP
pythonpandasdataframexlsxwriter
sfgroups
sfgroups

Reputation: 19099

Pandas xlsxwriter format date column

I am creating Excel file with date column, want to format date column into 'mmm-yy' format. some reason below code is not working.

import pandas as pd

df = pd.DataFrame({'test_date': pd.date_range('1/1/2022', periods=12, freq='M')})

with pd.ExcelWriter('format-01.xlsx', engine='xlsxwriter') as writer:
    df.to_excel(writer, sheet_name='test', index=False)
    workbook = writer.book
    worksheet = writer.sheets['test']
    format2 = workbook.add_format({'num_format': 'mmm-yy'})
    worksheet.set_column('A:A', 30, format2)

Trying to understand why format is not working?

This give data in this format enter image description here

Expecting this format

expected

Upvotes: 1

Views: 429

Answers (1)

BigBen
BigBen

Reputation: 50008

I'd use the datetime_format parameter of ExcelWriter here:

with pd.ExcelWriter(
    'format-01.xlsx', 
    engine='xlsxwriter', 
    datetime_format='mmm-yy'
) as writer:
    df.to_excel(writer, sheet_name='test', index=False)
    worksheet = writer.sheets['test']
    worksheet.set_column('A:A', 30)

Upvotes: 2

Related Questions