CODEWITHSUNDEEP
pythonpandasdataframedatetimetime-series
ak40837
ak40837

Reputation: 13

How do you convert TD Ameritrade's API time stamp to pandas datetime?

I am trying to use a pandas dataframe to create a time series visualization from stock price data I pulled from TD Ameritrade's API. In order to do this, I've been trying to convert the timestamps in the datetime column of my dataframe to datetime objects. This way, I can set datetime column as the new index and have visualization with a cleanly formatted x-axis.

       open    high     low   close  volume       datetime
0    336.89  336.90  336.69  336.77   26232  1599822000000
1    336.90  337.05  336.69  336.92   13180  1599822300000
2    336.98  337.24  336.98  337.23   31810  1599822600000
3    337.01  337.25  337.00  337.15    8749  1599822900000
4    337.10  337.10  336.70  336.70    9664  1599823200000
..      ...     ...     ...     ...     ...            ...

I've tried achieving this via the advice in this thread to no avail. I've also tried using the following code:

df['adj_datetime'] = pd.to_datetime((df['datetime']/1000))

However, this is the result:

       open    high  ...       datetime                  adj_datetime
0    336.89  336.90  ...  1599822000000 1970-01-01 00:00:01.599822000
1    336.90  337.05  ...  1599822300000 1970-01-01 00:00:01.599822300
2    336.98  337.24  ...  1599822600000 1970-01-01 00:00:01.599822600
3    337.01  337.25  ...  1599822900000 1970-01-01 00:00:01.599822900
4    337.10  337.10  ...  1599823200000 1970-01-01 00:00:01.599823200
..      ...     ...  ...            ...                           ...

This is not the result I am looking for because 1.) this data is from 2020-09-11 and 2.) this is not extended hours trading data so 12:01 AM is not the appropriate time.

Any advice, feedback, or additional resources would be greatly appreciated!

Upvotes: 1

Views: 2638

Answers (1)

yibo
yibo

Reputation: 527

Problem

Default time unit in pd.to_datetime is in nanosecond (ns) but your datetime column has timestamps in millisecond (ms).

Solution

Specify parameter unit=ms so pd.to_datetime interprets timestamps correctly.

>>> pd.to_datetime(1599822000000, unit='ms')
Timestamp('2020-09-11 11:00:00')

Could also use pd.Timestamp

>>> pd.Timestamp(1599822000000, unit='ms')
Timestamp('2020-09-11 11:00:00')

Upvotes: 4

Related Questions