Problem with Python Pandas data output to excel in date format

Question

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 values are output to an Excel sheet. After some conditional/functional calculations are done, the value is then output to Excel. My Current value seems to be in string format which is not an Excel friendly date format.

Output of the value looks like this :

Output of the value looks like this

Needed to format the output to the date format

I did try the options of strptime, but as per my understanding, these values will also give the output in string format. the strange part is, I am not able to format the column in Excel to date format using Excel formatting option as well.

Thank you for your time and help.

My code is something like this:

def calculate(snumber,owner,reason):
    #some if conditions and then
    date11 = Date + relativedelta(months = 1)
    return date11.strftime('%d %b %Y')


df['date1'] = df.apply(lambda x: calculate(x['snumber'], x['owner'], x['reason']), axis=1)

score 4 · Answer 1 · answered Aug 30 '20 at 06:58

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()

score 1 · Accepted Answer · answered Aug 30 '20 at 06:42

Convert date format in pandas dataframe itself using below:

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

Example:

I have a dataframe

PRODUCT     PRICE   PURCHSE     date
0   ABC     5000    True    2020/06/01
1   ABB     2500    False   2020/06/01

apply above given formulae on date in dataframe

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

Output will like:

    PRODUCT     PRICE   PURCHSE     date
0   ABC     5000    True    2020-06-01
1   ABB     2500    False   2020-06-01

Problem with Python Pandas data output to excel in date format

2 Answers2

Linked