Say you have the following data frame and you needed to know how many Assays were done per month.

type,"Date Tested"
Assay,2022/01/28
Assay,2022/01/31
Assay,2022/02/02
Assay,2022/03/31
Assay,2022/04/21
Assay,2022/05/12
Assay,2022/06/02
Assay,2022/02/03
Assay,2022/06/03

You can use value_counts() from Pandas to easily do this.

data['Date Tested']=pd.to_datetime(data['Date Tested'], format = "%Y/%m/%d")
months = data['Date Tested'].dt.month.value_counts(sort=False)
print(months)

Which outputs:

1    2
2    2
3    1
4    1
5    1
6    2
Name: Date Tested, dtype: int64

The ‘numbers’ in the first column are each month (i.e 01 – Jan, 02 – Feb etc..) but this isn’t great. What if the dataset started at March? Then March = 01.
Or what if I needed to do the same thing but by weeks? How could you workout what, say 12 was in terms of a week?

How can you modify the output of value_count to include the corresponding month/week? This information is present in the dataframe, shown by:

print(data['Date Tested'])

Which gives:

0   2022-01-28
1   2022-01-31
2   2022-02-02
3   2022-03-31
4   2022-04-21
5   2022-05-12
6   2022-06-02
7   2022-02-03
8   2022-06-03
Name: Date Tested, dtype: datetime64[ns]

Ideally, my count output would be something like this:

2022-01   2
2022-02   2
2022-03   1
2022-04   1
2022-05   1
2022-06   2
Name: Date Tested, dtype: datetime64[ns]

>Solution :

You can convert to_period (M = monthly) and count:

data.groupby(data['Date Tested'].dt.to_period('M'))['type'].count()

output:

Date Tested
2022-01    2
2022-02    2
2022-03    1
2022-04    1
2022-05    1
2022-06    2
Freq: M, Name: type, dtype: int64

By week:

data.groupby(data['Date Tested'].dt.to_period('W'))['type'].count()

output:

Date Tested
2022-01-24/2022-01-30    1
2022-01-31/2022-02-06    3
2022-03-28/2022-04-03    1
2022-04-18/2022-04-24    1
2022-05-09/2022-05-15    1
2022-05-30/2022-06-05    2
Freq: W-SUN, Name: type, dtype: int64