CODEWITHSUNDEEP
pythonpandasdataframe
ragendthsfdc pk
ragendthsfdc pk

Reputation: 29

getting previous week highs and lows in pandas dataframe using 30 min data

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

Upvotes: 0

Views: 896

Answers (2)

Timus
Timus

Reputation: 11321

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"])

Upvotes: 1

Smitaranjan Nayak
Smitaranjan Nayak

Reputation: 11

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.

Upvotes: 0

Related Questions