How to find duplicate values (not rows) in an entire pandas dataframe?

user_2962397 0
Viewed 41

Consider this dataframe.

df = pd.DataFrame(data={'one': list('abcd'),
                        'two': list('efgh'),
                        'three': list('ajha')})
  one two three
0   a   e     a
1   b   f     j
2   c   g     h
3   d   h     a

How can I output all duplicate values and their respective index? The output can look something like this.

  id value
0  2     h
1  3     h
2  0     a
3  0     a
4  3     a
pandas duplicates python
3 Answers

Try .melt + .duplicated:

x = df.reset_index().melt("index")
print(
    x.loc[x.duplicated(["value"], keep=False), ["index", "value"]]
    .reset_index(drop=True)
    .rename(columns={"index": "id"})
)

Prints:

   id value
0   0     a
1   3     h
2   0     a
3   2     h
4   3     a
user_100359850

We can stack the DataFrame, use Series.loc to keep only where value is Series.duplicated then Series.reset_index to convert to a DataFrame:

new_df = (
    df.stack()  # Convert to Long Form
        .droplevel(-1).rename_axis('id')  # Handle MultiIndex
        .loc[lambda x: x.duplicated(keep=False)]  # Filter Values
        .reset_index(name='value')  # Make Series a DataFrame
)

new_df:

   id value
0   0     a
1   0     a
2   2     h
3   3     h
4   3     a
user_154978880

I used here melt to reshape and duplicated(keep=False) to select the duplicates:

(df.rename_axis('id')
   .reset_index()
   .melt(id_vars='id')
   .loc[lambda d: d['value'].duplicated(keep=False), ['id','value']]
   .sort_values(by='id')
   .reset_index(drop=True)
 )

Output:

    id value
0   0     a
1   0     a
2   2     h
3   3     h
4   3     a
user_163434640
Related