How to replace values inside a list inside a pandas row

Question

I have a pandas dataframe that looks like this

2   zero    zero    zero    zero    zero    zero    zero    2   zero    zero    ... 6   6   zero    [2, 4]  zero    2   zero    zero    zero    zero
3   1   zero    6   1   zero    zero    zero    zero    zero    zero    ... zero    zero    zero    zero    4   zero    zero    5   zero    zero
4   zero    zero    zero    zero    6   zero    zero    [2, 0]  zero    zero    ... zero    zero    zero    zero    zero    zero    zero    zero    zero    zero

each row contains some integers, lists and zero string

I'm trying to replace the 0 inside the list with another value like this one [2,0]. I want to convert it to [2,7].

I tried train['finally'] = train['finally'].map({0:7})

this is how train['finally'] looks like

0    [zero, zero, zero, zero, 2, zero, zero, zero, ...
1    [zero, zero, zero, 3, zero, zero, zero, 4, zer...
2    [zero, zero, zero, zero, zero, zero, zero, 2, ...
3    [1, zero, 6, 1, zero, zero, zero, zero, zero, ...
4    [zero, zero, zero, zero, 6, zero, zero, [2, 0]...
5    [zero, zero, 3, zero, zero, 4, zero, zero, zer...
6    [zero, zero, zero, zero, zero, zero, zero, zer...
7    [zero, zero, zero, zero, [3, 6], zero, zero, z...
8    [zero, [1, 3], zero, zero, zero, zero, zero, z...
9    [zero, zero, 5, zero, zero, 3, zero, 1, 2, [1,...
Name: finally, dtype: object

and I also tried visits_df.replace(0,7)

but they didn't give me the desired result How can I do it?

Thanks in advance

What about 0s outside the lists? If there are any, what happens to those? — cs95, Oct 22 '17 at 02:25
I can replace them using df.replace with no problem, the problem is the values inside the list — Muhammed Eltabakh, Oct 22 '17 at 02:27
So, the problem is you have a column of strings, but are making pandas search for the integer 0. — cs95, Oct 22 '17 at 02:29
no I made sure of that all the elements inside the data frame are int64 except for the zero(string) — Muhammed Eltabakh, Oct 22 '17 at 02:32
But you're accessing `train['finally']` which is a single column containing all this stuff. Or am I wrong? Can you please post only the relevant columns in your data that you are trying to replace please? — cs95, Oct 22 '17 at 02:32
yes I tried that and I tried converting the column to a seperate data frame to replace the values inside it — Muhammed Eltabakh, Oct 22 '17 at 02:34
Please post the first 10 rows of df['finally'] only, please. Also print `df['finally'].dtypes and tell me what it says. — cs95, Oct 22 '17 at 02:35
I'm sorry for the confusion check the latest update, just to clear visits_df and train['finally'] are the same data, but in two shapes a pandas column and a pandas data frame so any way would do it for me — Muhammed Eltabakh, Oct 22 '17 at 02:42

cs95 · Accepted Answer · 2017-10-22T02:54:43.220

2

df.replace will look for cells that contain that value, and replace it with the target.

In your case, you're dealing with a column of lists, so something a little more aggressive is needed. Let's try astype(str) + str.replace + ast.literal_eval.

import ast

df['finally'] = df['finally'].astype(str)\
         .str.replace('0', '7').apply(ast.literal_eval)

Cast to string, convert, and convert back as needed.

Miniature demo with a python list. The principle remains the same when translated to pandas.

In [52]: data = ['zero', [0, 1, 0, 2], 'zero', 'zero', 1, 0]

In [53]: str(data).replace('0', '7')
Out[53]: "['zero', [7, 1, 7, 2], 'zero', 'zero', 1, 7]"

In [54]: ast.literal_eval(_)
Out[54]: ['zero', [7, 1, 7, 2], 'zero', 'zero', 1, 7]

edited Oct 22 '17 at 02:54

answered Oct 22 '17 at 02:20

cs95

379,657
97
704
746

Thanks a lot, it works, but is there a way to keep 7 as an int cause I kinda need to maintain that for further processing – Muhammed Eltabakh Oct 22 '17 at 02:52
@MuhammedEltabakh Fortunately for you, `ast` will convert it back. See my edit for a demo. – cs95 Oct 22 '17 at 02:54
Hello could you help with this https://stackoverflow.com/questions/46879936/ast-module-zero-zero-zero-zero-2-zero-zero-zero-zero-zero-ze – Muhammed Eltabakh Oct 22 '17 at 22:53

How to replace values inside a list inside a pandas row

1 Answers1