时间戳

首页 > 解决方案 > 从 json 在 pandas 中创建一个表

python - 从 json 在 pandas 中创建一个表

问题描述

我真的被困在从嵌套的 json 创建一个表中。Coinmarketcap API 请求的 json 输出：

data = {'status': {'timestamp': '2020-05-05T21:34:45.057Z', 'error_code': 0, 'error_message': None, 'elapsed': 8, 'credit_count': 1, 'notice': None}, 'data': {'1': {'urls': {'website': ['https://bitcoin.org/'], 'technical_doc': ['https://bitcoin.org/bitcoin.pdf'], 'twitter': [], 'reddit': ['https://reddit.com/r/bitcoin'], 'message_board': ['https://bitcointalk.org'], 'announcement': [], 'chat': [], 'explorer': ['https://blockchain.coinmarketcap.com/chain/bitcoin', 'https://blockchain.info/', 'https://live.blockcypher.com/btc/', 'https://blockchair.com/bitcoin', 'https://explorer.viabtc.com/btc'], 'source_code': ['https://github.com/bitcoin/']}, 'logo': 'https://s2.coinmarketcap.com/static/img/coins/64x64/1.png', 'id': 1, 'name': 'Bitcoin', 'symbol': 'BTC', 'slug': 'bitcoin', 'description': 'Bitcoin (BTC) is a consensus network that enables a new payment system and a completely digital currency. Powered by its users, it is a peer to peer payment network that requires no central authority to operate. On October 31st, 2008, an individual or group of individuals operating under the pseudonym "Satoshi Nakamoto" published the Bitcoin Whitepaper and described it as: "a purely peer-to-peer version of electronic cash, which would allow online payments to be sent directly from one party to another without going through a financial institution."', 'notice': None, 'date_added': '2013-04-28T00:00:00.000Z', 'tags': ['mineable'], 'tag-names': ['Mineable'], 'tag-groups': ['APPLICATION'], 'is_hidden': 0, 'platform': None, 'category': 'coin'}, '2': {'urls': {'website': ['https://litecoin.org/'], 'technical_doc': [], 'twitter': ['https://twitter.com/LitecoinProject'], 'reddit': ['https://reddit.com/r/litecoin'], 'message_board': ['https://litecointalk.io/', 'https://litecoin-foundation.org/'], 'announcement': ['https://bitcointalk.org/index.php?topic=47417.0'], 'chat': ['https://telegram.me/litecoin'], 'explorer': ['https://blockchair.com/litecoin', 'https://chainz.cryptoid.info/ltc/', 'http://explorer.litecoin.net/chain/Litecoin', 'https://ltc.tokenview.com/en', 'https://explorer.viabtc.com/ltc'], 'source_code': ['https://github.com/litecoin-project/litecoin']}, 'logo': 'https://s2.coinmarketcap.com/static/img/coins/64x64/2.png', 'id': 2, 'name': 'Litecoin', 'symbol': 'LTC', 'slug': 'litecoin', 'description': 'Litecoin is a peer-to-peer cryptocurrency created by Charlie Lee. It was created based on the Bitcoin protocol but differs in terms of the hashing algorithm used. Litecoin uses the memory intensive Scrypt proof of work mining algorithm. Scrypt allows consumer-grade hardware such as GPU to mine those coins.', 'notice': None, 'date_added': '2013-04-28T00:00:00.000Z', 'tags': ['mineable'], 'tag-names': ['Mineable'], 'tag-groups': ['APPLICATION'], 'is_hidden': 0, 'platform': None, 'category': 'coin'}}}

编码：

result = pd.json_normalize(data,'data',['website'], errors='ignore')
print(result)

输出：

0     website
0  1  NaN
1  2  NaN

我想要实现的是这样的：

0     website
0  1  https://bitcoin.org/
1  2  https://litecoin.org/

我尝试了很多东西，真的很沮丧。提前致谢！

标签： pythonjsonpandasdataframe

解决方案

您需要稍微处理一下数据以获得您想要的数据。

(
    pd.DataFrame(data['data'])
    .loc['urls']
    .apply(lambda x: x['website'][0])
    .to_frame('website')
)

    website
1   https://bitcoin.org/
2   https://litecoin.org/

要获取技术文档，您可以执行类似的操作。您收到错误的原因是因为某些元素没有技术文档。

(
    pd.DataFrame(data['data'])
    .loc['urls']
    .apply(lambda x: (x['technical_doc']+[''])[0])
    .to_frame('website')
)

要获取徽标，您需要定位“logo”而不是“urls”，因为它与“urls”处于同一级别

(
    pd.DataFrame(data['data'])
    .loc['logo']
    .to_frame('logo')
)

推荐阅读