CODEWITHSUNDEEP
pythonpandas
spacedinosaur10
spacedinosaur10

Reputation: 725

strftime function pandas - Python

I am trying to strip the time out of the date when the dataframe writes to Excel, but have been unsuccessful.

The weird thing is, I am able to manipulate the dataframe by using the lambda and striptime function below, and can see it the function is working by my print df6.

However, for some reason when I open the Excel file it shows in the format %m%d%y: 12:00am. I would like to just have the date show. Ideas?

Here is my code:

df6 = df
workbook = writer.book
df6.to_excel(writer, sheet_name= 'Frogs', startrow= 4, index=False)
df6['ASOFDATE'] = df6['ASOFDATE'].apply(lambda x:x.date().strftime('%d%m%y'))
worksheet = writer.sheets['Frogs']
format5 = workbook.add_format({'num_format': '0.00%'})
#format6= workbook.add_format({'num_format': 'mmm d yyyy'})
#worksheet.set_column('A:A', 18, format6)
worksheet.set_column('B:B', 18, format5)
worksheet.set_column('C:C', 18, format5)
worksheet.set_column('D:D', 18, format5)
now = datetime.now()
date= dt.datetime.today().strftime("%m/%d/%Y")
link_format = workbook.add_format({'color': 'black', 'bold': 1})
nrows = df6.shape[0]
worksheet.write(0, 0, 'Chart4', link_format) 
worksheet.write(1, 0, 'Frogs',link_format) 
#Multiply dataframe by 100 to get out of decimals (this method is used when dates are in the picture)
df6[df6.columns[np.all(df6.gt(0) & df6.lt(1),axis=0)]] *= 100
print df6
writer.save()

Upvotes: 1

Views: 568

Answers (1)

nullop
nullop

Reputation: 554

strftime is creating a textual representation of a datetime value which later is formatted and displayed by Excel according to its formatting templates. We could change this formatting template using date_format parameter in our ExcelWriter constructor e.g.:writer = pandas.ExcelWriter("test.xlsx", engine="xlsxwriter", date_format="ddmmyyyy"). I've slightly modified your sample:

import pandas as pd
import datetime
df = pd.DataFrame()
df['ASOFDATE'] = [datetime.datetime.now() for x in xrange(3)]
for i in xrange(3):
    df[i] = [1,2,3]
df6 = df
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter', date_format="ddmmyyyy")
workbook = writer.book
df6.to_excel(writer, sheet_name= 'Frogs', startrow= 4, index=False)
worksheet = writer.sheets['Frogs']
format5 = workbook.add_format({'num_format': '0.00%'})
worksheet.set_column('B:B', 18, format5)
worksheet.set_column('C:C', 18, format5)
worksheet.set_column('D:D', 18, format5)
link_format = workbook.add_format({'color': 'black', 'bold': 1})
worksheet.write(0, 0, 'Chart4', link_format)
worksheet.write(1, 0, 'Frogs',link_format)
writer.save()

This code produces a file which has date values formatted according to the specified template.

Upvotes: 1

Related Questions