1

I want to achieve something like in this post: Python Dataframe: Remove duplicate words in the same cell within a column in Python, but for the entire dataframe in a efficient way.

My data looks something like this: It is a pandas data frame with a lot of columns. It has comma separated strings where there are a lot of duplicates - and I wish to remove all duplicates within those individual strings.

+--------------------+---------+---------------------+
|        Col1        |  Col2   |        Col3         |
+--------------------+---------+---------------------+
| Dog, Dog, Dog      | India   | Facebook, Instagram |
| Dog, Squirrel, Cat | Norway  | Facebook, Facebook  |
| Cat, Cat, Cat      | Germany | Twitter             |
+--------------------+---------+---------------------+

Reproducable example:

df = pd.DataFrame({"col1": ["Dog, Dog, Dog", "Dog, Squirrel, Cat", "Cat, Cat, Cat"],
                     "col2": ["India", "Norway", "Germany"],
                     "col3": ["Facebook, Instagram", "Facebook, Facebook", "Twitter"]})

I would like it to transform it to this:

+--------------------+---------+---------------------+
|        Col1        |  Col2   |        Col3         |
+--------------------+---------+---------------------+
| Dog                | India   | Facebook, Instagram |
| Dog, Squirrel, Cat | Norway  | Facebook            |
| Cat                | Germany | Twitter             |
+--------------------+---------+---------------------+
torkestativ
  • 352
  • 2
  • 15

3 Answers3

3

Try:

for col in ["col1", "col2", "col3"]:
    df[col]=df[col].str.split(", ").map(set).str.join(", ")

Outputs:

>>> df

                 col1     col2                 col3
0                 Dog    India  Facebook, Instagram
1  Dog, Cat, Squirrel   Norway             Facebook
2                 Cat  Germany              Twitter
Grzegorz Skibinski
  • 12,624
  • 2
  • 11
  • 34
2

Let us do get_dummies then dot 

s=df.col1.str.get_dummies(', ')
df['Col1']=s.dot(s.columns+',').str[:-1]
df
Out[460]: 
                 col1     col2                 col3              Col1
0       Dog, Dog, Dog    India  Facebook, Instagram               Dog
1  Dog, Squirrel, Cat   Norway   Facebook, Facebook  Cat,Dog,Squirrel
2       Cat, Cat, Cat  Germany              Twitter               Cat
BENY
  • 317,841
  • 20
  • 164
  • 234
2

you can do this:

for col in df.columns.tolist():
    df[col] = df[col].str.replace(r'\b(\w+)(,+\s+\1)+\b', r'\1')
Mit
  • 679
  • 6
  • 17