Pandas Resample OHCL

Question

index	close
2022-02-21	3
2022-02-22	1
2022-02-23	5
2022-02-24	5
2022-02-25	7
2022-03-02	4
2022-03-03	2
2022-03-04	1

My output should be:

index	close
2022-02-21	7
2022-03-02	1

I have tried

df.resample('W-MON', closed='left', label='left').last()

but I got a wrong label.

index	close
2022-02-21	7
2022-02-28	1

The problem is that I could have "missing days", like 2022-02-28 and 2022-03-01 and I would like to use the first "available" day of the week. e.g 2022-02-21 (Monday) and 2022-03-02 (Wednesday)

score 1 · Accepted Answer · answered May 16 '22 at 01:37

You can assign the index to a new column then keep the first value in this group

out = (df.assign(index=df.index)
       .groupby(pd.Grouper(freq='W-MON', closed='left', label='left')).agg({'index': 'first', 'close': 'last'})
       .reset_index(drop=True))

print(out)

       index  close
0 2022-02-21      7
1 2022-03-02      1

Pandas Resample OHCL

1 Answers1