Replace empty list values in Pandas DataFrame with NaN

Question

I know that similar questions have been asked before, but I literarily tried every possible solution listed here and none of them worked.

I am having a dataframe which consists of dates, strings, empty values, and empty list values. It is very huge, 8 million rows.

I want to replace all of the empty list values - so only cells that contain only [], nothing else with NaN. Nothing seems to work.

I tried this:

df = df.apply(lambda y: np.nan if (type(y) == list and len(y) == 0) else y)

as advised similarly in this question replace empty list with NaN in pandas dataframe but it doesn't change anything in my dataframe.

Any help would be appreciated.

I think maybe it's not a problem of your code. You may check the real data type of your columns. Maybe it's default to object. — Feishi
– Feishi, Commented May 4, 2017 at 17:09

Allen Qin · Accepted Answer · 2017-05-04 18:42:24Z

13

Just to assume the OP wants to convert empty list, the string '[]' and the object '[]' to na, below is a solution.

Setup

#borrowed from piRSquared's answer.
df = pd.DataFrame([
        [1, 'hello', np.nan, None, 3.14],
        ['2017-06-30', 2, 'a', 'b', []],
        [pd.to_datetime('2016-08-14'), 'x', '[]', 'z', 'w']
    ])

df
Out[1062]: 
                     0      1    2     3     4
0                    1  hello  NaN  None  3.14
1           2017-06-30      2    a     b    []
2  2016-08-14 00:00:00      x   []     z     w

Solution:

#convert all elements to string first, and then compare with '[]'. Finally use mask function to mark '[]' as na
df.mask(df.applymap(str).eq('[]'))
Out[1063]: 
                     0      1    2     3     4
0                    1  hello  NaN  None  3.14
1           2017-06-30      2    a     b   NaN
2  2016-08-14 00:00:00      x  NaN     z     w

edited May 4, 2017 at 18:42

answered May 4, 2017 at 18:20

Allen Qin

20k9 gold badges55 silver badges68 bronze badges

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

1 Comment

SLack A Over a year ago

Thank you, yes I wanted to convert all of them to NaN's. Do you have any advice for performance? It is slow on 8 million rows. Could it be improved?

piRSquared · Accepted Answer · 2017-05-04 17:33:28Z

3

I'm going to make the assumption that you want to mask actual empty lists.

pd.DataFrame.mask will turn cells that have corresponding True values to np.nan
I want to find actual list values. So I'll use df.applymap(type) to get the type in every cell and see if it is equal to list
I know that [] evaluates to False in a boolean context, so I'll use df.astype(bool) to see.
I'll end up masking those cells that are both list type and evaluate to False

Consider the dataframe df

df = pd.DataFrame([
        [1, 'hello', np.nan, None, 3.14],
        ['2017-06-30', 2, 'a', 'b', []],
        [pd.to_datetime('2016-08-14'), 'x', '[]', 'z', 'w']
    ])

df

                     0      1    2     3     4
0                    1  hello  NaN  None  3.14
1           2017-06-30      2    a     b    []
2  2016-08-14 00:00:00      x   []     z     w

Solution

df.mask(df.applymap(type).eq(list) & ~df.astype(bool))

                     0      1    2     3     4
0                    1  hello  NaN  None  3.14
1           2017-06-30      2    a     b   NaN
2  2016-08-14 00:00:00      x   []     z     w

edited May 4, 2017 at 17:33

answered May 4, 2017 at 17:27

piRSquared

296k68 gold badges509 silver badges654 bronze badges

1 Comment

SLack A Over a year ago

This is great, it works, but @Allen was right, I need to convert all of them into NaN's so I will mark his answer.

Collectives™ on Stack Overflow

Replace empty list values in Pandas DataFrame with NaN

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related