文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >将CoinMarketCap API json数据转换为扁平熊猫数据的问题

问将CoinMarketCap API json数据转换为扁平熊猫数据的问题
EN

Stack Overflow用户
提问于 2022-09-05 19:35:02
回答 1查看 73关注 0票数 1

我正在成功地将CMC数据从导入到嵌套的JSON中，如本例所示：https://coinmarketcap.com/api/documentation/v1/#operation/getV2CryptocurrencyQuotesLatest

工作API调用

代码语言：javascript
复制
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)

结果

它返回嵌套的JSON，如上面的示例所示，与文档示例中未更新的数据略有不同。

这是为上面的代码返回的第一个id：

代码语言：javascript
复制
{'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},

我现在想将json数据平平，并将其转换为熊猫数据，因此我将上面的最后一行更改为：

备用码

代码语言：javascript
复制
df = pd.json_normalize(data)
pprint.pprint(df)

结果

这将生成一个包含454列的行，而对于我指定的所有13个id的行以及相应的列值，则生成一个dataframe。

据我所读，json_normalize应该是扁平数据的方法，我认为问题是指定要将嵌套的quote数据扁平化，我可能应该删除字典的嵌套tags列表，但我找不到关于如何做到这一点的任何信息。

我尝试将record_path参数添加到json_normalize for quote，但这只是返回KeyError: 'quote'。

我没有找到任何其他的信息来解决这个问题，并且非常希望得到任何关于如何解决这个问题的指导。

修正代码

新版

代码语言：javascript
复制
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)

结果

我还希望在键:值对的列和值中包含quote_USD数据，如果这大大简化了tags数据，就可以删除它。

代码语言：javascript
复制
       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
python
json
pandas
coinmarketcap
EN

回答 1

Stack Overflow用户

发布于 2022-09-05 19:46:26

您可以使用此示例将从Json加载的数据压缩(dct包含字典)：

代码语言：javascript
复制
df = pd.DataFrame(data["data"]).T
df = df.drop(columns="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("quote_USD").apply(pd.Series).add_prefix("quote_USD_")], axis=1
)


print(df)

指纹：

代码语言：javascript
复制
      circulating_supply cmc_rank                date_added     id is_active is_fiat              last_updated  max_supply               name num_market_pairs self_reported_circulating_supply self_reported_market_cap               slug symbol total_supply tvl_ratio  platform_id platform_name platform_slug platform_symbol                      platform_token_address  quote_USD_fully_diluted_market_cap    quote_USD_last_updated  quote_USD_market_cap  quote_USD_market_cap_dominance  quote_USD_percent_change_1h  quote_USD_percent_change_24h  quote_USD_percent_change_30d  quote_USD_percent_change_60d  quote_USD_percent_change_7d  quote_USD_percent_change_90d  quote_USD_price quote_USD_tvl  quote_USD_volume_24h  quote_USD_volume_change_24h
10688   116331159.161102      347  2021-06-28T00:00:00.000Z  10688         1       0  2022-09-05T19:20:00.000Z  1000000000  Yield Guild Games               98                         87906250          42406487.518676  yield-guild-games    YGG   1000000000      None         1027      Ethereum      ethereum             ETH  0x25f8087ead173b73d6e8b84329989a8eea16cf73                        4.824058e+08  2022-09-05T19:20:00.000Z          5.611883e+07                          0.0057                     0.438113                     -3.216319                    -40.695306                    -32.684376                    -8.813678                    -16.905002         0.482406          None          1.120533e+07                      -48.174
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/73613974

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档