CODEWITHSUNDEEP
pythonpandasdatetimeindexing
BillyRay
BillyRay

Reputation: 413

How to get last n-number of rows with datetime index in a pandas dataframe?

I am trying to get the last 32 data points from a pandas dataframe indexed by date. I have multiple re-sampled dataframes numbered data1, data2, data3, ect... that have been re-sampled from 1 hour, 4 hour, 12 hour, 1 day.

I already tried to use get_loc with the datetime index that I want to end on for each dataframe but the problem is that my datetime index is sampled differently so the datetime index is off by a few hours. I also tried to just subtract the equivalent hours from datetime but this does not guarantee 32 data points

from datetime import timedelta
import pandas as pd

data1 = data.resample('4H').last().ffill()
data2 = data.resample('6H').last().ffill()
data3 = data.resample('12H').last().ffill()
data4 = data.resample('1D').last().ffill()

# datetime I want to end my row with and get last 32 values
end_index = pd.Timestamp("2019-02-27 00:00:00+00:00")

# this method does not always guarantee 32 data points
b = data1.loc[end_index - timedelta(hours=192): end_index].bfill().ffill()
c = data2.loc[end_index - timedelta(hours=380): end_index].bfill().ffill()
d = data3.loc[end_index - timedelta(hours=768): end_index].bfill().ffill()
e = data4.loc[end_index - timedelta(hours=768): end_index].bfill().ffill()

# this method throws an error because end_index is off by a few hours sometimes
pos = data1.index.get_loc(end_index)
b = data1.loc[pos - 32: pos].bfill().ffill()

pos = data2.index.get_loc(end_index)
c = data2.loc[pos - 32: pos].bfill().ffill()

pos = data3.index.get_loc(end_index)
d = data3.loc[pos - 32: pos].bfill().ffill()

pos = data2.index.get_loc(end_index)
e = data4.loc[pos - 32: pos].bfill().ffill()

KeyError: 1498208400000000000 During handling of the above exception, another exception occurred:

Upvotes: 2

Views: 2465

Answers (2)

BillyRay
BillyRay

Reputation: 413

As Code Different suggested, using .tail(32) with loc indexing works!

b = data1.loc[: test_index].bfill().ffill().tail(32)
c = data2.loc[: test_index].bfill().ffill().tail(32)
d = data3.loc[: test_index].bfill().ffill().tail(32)
e = data4.loc[: test_index].bfill().ffill().tail(32)

Upvotes: 0

jezrael
jezrael

Reputation: 863531

I think you need iloc for select by positions:

pos = data2.index.get_loc(end_index)
c = data2.iloc[pos - 32: pos].bfill().ffill()

pos = data3.index.get_loc(end_index)
d = data3.iloc[pos - 32: pos].bfill().ffill()

pos = data2.index.get_loc(end_index)
e = data4.iloc[pos - 32: pos].bfill().ffill()

Upvotes: 2

Related Questions