Extracting item from a list stored as string in pandas dataframe

Question

I have the following pandas dataframe

data = [{'a': 1, 'b': '[2,3,4,5,6' }, {'a': 10, 'b': '[54,3,40,5'}] 
test = pd.DataFrame(data) 
display(test)

    a     b
0   1   [2,3,4,5,6
1   10  [54,3,40,5

I want to list the number in column b, but as the list has the [ only at the beginning, doesnt allow me to create the list, I'm trying to remove the "[" so I can extract the numbers, but I keep getting errors, what I'm doing wrong?

This is how the numbers are stored

test.iloc[1,1]

'[54,3,40,5'

And this is what I've tried to remove the "[".

test.iloc[0,1].replace("[",'', regex=True).to_list()

test.iloc[0,1].str.replace("[\]\[]", "")

What i want to achieve is to have b as a proper list so i can apply other functions.

    a     b
0   1   [2,3,4,5,6]
1   10  [54,3,40,5]

When did you get the error, while replacing the character or converting to list? — Sai
– Sai, Commented Jul 17, 2020 at 10:21
You cannot convert a string to list directly using to_list() — Sai
– Sai, Commented Jul 17, 2020 at 10:21
try this, pd.eval(test['b'] + "]") or test.b.str.replace(r"[", "").str.split(",") — sushanth
– sushanth, Commented Jul 17, 2020 at 10:28
You can try this test['b'] = test['b'].str.findall(r'\d+') — Ch3steR
– Ch3steR, Commented Jul 17, 2020 at 11:48

Ric S · Accepted Answer · 2020-07-17 10:37:16Z

1

To make your 'b' column a list you can first delete the open squared bracket at the beginning, and then use the split method on each element of your 'b' column

test['b'] = test['b'].str.replace('[', '').map(lambda x: x.split(','))

test
#     a            b
# 0   1  [2, 3, 4, 5, 6]
# 1  10  [54, 3, 40, 5]

edited Jul 17, 2020 at 10:37

answered Jul 17, 2020 at 10:24

Ric S

9,3184 gold badges30 silver badges57 bronze badges

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

Comments

Phạm Ngọc Quý · Accepted Answer · 2020-07-17 10:31:40Z

0

try it:

def func(col):
  return eval(col+']')

test['b'] = test['b'].apply(func)

answered Jul 17, 2020 at 10:31

Phạm Ngọc Quý

3692 silver badges10 bronze badges

2 Comments

Perry Over a year ago

This would be a better answer if you explained how the code you provided answers the question.

Phạm Ngọc Quý Over a year ago

i add ']' in a string and call function eval() to convert string to object

vagitus · Accepted Answer · 2020-07-17 10:34:51Z

0

import pandas as pd
data = [{'a': 1, 'b': '[2,3,4,5,6' }, {'a': 10, 'b': '[54,3,40,5'}] 
test = pd.DataFrame(data) 
print(test['b'][0][1:])
for i in range(len(test['b'])):
    test['b'][i] = test['b'][i][1:]

answered Jul 17, 2020 at 10:34

vagitus

4964 silver badges12 bronze badges

Collectives™ on Stack Overflow

Extracting item from a list stored as string in pandas dataframe

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related