How do I stop pandas dataframe.resample('T') from automatically adding extra indexes to dataframe?

Question

I'm trying to downsample a dataframe that has minute by minute data into 5 minute bins. Here is my current code:

df = pd.read_csv('stockPrices/closingPrices-apr3.csv',index_col='date',parse_dates=True)
df['close'] = df['close'].shift()
df5min = df.resample('5T').last()
print(df5min.tail())

The link to the csv file is here: https://drive.google.com/file/d/1uvkUaJwrQNsmte5IQIsJ_g5GS8RjVd8B/view?usp=sharing

The output should stop at 2019-04-03 14:40:00 because the last value is 14:48:00, and a 5 minute bin from 14:45-14:49 is not possible. However, I get the following datetime index values that don't exist in my csv file:

2019-04-03 14:45:00  286.35
2019-04-03 14:50:00  286.52
2019-04-03 14:55:00  286.32
2019-04-03 15:00:00  286.45
2019-04-03 15:05:00  280.64

The only fix I can find thus far is using the following code, but then all my data from the previous days get cut off at 14:40:

df5min = df.resample('5T').last().between_time(start_time='9:30',end_time='14:40')

Any help on this is appreciated.

Hi. I'm trying to see where you found a 15:08:00 value(since that would solve my problem). I checked my csv file I can only see 14:48 value on the second line. — way_of_the_fist, Apr 03 '19 at 21:48
I would like the last index to be 14:40 (representing the values of 14;40-14:44). I've organized this so that the very first index of 9:30:00 holds the last value of the indexes 9:30-9:34:00, or the close of a 5 minute "candle". I'm using this to quickly compare data between a candle chart and my personally collected data. — way_of_the_fist, Apr 03 '19 at 22:01
ok so for 2019-04-03 14:40:00 what would be the correct number? for all those, they should be the close that is 5 minutes later? — Aiden Zhao, Apr 03 '19 at 22:18
originally 2019-04-03 14:40:00 has value of 286.3200, you want 286.2100 instead? — Aiden Zhao, Apr 03 '19 at 22:23
yes, 2019-04-03 14:40:00 would be 286.21 on a 5min resampled df. — way_of_the_fist, Apr 03 '19 at 22:26
ok edited my answer try it out, should be what you are looking for now. — Aiden Zhao, Apr 03 '19 at 22:28
oh hey, your data actually stops at 2019-04-03 15:08 286.33. your csv is actually not sorted. — Aiden Zhao, Apr 03 '19 at 23:04
Ok, thanks for pointing that out. My api appended the data instead of writing it fresh to the file. Thanks for your help. — way_of_the_fist, Apr 04 '19 at 00:03

Aiden Zhao · Accepted Answer · 2019-04-03T23:11:26.980

0

the solution will produce a row that you probably don't want for 4/3/2018 15:05

df = pd.read_csv('./closingPrices-apr3.csv', index_col='date',parse_dates=True)
df.sort_index(inplace = True)
df = df.shift(5)
df_5min = df.resample('5T').first()

edited Apr 03 '19 at 23:11

answered Apr 03 '19 at 21:51

Aiden Zhao

633
4
15

Thank you for this fix, I'll use it for the time being. Does the .resample() method have a bug that we need such an elaborate workaround? – way_of_the_fist Apr 03 '19 at 22:35
not sure if it was a bug, I tried it and it didn't do what I thought it would do. – Aiden Zhao Apr 03 '19 at 22:36
I think your older solution worked better. There shouldn't be any values after 14:48:00. I get values at 14:50 - 15:05 in 5 minute increments with your new solutions, none of which appear on the csv – way_of_the_fist Apr 03 '19 at 23:20
actually your file is not sorted, they sure look like sorted, but try to sort it yourself. or try to find the max of the index. df[np.array(list(df.index)) == max(list(df.index))] – Aiden Zhao Apr 03 '19 at 23:59
look at row 13139, 3755,5630,15033, empty values and data after 14:48:00 – Aiden Zhao Apr 04 '19 at 00:04
not only that, your data has duplicates, so we should also remove duplicates first then do the shift – Aiden Zhao Apr 04 '19 at 00:06

How do I stop pandas dataframe.resample('T') from automatically adding extra indexes to dataframe?

1 Answers1