Remove row where value is found as a substring of another row in Pandas

user_13044871 0
Viewed 44

I have a Dataframe below:

data = {'Name':['Trump', 'Donald Trump', 'Melania Trump', 'Mike Pence'], 'number':[20, 21, 19, 18]}

I would like to remove a row if the string in Name on that row is found as part of another row with a leading space, i.e. Trump is found in Donald Trump (it matches Trump), and thus it remove the row with Trump.

What it is the most optimised way of doing this?

Expected output is rows 1, 2, 3 (i.e. only first row with 'Trump' is removed)

pandas python
1 Answers

I would first make a list of names to be removed, then filter the dataframe by that.

In [25]: import pandas as pd

In [26]: data = {'Name':['Trump', 'Donald Trump', 'Melania Trump', 'Mike Pence']
    ...: , 'number':[20, 21, 19, 18]}
    ...:

In [27]: df = pd.DataFrame(data)

In [28]: df.head()
Out[28]:
            Name  number
0          Trump      20
1   Donald Trump      21
2  Melania Trump      19
3     Mike Pence      18

In [29]: names_to_remove = [name for name in df.Name if any([name in _str.split(
    ...: ' ') for _str in df.Name])]


In [30]: names_to_remove
Out[30]: ['Trump']

In [31]: df = df[~df.Name.isin(names_to_remove)]

In [32]: df
Out[32]:
            Name  number
1   Donald Trump      21
2  Melania Trump      19
3     Mike Pence      18
user_31900760
Related