Get value based on index data frame in python

Question

data2 = pd.read_excel(fpath2,header = [0,1,2])
a = data2[1][0] #1 represents month - Jan, 0 represents whether it is weekday

And the results was like this:

Timestamp       L       U
00:00:00   239.90  394.04
01:00:00   228.30  400.57
02:00:00   195.42  395.23
03:00:00   166.02  390.32
04:00:00   143.73  373.59
05:00:00   135.43  355.78
06:00:00    79.48  359.10
........................

What I want to do is to get the L(lower band) and U(upper band) values based on a specific time, e.g. at 5 a.m. It would be very appreciated if anyone could help me with that.

The file loaded was like this:

                           1                 # First header
                  0        |       1         # Second haeder
Timestamp    L    |   U       L    |   U     # Third header
0:00       239.9    394.04  252.9   344.22
1:00       228.3    400.57  240.34  323.33
2:00       195.42   395.23  214.07  288.09
3:00       166.02   390.32  183.05  262.1
4:00       143.73   373.59  158.42  244.5
5:00       135.43   355.78  136.85  419.55
6:00       79.48    359.1   126.33  597.67

I tried

a = data2[1][0][5:00]

But it gave me the result of empty dataframe

Empty DataFrame
Columns: [L, U]
Index: []

It was even weird when I tried a = data2[5:00], it gave me:

Empty DataFrame
Columns: [(1, 0, L), (1, 0, U), (1, 1, L), (1, 1, U), (2, 0, L), (2, 0, U), (2, 1, L), (2, 1, U), (3, 0, L), (3, 0, U), (3, 1, L), (3, 1, U), (4, 0, L), (4, 0, U), (4, 1, L), (4, 1, U), (5, 0, L), (5, 0, U), (5, 1, L), (5, 1, U), (6, 0, L), (6, 0, U), (6, 1, L), (6, 1, U), (7, 0, L), (7, 0, U), (7, 1, L), (7, 1, U), (8, 0, L), (8, 0, U), (8, 1, L), (8, 1, U), (9, 0, L), (9, 0, U), (9, 1, L), (9, 1, U), (10, 0, L), (10, 0, U), (10, 1, L), (10, 1, U), (11, 0, L), (11, 0, U), (11, 1, L), (11, 1, U), (12, 0, L), (12, 0, U), (12, 1, L), (12, 1, U)]
Index: []

[0 rows x 48 columns]

Did you try data2["5:00"] (notice the quotes)? This should work if your index isnt a datetime object and there is only one 5:00 row — krflol, Jul 31 '18 at 20:25

Hermis14 · Answer 1 · 2018-07-31T20:30:30.940

0

5:00 does not indicate the time.

Here's a relevant example.

a = range(10)
print(a[5:00])
print(list(a[5:00]))

gives

range(5,0)
[]

Try '5:00' instead.

edited Jul 31 '18 at 20:30

answered Jul 31 '18 at 20:25

Hermis14

214
3
12

sundance · Answer 2 · 2018-07-31T20:35:52.260

0

a[5:00] is slicing the datetframe. You want to index it using a time type or a string, depending on the type of the Timestamp column:

import datetime
a = a.set_index("Timestamp")

# if it's a string:
a.ix["05:00:00"]

# if it's a timestamp:
a.ix[datetime.time(5)]

edited Jul 31 '18 at 20:35

answered Jul 31 '18 at 20:26

sundance

2,905
4
21
31

score 0 · Accepted Answer · edited Jul 31 '18 at 20:49

If the index is a datetime object, you'll likely need to use a datetime object to reference the desired row.

For example, you can try something like:

your_time = pd.to_datetime(5, unit = 'h')

Then, again assuming that the index is a datetime, you can do a standard query like:

df[df.index == your_time]

If your index is not a datetime, try making it a datetime first:

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

Get value based on index data frame in python

3 Answers3