Python - format date values when exporting to excel

Question

df1 = pd.DataFrame(
          columns=["A", "B", "C"], 
          data=[['2017-01-01',np.nan, "ok"], ['2017-02-01',8,"fine"], ['2017-03-01',100,np.nan]])
df1.to_excel('test.xlsx', index=False)

I have a dataframe that column is string. I want to export the df to excel and make column A the date type with the format DDMMMYYYY (i.e. instead of '2017-01-01' I need '01JAN2017').

I have tried two things that they don't work:

df1['A'] = pd.to_datetime(df1['A']).dt.strftime('%d%b%Y')

or

df1['A'] = pd.to_datetime(df1['A'], format="%d%b%Y")

How to do this?

http://stackoverflow.com/questions/16852911/how-do-i-convert-dates-in-a-pandas-data-frame-to-a-date-data-type — Rick S, May 02 '17 at 18:52

polkattt · Accepted Answer · 2017-05-03T15:29:31.447

i had a similar use case. i used pandas and arrow!

maybe something like this?

import pandas as pd
import numpy as np
import arrow

df1 = pd.DataFrame(
    columns=["A", "B", "C"],
    data=[
        ['2017-01-01',np.nan, "ok"],
        ['2017-02-01',8,"fine"],
        ['2017-03-01',100,np.nan]
    ]
)

df1['A'] = df1['A'].apply(
    lambda val: arrow.get(val).format("DDMMMYYYY").upper()
)

df1.to_excel('test.xlsx', index=False)

score 0 · Answer 2 · answered May 02 '17 at 18:51

0

In Excel, format column A as a custom format:

dd*mmm*yyyy

The stars should allow for no spaces or operators to appear between the dates elements.

Also, read this for another take on formatting.

answered May 02 '17 at 18:51

Chef1075

2,614
9
40
57

score -1 · Answer 3 · answered May 02 '17 at 18:52

-1

You can first parse it to datetime object and then convert it to required format

from datetime import datetime
df1['A'] = datetime.strptime(df1['A'], '%Y-%m-%d').strftime("%d%b%Y").upper()

answered May 02 '17 at 18:52

Zohaib Ijaz

21,926
7
38
60

Thanks Zohaib. I got this error message: "TypeError: strptime() argument 1 must be string, not Series". Any idea how to resolve this issue? – DanZimmerman May 02 '17 at 22:15
from your question, it seems that `df1['A']` is your date string. So find the expression for your date str and pass this in place of `df1['A']` – Zohaib Ijaz May 03 '17 at 07:07

Python - format date values when exporting to excel

3 Answers3