Loop through a dataframe, using specific values for individual dates

January 28, 2023

I have a dataframe that looks like the below. I want to create a new column titled ‘Daily Change’ which will be the result of the first ‘Open’ price of each day divided by the ‘Open’ price for each 5 minute interval. Once the dates changes I need to the first open price of that day and so on. This is a sample and i want to be able to handle more than two days.

Datetime            Open    High    Low
2022-10-31 09:30:00 386.4   387.0   386.1
2022-10-31 09:35:00 386.9   387.5   386.5
2022-10-31 09:40:00 387.1   387.4   386.6
2022-10-31 09:45:00 387.1   387.1   385.8
2022-10-31 09:50:00 385.8   386.1   385.5
2022-10-31 09:55:00 386.0   386.1   385.5
2022-11-01 09:30:00 390.1   390.4   389.6
2022-11-01 09:35:00 389.9   390.2   389.4
2022-11-01 09:40:00 389.9   390.2   389.5
2022-11-01 09:45:00 389.7   389.7   388.1
2022-11-01 09:50:00 388.4   388.5   387.7
2022-11-01 09:55:00 388.0   388.3   387.9

I tried the below but get a response stating that the truth value is ambiguous.

days = pd.Series(data.index.date).unique()
for day in days:
    temp_df = data[data.index.date == day]
    price_df = temp_df[temp_df.index.time == datetime.time(9, 30)]
    print(price_df)
    if price_df.index.date == temp_df.index.date:
        temp_df['Return'] = (temp_df['Open'] / price_df['Open'] - 1)

>Solution :

You can use resample_first to get the first opening value per day then broadcast the value along the rows:

# Convert to DatetimeIndex if needed
df['Datetime'] = pd.to_datetime(df['Datetime'])
df['Daily Change'] = df['Open'] / df.resample('D', on='Datetime')['Open'].transform('first')

              Datetime   Open   High    Low  Daily Change
0  2022-10-31 09:30:00  386.4  387.0  386.1      1.000000
1  2022-10-31 09:35:00  386.9  387.5  386.5      1.001294
2  2022-10-31 09:40:00  387.1  387.4  386.6      1.001812
3  2022-10-31 09:45:00  387.1  387.1  385.8      1.001812
4  2022-10-31 09:50:00  385.8  386.1  385.5      0.998447
5  2022-10-31 09:55:00  386.0  386.1  385.5      0.998965
6  2022-11-01 09:30:00  390.1  390.4  389.6      1.000000
7  2022-11-01 09:35:00  389.9  390.2  389.4      0.999487
8  2022-11-01 09:40:00  389.9  390.2  389.5      0.999487
9  2022-11-01 09:45:00  389.7  389.7  388.1      0.998975
10 2022-11-01 09:50:00  388.4  388.5  387.7      0.995642
11 2022-11-01 09:55:00  388.0  388.3  387.9      0.994617