Goal I'm successfully importing CMC data from the APi into nested JSON, per this example: https://coinmarketcap.com/api/documentation/v1/#operation/getV2CryptocurrencyQuotesLatest
Working API Call
url = 'https://pro-api.coinmarketcap.com/v2/cryptocurrency/quotes/latest'
parameters = {
'id': '2280,4558,5221,1518,7653,5026,10688,5632,5567,5692,5566,8075,7083',
'convert': 'USD'
}
headers = {
'Accepts': 'application/json',
'X-CMC_PRO_API_KEY': CMC_AUTH
}
session = Session()
session.headers.update(headers)
response = session.get(url, params=parameters)
data = json.loads(response.text)
pprint.pprint(data)
Result
That returns the nested JSON as in the example above, with slightly different data that hasn't been updated in the documentation example.
This is the first id returned for the code above:
{'data': {'10688': {'circulating_supply': 116331159.1611021,
'cmc_rank': 347,
'date_added': '2021-06-28T00:00:00.000Z',
'id': 10688,
'is_active': 1,
'is_fiat': 0,
'last_updated': '2022-09-05T19:20:00.000Z',
'max_supply': 1000000000,
'name': 'Yield Guild Games',
'num_market_pairs': 98,
'platform': {'id': 1027,
'name': 'Ethereum',
'slug': 'ethereum',
'symbol': 'ETH',
'token_address': '0x25f8087ead173b73d6e8b84329989a8eea16cf73'},
'quote': {'USD': {'fully_diluted_market_cap': 482405830.29,
'last_updated': '2022-09-05T19:20:00.000Z',
'market_cap': 56118829.42337308,
'market_cap_dominance': 0.0057,
'percent_change_1h': 0.43811262,
'percent_change_24h': -3.21631892,
'percent_change_30d': -40.69530618,
'percent_change_60d': -32.68437619,
'percent_change_7d': -8.8136777,
'percent_change_90d': -16.90500242,
'price': 0.48240583028710726,
'tvl': None,
'volume_24h': 11205334.0356885,
'volume_change_24h': -48.174}},
'self_reported_circulating_supply': 87906250,
'self_reported_market_cap': 42406487.51867602,
'slug': 'yield-guild-games',
'symbol': 'YGG',
'tags': [{'category': 'CATEGORY',
'name': 'Collectibles & NFTs',
'slug': 'collectibles-nfts'},
{'category': 'INDUSTRY',
'name': 'Gaming',
'slug': 'gaming'},
{'category': 'INDUSTRY',
'name': 'Entertainment',
'slug': 'entertainment'},
{'category': 'CATEGORY',
'name': 'DAO',
'slug': 'dao'},
{'category': 'CATEGORY',
'name': 'Metaverse',
'slug': 'metaverse'},
{'category': 'CATEGORY',
'name': 'a16z Portfolio',
'slug': 'a16z-portfolio'},
{'category': 'CATEGORY',
'name': 'Play To Earn',
'slug': 'play-to-earn'},
{'category': 'CATEGORY',
'name': 'Animoca Brands Portfolio',
'slug': 'animoca-brands-portfolio'},
{'category': 'CATEGORY',
'name': 'Gaming Guild',
'slug': 'gaming-guild'},
{'category': 'CATEGORY',
'name': 'OKEx Blockdream Ventures Portfolio',
'slug': 'okex-blockdream-ventures-portfolio'}],
'total_supply': 1000000000,
'tvl_ratio': None},
I'm now wanting to flatten the json data and convert it to a pandas dataframe, so I've changed the last line of the above to:
Alternate code
df = pd.json_normalize(data)
pprint.pprint(df)
Result
That produces a single row with 454 columns, versus a dataframe with rows for all 13 of the id's I've specified and the corresponding column values.
From what I've read, json_normalize should be the way to flatten the data, and I'm thinking the issue is specifying that I want the nested quote data flattened and I should probably remove the nested tags list of dictionaries, but I'm not finding any information on how to do that.
I've tried adding the record_path parameter to json_normalize for quote, but that just returns KeyError: 'quote'.
I'm not finding any additional information for troubleshooting this, and would greatly appreciate any guidance in how to approach this.
AMENDED CODE
New Version
url = 'https://pro-api.coinmarketcap.com/v2/cryptocurrency/quotes/latest'
parameters = {
'id': '2280,4558',
'convert': 'USD'
}
headers = {
'Accepts': 'application/json',
'X-CMC_PRO_API_KEY': CMC_AUTH
}
session = Session()
session.headers.update(headers)
response = session.get(url, params=parameters)
data = json.loads(response.text)
df = pd.DataFrame(data["data"]).T.explode("tags")
df = pd.concat(
[df, df.pop("platform").apply(pd.Series).add_prefix("platform_")], axis=1
)
df = pd.concat(
[df, df.pop("quote").apply(pd.Series).add_prefix("quote_")], axis=1
)
df = pd.concat([df, df.pop("tags").apply(pd.Series).add_prefix("tag_")], axis=1)
print(df)
Result
I'm wanting to also have the quote_USD data in columns and values for the key:value pairs, and could drop the tags data if that simplifies it considerably.
id name symbol slug num_market_pairs \
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
2280 2280 Filecoin FIL filecoin 241
4558 4558 Flow FLOW flow 87
4558 4558 Flow FLOW flow 87
4558 4558 Flow FLOW flow 87
4558 4558 Flow FLOW flow 87
4558 4558 Flow FLOW flow 87
4558 4558 Flow FLOW flow 87
4558 4558 Flow FLOW flow 87
date_added max_supply circulating_supply total_supply \
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
2280 2017-12-13T00:00:00.000Z None 275966217 275966217
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
4558 2021-01-27T00:00:00.000Z None 1036200000 1390757889
is_active cmc_rank is_fiat self_reported_circulating_supply \
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
2280 1 34 0 None
4558 1 31 0 None
4558 1 31 0 None
4558 1 31 0 None
4558 1 31 0 None
4558 1 31 0 None
4558 1 31 0 None
4558 1 31 0 None
self_reported_market_cap tvl_ratio last_updated \
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
2280 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
4558 None None 2022-09-06T02:31:00.000Z
quote_USD \
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
2280 {'price': 6.094999087542264, 'volume_24h': 209...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
4558 {'price': 1.9051178692194919, 'volume_24h': 47...
tag_slug tag_name tag_category
2280 mineable Mineable OTHERS
2280 distributed-computing Distributed Computing INDUSTRY
2280 filesharing Filesharing INDUSTRY
2280 storage Storage CATEGORY
2280 polychain-capital-portfolio Polychain Capital Portfolio CATEGORY
2280 blockchain-capital-portfolio Blockchain Capital Portfolio CATEGORY
2280 boostvc-portfolio BoostVC Portfolio CATEGORY
2280 dcg-portfolio DCG Portfolio CATEGORY
2280 hashkey-capital-portfolio Hashkey Capital Portfolio CATEGORY
2280 a16z-portfolio a16z Portfolio CATEGORY
2280 winklevoss-capital-portfolio Winklevoss Capital Portfolio CATEGORY
2280 pantera-capital-portfolio Pantera Capital Portfolio CATEGORY
2280 web3 Web3 INDUSTRY
2280 bnb-chain BNB Chain PLATFORM
4558 collectibles-nfts Collectibles & NFTs CATEGORY
4558 coinbase-ventures-portfolio Coinbase Ventures Portfolio CATEGORY
4558 coinfund-portfolio Coinfund Portfolio CATEGORY
4558 dcg-portfolio DCG Portfolio CATEGORY
4558 ledgerprime-portfolio LedgerPrime Portfolio CATEGORY
4558 a16z-portfolio a16z Portfolio CATEGORY
4558 animoca-brands-portfolio Animoca Brands Portfolio CATEGORY
\n#pprint.pprint(data)\n\n#df = pd.json_normalize(data)\ndf = pd.json_normalize(data, record_path =['quote'])\npprint.pprint(df)\n
You can use this example to flatten the dataframe loaded from the Json from the question (
dctcontains the dictionary):Prints: