0

I have 5 minutes stock data dataframe for multiple days. I am trying to get first, current, min, max values for columns within the day in same index and same dataframe.

I have a pandas dataframe with 5 minutes stock data:

Index High Low Open Close Tvol Vol
1/3/2022 4:05 178.34 178.15 178.26 178.15 7418 4771
1/3/2022 4:10 178.2 177.76 178.2 177.99 12144 2522
1/3/2022 4:15 178.08 177.9 177.93 178.08 15856 2583
1/3/2022 4:20 178.12 177.81 178.08 177.86 24365 6007
1/3/2022 4:25 178.02 178 178.02 178 25898 561
1/3/2022 4:30 177.92 177.81 177.9 177.82 32159 3424
1/3/2022 4:35 177.87 177.76 177.87 177.85 35847 1748
1/3/2022 4:40 177.88 177.77 177.85 177.77 41561 3561
1/3/2022 4:45 177.89 177.81 177.83 177.89 47914 4251
1/3/2022 4:50 178.01 177.81 177.88 178.01 52718 3159
1/3/2022 4:55 178.06 177.92 177.96 178.06 57378 3208
1/3/2022 5:00 178.1 177.93 177.96 178.08 63095 3972
1/3/2022 5:05 178.13 177.99 178.08 177.99 70300 4856
…
1/3/2022 19:10 181.89 181.82 181.89 181.82 104641165 1909
1/3/2022 19:15 182 181.82 181.82 181.95 104645653 2864
1/3/2022 19:20 181.94 181.88 181.92 181.88 104647370 960
1/3/2022 19:25 181.93 181.88 181.88 181.93 104648887 1233
1/3/2022 19:30 181.94 181.75 181.94 181.75 104661587 11467
1/3/2022 19:35 181.83 181.8 181.8 181.81 104664897 2937
1/3/2022 19:40 181.9 181.82 181.82 181.9 104671047 5214
1/3/2022 19:45 181.93 181.82 181.85 181.93 104675034 2979
1/3/2022 19:50 182 181.85 181.93 182 104679458 3673
1/3/2022 19:55 182 181.91 181.91 182 104685275 4543
1/3/2022 20:00 182 181.94 181.97 181.99 104701218 14135
1/4/2022 4:05 182.67 182.1 182.22 182.34 10064 5364
1/4/2022 4:10 182.5 182.3 182.39 182.3 19767 5692
1/4/2022 4:15 182.41 182.26 182.32 182.41 23957 2750
1/4/2022 4:20 182.55 182.43 182.43 182.5 30848 2888
1/4/2022 4:25 182.61 182.52 182.57 182.61 36930 3833
1/4/2022 4:30 182.6 182.54 182.6 182.57 39015 650
1/4/2022 4:35 182.59 182.53 182.59 182.53 41933 1058
1/4/2022 4:40 182.6 182.51 182.53 182.59 45514 2682
1/4/2022 4:45 182.87 182.6 182.61 182.87 53510 4558
1/4/2022 4:50 182.85 182.72 182.85 182.75 55486 1011
1/4/2022 4:55 182.81 182.78 182.8 182.78 57486 1117
1/4/2022 5:00 182.95 182.78 182.78 182.87 61631 1565
…
1/4/2022 19:00 179.39 179.34 179.39 179.35 99248684 507
1/4/2022 19:05 179.35 179.31 179.35 179.33 99252312 2929
1/4/2022 19:10 179.35 179.34 179.35 179.35 99257528 4725
1/4/2022 19:15 179.39 179.33 179.35 179.35 99259476 1475
1/4/2022 19:20 179.38 179.31 179.38 179.31 99265392 5256
1/4/2022 19:25 179.35 179.31 179.32 179.32 99276227 9707
1/4/2022 19:30 179.34 179.3 179.31 179.33 99278111 1460
1/4/2022 19:35 179.33 179.3 179.33 179.32 99283303 4693
1/4/2022 19:40 179.3 179.25 179.3 179.25 99287343 2947
1/4/2022 19:45 179.29 179.25 179.26 179.29 99290635 2509
1/4/2022 19:50 179.35 179.25 179.25 179.29 99296217 4359
1/4/2022 19:55 179.3 179.25 179.25 179.3 99304949 7358
1/4/2022 20:00 179.38 179.3 179.38 179.33 99310395 3873

