Convert JSON dictionary to Pandas Dataframe

I need to convert a JSON dictionary to a Pandas DataFrame, but the embedding is tripping me up.

Here is basically what the JSON dict looks like.

{
  "report": "{'name':{'data':[{'key 1':'value 1','key 2':'value 2'},{'key 1':'value 1','key 2':'value 2'}]}}"
}

In the DataFrame, I want the keys to be the column headers and values in the rows below them.

The extra layer of embedding is throwing me off somewhat from all the usual methods of doing this.

One tricky part is ‘name’ will change each time I get this JSON dict, so I can’t use an exact sting value for ‘name’.

>Solution :

Your JSON looks a bit odd. It looks more like a Python dict converted to a string, so you can use ast.literal_eval (a built-in function) to convert it to a real dict, and then use pd.json_normalize to get it into a dataframe form:

import ast

j = ...
parsed_json = ast.literal_eval(j['report'])
df = pd.json_normalize(parsed_json, record_path=[list(parsed_json)[0], 'data'])

Output:

>>> df
     key 1    key 2
0  value 1  value 2
1  value 1  value 2