CODEWITHSUNDEEP
pythonpandasdatetime
nish
nish

Reputation: 271

how to convert Date and time column of dataframe to datetime format of pandas?

I have a DateTime column in my dataframe df as shown below:

   DateTime
3/1/2016 12:15:00 AM    
3/1/2016 12:30:00 AM    
3/1/2016 12:45:00 AM    
3/1/2016 1:00:00 AM 
3/1/2016 1:15:00 AM 
3/1/2016 1:30:00 AM 
3/1/2016 1:45:00 AM 
3/1/2016 2:00:00 AM     
3/1/2016 2:15:00 AM

I would like to convert it into the following format, i.e. 24 hours format as shown below:

03-01-2016 12:15:00

How can i do this?

Upvotes: 2

Views: 4738

Answers (2)

jezrael
jezrael

Reputation: 862511

You can only use to_datetime:

print (df)
               DateTime
0  3/1/2016 12:15:00 AM
1  3/1/2016 12:30:00 AM
2  3/1/2016 12:45:00 AM
3   3/1/2016 1:00:00 AM
4   3/1/2016 1:15:00 AM
5   3/1/2016 1:30:00 AM
6   3/1/2016 1:45:00 AM
7   3/1/2016 2:00:00 AM
8  3/1/2016 2:15:00 PM  <-date is changed for better testing

df.DateTime = pd.to_datetime(df.DateTime)
print (df)
             DateTime
0 2016-03-01 00:15:00
1 2016-03-01 00:30:00
2 2016-03-01 00:45:00
3 2016-03-01 01:00:00
4 2016-03-01 01:15:00
5 2016-03-01 01:30:00
6 2016-03-01 01:45:00
7 2016-03-01 02:00:00
8 2016-03-01 14:15:00

EDIT:

Then need parameter errors='coerce' for replace problematic values to NaT:

print (df)
               DateTime
0  3/1/2016 28:15:00 AM <- wrong date
1  3/1/2016 12:30:00 AM
2  3/1/2016 12:45:00 AM
3   3/1/2016 1:00:00 AM
4   3/1/2016 1:15:00 AM
5   3/1/2016 1:30:00 AM
6   3/1/2016 1:45:00 AM
7   3/1/2016 2:00:00 AM
8  3/1/2016 2:15:00 PM 


df.DateTime = pd.to_datetime(df.DateTime, errors='coerce')
print (df)
             DateTime
0                 NaT
1 2016-03-01 00:30:00
2 2016-03-01 00:45:00
3 2016-03-01 01:00:00
4 2016-03-01 01:15:00
5 2016-03-01 01:30:00
6 2016-03-01 01:45:00
7 2016-03-01 02:00:00
8 2016-03-01 14:15:00

For checking problematic values use boolean indexing:

print (df[pd.to_datetime(df.DateTime, errors='coerce').isnull()])
                DateTime
0  3/1/2016 28:15:00 AM

Upvotes: 3

Alex Fung
Alex Fung

Reputation: 2006

This should work :

df["DateTime"] = pd.to_datetime(df["DateTime"],errors="coerce").dt.strftime("%d-%m-%Y %H:%M:%S")

print (df["DateTime"])

Output:

0    01-03-2016 00:15:00
1    01-03-2016 00:30:00
2    01-03-2016 00:45:00
3    01-03-2016 01:00:00
4    01-03-2016 01:15:00
5    01-03-2016 01:30:00
6    01-03-2016 01:45:00
7    01-03-2016 02:00:00
8    01-03-2016 02:15:00
Name: DateTime, dtype: object

Upvotes: 4

Related Questions