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

Question

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).

@anon, did one of the below solutions help? If so, feel free to accept one (green tick on left), or ask further questions. — jpp
– jpp, Commented Mar 17, 2018 at 3:22

jpp · Accepted Answer · 2018-03-09 16:02:39Z

5

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

answered Mar 9, 2018 at 16:02

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scott Boston · Accepted Answer · 2020-03-26 01:29:34Z

4

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.

edited Mar 26, 2020 at 1:29

answered Mar 9, 2018 at 15:47

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Comments

anon · Accepted Answer · 2018-03-09 17:46:51Z

0

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!

edited Mar 9, 2018 at 17:46

answered Mar 9, 2018 at 17:40

anon

1692 gold badges3 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related