4 dimensional nested dictionary to pandas data frame
<p>I need your help with converting a multidimensional dict to a pandas data frame. I get the dict from a JSON file which I retrieve from a API call (Shopify).</p>

<pre><code>response = requests.get("URL", auth=("ID","KEY")) 
data = json.loads(response.text)
</code></pre>

<p>The "data" dictionary looks as follows:</p>

<pre><code> {'orders': [{'created_at': '2016-09-20T22:04:49+02:00',
             'email': 'test@aol.com',
             'id': 4314127108,
             'line_items': [{'destination_location': 
                                       {'address1': 'Teststreet 12',
                                        'address2': '',
                                        'city': 'Berlin',
                                        'country_code': 'DE',
                                        'id': 2383331012,
                                        'name': 'Test Test',
                                        'zip': '10117'}, 
                             'gift_card': False,
                             'name': 'Blueberry Cup'},
                             {'destination_location': 
                                       {'address1': 'Teststreet 12',
                                        'address2': '',
                                        'city': 'Berlin',
                                        'country_code': 'DE',
                                        'id': 2383331012,
                                        'name': 'Test Test',
                                        'zip': '10117'}, 
                             'gift_card': False,
                             'name': 'Strawberry Cup'}]
}]}
</code></pre>

<p>In this case the dictionary has 4 Dimensions and I would like to convert the dict into a pandas data frame. I tried everything ranging from json_normalize() to pandas.DataFrame.from_dict(), yet I did not manage to get anywhere. When I try to convert the dict to a df, I get columns which contain list of lists.</p>

<p>My goal is to have an individual row per product.
Thanks!</p>

<p>Desired Output:</p>

<pre><code>Created at  Email           id          Name
9/20/2016   test@test.de    4314127108  Blueberry Cup
9/20/2016   test@test.de    4314127108  Strawberry Cup
</code></pre>

<p>I don't really understand how <code>json_normalize()</code> fails this so hard, I have similar data with twice the nesting depth and <code>json_normalize()</code> still manages to give me a much better result.</p>

<p>I wrote this recursive function to replace the lists in your example with dictionaries:</p>

<pre><code>def removeList(D):
  for k in D.keys():
    if isinstance(D[k],list):
      T = {}
      for i in range(len(D[k])):
        T[str(i)] = D[k][i]
      D[k] = removeList(T)
      return D
    elif isinstance(D[k],dict):
      D[k] = removeList(D[k])
      return D
    else:
      return D
</code></pre>

<p><code>json_normalize()</code> can work with the outcome of that a bit better at least.</p>

<p>However I recommend doing it manually even if it's annoying. You can create your own dictionary with your own desired structure, write all data manually into it and then convert that into your dataframe. It's a good way to check the data for consistency and do all flattening, preprocessing and normalizing you need.</p>

<p>Since I have data that's structured similar to yours, I'm using a two-step process. In the first step I create a flattened dictionary that consists of no other dictionaries but still has a list in one key (that would be <code>line_items</code> in your case). Each list entry is also flattened into a simple dictionary. Then I create a second dataframe from that list of dictionaries like this:</p>

<pre><code>ListDF = pd.DataFrame.from_dict([iFr for sl in DF["List"] for iFr in sl])
</code></pre>

<p>Since I did all the normalizing manually I was able to add necessary keys to the list items so now I can just merge the two dataframes into my final dataframe using those keys. Then I drop the <code>List</code> column and my final data structure is complete, I went from a horribly nested dictionary to a simple relational scheme that can be easily worked with.</p>

<p>I figure this would also work best for you.</p>

python json pandas dictionary dataframe