Copy a row to multiple rows based on value
I cannot figure out a problem on stocks data. We have a dataframe with high, low and dates data. I want to make two new columns with max from 24_H and min from 24_L of whole week (min and max of whole week from days of week and copy it to every day/row of that week) problem is some days are missing (holidays) so you can not use a loop for every 5 rows. Hope that make sense I tried to explain it in a picture in excel and manually did it on two weeks (38 and 39).
EDIT: part of the code. how to print high and low value of each week : df.groupby(['YEAR', 'WEEK']).agg({'24_H': 'max', '24_L': 'min'}) but still don´t know how to print them back for every day of the week
EDIT2 part of the data (DoW is day of week):
| DATE | DoW | 24_H | 24_L | WEEK | MONTH | YEAR |
|---|---|---|---|---|---|---|
| 12.09.2005 | 1 | 1170.0 | 1165.0 | 37 | 9 | 2005 |
| 13.09.2005 | 2 | 1166.0 | 1157.0 | 37 | 9 | 2005 |
| 14.09.2005 | 3 | 1162.0 | 1151.0 | 37 | 9 | 2005 |
| 15.09.2005 | 4 | 1158.0 | 1150.0 | 37 | 9 | 2005 |
| 16.09.2005 | 5 | 1164.0 | 1152.0 | 37 | 9 | 2005 |
| 19.09.2005 | 1 | 1162.0 | 1153.0 | 38 | 9 | 2005 |
| 20.09.2005 | 2 | 1162.0 | 1145.0 | 38 | 9 | 2005 |
| 21.09.2005 | 3 | 1149.0 | 1134.0 | 38 | 9 | 2005 |
| 22.09.2005 | 4 | 1142.0 | 1130.0 | 38 | 9 | 2005 |
| 23.09.2005 | 5 | 1144.0 | 1134.0 | 38 | 9 | 2005 |
| 26.09.2005 | 1 | 1148.0 | 1136.0 | 39 | 9 | 2005 |
| 27.09.2005 | 2 | 1145.0 | 1135.0 | 39 | 9 | 2005 |
| 28.09.2005 | 3 | 1146.0 | 1137.0 | 39 | 9 | 2005 |
| 29.09.2005 | 4 | 1154.0 | 1136.0 | 39 | 9 | 2005 |
| 30.09.2005 | 5 | 1155.0 | 1149.0 | 39 | 9 | 2005 |
Data in excel:
Use the groupby() as you've described, then do an inner join (pandas.merge()) to copy the new (grouped) DF's data back to the main one. Also use reset_index() to make the YEAR and WEEK columns instead of an index in weekly_limits:
weekly_limits = df.groupby(['YEAR', 'WEEK']).agg(
{'24_H': 'max', '24_L': 'min'}
).reset_index()
df_with_weekly_limits = pandas.merge(df, weekly_limits, on=['YEAR', 'WEEK'])