Can't convert DataFrame Index to datetime

I can’t convert my dataframe index (unix epoch time) to a datetime index. I’ve tried so many things as seen below, and still it’s not working.

# ...
for item in mongodb.find({"time": {"$gt": "2022-06-15 12:49:00"}}):
    if item["stock_price_onehour"] != "NaN":
        data = literal_eval(item["stock_price_onehour"])
        df = pd.DataFrame.from_dict(data)
        print(df.index)

>>> Index(['1655286600000', '1655286660000', '1655286720000', '1655286780000',
       '1655286840000', '1655286900000', '1655286960000', '1655287020000',
       '1655287080000', '1655287140000', '1655287200000', '1655287260000',
       '1655287320000', '1655287380000', '1655287440000', '1655287500000',
       '1655287560000', '1655287620000', '1655287680000', '1655287800000',
       '1655287860000', '1655287920000', '1655287980000', '1655288040000',
       '1655288100000', '1655288160000', '1655288220000', '1655288280000',
       '1655288340000', '1655288400000', '1655288460000', '1655288520000',
       '1655288580000', '1655288640000', '1655288700000', '1655288760000',
       '1655288820000', '1655288880000', '1655288940000', '1655289000000',
       '1655289060000', '1655289120000', '1655289209000'],
      dtype='object')

This is what I’ve tried and the errors that I get:

df.index = datetime.fromtimestamp(df.index).strftime("%Y-%m-%d %H:%M:%S")
>>> TypeError: an integer is required (got type Index)

df.index = pd.DatetimeIndex(df.index)
>>> TypeError: invalid string coercion to datetime
>>> During handling of the above exception, another exception occurred: OverflowError: signed integer is greater than maximum

df.index = pd.to_datetime(df.index)
>>> TypeError: invalid string coercion to datetime
>>> During handling of the above exception, another exception occurred: OverflowError: signed integer is greater than maximum

df.index = pd.to_datetime(df.index.astype(str), errors="coerce")
>>> # prints NaT instead of datetime as index

How could this be fixed?

>Solution :

Your Index are expressed in ms. So you have to specify the unit:

df.index = pd.to_datetime(df.index, unit='ms')

Leave a Reply