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?

stackoverflow.com/questions/16852911/…

Rick S
– Rick S

2017-05-02 18:52:29 +00:00
Commented May 2, 2017 at 18:52 — Rick S
– Rick S, Commented May 2, 2017 at 18:52

polkattt · Accepted Answer · 2017-05-03 15:29:31Z

2

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)

edited May 3, 2017 at 15:29

answered May 2, 2017 at 19:23

polkattt

3331 gold badge4 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chef1075 · Accepted Answer · 2017-05-02 18:51:34Z

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 2, 2017 at 18:51

Chef1075

2,7349 gold badges45 silver badges57 bronze badges

Comments

Zohaib Ijaz · Accepted Answer · 2017-05-02 18:52:39Z

-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 2, 2017 at 18:52

Zohaib Ijaz

23k7 gold badges43 silver badges63 bronze badges

2 Comments

DanZimmerman Over a year ago

Thanks Zohaib. I got this error message: "TypeError: strptime() argument 1 must be string, not Series". Any idea how to resolve this issue?

Zohaib Ijaz Over a year ago

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']

Collectives™ on Stack Overflow

Python - format date values when exporting to excel

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related