How to change datetime format in dataframe with using pandas?

Question

I'm a pandas learner.

I have a dataframe with the column 'DATE', the datetime format of the column is like '11/1/2017 1:00'. I want to change the datetime format from '11/1/2017 1:00' to '1-Dec-17 1:00', I tried the following code:

dir_path = os.path.dirname(os.path.realpath("__file__"))
print(dir_path)

def parse_dates(x):
    return datetime.strptime(x, "%d-%b-%y %H:%M")

df = pd.read_csv(dir_path+"/TEST.csv", parse_dates=['DATE'],date_parser=parse_dates)

But it shows error:

ValueError: time data '11/1/2017 1:00' does not match format '%d-%b-%y %H:%M'

I also tried to convert the dataframe, but failed:

df=pd.read_csv(dir_path+"/TEST.csv")
df['DATE'] = pd.to_datetime(df['DATE'],format='%d-%b-%y %H:%M')

Again, it shows error:

ValueError: time data '11/1/2017 1:00' does not match format '%d-%b-%y %H:%M' (match)

Solution 1

df=pd.DataFrame({'Time':[ '11/1/2017 1:00', '11/1/2017 1:00', '11/1/2017 1:00', '11/1/2017 1:00']})
df.Time=pd.to_datetime(df.Time).dt.strftime('%d-%b-%y %H:%M')
df
Out[870]: 
              Time
0  01-Nov-17 01:00
1  01-Nov-17 01:00
2  01-Nov-17 01:00
3  01-Nov-17 01:00

Solution 2

The simple answer is that your format argument to the strptime function is wrong. You want datetime.strptime(x, "%m-%d-%Y %H:%M").

Also, make sure that all of your numbers are padded (i.e. for the month, make sure it is 01 for Jan instead of 1. Same idea for minutes and days), otherwise this may fail.

I would recommend you take a look at the python page for strptime formatting in order to learn more about how to format dates.

Comments

[Haven Shi]

that helps. Thanks Wen.

[BENY]

@HavenShi yw :-)

[Jo_]

That did it for me! Thanks for you solution!