Triple Nested Dictionary to Dataframe in Python

Question

So I have a triple nested dictionary like this:

{'id': 1, 'name': 'Bitcoin', 'symbol': 'BTC', 'slug': 'bitcoin', 'num_market_pairs': 9368, 'date_added': '2013-04-28T00:00:00.000Z', 'max_supply': 21000000, 'circulating_supply': 18613050, 'total_supply': 18613050, 'platform': None, 'cmc_rank': 1, 'last_updated': '2021-01-28T19:42:02.000Z', 'quote': {'USD': {'price': 32819.62178854571, 'volume_24h': 70502184174.49294, 'percent_change_1h': 1.15180104, 'percent_change_24h': 9.69504258, 'percent_change_7d': 3.08458722, 'percent_change_30d': 23.04831942, 'market_cap': 610873261331.2906, 'last_updated': '2021-01-28T19:42:02.000Z'}}}, 

{'id': 1027, 'name': 'Ethereum', 'symbol': 'ETH', 'slug': 'ethereum', 'num_market_pairs': 5692, 'date_added': '2015-08-07T00:00:00.000Z','max_supply': None, 'circulating_supply': 114440537.124, 'total_supply': 114440537.124, 'platform': None, 'cmc_rank': 2, 'last_updated': '2021-01-28T19:42:02.000Z', 'quote': {'USD': {'price': 1339.7405089427991, 'volume_24h': 33341920762.907803, 'percent_change_1h': -0.35870203, 'percent_change_24h': 8.07719578, 'percent_change_7d': 12.21481518, 'percent_change_30d': 85.22712223, 'market_cap': 153320623450.19507, 'last_updated': '2021-01-28T19:42:02.000Z'}}}, 

I tried to convert them into a df using this:

pd.DataFrame.from_dict(data, orient='columns')

This is what I got:

| id  | name    |...|quote                                            |
| --  | ------- |---|-------------------------------------------------|
| 1   | Bitcoin |   |{'USD': {'price': 32819.62178854571, 'volume_2...|
| 1027| Ethereum|   |{'USD': {'price': 1339.7405089427991, 'volume_...|

But this is what I wished for:

| id  | name    |...|price   |volume_24h|...|
| ----| ------- |---|--------|----------|---|
| 1   | Bitcoin |   |32819.62|9.69      |   |
| 1027| Ethereum|   |1339.74 |8.07      |   |

I was trying to remove the unnecessary secondary and tertiary keys like "quote" and "USD" to just get the values but I couldn't get around it. Any suggestion here?

question from:https://stackoverflow.com/questions/65944466/triple-nested-dictionary-to-dataframe-in-python

Answer 1

import pandas as pd

data = {'id': 1, 'name': 'Bitcoin', 'symbol': 'BTC', 'slug': 'bitcoin', 'num_market_pairs': 9368, 'date_added': '2013-04-28T00:00:00.000Z', 'max_supply': 21000000, 'circulating_supply': 18613050, 'total_supply': 18613050, 'platform': None, 'cmc_rank': 1, 'last_updated': '2021-01-28T19:42:02.000Z', 'quote': {'USD': {'price': 32819.62178854571, 'volume_24h': 70502184174.49294, 'percent_change_1h': 1.15180104, 'percent_change_24h': 9.69504258, 'percent_change_7d': 3.08458722, 'percent_change_30d': 23.04831942, 'market_cap': 610873261331.2906, 'last_updated': '2021-01-28T19:42:02.000Z'}}},

df = pd.json_normalize(data)