I would like to get for each dataframe index minimum of low, maximum for high, first for open, last for close and sum for vol columns from the start of the day from current index.

Expecting following result:

Index High Low Open Close Tvol Vol FROM_DAY_START_HIGH FROM_DAY_START_LOW FROM_DAY_START_OPEN FROM_DAY_START_CLOSE FROM_DAY_START_VOL
1/3/2022 4:05 178.34 178.15 178.26 178.15 7418 4771 178.34 178.15 178.26 178.15 4771
1/3/2022 4:10 178.2 177.76 178.2 177.99 12144 2522 178.34 177.76 178.26 177.99 7293
1/3/2022 4:15 178.08 177.9 177.93 178.08 15856 2583 178.34 177.76 178.26 178.08 9876
1/3/2022 4:20 178.12 177.81 178.08 177.86 24365 6007 178.34 177.76 178.26 177.86 15883
1/3/2022 4:25 178.02 178 178.02 178 25898 561 178.34 177.76 178.26 178 16444
1/3/2022 4:30 177.92 177.81 177.9 177.82 32159 3424 178.34 177.76 178.26 177.82 19868
1/3/2022 4:35 177.87 177.76 177.87 177.85 35847 1748 178.34 177.76 178.26 177.85 21616
1/3/2022 4:40 177.88 177.77 177.85 177.77 41561 3561 178.34 177.76 178.26 177.77 25177
1/3/2022 4:45 177.89 177.81 177.83 177.89 47914 4251 178.34 177.76 178.26 177.89 29428
1/3/2022 4:50 178.01 177.81 177.88 178.01 52718 3159 178.34 177.76 178.26 178.01 32587
1/3/2022 4:55 178.06 177.92 177.96 178.06 57378 3208 178.34 177.76 178.26 178.06 35795
1/3/2022 5:00 178.1 177.93 177.96 178.08 63095 3972 178.34 177.76 178.26 178.08 39767
1/3/2022 5:05 178.13 177.99 178.08 177.99 70300 4856 178.34 177.76 178.26 177.99 44623
…
1/3/2022 19:10 181.89 181.82 181.89 181.82 104641165 1909 182.88 177.5 178.26 181.82 89930187
1/3/2022 19:15 182 181.82 181.82 181.95 104645653 2864 182.88 177.5 178.26 181.9599 89933051
1/3/2022 19:20 181.94 181.88 181.92 181.88 104647370 960 182.88 177.5 178.26 181.88 89934011
1/3/2022 19:25 181.93 181.88 181.88 181.93 104648887 1233 182.88 177.5 178.26 181.93 89935244
1/3/2022 19:30 181.94 181.75 181.94 181.75 104661587 11467 182.88 177.5 178.26 181.75 89946711
1/3/2022 19:35 181.83 181.8 181.8 181.81 104664897 2937 182.88 177.5 178.26 181.81 89949648
1/3/2022 19:40 181.9 181.82 181.82 181.9 104671047 5214 182.88 177.5 178.26 181.9 89954862
1/3/2022 19:45 181.93 181.82 181.85 181.93 104675034 2979 182.88 177.5 178.26 181.9399 89957841
1/3/2022 19:50 182 181.85 181.93 182 104679458 3673 182.88 177.5 178.26 182 89961514
1/3/2022 19:55 182 181.91 181.91 182 104685275 4543 182.88 177.5 178.26 182 89966057
1/3/2022 20:00 182 181.94 181.97 181.99 104701218 14135 182.88 177.5 178.26 181.99 89980192
1/4/2022 4:05 182.67 182.1 182.22 182.34 10064 5364 182.67 182.1 182.22 182.34 5364
1/4/2022 4:10 182.5 182.3 182.39 182.3 19767 5692 182.67 182.1 182.22 182.3 11056
1/4/2022 4:15 182.41 182.26 182.32 182.41 23957 2750 182.67 182.1 182.22 182.41 13806
1/4/2022 4:20 182.55 182.43 182.43 182.5 30848 2888 182.67 182.1 182.22 182.5 16694
1/4/2022 4:25 182.61 182.52 182.57 182.61 36930 3833 182.67 182.1 182.22 182.61 20527
1/4/2022 4:30 182.6 182.54 182.6 182.57 39015 650 182.67 182.1 182.22 182.57 21177
1/4/2022 4:35 182.59 182.53 182.59 182.53 41933 1058 182.67 182.1 182.22 182.53 22235
1/4/2022 4:40 182.6 182.51 182.53 182.59 45514 2682 182.67 182.1 182.22 182.59 24917
1/4/2022 4:45 182.87 182.6 182.61 182.87 53510 4558 182.87 182.1 182.22 182.87 29475
1/4/2022 4:50 182.85 182.72 182.85 182.75 55486 1011 182.87 182.1 182.22 182.75 30486
1/4/2022 4:55 182.81 182.78 182.8 182.78 57486 1117 182.87 182.1 182.22 182.78 31603
1/4/2022 5:00 182.95 182.78 182.78 182.87 61631 1565 182.95 182.1 182.22 182.87 33168
…
1/4/2022 19:00 179.39 179.34 179.39 179.35 99248684 507 183.38 179.12 182.22 179.35 77209201
1/4/2022 19:05 179.35 179.31 179.35 179.33 99252312 2929 183.38 179.12 182.22 179.33 77212130
1/4/2022 19:10 179.35 179.34 179.35 179.35 99257528 4725 183.38 179.12 182.22 179.35 77216855
1/4/2022 19:15 179.39 179.33 179.35 179.35 99259476 1475 183.38 179.12 182.22 179.35 77218330
1/4/2022 19:20 179.38 179.31 179.38 179.31 99265392 5256 183.38 179.12 182.22 179.31 77223586
1/4/2022 19:25 179.35 179.31 179.32 179.32 99276227 9707 183.38 179.12 182.22 179.32 77233293
1/4/2022 19:30 179.34 179.3 179.31 179.33 99278111 1460 183.38 179.12 182.22 179.33 77234753
1/4/2022 19:35 179.33 179.3 179.33 179.32 99283303 4693 183.38 179.12 182.22 179.32 77239446
1/4/2022 19:40 179.3 179.25 179.3 179.25 99287343 2947 183.38 179.12 182.22 179.25 77242393
1/4/2022 19:45 179.29 179.25 179.26 179.29 99290635 2509 183.38 179.12 182.22 179.29 77244902
1/4/2022 19:50 179.35 179.25 179.25 179.29 99296217 4359 183.38 179.12 182.22 179.29 77249261
1/4/2022 19:55 179.3 179.25 179.25 179.3 99304949 7358 183.38 179.12 182.22 179.3 77256619
1/4/2022 20:00 179.38 179.3 179.38 179.33 99310395 3873 183.38 179.12 182.22 179.33 77260492

I have realisation with rolling apply but it is very slow. For volume columns I have the solution with groupby and cumsum.

Thank you.

jwalton
  • 5,286
  • 1
  • 18
  • 36
user22245178
  • 1
  • 1
  • Please format your example and make it minimal, you'll need to group by date, then aggregate with the desired functions, something like: `df.groupby(pd.to_datetime(df['date_column']).dt.normalize()).agg(['min', 'max', 'first', 'last', 'sum'])` – mozway Jul 18 '23 at 09:32
  • mozway, thank you for your reply, df["Low"].groupby(df.index.date).agg(['min']) returns daily data. However, I am looking for still 5 minutes data, but aggregation starts from the beggining of the day in current index – user22245178 Jul 18 '23 at 10:07

0 Answers0