'Calculate number of (distinct/unique) times a person has entered a building per day

Trying to count distinct number of times a person entered a facility on each day.

What I've achieved: Distinct entries by the person for the four columns.

df = pd.read_excel("Sample.xlsx)
df1 = df.groupby(['Date', 'Facility'], as_index=False)['By_Name'].nunique()
df2 = df.groupby(['Date', 'Facility'], as_index=False)['Preceptor'].nunique()
df3 = df.groupby(['Date', 'Facility'], as_index=False)['Preceptee'].nunique()
df4 = df.groupby(['Date', 'Facility'], as_index=False)['Circulator'].nunique()

Where I'm struggling: Checking if that person already entered the facility

Sample Input (Sample.xlsx) of a given day [actual data is for every day of the month]:

Date Facility Begin Time By Name Preceptor Preceptee Circulator
2021-09-10 Betsy House 14:50 DS
2021-09-10 Betsy House 15:30 DS
2021-09-10 Betsy House 18:20 DS
2021-09-10 Brian South 13:10 DS
2021-09-10 Camden Place 18:10 NP
2021-09-10 Cape Fear 10:01 DI KL
2021-09-10 Cape Fear 10:31 DI KL
2021-09-10 Cape Fear 10:36 DI KL
2021-09-10 Cape Fear 11:58 DI KL
2021-09-10 Cape Fear 12:11 DI KL
2021-09-10 Cape Fear 12:56 DI KL
2021-09-10 Cape Fear 13:35 DI KL
2021-09-10 Cape Fear 17:30 DI KL
2021-09-10 Cape Fear 09:50 KL DI
2021-09-10 Cape Fear 10:47 KL DI
2021-09-10 Cape Fear 11:14 KL DI
2021-09-10 Cape Fear 16:18 KL DI
2021-09-10 Cape Fear 16:34 KL DI
2021-09-10 Cape Fear 18:09 KL DI
2021-09-10 Cape Fear 18:20 KL DI
2021-09-10 Cape Fear 09:30 LOA WH
2021-09-10 Cape Fear 09:48 SR JR
2021-09-10 Cape Fear 11:03 SR JR
2021-09-10 Cape Fear 12:10 SR JR
2021-09-10 Cape Fear 13:10 SR JR
2021-09-10 Cape Fear 13:34 SR JR
2021-09-10 Cape Fear 13:55 SR JR
2021-09-10 Cape Fear 16:19 SR JR
2021-09-10 Cape Fear 16:19 SR JR
2021-09-10 Cape Fear 16:43 SR JR
2021-09-10 Cape Fear 16:43 SR JR
2021-09-10 Cape Fear 17:09 SR JR
2021-09-10 Cape Fear 11:52 WH
2021-09-10 Capital Center 11:45 DS
2021-09-10 CHH 17:09 AA
2021-09-10 Chatham 15:47 RB
2021-09-10 Chatham 17:25 RB
2021-09-10 CIH 09:48 MP
2021-09-10 CRHS 16:15 WH LOA
2021-09-10 CRHS 16:15 WH LOA
2021-09-10 HRMC 13:29 MP
2021-09-10 HRMC 14:01 MP
2021-09-10 HRMC 15:35 MP
2021-09-10 IMH 09:50 LD
2021-09-10 IMH 11:15 LD
2021-09-10 IMH 18:30 LD
2021-09-10 IMH 19:40 LD
2021-09-10 MUSC 11:20 CT
2021-09-10 MUSC 11:50 CT
2021-09-10 MUSC 12:50 CT
2021-09-10 MUSC 13:43 CT
2021-09-10 MUSC 14:55 CT
2021-09-10 MUSC 15:40 CT
2021-09-10 MUSC 16:09 CT
2021-09-10 MUSC 16:20 CT
2021-09-10 MUSC 16:38 CT
2021-09-10 NCSH 10:20 NP
2021-09-10 NCN 17:40 EK
2021-09-10 Oak Forest 10:50 RB
2021-09-10 Oak Forest 11:50 RB
2021-09-10 PRC 13:05 LD
2021-09-10 PMH 15:15 NP
2021-09-10 SSHD 11:50 NP
2021-09-10 SSHD 12:00 NP
2021-09-10 SSHD 12:20 NP
2021-09-10 SSHG 13:44 RB
2021-09-10 SCC 11:36 MP
2021-09-10 SCC 17:55 MP
2021-09-10 SHD 11:00 LB
2021-09-10 SHD 13:10 LB
2021-09-10 SHD 15:00 LB
2021-09-10 SHD 16:10 LB
2021-09-10 SHM 09:45 LJ
2021-09-10 SHM 11:30 LJ
2021-09-10 SHM 11:50 LJ
2021-09-10 SHM 12:11 LJ
2021-09-10 SHM 12:25 LJ
2021-09-10 SHM 16:50 LJ
2021-09-10 VEH 11:40 AA

Required Output (By Facility)

Date Facility Entries Begin Time By Name Preceptor Preceptee Circulator
2021-09-10 Betsy House 1 14:50 DS
2021-09-10 Brian South 1 13:10 DS
2021-09-10 Camden Place 1 18:10 NP
2021-09-10 Capital Center 1 11:45 DS
2021-09-10 CHH 1 17:09 AA
2021-09-10 Chatham 1 15:47 RB
2021-09-10 CIH 1 09:48 MP
2021-09-10 CRHS 2 16:15 WH LOA
2021-09-10 HRMC 1 13:29 MP
2021-09-10 IMH 2 09:50 LD
2021-09-10 IMH 18:30 LD
2021-09-10 MUSC 1 11:20 CT
2021-09-10 NCSH 1 10:20 NP
2021-09-10 NCN 1 17:40 EK
2021-09-10 Oak Forest 1 10:50 RB
2021-09-10 PRC 1 13:05 LD
2021-09-10 PMH 1 15:15 NP
2021-09-10 SSHD 1 11:50 NP
2021-09-10 SSHG 1 13:44 RB
2021-09-10 SCC 2 11:36 MP
2021-09-10 SCC 17:55 MP
2021-09-10 SHD 1 11:00 LB
2021-09-10 SHM 1 09:45 LJ
2021-09-10 VEH 1 11:40 AA
2021-09-10 Cape Fear 6 09:30 LOA WH
2021-09-10 Cape Fear 09:48 SR JR
2021-09-10 Cape Fear 09:50 KL DI
2021-09-10 Cape Fear 10:01 DI KL

Any and all help will be appreciated. TIA



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source