Drop duplicates with condition

user_14795898 0
Viewed 66

I have the following pandas dataframe:

index so_id
10 390
10 395
10 405
11 390
11 395
11 405
12 390
12 395
12 405

The desired output would be the following:

index so_id
10 390
11 395
12 405

Basically my goal is to drop duplicates on the column 'index' while keeping a different ascending value for the column 'so_id'

pandas python
2 Answers

We can do it but importantly, pay attention to comment above.

df=df.sort_values (by=['so_id'])#Sort df

Create temporary column t which is a classification of so_id and resort df back to original df=df.assign(t=df['so_id'].ne(df['so_id'].shift(1)).cumsum()).sort_values(by='index')

Create a temporary classification of index

df=df.assign(t1=df['index'].ne(df['index'].shift(1)).cumsum())

Select where two classess above are similar

df=df[df['t']==df['t1']].drop(columns=['t','t1'])

print(df)

    index  so_id
0     10    390
4     11    395
8     12    405
user_89869750

Here I created a dictionary which maps old index to new index by adding values to a set to get unique values and then sorting in the ascending order which is required. After dictionary is formed, we can check if values in a particular row match with the data inside the dictionary, if it matches then that combination is correct and we don't do anything to that row, if it doesn't match we simply drop that row:

import pandas as pd
df = pd.DataFrame([[10, 390], [10, 395], [10, 405], [11, 390], [11, 395], [11, 405], [12, 390], [12, 395], [12, 405]], columns=['id', 'so_id'])
idx_map = {}
idx=set()
new_idx=set()
for row in df.iterrows():
    idx.add(row[1]['id'])
    new_idx.add(row[1]['so_id'])

for i in range(len(idx)):
    idx_map[sorted(idx)[i]]=sorted(new_idx)[i]


for idx, row in df.iterrows():
    if idx_map[row[0]]==row[1]:
        continue
    else:
        df = df.drop(idx)

print(df)

Output

   id  so_id
0  10    390
4  11    395
8  12    405

Here, idx_map dictionary looks like this:

{10: 390, 11: 395, 12: 405}
user_146774350
Related