Python: pandas data_reader -> plotly candelstick graph not recognizing date column from tiingo stock data api import

Question

The task was to import stock data from tiingo api via pandas data_reader within a certain date range and then plot it into a candle stick graph. The import works. However the plotting is not recognizing the "date" as x axis variable. See the error message at the end.

Code:

import os
import pandas_datareader as dr
import pandas as pd
import plotly.graph_objects as go
from datetime import datetime
start = datetime(2019, 11, 1)
end = datetime(2020, 10, 31)
my_api_key = os.environ.get("TIINGO_API_KEY")
stock_df = dr.get_data_tiingo('TTWO', start=start, end=end, api_key= my_api_key)
fig = go.Figure(data=[go.Candlestick(x=stock_df['date'],
               open=stock_df['open'],
               high=stock_df['high'],
               low=stock_df['low'],
               close=stock_df['close'])])

fig.show()

Error Message:

    ---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
~\miniconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2894             try:
-> 2895                 return self._engine.get_loc(casted_key)
   2896             except KeyError as err:

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\index.pyx in pandas._libs.index.IndexEngine.get_loc()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

pandas\_libs\hashtable_class_helper.pxi in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'date'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
<ipython-input-5-e6052ae4135a> in <module>
----> 1 fig = go.Figure(data=[go.Candlestick(x=stock_df['date'],
      2                open=stock_df['open'],
      3                high=stock_df['high'],
      4                low=stock_df['low'],
      5                close=stock_df['close'])])

~\miniconda3\lib\site-packages\pandas\core\frame.py in __getitem__(self, key)
   2900             if self.columns.nlevels > 1:
   2901                 return self._getitem_multilevel(key)
-> 2902             indexer = self.columns.get_loc(key)
   2903             if is_integer(indexer):
   2904                 indexer = [indexer]

~\miniconda3\lib\site-packages\pandas\core\indexes\base.py in get_loc(self, key, method, tolerance)
   2895                 return self._engine.get_loc(casted_key)
   2896             except KeyError as err:
-> 2897                 raise KeyError(key) from err
   2898 
   2899         if tolerance is not None:

KeyError: 'date'

To give you a bit more context of the dataframe stock_df here are the first five raws. The index and the columns Input of

stock_df.head()

Shows:

first five columns of stock_df

Input of

stock_df.columns

Shows:

Index(['close', 'high', 'low', 'open', 'volume', 'adjClose', 'adjHigh',
       'adjLow', 'adjOpen', 'adjVolume', 'divCash', 'splitFactor'],
      dtype='object')

Input of:

stock_df.index

Shows:

MultiIndex([('TTWO', '2019-11-01 00:00:00+00:00'),
            ('TTWO', '2019-11-04 00:00:00+00:00'),
            ('TTWO', '2019-11-05 00:00:00+00:00'),
            ('TTWO', '2019-11-06 00:00:00+00:00'),
            ('TTWO', '2019-11-07 00:00:00+00:00'),
            ('TTWO', '2019-11-08 00:00:00+00:00'),
            ('TTWO', '2019-11-11 00:00:00+00:00'),
            ('TTWO', '2019-11-12 00:00:00+00:00'),
            ('TTWO', '2019-11-13 00:00:00+00:00'),
            ('TTWO', '2019-11-14 00:00:00+00:00'),
            ...
            ('TTWO', '2020-10-19 00:00:00+00:00'),
            ('TTWO', '2020-10-20 00:00:00+00:00'),
            ('TTWO', '2020-10-21 00:00:00+00:00'),
            ('TTWO', '2020-10-22 00:00:00+00:00'),
            ('TTWO', '2020-10-23 00:00:00+00:00'),
            ('TTWO', '2020-10-26 00:00:00+00:00'),
            ('TTWO', '2020-10-27 00:00:00+00:00'),
            ('TTWO', '2020-10-28 00:00:00+00:00'),
            ('TTWO', '2020-10-29 00:00:00+00:00'),
            ('TTWO', '2020-10-30 00:00:00+00:00')],
           names=['symbol', 'date'], length=252) 

Answer 1

Thanks to the option given by @r-beginners I found the solution. To call a column within a multiindex the right code for my question is:

.index.get_level_values

Including that in my code to plot the candlestick graph the error message disapeared: Corrected code for the candlestick graph:

fig = go.Figure(data=[go.Candlestick(x=stock_df.index.get_level_values('date'),
               open=stock_df['open'],
               high=stock_df['high'],
               low=stock_df['low'],
               close=stock_df['close'])])

fig.show()

More detail in the Pandas Documentation on .index.get_level_values

Answer 2

The stock information I got from AlphaVantage's API in the data reader is indexed by date. tiingo would be the same, so the 'date' specification needs to be corrected to stock_df.index.

stock_df
            open    high    low     close   volume
2019-11-01  121.28  121.740     116.800     116.90  1984390
2019-11-04  118.27  118.400     116.620     117.20  1692870
2019-11-05  117.99  118.480     116.220     117.25  1492332
2019-11-06  116.99  117.710     115.310     116.68  1491648
2019-11-07  117.86  118.410     115.450     116.91  2795812
....

fig = go.Figure(data=[go.Candlestick(x=stock_df.index,
               open=stock_df['open'],
               high=stock_df['high'],
               low=stock_df['low'],
               close=stock_df['close'])])