Count 1's in a time series divided into weeks

Viewed 39

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.

1 Answers

use group by on week level

new_df = df.groupby([pd.Grouper(key='date', freq='W-MON')])['above_five'].sum()

code is taken from this question: group by week in pandas

Related