Removing values form a list in pandas dataframe column based on another list

Question

I have a column in a dataframe which contains lists. I want to be able to remove elements from these lists based on elements that I have in another list (as shown below).

I tried to use list comprehension but it seems to give no result.

import pandas as pd

sys_list = ['sys1', 'sys2', 'sys3']
df = pd.DataFrame({'A':[['sys1', 'sys2', 'user1'], 
                        ['user3', 'user6', 'user1'], 
                        ['sys1', 'sys2', 'sys3']]})

df['A'] = [item for item in df['A'] if item not in sys_list]

print(df)

                       A
0    [sys1, sys2, user1]
1  [user3, user6, user1]
2     [sys1, sys2, sys3]

I need to achieve this:

                       A
0                [user1]
1  [user3, user6, user1]
2                     []

Any thoughts?

jezrael · Accepted Answer · 2019-09-06 11:00:52Z

3

Use Series.apply:

df['B'] = df['A'].apply(lambda x: [item for item in x if item not in set(sys_list)])
print (df)
                       A                      B
0    [sys1, sys2, user1]                [user1]
1  [user3, user6, user1]  [user3, user6, user1]
2     [sys1, sys2, sys3]                     []

Or similar list comprehension like deleted answer:

df['B'] = [[item for item in l if item not in set(sys_list)] for l in df['A']]

Or solution with sets with set.difference:

df['B'] = df['A'].map(set(sys_list).difference).map(list)

edited Sep 6, 2019 at 11:00

answered Sep 6, 2019 at 10:46

jezrael

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

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

3 Comments

yatu Over a year ago

Surprisingly .map(set(sys_list).difference) performs slightly better. I would have expected both approaches to perform just as well

kajnwunda Over a year ago

Thank you! It worked on a sample that I had provided. However, when I apply any of these solutions to my actual df I get "TypeError: 'float' object is not iterable". I have checked values and all of them are strings so I am super puzzled whit this...

jezrael Over a year ago

@kajnwunda - I think there should be missing values, is possible remove them before by df = df.dropna(subset=['A']) ?

anky · Accepted Answer · 2019-09-06 10:45:50Z

2

with apply:

df.A.apply(lambda x: [i for i in x if i not in sys_list])

0                  [user1]
1    [user3, user6, user1]
2                       []
Name: A, dtype: object

answered Sep 6, 2019 at 10:45

anky

75.3k11 gold badges46 silver badges76 bronze badges

1 Comment

anky Over a year ago

@YevhenKuzmovych " [[item for item in l if item not in sys_list] for l in df['A']] also works , please dont delete

yatu · Accepted Answer · 2019-09-06 10:59:05Z

2

You may use sets for a better performance (this approach assumes that the order within the lists is not important, as it will change):

sys_set = set(['sys1', 'sys2', 'sys3'])

df['A'] = (df.A.map(set)-sys_set).map(list)

print(df)
                    A
0                [user1]
1  [user6, user1, user3]
2                     []

edited Sep 6, 2019 at 10:59

answered Sep 6, 2019 at 10:47

yatu

88.6k12 gold badges93 silver badges148 bronze badges

Collectives™ on Stack Overflow

Removing values form a list in pandas dataframe column based on another list

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related