I have a data frame that looks like this:
df =
date val1
01/02/2021 2.0
02/02/2021 2.0
03/02/2021 1.0
04/02/2021 1.5
05/02/2021 10.0
06/02/2021 7.0
07/02/2021 4.0
I then create another column where I want a 1 if val1 is above e.g. 5, and 0 otherwise, i.e.:
import numpy as np
import pandas as pd
df['above_five'] = np.where(df['val1'] > 5.0, 1, 0)
Which will return:
df =
date val1 above_five
01/02/2021 2.0 0
02/02/2021 2.0 0
03/02/2021 1.0 0
04/02/2021 1.5 0
05/02/2021 10.0 1
06/02/2021 7.0 1
07/02/2021 4.0 0
My next step is where I am a bit unsure. I now wish to "compress" the dataframe into weekly periods, but with a new column or something that counts the occurences of 1's for that week.
I tried with this one, but this counts everything, so it doesn't seem to focus on 1's:
new_df = df.resample('W', on='date').count()
In reality the output should look something like this (assuming 01/02/2021 is a Monday):
final_df =
date above_five_counts
Week 1* 2
*or just some date indicating that this is the first week, such as the first day of the week, or the last.