convert pandas df to json then parse dates

Question

I have a pandas df like this below with a Time Block index column and a Payload column that is an int:

    Payload
Time Block  
2021-08-20 00:00:00 1
2021-08-20 00:15:00 2
2021-08-20 00:30:00 3
2021-08-20 00:45:00 4
2021-08-20 01:00:00 5

Pandas to json, it seems to automatically convert to epoch time:

result = df.to_json(orient="index")

Looks like this:

'{"1629417600000":{"Payload":1},"1629418500000":{"Payload":2},"1629419400000":{"Payload":3},"1629420300000":{"Payload":4},"1629421200000":{"Payload":5}}`

parsing the json data:

import json

parsed = json.loads(result)

Looks like this:

{'1629417600000': {'Payload': 1},
 '1629418500000': {'Payload': 2},
 '1629419400000': {'Payload': 3},
 '1629420300000': {'Payload': 4},
 '1629421200000': {'Payload': 5}}

What I cant figure out is how do I convert the original time block column back into datetime?

For example the first date is 1629417600000, if I try:

from datetime import datetime


epoch_time = 1629417600000
datetime_time = datetime.fromtimestamp(epoch_time)

This will throw an error OSError: [Errno 22] Invalid argument

Is there anything that should be done to the Pandas time block column after the json data is parsed?

If I do:

import time

time.time()

Looks like this below a bit different that how pandas packaged my date time index to json:

1629571434.5085876

The time.time() also parses just fine too as shown below.

epoch_time = time.time()
datetime_time = datetime.fromtimestamp(epoch_time)

Any tips greatly appreciated. Its almost like I need to divide my pandas epoch values by 1000 but I am not entirely sure how epoch time is calculated to know if this would work OK.

The time you are getting is in Unix time format. https://stackoverflow.com/a/3682808/2681662 — MSH, Aug 21 '21 at 18:57
Ah thanks, whats the difference between unix and epoch time? I know epoch is a time value that can be calculated since a date in 1970, right? — bbartling, Aug 21 '21 at 19:11

not_speshal · Accepted Answer · 2021-08-21T19:28:56.890

The function fromtimestamp takes seconds as input and you're providing milliseconds.

Dividing by 1000 is exactly what you need to do (and will work as needed). You may however need to use the utcfromtimestamp function instead:

fromtimestamp gives you the date and time in local time
utcfromtimestamp gives you the date and time in UTC.

from datetime import datetime
>>> [datetime.utcfromtimestamp(int(ts)/1000) for ts in parsed]
[datetime.datetime(2021, 8, 20, 0, 0),
 datetime.datetime(2021, 8, 20, 0, 15),
 datetime.datetime(2021, 8, 20, 0, 30),
 datetime.datetime(2021, 8, 20, 0, 45),
 datetime.datetime(2021, 8, 20, 1, 0)]

convert pandas df to json then parse dates

1 Answers1