Pandas Dataframe: Find duplicate rows based on a criteria

Question

I have a dataframe with the following columns and rows:

    State   Rain    Sun     Snow    Day
1   NJ      yes     no      no      5/31/21
2   NJ      yes     no      no      6/1/21
3   NJ      yes     no      no      6/2/21
4   NJ      yes     no      no      6/3/21
5   NY      no      no      yes     5/31/21
6   NY      no      yes     NULL    6/1/21
7   NY      no      yes     NULL    6/2/21
8   NY      no      yes     NULL    6/3/21
9   IL      no      yes     no      5/31/21
10  IL      no      yes     no      6/1/21
11  IL      no      yes     no      6/2/21
12  IL      yes     no      no      6/3/21
13  FL      no      yes     no      5/31/21
14  FL      yes     no      NULL    6/1/21
15  FL      yes     NULL    NULL    6/2/21
16  FL      NULL    NULL    NULL    6/3/21

Here the Rain, Sun and Snow columns can be NULL values, but the other columns cannot be NULL. Also if the Rain column is null then Sun and Snow columns will also be NULL for example:

NJ NULL NULL  NULL 5/31/21 -> Valid
NJ no   yes   NULL 5/31/21 -> Valid
NJ yes  NULL  NULL 5/31/21 -> Valid
NJ NULL yes   no   5/31/21 -> Invalid
NJ yes  NULL  no   5/31/21 -> Invalid
NJ NULL NULL  no   5/31/21 -> Invalid

I want to find duplicates in this dataset but with a certain criteria and return the index of the duplicate values. The following cases should be considered duplicates:

Case 1:

NJ      yes     no      no      5/31/21
NJ      yes     no      no      5/31/21

Case 2:

NY      no      yes     NULL    6/1/21
NY      no      yes     NULL    6/1/21

Case 3:

FL      no      no      no      6/1/21
FL      no      no      NULL    6/1/21
FL      no      NULL    NULL    6/1/21
FL      NULL    NULL    NULL    6/1/21

I was able to solve for the 1st two cases:

    def find_duplicates(df: pd.DataFrame):
        dup_rows = df.duplicated(subset=['State', 'Rain', 'Sun', 'Snow', 'Day'], keep=False)
        dup_df = df[dup_rows]
        dup_df = dup_df.reset_index()
        dup_df.rename(columns={'index': 'row'}, inplace=True)
        group = dup_df.groupby(['State', 'Rain', 'Sun', 'Snow', 'Day'], dropna=False)
        dup_lst = [grp['row'].to_list() for ind, grp in group]
        return dup_lst

I'm not sure how to approach the 3rd case, I'm new to pandas dataframe any help would be appreciated.

user_7648578 · Answer

Perhaps this is what you are looking for?

import numpy as np
import pandas as pd


def colwise_dups(df, cols):
    return df[
        np.array(
            [
                (row.drop(index=cols).values == df.drop(columns=cols).values).all(
                    axis=1
                )
                for _, row in df[
                    (df[cols] == ["NULL"] * len(cols)).all(axis=1)
                ].iterrows()
            ]
        ).any(axis=0)
    ]


cols = []
duplicates = pd.DataFrame()
for col in ["Rain", "Sun", "Snow"][::-1]:
    cols.append(col)
    duplicates = pd.concat([duplicates, colwise_dups(df, cols)]).drop_duplicates()```

user_3934100 · Answer

If I understand the question correctly, I think you can accomplish your goal by creating two temporary dataframes that replace with 'NULL' with 'yes' and 'no' respectively. Then you can filter based on the duplicates.

Minimal example:

df = pd.DataFrame({'State': 'NJ NJ NY NY FL FL FL FL'.split(' '),
              'Rain': 'yes yes no no no no no NULL'.split(' '),
              'Sun': 'no no yes yes no no NULL NULL'.split(' '),
              'Snow': 'no no NULL NULL no NULL NULL NULL'.split(' '),
              'Day': '5/31/21 5/31/21 6/1/21 6/1/21 6/1/21 6/1/21 6/1/21 6/1/21' .split(' ')})

tmp = df.replace('NULL', 'no')
tmp2 = df.replace('NULL', 'yes')

df[~tmp.duplicated() & ~tmp2.duplicated()]

Gives the following output:


  State Rain  Sun  Snow      Day
0    NJ  yes   no    no  5/31/21
2    NY   no  yes  NULL   6/1/21
4    FL   no   no    no   6/1/21

Pandas Dataframe: Find duplicate rows based on a criteria

2 Answers