Reading csv containing a list in Pandas

Question

I'm trying to read this csv into pandas

HK,"[u'5328.1', u'5329.3', '2013-12-27 13:58:57.973614']"
HK,"[u'5328.1', u'5329.3', '2013-12-27 13:58:59.237387']"
HK,"[u'5328.1', u'5329.3', '2013-12-27 13:59:00.346325']"

As you can see there are only 2 columns and the second one is a list, is there a way to interpret it correctly ( meaning reading the values in the list as columns) when using pd.read_csv() with arguments ?

thank you

Answer 1

One option is to use ast.literal_eval as converter:

>>> import ast
>>> df = pd.read_clipboard(header=None, quotechar='"', sep=',', 
...                   converters={1:ast.literal_eval})
>>> df
    0                                             1
0  HK  [5328.1, 5329.3, 2013-12-27 13:58:57.973614]
1  HK  [5328.1, 5329.3, 2013-12-27 13:58:59.237387]
2  HK  [5328.1, 5329.3, 2013-12-27 13:59:00.346325]

And convert those lists to a DataFrame if needed, for example with:

>>> df = pd.DataFrame.from_records(df[1].tolist(), index=df[0],
...                           columns=list('ABC')).reset_index()
>>> df['C'] = pd.to_datetime(df['C'])
>>> df
    0       A       B                          C
0  HK  5328.1  5329.3 2013-12-27 13:58:57.973614
1  HK  5328.1  5329.3 2013-12-27 13:58:59.237387
2  HK  5328.1  5329.3 2013-12-27 13:59:00.346325

Answer 2

df['new_column'] = df['column'].apply(lambda x: ast.literal_eval(x))

Just run the above code on the column containing list as string.