How to split Pandas dictionary?

Question

I have a Pandas series like this

pd.DataFrame({"name": ['John','Mary','Tommy'],
              "time": ['"data": [{"t": "16:50"},{"t": "17:05"}]',
                       '"data": [{"t": "16:10"}, {"t": "17:11"}, {"t": "17:12"}]',
                       np.nan]})

Now when it comes to data it would look like this

    name                                               time
0   John            "data": [{"t": "16:50"},{"t": "17:05"}]
1   Mary  "data": [{"t": "16:10"}, {"t": "17:11"}, {"t":...
2  Tommy                                                NaN

In column time is a dictionary (in string format) and it contains a list (max 3 items, sometimes it is NaN). As the max list size is known so I would like to flatten my data into the following.

    name  time1  time2  time3
0   John  16:50  17:05    NaN
1   Mary  16:10  17:11  17:12
2  Tommy    NaN    NaN    NaN

Except using for loop, I am not sure how to do that in Panda's way. Thanks in advance.

Possible duplicate of [Splitting dictionary/list inside a Pandas Column into Separate Columns](https://stackoverflow.com/questions/38231591/splitting-dictionary-list-inside-a-pandas-column-into-separate-columns) — Anubhav Singh, Jun 05 '17 at 06:41
Thanks @AnubhavSingh. I also have a look at that one too. But I can't get my Pandas object right because my time column is a string. I guess what need to be done is 1. In time column, convert string to json 2. Extract the list from that JSON and put it into a column. When this 2 steps are done then I may be able to apply the solution on that page. — Winston, Jun 05 '17 at 06:53

score 1 · Accepted Answer · answered Jun 05 '17 at 06:48

You can evaluate the string to a dict and then convert the list to columns. Finally merge the new columns with name.

pd.concat([data['name'],
           data.time.apply(lambda x: eval('{'+x+'}')['data'] if pd.notnull(x) else np.nan)\
           .apply(pd.Series).applymap(lambda x: x['t'] if pd.notnull(x) else x)],axis=1)\
           .rename(columns={0:'time1',1:'time2',2:'time3'})
Out[567]: 
    name  time1  time2  time3
0   John  16:50  17:05    NaN
1   Mary  16:10  17:11  17:12
2  Tommy    NaN    NaN    NaN

How to split Pandas dictionary?

1 Answers1