CODEWITHSUNDEEP
pandas
Giladbi
Giladbi

Reputation: 1894

How to set an index within multiindex for datetime?

I have this df:

                      open     high      low    close    volume
date       symbol                                              
2014-02-20 AAPL    69.9986  70.5252  69.4746  69.7569  76529103
           MSFT    33.5650  33.8331  33.4087  33.7259  27541038
2014-02-21 AAPL    69.9727  70.2061  68.8967  68.9821  69757247
           MSFT    33.8956  34.2619  33.8241  33.9313  38030656
2014-02-24 AAPL    68.7063  69.5954  68.6104  69.2841  72364950
           MSFT    33.6723  33.9269  33.5382  33.6723  32143395

which is returned from here:

from datetime import datetime
from iexfinance.stocks import get_historical_data
from pandas_datareader import data
import matplotlib.pyplot as plt
import pandas as pd

start =  '2014-01-01'
end = datetime.today().utcnow()
symbol = ['AAPL', 'MSFT']

prices = pd.DataFrame()
datasets_test = []
for d in symbol:
    data_original = data.DataReader(d, 'iex', start, end)
    data_original['symbol'] = d
    prices = pd.concat([prices,data_original],axis=0)
prices = prices.set_index(['symbol'], append=True)
prices.sort_index(inplace=True)

when trying to get the day of the week:

A['day_of_week'] = features.index.get_level_values('date').weekday

I get error:

AttributeError: 'Index' object has no attribute 'weekday'

I tried to change the date index to date time with

prices['date'] = pd.to_datetime(prices['date'])

but got this error:

KeyError: 'date'

Any idea how to keep 2 indexs, date + symbol but to change one of them (date) tp datetime so I could get the day of the week?

Upvotes: 0

Views: 587

Answers (1)

Peter Leimbigler
Peter Leimbigler

Reputation: 11105

Looks like the date level of the index contains strings, not datetime objects. One solution is to reset all MultiIndex levels into columns, convert the date column to datetime, and set the MultiIndex back. Then you can proceed with pandas datetime accessors like .weekday in the usual way.

prices = prices.reset_index()
prices['date'] = pd.to_datetime(prices['date'])
prices = prices.set_index(['date', 'symbol'])

prices.index.get_level_values('date').weekday
Int64Index([3, 3, 4, 4, 0, 0, 1, 1, 2, 2,
            ...
            1, 1, 2, 2, 3, 3, 4, 4, 1, 1],
           dtype='int64', name='date', length=2516)

Upvotes: 1

Related Questions