How to remove empty values from the pandas DataFrame from a column type list

Question

Just looking forward a solution to remove empty values from a column which has values as a list in a sense where we are already replacing some strings beforehand, where it's a column of string representation of lists.

In df.color we are Just replacing *._Blue with empty string:

Example DataFrame:

df = pd.DataFrame({ 'Bird': ["parrot", "Eagle", "Seagull"], 'color': [ "['Light_Blue','Green','Dark_Blue']", "['Sky_Blue','Black','White', 'Yellow','Gray']", "['White','Jet_Blue','Pink', 'Tan','Brown', 'Purple']"] })

>>> df
      Bird                                              color
0   parrot                 ['Light_Blue','Green','Dark_Blue']
1    Eagle      ['Sky_Blue','Black','White', 'Yellow','Gray']
2  Seagull  ['White','Jet_Blue','Pink', 'Tan','Brown', 'Pu...

Result of above DF:

>>> df['color'].str.replace(r'\w+_Blue\b', '')
0                                 ['','Green','']
1           ['','Black','White', 'Yellow','Gray']
2    ['White','','Pink', 'Tan','Brown', 'Purple']
Name: color, dtype: object

Usually in python it easily been done as follows..

>>> lst = ['','Green','']
>>> [x for x in lst if x]
['Green']

I'm afraid if something like below can be done.

df.color.mask(df == ' ')

For dataframes that contain lists or other hard to paste objects, you should use to_dict to create a minimal reproducible example, so that it is easy to re-create. — user3483203
– user3483203, Commented Aug 15, 2019 at 15:30
@user3483203, sorry for that .. Just updated the info on the post , hope that will helpful. — Karn Kumar
– Karn Kumar, Commented Aug 15, 2019 at 15:34
So your column isn't a column of lists, it's a column of string representation of lists? — user3483203
– user3483203, Commented Aug 15, 2019 at 15:34

BENY · Accepted Answer · 2019-08-15 15:27:23Z

3

You can using the explode(pandas 0.25.0) then concat the list back

 df['color'].str.replace(r'\w+_Blue\b', '').explode().loc[lambda x : x!=''].groupby(level=0).apply(list)

edited Aug 15, 2019 at 15:27

answered Aug 15, 2019 at 15:19

BENY

324k22 gold badges176 silver badges250 bronze badges

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

5 Comments

Karn Kumar Over a year ago

thnx @Wen but np.nan doesn't work in version '0.21.0' , looking for generic solution which may work with almost all versions

BENY Over a year ago

@pygo check the updat e

Karn Kumar Over a year ago

It comes with error AttributeError: 'Series' object has no attribute 'explode'

BENY Over a year ago

@pygo explode is new in pandas 0.25.0, please update your pandas

Karn Kumar Over a year ago

:-) hmm , okay in that sense we need to have some other way around.. tnx

user3483203 · Accepted Answer · 2019-08-15 15:38:25Z

2

You don't have a column of lists, you have a column that contains string representation of lists. You can do this all in a single step using ast.literal_eval and str.endswith. I would use a list-comprehension here which should be faster than apply

import ast

fixed = [
    [el for el in lst if not el.endswith("Blue")]
    for lst in df['color'].apply(ast.literal_eval)
]

df.assign(color=fixed)

      Bird                              color
0   parrot                            [Green]
1    Eagle       [Black, White, Yellow, Gray]
2  Seagull  [White, Pink, Tan, Brown, Purple]

answered Aug 15, 2019 at 15:38

user3483203

51.3k10 gold badges72 silver badges104 bronze badges

1 Comment

Karn Kumar Over a year ago

Thnx mile @user3483203 .

anky · Accepted Answer · 2019-08-15 15:42:10Z

1

Another way using filter and apply:

(df['color'].str.replace(r'\w+_Blue\b', '')
     .apply(lambda x: list(filter(bool, ast.literal_eval(x)))))

0                              [Green]
1         [Black, White, Yellow, Gray]
2    [White, Pink, Tan, Brown, Purple]

answered Aug 15, 2019 at 15:42

anky

75.3k11 gold badges46 silver badges76 bronze badges

1 Comment

Karn Kumar Over a year ago

thnx @anky_91 :-)

Collectives™ on Stack Overflow

How to remove empty values from the pandas DataFrame from a column type list

Example DataFrame:

Result of above DF:

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Example DataFrame:

Result of above DF:

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related