Find Duplicates in Dataframe and Return First Index values compared with Duplicate Index values

Edd 0
Viewed 69

I am hoping you can help.

I have a dataframe with duplicate string values but different Index aka ID values. I am trying to find the index/IDs values where the strings are duplicates with the twist to compare the first index/ID values with the duplicate row index/IDs.

Here is a sample for the input data:

import pandas as pd
data = [[1, 'online delivery, and now offer dedicated learning platforms...'], 
        [7, 'verything is in a state of change. There ...'], 
        [52, 'online delivery, and now offer dedicated learning platforms...'],
        [84, 'verything is in a state of change. There ...'],
        [5699, 'online delivery, and now offer dedicated learning platforms...'],
        [105687, 'you have managed to get ahead'],
        [654684684, 'More Strings']
        ]
  
df = pd.DataFrame(data, columns=['Index_ID', 'Strings'])
df.set_index('Index_ID', inplace= True)
df

enter image description here

Here is a sample for the format of the ideal output data:

import pandas as pd
data = [[1, 52], 
        [1, 5699],
        [7, 84]        
       ]
  
duplicate_indexes = pd.DataFrame(data, columns=['First_Index_ID', 'Duplicate_Index_ID'])
duplicate_indexes

enter image description here

The dataframe I am working with is large with long strings, this an effort to reduce the size but retaining the Indexes/IDs

Thank you

duplicates indexing python
2 Answers

can u check this code it should help

import pandas as pd

data = [[1, 'online delivery, and now offer dedicated learning platforms...'],
        [7, 'verything is in a state of change. There ...'],
        [52, 'online delivery, and now offer dedicated learning platforms...'],
        [84, 'verything is in a state of change. There ...'],
        [5699, 'online delivery, and now offer dedicated learning platforms...'],
        [105687, 'you have managed to get ahead'],
        [654684684, 'More Strings']
        ]

df = pd.DataFrame(data, columns=['Index_ID', 'Strings'])
df1 = df.groupby('Strings')['Index_ID'].apply(list).reset_index(name='duplicates')
print(df1['duplicates'])
Wael Jlassi0

You could try the following:

res = (
    df
    .groupby('Strings').agg(
        First_Index_ID=('Index_ID', 'first'),
        Duplicate_Index_ID=('Index_ID', lambda c: c.to_list()[1:])
    )
    .explode('Duplicate_Index_ID')
    .loc[lambda df: df['Duplicate_Index_ID'].notna()]
    .reset_index(drop=True)
)
  • Grouping over column Strings collects the rows that have the same string.
  • Then aggregate the groups into 2 new columns, the ones you want: The first one picks the first Index_ID of the group, and the second one puts all other indices, the duplicates, in a list.
  • Now .explode the column with the duplicates list, and remove those rows in the result that are without duplicates (.explode turns empty lists into a NaN).

Result for your example is:

   First_Index_ID Duplicate_Index_ID
0               1                 52
1               1               5699
2               7                 84
Timus0
Related