Change date format of pandas column (month-day-year to day-month-year)

2
votes

Got the following issue.

I have an column in my pandas with some dates and some empty values.

Example:

    1 - 3-20-2019
    2 - 
    3 - 2-25-2019

etc

I want to convert the format from month-day-year to day-month-year, and when its empty, i just want to keep it empty.

What is the fastest approach?

Thanks!

2
pythonpandasdatetime

2 Answers

2
votes

One can initialize the data for the days using strings, then convert the strings to datetimes. A print can then deliver the objects in the needed format.

I will use an other format (with dots as separators), so that the conversion is clear between the steps.

Sample code first:

import pandas as pd
data = {'day': ['3-20-2019', None, '2-25-2019'] }
df = pd.DataFrame( data )

df['day'] = pd.to_datetime(df['day'])
df['day'] = df['day'].dt.strftime('%d.%m.%Y')
df[ df == 'NaT' ] = ''

Comments on the above. The first instance of df is in the ipython interpreter:

In [56]: df['day']                                                  
Out[56]: 
0    3-20-2019
1         None
2    2-25-2019
Name: day, dtype: object

After the conversion to datetime:

In [58]: df['day']                                               
Out[58]: 
0   2019-03-20
1          NaT
2   2019-02-25
Name: day, dtype: datetime64[ns]

so that we have 

In [59]: df['day'].dt.strftime('%d.%m.%Y')
Out[59]: 
0    20.03.2019
1           NaT
2    25.02.2019
Name: day, dtype: object

That NaT makes problems. So we replace all its occurrences with the empty string. 

In [73]: df[ df=='NaT' ] = ''

In [74]: df
Out[74]: 
          day
0  20.03.2019
1            
2  25.02.2019
1
votes

Not sure if this is the fastest way to get it done. Anyway,

df = pd.DataFrame({'Date': {0: '3-20-2019', 1:"", 2:"2-25-2019"}}) #your dataframe
df['Date'] = pd.to_datetime(df.Date) #convert to datetime format
df['Date'] = [d.strftime('%d-%m-%Y') if not pd.isnull(d) else '' for d in df['Date']]

Output:

         Date
0  20-03-2019
1            
2  25-02-2019