python - how to delete duplicate list in each row (pandas)?

Question

I have a list contained in each row and I would like to delete duplicated element by keeping the highest value from a score.

here is my data from data frame df1

        pair    score
0   [A , A ]    1.0000
1   [A , F ]    0.9990
2   [A , G ]    0.9985
3   [A , G ]    0.9975
4   [A , H ]    0.9985
5   [A , H ]    0.9990

I would like to see the result as

            pair    score
    0   [A , A ]    1.0000
    1   [A , F ]    0.9990
    2   [A , G ]    0.9985
    4   [A , H ]    0.9990

I have tried to use group by and set a score = max, but it's not working

Lists aren’t hashable, so you can’t group by them. Try making them tuples. — ALollz
– ALollz, Commented Oct 23, 2018 at 13:06

jezrael · Accepted Answer · 2018-10-23 13:06:51Z

1

First I think working with lists in pandas is not good idea.

Solution working if convert lists to helper column with tuples - then sort_values with drop_duplicates:

df['new'] = df.pair.apply(tuple)
df = df.sort_values('score', ascending=False).drop_duplicates('new')
print (df)
     pair   score     new
0  [A, A]  1.0000  (A, A)
1  [A, F]  0.9990  (A, F)
5  [A, H]  0.9990  (A, H)
2  [A, G]  0.9985  (A, G)

Or to 2 new columns:

df[['a', 'b']] = pd.DataFrame(df.pair.values.tolist())
df = df.sort_values('score', ascending=False).drop_duplicates(['a', 'b'])
print (df)
     pair   score  a  b
0  [A, A]  1.0000  A  A
1  [A, F]  0.9990  A  F
5  [A, H]  0.9990  A  H
2  [A, G]  0.9985  A  G

answered Oct 23, 2018 at 13:06

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mad_ · Accepted Answer · 2018-10-23 14:04:40Z

0

Make new column pair2 with sorted values of string type and then drop duplicates Will handle if pair have value [A,G] and [G,A] treating them same

df['pair2']=df.pair.map(sorted).astype(str)
df.sort_values('score',ascending=False).drop_duplicates('pair2',keep='first').drop('pair2',axis=1).reset_index(drop=True)

Ouput:

pair    score
[A, A]  1.0000
[A, F]  0.9990
[A, H]  0.9990
[A, G]  0.9985

edited Oct 23, 2018 at 14:04

answered Oct 23, 2018 at 13:58

mad_

8,2832 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

python - how to delete duplicate list in each row (pandas)?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related