3

I would like to change the date from dd/mm/yyyy to MMM dd/yy. ie 15/04/2021 to APR 15/21

My Date column format is object

I am doing this: df['Date'] = pd.to_datetime(df['Date'], format='%MMM %DD/%YY')

but I am getting ValueError: time data '15/04/2021' does not match format '%MMM %dd/%yy' (match)

Any help would be appreciated.

imdevskp
  • 2,103
  • 2
  • 9
  • 23
excelguy
  • 1,574
  • 6
  • 33
  • 67
  • For which usage? display dataframe in a console, export to excel? – Corralien Apr 27 '21 at 13:52
  • Yes. display in dataframe for eventual excel use. – excelguy Apr 27 '21 at 13:53
  • If you convert a string datetime to a `datetime64` dtype, the display will be always in the format "YYYY-MM-DD". See this [answer](https://stackoverflow.com/a/38067805/15239951). – Corralien Apr 27 '21 at 14:00
  • 1
    Does this answer your question? [How to change the datetime format in pandas](https://stackoverflow.com/questions/38067704/how-to-change-the-datetime-format-in-pandas) – Corralien Apr 27 '21 at 14:01

3 Answers3

3
>>> import pandas as pd

>>> df = pd.DataFrame({'Date':['15/04/2021', '12/05/2021', '4/6/2021']})
>>> df
         Date
0  15/04/2021
1  12/05/2021
2    4/6/2021

>>> df['Date'] = pd.to_datetime(df['Date'])
>>> df
        Date
0 2021-04-15
1 2021-12-05
2 2021-04-06

>>> df['date_formated'] = df['Date'].dt.strftime('%b %d/%y').str.upper()
>>> df
        Date date_formated
0 2021-04-15     APR 15/21
1 2021-12-05     DEC 05/21
2 2021-04-06     APR 06/21

>>> df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3 entries, 0 to 2
Data columns (total 2 columns):
 #   Column         Non-Null Count  Dtype         
---  ------         --------------  -----         
 0   Date           3 non-null      datetime64[ns]
 1   date_formated  3 non-null      object        
dtypes: datetime64[ns](1), object(1)
memory usage: 176.0+ bytes
imdevskp
  • 2,103
  • 2
  • 9
  • 23
3

You need to convert your original Date column to datetime format (pandas will read dates as a strings by default). After doing so, just change the display format.

Try this:

# Convert to datetime
df['Date'] = pd.to_datetime(df['Date'], format='%d/%m/%y')

# Solution for title: change display to 'MMM dd/y'
df['Date'] = df['Date'].dt.strftime('%b %d/%y').astype(str).str.upper()

# Solution for comment: change display to 'MMM dd y'
df['Date'] = df['date'].dt.strftime('%b %d %y').astype(str).str.upper()
Arturo Sbr
  • 5,567
  • 4
  • 38
  • 76
1

Try -

df.loc['Date'] = pd.to_datetime(df.Date).dt.strftime('%b %d/%y').str.upper()
Nk03
  • 14,699
  • 2
  • 8
  • 22
  • Make sure the Data column contains no NAN values. If it does then handle them before using this :) – Nk03 Apr 27 '21 at 14:11
  • thanks! seems to work! however getting this msg: `SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead` – excelguy Apr 27 '21 at 14:21
  • You need to add .loc df.loc['Date'] – Nk03 Apr 27 '21 at 14:26
  • so `.loc df.loc['Date'] = ...` ? – excelguy Apr 27 '21 at 14:27
  • 1
    thanks works! but seems to have made 1 extra row to my dataframe with the word Date and the row with Nan's – excelguy Apr 27 '21 at 14:43
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/231656/discussion-between-nk03-and-excelguy). – Nk03 Apr 27 '21 at 14:43