TheDude TheDude - 29 days ago 6
Python Question

Pandas Conversion of datetime representation

I got a Dataframe with a date column as a datetime64[ns] type in the following representation:

For 31st January 2016 it is: 2016-01-31

What I want is a representation looking like this:

For 31st January 2016: 1/31/2016

For 31st December 2016: 12/31/2016

The important thing is, that for all the months before october, the 0 at the beginning has to be dropped, that is just having one digit for the months up to september.

Answer

You can use dt.strftime to give you a string representation to swap the day month order if the dtype is already datetime:

In [3]:
df = pd.DataFrame({'date':['2016-01-31', '2016-12-31']})
df['date'] = pd.to_datetime(df['date'])
df

Out[3]:
        date
0 2016-01-31
1 2016-12-31

In [4]:
df['strftime'] = df['date'].dt.strftime('%m/%d/%Y')
df

Out[4]:
        date    strftime
0 2016-01-31  01/31/2016
1 2016-12-31  12/31/2016

However, as you don't want leading zeroes for day and month then you can just access the individual day, month, year components of the datetimes and cast these to str and add the separator yourself:

In [5]:
df['custom'] = df['date'].dt.month.astype(str) + '/' + df['date'].dt.day.astype(str) + '/' + df['date'].dt.year.astype(str)
df

Out[5]:
        date    strftime      custom
0 2016-01-31  01/31/2016   1/31/2016
1 2016-12-31  12/31/2016  12/31/2016

However, I advise against this, a datetime dtype allows easier filtering and arithmetic operations which are not easy when the dtype is a str so the column loses it's usefulness IMO