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)
CodePudding user response:
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