CODEWITHSUNDEEP
pythonpandasdataframe
Msquare
Msquare

Reputation: 835

Select all rows from Time Series data for a given day (by timestamp)

My dataframe has timestamps index and a column of data for many days. I would like to retrieve the data of particular days. 

raw_df = 
                            Temp
TIMESTAMP                       
2015-01-01 09:50:00-05:00  0.064
2015-01-01 09:51:00-05:00  0.236
2015-01-01 09:52:00-05:00  0.268
2015-01-01 09:53:00-05:00  0.072
2015-01-01 10:07:00-05:00  0.059
2015-01-02 10:08:00-05:00  0.189
2015-01-02 10:09:00-05:00  0.198
2015-01-02 10:10:00-05:00  0.258
2015-01-02 10:11:00-05:00  0.587
2015-01-03 10:12:00-05:00  0.780
2015-01-03 10:13:00-05:00  0.990
2015-01-03 10:14:00-05:00  1.022
2015-01-03 10:15:00-05:00  1.275
2015-01-04 10:16:00-05:00  1.256
2015-01-04 10:17:00-05:00  1.255
2015-01-04 10:18:00-05:00  1.254
2015-01-04 10:19:00-05:00  1.305
2015-01-04 10:20:00-05:00  1.347
2015-01-04 10:21:00-05:00  1.434
2015-01-05 10:22:00-05:00  1.397

I would like to extract the data on 2015-01-04 and my code is given below

i = 2015-01-04
raw_df['Temp'].loc[i]

But this gives an error 

SyntaxError: invalid token

Upvotes: 0

Views: 1704

Answers (1)

cs95
cs95

Reputation: 402483

There are a multitude of options but all of them will need to bridge the gap between the datetime index and the date string.

Boolean Indexing

# df[df.index.tz_localize(None).floor('D') == '2015-01-04']
df[df.index.floor('D') == '2015-01-04']
# Or,
df[df.index.normalize() == '2015-01-04']

Or, to select just "Temp", use loc:

df.loc[df.index.floor('D') == '2015-01-04', 'Temp']

TIMESTAMP
2015-01-04 10:16:00-05:00    1.256
2015-01-04 10:17:00-05:00    1.255
2015-01-04 10:18:00-05:00    1.254
2015-01-04 10:19:00-05:00    1.305
2015-01-04 10:20:00-05:00    1.347
2015-01-04 10:21:00-05:00    1.434
Name: Temp, dtype: float64

Slicing with Index.get_loc

# df.iloc[df.index.get_loc('2015-01-04')]
df.iloc[df.index.get_loc('2015-01-04'), df.columns.get_loc('Temp')]

TIMESTAMP
2015-01-04 10:16:00-05:00    1.256
2015-01-04 10:17:00-05:00    1.255
2015-01-04 10:18:00-05:00    1.254
2015-01-04 10:19:00-05:00    1.305
2015-01-04 10:20:00-05:00    1.347
2015-01-04 10:21:00-05:00    1.434
Name: Temp, dtype: float64

Upvotes: 3

Related Questions