Pandas DataFrame stored list as string: How to convert back to list?

I have an n-by-m Pandas DataFrame df defined as follows. (I know this is not the best way to do it. It makes sense for what I'm trying to do in my actual code, but that would be TMI for this post so just take my word that this approach works in my particular scenario.)

>>> df = DataFrame(columns=['col1'])
>>> df.append(Series([None]), ignore_index=True)
>>> df
Empty DataFrame
Columns: [col1]
Index: []

I stored lists in the cells of this DataFrame as follows.

>>> df['column1'][0] = [1.23, 2.34]
>>> df
     col1
0  [1, 2]

For some reason, the DataFrame stored this list as a string instead of a list.

>>> df['column1'][0]
'[1.23, 2.34]'

I have 2 questions for you.

  1. Why does the DataFrame store a list as a string and is there a way around this behavior?
  2. If not, then is there a Pythonic way to convert this string into a list?

Update

The DataFrame I was using had been saved and loaded from a CSV format. This format, rather than the DataFrame itself, converted the list from a string to a literal.

Answers


As you pointed out, this can commonly happen when saving and loading pandas DataFrames as .csv files, which is a text format.

In your case this happened because list objects have a string representation, allowing them to be stored as .csv files. Loading the .csv will then yield that string representation.

If you want to store the actual objects, you should you use DataFrame.to_pickle() (note: objects must be picklable!).

To answer your second question, you can convert it back with ast.literal_eval:

>>> from ast import literal_eval
>>> literal_eval('[1.23, 2.34]')
[1.23, 2.34]

I just came across this problem and there is a very simple solution (pandas.eval()). I'm using pandas 0.20.0.

# SETUP
import pandas as pd
import io

csv = io.StringIO(u'''
id  list
A1  [1,2]
A2  [3,4]
A3  [5,6]
''')

df = pd.read_csv(csv, delim_whitespace = True)

# TYPE CHECK <type 'str'>
print type(df.at[0, 'list'])

# MAIN CONVERSION
df['list'] = pd.eval(df['list'])

# TYPE CHECK <type 'list'>
print type(df.at[0, 'list'])

1) There is a way around this behavior. Use loc helps here.

>>> import pandas as pd

>>> df = pd.DataFrame(columns=['column1'])
>>> df = df.append(pd.Series(data = {'column1':[None]}), ignore_index = True)

   column1
0  [None]

>>> # Add list to index 0 in column1
>>> df.loc[0,'column1'] = [1.23, 2.34]
>>> print(df.loc[0, 'column1'])
[1.23, 2.34]

2) Pythonic way to convert this string into a list. (This is probably what you want as the DataFrame you are using had been been saved and loaded from a CSV format, there are a couple solutions for this). This is an addition on pshep123's answer.

from ast import literal_eval
import pandas as pd

csv = io.StringIO(u'''
id  list
A1  [1,2]
A2  [3,4]
A3  [5,6]
''')
df = pd.read_csv(csv, delim_whitespace = True)

# Output is a string
df.loc[0, 'list']
'[1,2]'

# Convert entire column to a list
df.loc[:,'list'] = df.loc[:,'list'].apply(lambda x: literal_eval(x))

# Output is a list
df.loc[0, 'list']
[1, 2]

for reference only... pandas don't convert lists into string. ..

In [29]: data2 = [{'a': [1, 5], 'b': 2}, {'a': 5, 'b': 10, 'c': 20}]                                                                                        

In [30]: df = pd.DataFrame(data2)                                                                                                                           

In [31]: df                                                                                                                                                 
Out[31]: 
        a   b   c
0  [1, 5]   2 NaN
1       5  10  20

In [32]: df['a'][0], type(df['a'][0])                                                                                                                       
Out[32]: ([1, 5], list)

In [33]: pd.__version__
Out[33]: '0.12.0'

I had the same problem. When storing a dataframe list column to a CSV file using df.to_csv(), list columns are converted to a string e.g. "[42, 42, 42]" instead of [42, 42, 42]

Alex answer is correct and you can use literal_eval to convert the string back to a list. The problem with this approach is that you need to import an additional library and you need to apply or map the function to your dataframe. As easier way is to force Pandas to read the column as a Python object (dtype)

df["col1"].astype('O')

The O is used for Python objects including lists. More info here. Please note that this method fails if you parse empty list strings: "[]"

Alternatively you can also apply a function to your column (this one is for integers):

def stringToList(string):
    # input format : "[42, 42, 42]" , note the spaces after the commas, in this case I have a list of integers
    string = string[1:len(string)-1]
    try:
        if len(string) != 0: 
            tempList = string.split(", ")
            newList = list(map(lambda x: int(x), tempList))
        else:
            newList = []
    except:
        newList = [-9999]
    return(newList)

df["col1"] = df["col1"].apply(lambda x: stringToList(x))

You can directly use pandas - df = pd.read_csv(df_name, converters={'column_name': eval})

This will read that column as a it's corresponding dtype in python instead of a string.


A simple hack I used is to call a lambda function that indexes out the first and last elements (the list brackets in str form) and calls the split method followed by another that replaces the list elements with ints.

df['column1'] = df['column1'].apply(lambda x:x[1:-1].split(',')).apply(lambda x:[int(i) for i in x])

Need Your Help

How to use specified weights for fonts in XML

android android-xml android-styles android-fonts

Using the Fonts in XML feature you can specify various font weights for a font family. For example: