0

I have a few set of days where the index is based on 30min data from monday to friday. There might some missing dates (Might be because of holidays). But i would like to find the highest from column high and lowest from column low for ever past week. Like i am calculating today so previous week high and low is marked in the yellow of attached image.

Tried using rolling , resampling but some how not working. Can any one help

enter image description here

ragendthsfdc pk
  • 9
  • 3
  • can you send your desired output to help you – Alireza75 Jul 18 '22 at 09:30
  • also it might that max high in a week is different from min low on this week. In this case what do you do?? – Alireza75 Jul 18 '22 at 09:32
  • 1
    it would be best to post simple code to generate an example of your data , and what did you try so far – Ran A Jul 18 '22 at 09:47
  • 1
    Please add a [mre](https://stackoverflow.com/help/minimal-reproducible-example) (also look [here](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples)), i.e. code that produces the sample, and also include the expected output for the sample. – Timus Jul 18 '22 at 11:48

2 Answers2

1

You really should add sample data to your question (by that I mean a piece of code/text that can easily be used to create a dataframe for illustrating how the proposed solution works).

Here's a suggestion. With df your dataframe, and column datatime with datetimes (and not strings):

df["week"] = (
    df["datetime"].dt.isocalendar().year.astype(str)
    + df["datetime"].dt.isocalendar().week.astype(str)
)
mask = df["high"] == df.groupby("week")["high"].transform("max")
df = df.merge(
    df[mask].rename(columns={"low": "high_low"})
            .groupby("week").agg({"high_low": "min"}).shift(),
    on="week", how="left"
).drop(columns="week")
  • Add a week column to df (year + week) for grouping along weeks.
  • Extract the rows with the weekly maximum highs by mask (there could be more than one for a week).
  • Build a corresponding dataframe with the weekly minimum of the lows corresponding to the weekly maximum highs (column named high_low), shift it once to get the value from the previous week, and .merge it to df.

If column datetime doesn't contain datetimes:

df["datetime"] = pd.to_datetime(df["datetime"])
Timus
  • 10,974
  • 5
  • 14
  • 28
0

If I have understood correctly, the solution should be

  1. get the week number from the date
  2. groupby the week number and fetch the max and min number.
  3. groupby the week fetch max date to get max/last date for a week
  4. now merge all the dataframes into one based on date key
  5. Once the steps are done, you could do any formatting as required.
Smitaranjan Nayak
  • 11
  • 4
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 19 '22 at 17:26