How to find all rows having common dates on different party

user_16411269 0
Viewed 87

I have a dataframe in which I want to extract only those rows which have common dates (skipping time part of datetime) among different Party .

Example Dataframe :

Party Date & Time
A 2021-10-01 8:30:40
A 2021-10-01 9:45:40
A 2021-10-01 10:50:40
A 2021-10-02 7:30:40
A 2021-10-02 7:30:40
B 2021-10-01 8:30:40
B 2021-10-01 8:30:40
B 2021-10-05 8:25:40
C 2021-10-01 6:10:40
C 2021-10-05 10:25:30

Output I want : In Input Dataframe A,B and C party have common date 2021-10-01 and B,C party have common date 2021-10-05 .I want all these rows irrespective of different times.

Party Date & Time
A 2021-10-01 8:30:40
A 2021-10-01 9:45:40
A 2021-10-01 10:50:40
B 2021-10-01 8:30:40
B 2021-10-01 8:30:40
B 2021-10-05 8:25:30
C 2021-10-01 6:10:40
C 2021-10-05 10:25:30

The code I tried :

df.loc[df['Date & Time'].dt.date.duplicated(keep=False)]

but it includes dates that are only found in same Party too. I want all the rows that have common dates among different Parties.

data-science pandas dataframe python
1 Answers

You can group the Party column by date then transform with nunique to count the number of unique parties then keep the rows which have unique count >= 2

d = df['Date & Time'].dt.date
df[df.groupby(d)['Party'].transform('nunique').ge(2)]

  Party         Date & Time
0     A 2021-10-01 08:30:40
1     A 2021-10-01 09:45:40
2     A 2021-10-01 10:50:40
5     B 2021-10-01 08:30:40
6     B 2021-10-01 08:30:40
7     B 2021-10-05 08:25:40
8     C 2021-10-01 06:10:40
9     C 2021-10-05 10:25:30
user_128331660
Related