'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 |
|---|
