Row duplicates in a Pandas DataFrame containing a column of lists (Python3)

user_5209210 0
Viewed 24

Suppose I have this Pandas Dataframe df:

     A           B
0  100 [2, 18, 20]
1  200     [3, 17]
2  200     [3, 17]
...

where column A is of type integer and column B is of type list of integers. And suppose I wish to count how many duplicate rows are there. In this small example of 3 rows, there's one duplicate row. So df.duplicated().sum() should output me 1 for this small example. But somehow it throws an error whenever I execute the command:

TypeError: unhashable type: 'list'

How I understand why this happens is that each value in the row becomes a key to an under the hood dictionary and a value would be counting how many of such keys exist in the dataframe. But since a type list cannot be a key in a dictionary, then this fails. Not sure if I understood it correctly.

But anyways, does anybody know a workaround method for finding how many duplicates there are in the dataframe that contains a column that has lists? And how to remove them?

Would converting the list to a string then dropping the list help? Although I would be using the list again in a later time, so that might be a hassle to go back to a list when I only have the strings. Any help is greatly appreciated. Thank you.

pandas dataframe python-3.x python
1 Answers

first off, having a list in cells of a Series mightn't be super cool, hinders some fast calculations etc.

second, you can temporarily cast those lists to tuples which are hashable, detect dupes, and remove from the original frame.

so

df.loc[~df.assign(B=df.B.apply(tuple)).duplicated()]

sample run:

In [561]: df
Out[561]:
   A           B
0  1       [300]
1  3  [300, 500]
2  1       [300]
3  3    [200, 0]

In [562]: df.assign(B=df.B.apply(tuple))
Out[562]:
   A           B
0  1      (300,)
1  3  (300, 500)
2  1      (300,)
3  3    (200, 0)

In [563]: df.assign(B=df.B.apply(tuple)).duplicated()
Out[563]:
0    False
1    False
2     True
3    False
dtype: bool

In [564]: df.loc[~df.assign(B=df.B.apply(tuple)).duplicated()]
Out[564]:
   A           B
0  1       [300]
1  3  [300, 500]
3  3    [200, 0]
user_93321870
Related