How to consolidate the attendance list from MS Teams?

Question

Recently, Microsoft announced the option to download attendance in MS teams to keep track of who is joining and leaving. But, to take attendance in a class team, it is not quite useful since it does not give a proper way to know how much time the participant is there in the meeting.

For example,

df = pd.DataFrame([["Organiser Name","Joined","03/08/2020, 16:30:41"],
["Organiser Name","Left","03/08/2020, 17:03:32"],
["Organiser Name","Joined","03/08/2020, 17:04:25"],
["Student 1","Joined before","03/08/2020, 16:30:41"],
["Student 1","Joined before","03/08/2020, 17:04:27"],
["Student 2","Joined before","03/08/2020, 16:30:41"],
["Student 2","Joined","03/08/2020, 17:04:27"],
["Student 3","Joined","03/08/2020, 16:31:47"],
["Student 3","Joined","03/08/2020, 17:04:27"],
["Student 3","Left","03/08/2020, 17:30:32"],
["Student 4","Joined","03/08/2020, 16:32:01"],
["Student 4","Left","03/08/2020, 16:37:20"],
["Student 4","Joined","03/08/2020, 16:39:27"],
["Student 4","Joined","03/08/2020, 17:04:27"],
["Student 4","Left","03/08/2020, 17:17:19"],
["Student 4","Joined","03/08/2020, 17:19:13"],
["Student 5","Joined","03/08/2020, 16:35:41"],
["Student 5","Left","03/08/2020, 16:36:46"],
["Student 6","Joined","03/08/2020, 16:38:01"],
["Student 6","Left","03/08/2020, 16:40:14"],
["Student 7","Joined","03/08/2020, 17:15:08"],
["Student 7","Left","03/08/2020, 17:15:44"],
["Student 7","Joined","03/08/2020, 17:15:48"],
["Student 7","Left","03/08/2020, 17:15:54"],
["Student 8","Joined","03/08/2020, 17:18:12"],
["Student 8","Left","03/08/2020, 17:19:59"]], columns = ["Full Name","User Action","Timestamp"])

This is an original meeting attendance list (only the names are replaced). Let's say suppose I end the meeting at 03/08/2020, 17:22:00 and I downloaded the attendance 2 minutes earlier,is there any way that I can think of summarizing this data using python? Like,

List of students and his/her duration in which he was there in the class

Then I can compare that with all the students and get the absentees list and as wells as will be able to decide whether to give attendance or not depends on how long the students are in the class.

I don't have any base code in mind since I am confused about the following:

1. In between I miss some left time but there are two consecutive joined time
2. When I leave the meeting due to some internet issues and rejoin, then there is no information about who left and joined between myself leaving and rejoining.

Anyone have sorted this problem?

Or any idea to summarize this data?

or any fresh idea to decide the eligibility of the attendance?

Thanks in advance.

Note: The solution I expect is using python, however, solution in any language or in excel also are welcome.

Answer 1

Definition of Attendance

I think the main problem here is a sensible definition of attendance. For the specific use case of teacher (= organizer) and students this is straightforward:

"Attendance is the period in which both teacher/organizer and students are present."

Rationale:

• If students log on early, the lesson has not started yet. They aren't attending anything.
• If students log off while the teacher is still logged on, they will miss something.
• It doesn't matter whether students log off/on while the teacher is away, they don't miss anything. (Note: This is a major difference between virtual and real classroom.)

That is exactly how MS Teams thinks about attendance. From the MS Office website:

Keep in mind that you'll only be able to download the attendance report while the meeting is in progress and participants are still present. [...] If attendees join a meeting before the organizer, their join times will match the time that the organizer joined the meeting.

The data has one big problem: The end time of the meeting is missing. The organizer cannot leave, because then the report cannot be downloaded. But the organizer will always download after the end of the lesson. You'll have to provide the end date manually or use a sensible

Answer

With the definition we can center all time periods on the organizer. There is a lot going on in the code. I commented as best as I can.

    # neccessary inputs (not in original data)
    end_timestamp = pd.Timestamp('2020-03-08 17:40')
    organizer_name = 'Organiser Name'

    # Pivot User Action values to columns; we don't need 'Join before'
    df['Timestamp'] = pd.to_datetime(df['Timestamp'])
    df['User Action'] = df['User Action'].str.replace('Joined before', 'Joined')
    df = df.set_index(['Full Name', 'User Action'], append=True).unstack()
    df.columns = df.columns.get_level_values(1)

    # we can (always) shift the 'Left' dates due to underlying data structure
    df['Left'] = df['Left'].shift(-1)
    df = df.dropna(how='all')

    # organizer can only have one missing value: the end value
    mask_organizer = df.index.get_level_values('Full Name') == organizer_name
    df.loc[mask_organizer, 'Left'] = df.loc[mask_organizer, 'Left'].fillna(end_timestamp)
    replace_na_dates = list(df.loc[mask_organizer, 'Left'])

    def fill_missing_dates(join_date, left_date, replace_dates):
        if left_date is not pd.NaT:
            return left_date
        for date in replace_dates:
            if join_date < date:
                return date
        return replace_dates[-1]

    df['Left'] = df.apply(lambda x: fill_missing_dates(x['Joined'], x['Left'], replace_na_dates), axis=1)
    df['Attendance'] = df['Left'] - df['Joined']
    df = df.groupby(level=1).sum()

Output:

                Attendance
Full Name                
Organiser Name   01:08:26
Student 1        01:08:24
Student 2        01:08:24
Student 3        00:57:50
Student 4        01:03:03
Student 5        00:01:05
Student 6        00:02:13
Student 7        00:00:42
Student 8        00:01:47

You may notice that the organizer has two seconds more attendance than everyone else. I think that MS Teams logs the join of the organizer correctly, but it takes a little longer until it has feedback from all participants in the meeting. In other words: it is the time between "I am back" and "Now I can see you all".