CODEWITHSUNDEEP
pythonpython-3.xpandastime-seriesdatetimeindex
anon
anon

Reputation: 169

Select Range of DatetimeIndex Rows Using .loc (Pandas Python 3)

Working with a pandas series with DatetimeIndex. Desired outcome is a dataframe containing all rows within the range specified within the .loc[] function.

When I try the following code:

aapl.index = pd.to_datetime(aapl.index)
print(aapl.loc[pd.Timestamp('2010-11-01'):pd.Timestamp('2010-12-30')])

I am returned:

Empty DataFrame
Columns: [Open, High, Low, Close, Volume, ExDividend, SplitRatio, 
AdjOpen, AdjHigh, AdjLow, AdjClose, AdjVolume]
Index: []

Just to re-iterate, my desired outcome is a subset of the dataframe, containing all rows the are within the range (2010-11-01):(2010-12-30).

Upvotes: 5

Views: 15658

Answers (3)

Scott Boston
Scott Boston

Reputation: 153510

IIUC:

import pandas_datareader as web
aapl = web.get_data_yahoo('aapl')

aapl.loc['2010-11-01':'2010-12-30']

Using partial string indexing and slicing.

Upvotes: 4

anon
anon

Reputation: 169

Out of curiosity I tried putting the most recent date as the start of the selection, and the less recent date as the end. To my surprise this worked but the Time series data was in reverse order.

In:

aapl.loc[pd.Timestamp('2010-12-30'):pd.Timestamp('2010-11-01')]

So... duh I realised my Timeseries data must be in reverse order. The question now becomes, how do I sort a DatetimeIndex df into the correct order?

Desired order would have nth date as the last row, and the earliest date as the first row.

******EDIT******

aapl.index = pd.to_datetime(aapl.index)
aapl =  aapl.sort_index(ascending=True)

aaplrange = aapl.loc[pd.Timestamp('2010-11-01'):pd.Timestamp('2010-12-30')]

Works!

Upvotes: 0

jpp
jpp

Reputation: 164793

It seems like you need to convert your index to datetime, then use standard indexing / slicing notation.

import pandas as pd, numpy as np

df = pd.DataFrame(list(range(365)))

# these lines are for demonstration purposes only
df['date'] = pd.date_range('2010-1-1', periods=365, freq='D').astype(str)
df = df.set_index('date')

df.index = pd.to_datetime(df.index)

res = df[pd.Timestamp('2010-11-01'):pd.Timestamp('2010-11-10')]

#               0
# date           
# 2010-11-01  304
# 2010-11-02  305
# 2010-11-03  306
# 2010-11-04  307
# 2010-11-05  308
# 2010-11-06  309
# 2010-11-07  310
# 2010-11-08  311
# 2010-11-09  312
# 2010-11-10  313

Upvotes: 5

Related Questions