API Call - Multi 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'}]
}]}
</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>Does anyone know how to approach that?</p>

<p>Thanks</p>

<p>EDITED:</p>

<p>Thank you @piRSquared. Your solution works fine! However, how you solve it if there was another product in the order? Because then it does work. JSON response of an order with 2 products is as follows (goals is to have a second row with the same "created_at". "email" etc. columns):</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>So the df in the end should be on a row by row basis for all sold products. Thank you, I really appreciate your help!</p>

<p>There are a number of ways to do this.  This is just a way I decided to do it.  You need to explore how you want to see this represented, then figure out how to get there.</p>

<pre><code>df = pd.DataFrame(data['orders'])

df1 = df.line_items.str[0].apply(pd.Series)

df2 = df1.destination_location.apply(pd.Series)

pd.concat([df.drop('line_items', 1), df1.drop('destination_location', 1), df2],
          axis=1, keys=['', 'line_items', 'destination_location'])
</code></pre>

<p><a href="http://i.stack.imgur.com/zYw6A.png" rel="nofollow"><img src="http://i.stack.imgur.com/zYw6A.png" alt="enter image description here"></a></p>

python json pandas dictionary dataframe