Split a column of pandas dictionaries into multiple columns

Question

I have the following csv with the first line as the header:

id,data a,"{'1': 0.7778, '3': 0.5882, '2': 0.9524, '4': 0.5556}" b,"{'1': 0.7778, '3': 0.5, '2': 0.7059, '4': 0.2222}" c,"{'1': 0.8182, '3': 0.2609, '2': 0.5882}" 

I need to find something like this

 id 1 2 3 4 a 0.7778 0.9524 0.5882 0.5556 b 0.7778 0.7059 0.5 0.2222 c 0.8182 0.5882 0.2609 NaN 

where the dictionary keys are columns.

How to do this using pandas?

Answer 1

You can do this with the Python ast module:

 import ast import pandas as pd df = pd.read_csv('/path/to/your.csv') dict_df = pd.DataFrame([ast.literal_eval(i) for i in df.data.values]) >>> dict_df 1 2 3 4 0 0.7778 0.9524 0.5882 0.5556 1 0.7778 0.7059 0.5000 0.2222 2 0.8182 0.5882 0.2609 NaN df = df.drop('data',axis=1) final_df = pd.concat([df,dict_df],axis=1) >>> final_df id 1 2 3 4 0 a 0.7778 0.9524 0.5882 0.5556 1 b 0.7778 0.7059 0.5000 0.2222 2 c 0.8182 0.5882 0.2609 NaN