Skip to content
Advertisement

What is the best practice to convert HTTP timestamps to standard format during dataframing using pandas in python?

I’m trying to convert HTTP timestamps into standard timestamp for complete data framing and getting time-series plots. I’m looking for an efficient way to do this for the large dataset. My actual data frame is as follows:

# +----------------------+-------+
# |       timestamp      |  col1 |
# +----------------------+-------+
# | 10/May/2021:19:48:36 |  714  |
# | 10/May/2021:00:28:12 |  None |
# | 10/May/2021:15:34:26 |  634  |
# | 10/May/2021:00:20:25 |  8453 |
# | 10/May/2021:14:31:32 |  None |
# +---------------------+-------+

I have tried couple of the following methods and get errors:

df["timestamp"] = pd.to_datetime(df['timestamp'], errors='coerce', format='%Y-%m-%d %H:%M:%S')
df["timestamp"] 

This returns me NaT which is strange!

0         NaT
1         NaT
2         NaT
3         NaT
4         NaT
           ..

I updated the format and change it from format='%Y-%m-%d %H:%M:%S' to format='%d/%b/%Y:%H:%M:%S' and worked and I can use df.plot(x='timestamp', y='col1'). Additionally I don’t see any changes/formatting in timestamps in plot:

img

The problem is when I plot it, timestamps related xticks in x axis will be plotted as they are but I couldn’t limited time stamps to hours in the same day. This is not practical for my plot when events are belong to a same day. I tried to access/filter hours by this:

df['timestamp'].dt.hour but it returns this error: AttributeError: Can only use .dt accessor with datetimelike values I searched this error and checks previous posts in this regard but didn’t work for my case like this answer.

Any help will be highly appreciated

Advertisement

Answer

Try via split(), join() and to_datetime() method:

df['timestamp']=pd.to_datetime(df['timestamp'].str.split(':',1).str.join(' '),errors='coerce')

output of df:

    timestamp               col1
0   2021-05-10 19:48:36     714
1   2021-05-10 00:28:12     None
2   2021-05-10 15:34:26     634
3   2021-05-10 00:20:25     8453
4   2021-05-10 14:31:32     None
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement