pd.to_datetime from int64 is giving 1970s for dates-CodePudding

I am reading ohlc from sqlite3, and my index is of type int64:

                           index   open   high    low  close   volume  trade_count       vwap ticker
30273  2022-09-23 10:11:00 00:00  22.48  22.63  22.40  22.41  12289.0         13.0  22.417248    BKR

When I print the df.index I get:

        ...
        23127, 23128, 23129, 23130, 23131, 23132, 23133, 23134, 23135,
        23136],
       dtype='int64', length=2427)

I then convert the index to a datetime:

df = pd.read_sql_query("SELECT * from ohlc_minutes", conn)
df.index = pd.to_datetime(df.index, unit='s')

Which gives me:

           ...
           '1970-01-03 16:15:48', '1970-01-03 16:15:49',
           '1970-01-03 16:15:50', '1970-01-03 16:15:51',
           '1970-01-03 16:15:52', '1970-01-03 16:15:53',
           '1970-01-03 16:15:54', '1970-01-03 16:15:55',
           '1970-01-03 16:15:56', '1970-01-03 16:15:57'],
          dtype='datetime64[ns]', length=2629, freq=None)

Which converts my date to 1970.

I just want it to convert to a normal datetime, using all the information that appears in the index of my df, e.g:

2022-09-23 10:11:00 00:00

UPDATE

Tried using .loc as suggested by jkr:

df.loc['index'] =  pd.to_datetime(df.loc['index'], unit='s')

Which gives the error:

Traceback (most recent call last):
  File "/home/dan/Documents/code/wolfhound/vectorbt.py", line 19, in <module>
    df.loc['index'] =  pd.to_datetime(df.loc['index'], unit='s')
  File "/home/dan/.local/lib/python3.10/site-packages/pandas/core/indexing.py", line 967, in __getitem__
    return self._getitem_axis(maybe_callable, axis=axis)
  File "/home/dan/.local/lib/python3.10/site-packages/pandas/core/indexing.py", line 1205, in _getitem_axis
    return self._get_label(key, axis=axis)
  File "/home/dan/.local/lib/python3.10/site-packages/pandas/core/indexing.py", line 1153, in _get_label
    return self.obj.xs(label, axis=axis)
  File "/home/dan/.local/lib/python3.10/site-packages/pandas/core/generic.py", line 3864, in xs
    loc = index.get_loc(key)
  File "/home/dan/.local/lib/python3.10/site-packages/pandas/core/indexes/range.py", line 389, in get_loc
    raise KeyError(key)
KeyError: 'index'

CodePudding user response：

Problem is index is column, so first convert it to df.index, also omit unit='s':

#index column to df.index
df = df.set_index('index')
#convert to DatetimeIndex
df.index = pd.to_datetime(df.index)

Or:

#convert index column to datetimes
df['index'] = pd.to_datetime(df['index'])
#convert column index to df.index
df = df.set_index('index')

CodePudding user response：

to_datetime usses per default POSIX time (see doc If 'unix' (or POSIX) time; origin is set to 1970-01-01.)

You can change either specify the origin or give an offset if you have POSIX time but without year (relative)

What format has your input?

CodePudding user response：

This is because the int is the number of seconds since epoch. See for example https://www.epochconverter.com/