["python","json","pandas","dataframe","flatten"]
How to flatten a pandas dataframe with some columns as json?

I have a dataframe df that loads data from a database. Most of the columns are json strings while some are even list of jsons. For example: id name columnA columnB 1 John {"dist": "600", "time": "0:12.10"} [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "3rd", "value": "200"}, {"pos": "total", "value": "1000"}] 2 Mike {"dist": "600"} [{"pos": "1st", "value": "500"},{"pos": "2nd", "value": "300"},{"pos": "total", "value": "800"}] ... As you can see, not all the rows have the same number of elements in the json strings for a column. What I need to do is keep the normal columns like id and name as it is and flatten the json columns like so: id name columnA.dist columnA.time columnB.pos.1st columnB.pos.2nd columnB.pos.3rd columnB.pos.total 1 John 600 0:12.10 500 300 200 1000 2 Mark 600 NaN 500 300 Nan 800 I have tried using json_normalize like so: from pandas.io.json import json_normalize json_normalize(df) But there seems to be some problems with keyerror. What is the correct way of doing this?
Here's a solution using json_normalize() again by using a custom function to get the data in the correct format understood by json_normalize function. import ast from pandas.io.json import json_normalize def only_dict(d): ''' Convert json string representation of dictionary to a python dict ''' return ast.literal_eval(d) def list_of_dicts(ld): ''' Create a mapping of the tuples formed after converting json strings of list to a python list ''' return dict([(list(d.values())[1], list(d.values())[0]) for d in ast.literal_eval(ld)]) A = json_normalize(df['columnA'].apply(only_dict).tolist()).add_prefix('columnA.') B = json_normalize(df['columnB'].apply(list_of_dicts).tolist()).add_prefix('columnB.pos.') Finally, join the DFs on the common index to get: df[['id', 'name']].join([A, B]) EDIT:- As per the comment by @MartijnPieters, the recommended way of decoding the json strings would be to use json.loads() which is much faster when compared to using ast.literal_eval() if you know that the data source is JSON.