Count my students presence considering weeks starting at wednesdays

Question

I have a dataframe like this:

Timestamp              Student Name
2020-04-01 15:05:17    Student1
2020-04-01 14:56:37    Student2
2020-04-02 14:25:16    Student1
2020-04-08 14:15:32    Student1
2020-04-09 13:41:01    Student2
2020-04-15 12:50:30    Student2
...

I have to count the weekly frequency of students, but my week starts at Wednesdays. I tried this solution and it is similar to the output I want, but considering weeks starting at Wednesdays.

My students can mark their presence between a given wednesday and the next tuesday and this will be counted as a presence.

Example of desired output:

Week                  Student Name   
Week of 2020-04-01    Student1    2
                      Student2    1
Week of 2020-04-08    Student1    1
                      Student2    1
Week of 2020-04-15    Student2    1
...

score 1 · Accepted Answer · answered Oct 12 '20 at 14:29

Use pandas groupby with Goruper

from pandas.tseries.offsets import DateOffset
import pandas as pd

# groupby student name and your week ending on Tuesday and get the count
new_df = df.groupby(['Student Name', pd.Grouper(key='Timestamp', freq='W-Tue')])['Student Name'].count().reset_index(level=1)
# use DateOffset to change the dates from the week end to the week start
new_df['Timestamp'] = new_df['Timestamp'] - DateOffset(6)

              Timestamp  Student Name
Student Name                         
Student1     2020-04-01             2
Student1     2020-04-08             1
Student2     2020-04-01             1
Student2     2020-04-08             1
Student2     2020-04-15             1

Count my students presence considering weeks starting at wednesdays

1 Answers1