CODEWITHSUNDEEP
pythonpandasdataframe
lukehawk
lukehawk

Reputation: 1493

In pandas, how to get row from timestamp index?

I am trying to use quantopian. It is frustrating the hell out of me.

I have this:

import pandas as pd
import numpy as np
spy_minute_opens = get_pricing(
    'SPY', fields='open_price',
    start_date='2005-01-01', end_date = '2017-04-01', 
    frequency='minute')
spy_minute_opens.index.tz = 'US/Eastern'
spy_minute_opens = spy_minute_opens.to_frame()
spy_5min = spy_minute_opens.groupby(pd.TimeGrouper('5T')).agg(['first'])
spy_5min.columns = ['SPY']

This produces the following:

spy_5min.head(5)
                               SPY
2005-01-03 09:30:00-05:00   95.507
2005-01-03 09:35:00-05:00   95.531
2005-01-03 09:40:00-05:00   95.625
2005-01-03 09:45:00-05:00   95.547
2005-01-03 09:50:00-05:00   95.586

I am trying to get the row of the minimum value. I am getting a keyError.

spy_5min.idxmin()

SPY   2009-03-06 15:10:00-05:00
dtype: datetime64[ns, US/Eastern]

spy_5min[spy_5min.idxmin()]

KeyError: "['2009-03-06T20:10:00.000000000'] not in index"

Any help?!?!

Upvotes: 0

Views: 2505

Answers (1)

akuiper
akuiper

Reputation: 214927

You are calling idxmin on the data frame which gives back a series instead of an index value, call it on SPY column instead:

spy_5min.idxmin()
#SPY    2005-01-03 09:30:00-05:00
#dtype: object

spy_5min.SPY.idxmin()
#'2005-01-03 09:30:00-05:00'

Also as @MaxU commented, use loc to extract the row with specific index:

spy_5min.loc[spy_5min.SPY.idxmin()]
#SPY    95.507
#Name: 2005-01-03 09:30:00-05:00, dtype: float64

Upvotes: 2

Related Questions