I have a json output from CoinMarketCap API, I want to know how can I convert it to DataFrame? {'data': {'1027': {'circulating_supply': 119359483.749, 'cmc_rank': 2, 'date_added': '2015-08-07T00:00:00.000Z', 'id': 1027, 'is_active': 1, 'is_fiat': 0, 'last_updated': '2022-01-29T12:48:00.000Z', 'max_supply': None, 'name': 'Ethereum', 'num_market_pairs': 5482, 'platform': None, 'quote': {'USD': {'fully_diluted_market_cap': 307402654581.68, 'last_updated': '2022-01-29T12:48:00.000Z', 'market_cap': 307402654581.679, 'market_cap_dominance': 17.8235, 'percent_change_1h': 1.06045615, 'percent_change_24h': 8.70029864, 'percent_change_30d': -31.11373609, 'percent_change_60d': -44.18789242, 'percent_change_7d': 4.3122346, 'percent_change_90d': -38.78235831, 'price': 2575.435524068731, 'volume_24h': 12753359871.597496, 'volume_change_24h': -18.1848}}, 'self_reported_circulating_supply': None, 'self_reported_market_cap': None, 'slug': 'ethereum', 'symbol': 'ETH', 'tags': ['mineable', 'pow', 'smart-contracts', 'ethereum-ecosystem', 'binance-smart-chain', 'coinbase-ventures-portfolio', 'three-arrows-capital-portfolio', 'polychain-capital-portfolio', 'binance-labs-portfolio', 'blockchain-capital-portfolio', 'boostvc-portfolio', 'cms-holdings-portfolio', 'dcg-portfolio', 'dragonfly-capital-portfolio', 'electric-capital-portfolio', 'fabric-ventures-portfolio', 'framework-ventures-portfolio', 'hashkey-capital-portfolio', 'kenetic-capital-portfolio', 'huobi-capital-portfolio', 'alameda-research-portfolio', 'a16z-portfolio', '1confirmation-portfolio', 'winklevoss-capital-portfolio', 'usv-portfolio', 'placeholder-ventures-portfolio', 'pantera-capital-portfolio', 'multicoin-capital-portfolio', 'paradigm-portfolio'], 'total_supply': 119359483.749}}, 'status': {'credit_count': 1, 'elapsed': 47, 'error_code': 0, 'error_message': None, 'notice': None, 'timestamp': '2022-01-29T12:49:23.283Z'}}
1 Answers
You can do this:
This is your data:
data = {'data': {'1027': {'circulating_supply': 119359483.749, 'cmc_rank': 2, 'date_added': '2015-08-07T00:00:00.000Z', 'id': 1027, 'is_active': 1, 'is_fiat': 0, 'last_updated': '2022-01-29T12:48:00.000Z', 'max_supply': None, 'name': 'Ethereum', 'num_market_pairs': 5482, 'platform': None, 'quote': {'USD': {'fully_diluted_market_cap': 307402654581.68, 'last_updated': '2022-01-29T12:48:00.000Z', 'market_cap': 307402654581.679, 'market_cap_dominance': 17.8235, 'percent_change_1h': 1.06045615, 'percent_change_24h': 8.70029864, 'percent_change_30d': -31.11373609, 'percent_change_60d': -44.18789242, 'percent_change_7d': 4.3122346, 'percent_change_90d': -38.78235831, 'price': 2575.435524068731, 'volume_24h': 12753359871.597496, 'volume_change_24h': -18.1848}}, 'self_reported_circulating_supply': None, 'self_reported_market_cap': None, 'slug': 'ethereum', 'symbol': 'ETH', 'tags': ['mineable', 'pow', 'smart-contracts', 'ethereum-ecosystem', 'binance-smart-chain', 'coinbase-ventures-portfolio', 'three-arrows-capital-portfolio', 'polychain-capital-portfolio', 'binance-labs-portfolio', 'blockchain-capital-portfolio', 'boostvc-portfolio', 'cms-holdings-portfolio', 'dcg-portfolio', 'dragonfly-capital-portfolio', 'electric-capital-portfolio', 'fabric-ventures-portfolio', 'framework-ventures-portfolio', 'hashkey-capital-portfolio', 'kenetic-capital-portfolio', 'huobi-capital-portfolio', 'alameda-research-portfolio', 'a16z-portfolio', '1confirmation-portfolio', 'winklevoss-capital-portfolio', 'usv-portfolio', 'placeholder-ventures-portfolio', 'pantera-capital-portfolio', 'multicoin-capital-portfolio', 'paradigm-portfolio'], 'total_supply': 119359483.749}}, 'status': {'credit_count': 1, 'elapsed': 47, 'error_code': 0, 'error_message': None, 'notice': None, 'timestamp': '2022-01-29T12:49:23.283Z'}}
Define the following function that unnests any json:
def flatten_nested_json_df(df):
df = df.reset_index()
s = (df.applymap(type) == list).all()
list_columns = s[s].index.tolist()
s = (df.applymap(type) == dict).all()
dict_columns = s[s].index.tolist()
while len(list_columns) > 0 or len(dict_columns) > 0:
new_columns = []
for col in dict_columns:
horiz_exploded = pd.json_normalize(df[col]).add_prefix(f'{col}.')
horiz_exploded.index = df.index
df = pd.concat([df, horiz_exploded], axis=1).drop(columns=[col])
new_columns.extend(horiz_exploded.columns) # inplace
for col in list_columns:
#print(f"exploding: {col}")
df = df.drop(columns=[col]).join(df[col].explode().to_frame())
new_columns.append(col)
s = (df[new_columns].applymap(type) == list).all()
list_columns = s[s].index.tolist()
s = (df[new_columns].applymap(type) == dict).all()
dict_columns = s[s].index.tolist()
return df
and do the following:
results = pd.json_normalize(data)
df = pd.DataFrame(results)
outdf = flatten_nested_json_df(df)
which gives:
index data.1027.circulating_supply data.1027.cmc_rank \
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
0 0 1.193595e+08 2
data.1027.date_added data.1027.id data.1027.is_active \
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
0 2015-08-07T00:00:00.000Z 1027 1
data.1027.is_fiat data.1027.last_updated data.1027.max_supply \
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
0 0 2022-01-29T12:48:00.000Z None
data.1027.name ... data.1027.slug data.1027.symbol \
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
0 Ethereum ... ethereum ETH
data.1027.total_supply status.credit_count status.elapsed \
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
0 1.193595e+08 1 47
status.error_code status.error_message status.notice \
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
0 0 None None
status.timestamp data.1027.tags
0 2022-01-29T12:49:23.283Z mineable
0 2022-01-29T12:49:23.283Z pow
0 2022-01-29T12:49:23.283Z smart-contracts
0 2022-01-29T12:49:23.283Z ethereum-ecosystem
0 2022-01-29T12:49:23.283Z binance-smart-chain
0 2022-01-29T12:49:23.283Z coinbase-ventures-portfolio
0 2022-01-29T12:49:23.283Z three-arrows-capital-portfolio
0 2022-01-29T12:49:23.283Z polychain-capital-portfolio
0 2022-01-29T12:49:23.283Z binance-labs-portfolio
0 2022-01-29T12:49:23.283Z blockchain-capital-portfolio
0 2022-01-29T12:49:23.283Z boostvc-portfolio
0 2022-01-29T12:49:23.283Z cms-holdings-portfolio
0 2022-01-29T12:49:23.283Z dcg-portfolio
0 2022-01-29T12:49:23.283Z dragonfly-capital-portfolio
0 2022-01-29T12:49:23.283Z electric-capital-portfolio
0 2022-01-29T12:49:23.283Z fabric-ventures-portfolio
0 2022-01-29T12:49:23.283Z framework-ventures-portfolio
0 2022-01-29T12:49:23.283Z hashkey-capital-portfolio
0 2022-01-29T12:49:23.283Z kenetic-capital-portfolio
0 2022-01-29T12:49:23.283Z huobi-capital-portfolio
0 2022-01-29T12:49:23.283Z alameda-research-portfolio
0 2022-01-29T12:49:23.283Z a16z-portfolio
0 2022-01-29T12:49:23.283Z 1confirmation-portfolio
0 2022-01-29T12:49:23.283Z winklevoss-capital-portfolio
0 2022-01-29T12:49:23.283Z usv-portfolio
0 2022-01-29T12:49:23.283Z placeholder-ventures-portfolio
0 2022-01-29T12:49:23.283Z pantera-capital-portfolio
0 2022-01-29T12:49:23.283Z multicoin-capital-portfolio
0 2022-01-29T12:49:23.283Z paradigm-portfolio
[29 rows x 37 columns]
Related