python - How to iterate through a time frame?

Question

Okay so I have some S&P 500 minute data from a csv file. I am looking to iterate through a timestamp based on time. So far the code looks like this:

import datetime as dt
import pandas as pd
d = pd.read_csv('/Volumes/Seagate Portable/usindex_2020_all_tickers_awvbxk9/SPX_2020_2020.txt')
d.columns = ['Dates', 'Open', 'High', 'Low', 'Close']
d.Dates = pd.to_datetime(d.Dates)
d = d[(d.Dates.dt.time == dt.time(9, 30)) | (d.Dates.dt.time == dt.time(16, 0))].copy()
d.drop(['High', 'Low'], axis=1, inplace=True)
d.index = range(len(d.Open))

for i in d.index:
    if dt.time(16, 0) in d.Dates[i]:
        d['Open'][i] == np.NaN

The imported csv looks like this:

    Date               Open Close
0   2020-01-02 16:00:00 3258.14 3257.98
1   2020-01-03 09:30:00 3226.36 3225.79
2   2020-01-03 16:00:00 3234.35 3234.57
3   2020-01-06 09:30:00 3217.55 3215.01
4   2020-01-06 16:00:00 3246.23 3246.28
5   2020-01-07 09:30:00 3241.86 3238.09
6   2020-01-07 16:00:00 3237.13 3237.18
7   2020-01-08 09:30:00 3238.59 3236.82
8   2020-01-08 16:00:00 3253.21 3253.06
9   2020-01-09 09:30:00 3266.03 3270.29
10  2020-01-09 16:00:00 3274.74 3274.66
11  2020-01-10 09:30:00 3281.81 3281.20
12  2020-01-10 16:00:00 3265.39 3265.34
13  2020-01-13 09:30:00 3271.13 3273.28
14  2020-01-13 16:00:00 3287.98 3288.05
15  2020-01-14 09:30:00 3285.35 3285.09
16  2020-01-14 16:00:00 3282.93 3282.89
17  2020-01-15 09:30:00 3282.27 3281.75
18  2020-01-15 16:00:00 3289.76 3289.40
19  2020-01-16 09:30:00 3302.97 3304.34

I am getting the error Im getting is TypeError: argument of type 'Timestamp' is not iterable What I am trying to do is fill all Open values at 16:00:00 NaN values, then keep the Close valyes for that time. I can I either iterate through the time stamp with the same for loop? Or is there another possible way to sort through this and fill in the respective NaN values? Thanks!

question from:https://stackoverflow.com/questions/65541423/how-to-iterate-through-a-time-frame

Answer 1

in is used to test for membership in a collection or to find substring within a string. You cannot use it to test for the time in a Timestamp.

If you want to use a for loop:

for i in d.index:
    if d.loc[i, 'Date'].time() == dt.time(16,0):
        d.loc[i, 'Open'] == np.NaN

But it's always better to use a vectorized function:

d['Open'] = d['Open'].mask(d['Dates'].dt.time == dt.time(16, 0))