In pandas/python, reading array stored as string

Question

I have a pandas dataframe where one of the columns has array of strings as each element.

So something like this.

  col1 col2
0 120  ['abc', 'def']
1 130  ['ghi', 'klm']

Now when i store this to csv using to_csv it seems fine. When i read it back using from_csv i seems to read back. But then when i analyse the value in each cell the array is

'[' ''' 'a' 'b' 'c' and so on. So essentially its not reading it as an array but a set of strings. Can somebody suggest how I can convert this string into an array?

I mean to say the array has been stored like a string

'[\'abc\',\'def\']'

Andy Hayden · Accepted Answer · 2014-04-16 20:43:19Z

40

As mentioned in the other questions, you should use literal_eval here:

from ast import literal_eval
df['col2'] = df['col2'].apply(literal_eval)

In action:

In [11]: df = pd.DataFrame([[120, '[\'abc\',\'def\']'], [130, '[\'ghi\',\'klm\']']], columns=['A', 'B'])

In [12]: df
Out[12]:
     A              B
0  120  ['abc','def']
1  130  ['ghi','klm']

In [13]: df.loc[0, 'B']  # a string
Out[13]: "['abc','def']"

In [14]: df.B = df.B.apply(literal_eval)

In [15]: df.loc[0, 'B']  # now it's a list
Out[15]: ['abc', 'def']

answered Apr 16, 2014 at 20:43

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

4 Comments

Hammad Hassan Over a year ago

Can I get some explanation how literal_eval is working for the mentioned problem?

Andy Hayden Over a year ago

@HammadHassan it tries to parse a string into a python object, similar to json.loads.

Marco Over a year ago

I'm pretty sure that this is slower than using pandas own split function -- see the answer below.

pas-calc Aug 20 at 10:22

worth to mention that this works if the data was stored as a list, and not as numpy array, (because the string representation is using spaces instead of comma), so df["col"] = list(data) or data.tolist() is helpful

AMM · Accepted Answer · 2014-04-16 20:33:57Z

6

Nevermind got it.

All i had to do was

arr = s[1:-1].split(',')

This got rid of the square brackets and also split the string into an array like I wanted.

answered Apr 16, 2014 at 20:33

AMM

18.1k24 gold badges68 silver badges78 bronze badges

Comments

shaktimaan · Accepted Answer · 2014-04-16 20:35:32Z

2

Without pandas, this is one way to do it using the ast modules' literal_eval():

>>> data = "['abc', 'def']"
>>> import ast
>>> a_list = ast.literal_eval(data)
>>> type(a_list)
<class 'list'>
>>> a_list[0]
'abc'

answered Apr 16, 2014 at 20:35

shaktimaan

12.1k2 gold badges31 silver badges33 bronze badges

3 Comments

Andy Hayden Over a year ago

with pandas, you should also use literal_eval!

shaktimaan Over a year ago

@AndyHayden Ah okay! Never used pandas, wouldn't know :)

AMM Over a year ago

this is more what i wanted.

Alex S · Accepted Answer · 2014-04-16 20:33:15Z

0

Maybe try using a different separator value? Like so:

DataFrame.to_csv(filepath, sep=';')

and then read with

DataFrame.from_csv(filepath, sep=';')

answered Apr 16, 2014 at 20:33

Alex S

4,9147 gold badges45 silver badges70 bronze badges

Collectives™ on Stack Overflow

In pandas/python, reading array stored as string

4 Answers 4

4 Comments

Comments

3 Comments

Comments

Hot Network Questions