如何将API返回的列表解析为数据帧

Question

我请求一个数据循环，我想得到一堆硬币的id，通过它们运行一个指示器来检测哪一个是趋势，并过滤掉其余的

API_URL = 'https://api.coingecko.com/api/v3'

r_coins_d = requests.get(API_URL + '/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=10&page=1&sparkline=false')
d_coins = r_coins_d.json()
df_coins_markets = pd.DataFrame(d_coins)

#=========== GET COIN IDS =======
for n in range(10):
    coin_id = df_coins_markets.id[n]
    r_market_d = requests.get(API_URL + f'/coins/{coin_id}/market_chart?vs_currency=usd&days=30&interval=hourly')
    d_market = r_market_d.json()
    df_market = pd.DataFrame(d_market)
    df_market.columns = ['date', 'price', 'haha']
    #df_market['date'] = pd.to_datetime(df_market['date'], unit='ms')
    print(d_market)

但是，以下代码返回一个错误：

TypeError: unhashable type: 'list'

如何进步

编辑：完整错误消息：

le "debug.py", line 24, in <module>
    df_market['date'] = pd.to_datetime(df_market['date'], unit='ms')
  File "/Users/teo/.local/share/virtualenvs/trendingcoin-nriNAUCq/lib/python3.8/site-packages/pandas/core/tools/datetimes.py", line 801, in to_datetime
    cache_array = _maybe_cache(arg, format, cache, convert_listlike)
  File "/Users/teo/.local/share/virtualenvs/trendingcoin-nriNAUCq/lib/python3.8/site-packages/pandas/core/tools/datetimes.py", line 173, in _maybe_cache
    if not should_cache(arg):
  File "/Users/teo/.local/share/virtualenvs/trendingcoin-nriNAUCq/lib/python3.8/site-packages/pandas/core/tools/datetimes.py", line 137, in should_cache
    unique_elements = set(islice(arg, check_count))
TypeError: unhashable type: 'list'

更新：在遵循Trenton的建议后，如果我将输入保持为df_market = pd.DataFrame(d_market['prices'], columns=['dateTime', 'price'])，它就会工作

但是，如果我添加另一列，我希望

df_market = pd.DataFrame(d_market[['prices', 'total_volumes']], columns=['dateTime', 'price']) 我再次得到一个错误，TypeError:unhabable Type:list

Answer 1

第一节

import pandas as pd
import requests
import matplotlib.pyplot as plt

# make the API call
API_URL = 'https://api.coingecko.com/api/v3'

r_coins_d = requests.get(API_URL + '/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=10&page=1&sparkline=false')
d_coins = r_coins_d.json()

# create the markets dataframe
df_coins_markets = pd.DataFrame(d_coins)

# display(df_coins_markets.head())
         id symbol      name                                                                           image  current_price    market_cap  market_cap_rank  fully_diluted_valuation  total_volume  high_24h       low_24h  price_change_24h  price_change_percentage_24h  market_cap_change_24h  market_cap_change_percentage_24h  circulating_supply  total_supply  max_supply       ath  ath_change_percentage                  ath_date        atl  atl_change_percentage                  atl_date                                                                               roi              last_updated
0   bitcoin    btc   Bitcoin        https://assets.coingecko.com/coins/images/1/large/bitcoin.png?1547033579       37958.00  704703722512                1             7.947549e+11   47467298406  38237.00  36673.000000        723.720000                      1.94367           1.168963e+10                           1.68678        1.862056e+07  2.100000e+07  21000000.0  41940.00               -9.76302  2021-01-08T15:05:37.863Z  67.810000            55711.87093  2013-07-06T00:00:00.000Z                                                                              None  2021-02-05T23:14:54.266Z
1  ethereum    eth  Ethereum     https://assets.coingecko.com/coins/images/279/large/ethereum.png?1595348880        1713.19  195808148792                2                      NaN   38923140914   1752.05   1587.800000         92.100000                      5.68139           9.863290e+09                           5.30442        1.145515e+08           NaN         NaN   1752.05               -2.72207  2021-02-05T16:15:12.089Z   0.432979           393534.55297  2015-10-20T00:00:00.000Z  {'times': 59.35104667718437, 'currency': 'btc', 'percentage': 5935.104667718438}  2021-02-05T23:13:13.252Z
2    tether   usdt    Tether  https://assets.coingecko.com/coins/images/325/large/Tether-logo.png?1598003707           1.00   27851925281                3                      NaN   88244095697      1.00      0.994689          0.005368                      0.53863           1.790294e+07                           0.06432        2.779522e+10  2.779522e+10         NaN      1.32              -24.36226  2018-07-24T00:00:00.000Z   0.572521               74.79873  2015-03-02T00:00:00.000Z                                                                              None  2021-02-05T23:10:05.466Z

---

第二节

• 如果使用df_market = pd.DataFrame(d_market)，将创建lists列

                                prices                         market_caps                        total_volumes
0  [1609978421091, 10.051749696667137]  [1609978421091, 9599471028.799435]  [1609978421091, 1433598906.2376666]
1  [1609981399992, 10.404913579249927]  [1609981399992, 9800416299.991219]   [1609981399992, 1484399005.370463]

• 每个列表分别包含一个'date'和'price'、'market_caps'和'total_volumes'的相关值
• 循环已被更新，以正确地将lists中的信息提取到单独的数据帧中，然后将数据帧与pandas.concat组合

df_market_dict = dict()  # create a dict of dataframes

# iterate through the unique coin market ids
for coin_id in df_coins_markets.id.unique()[:5]:  # first 5
    print(f'Coin ID: {coin_id}')

    # call the API
    r_market_d = requests.get(API_URL + f'/coins/{coin_id}/market_chart?vs_currency=usd&days=30&interval=hourly')

    # extract the JSON from the response
    d_market = r_market_d.json()
    
    # separately extract each key from the json
    prices = pd.DataFrame(d_market['prices'], columns=['date', 'prices']).set_index('date')
    market_caps = pd.DataFrame(d_market['market_caps'], columns=['date', 'market_caps']).set_index('date')
    total_volumes = pd.DataFrame(d_market['total_volumes'], columns=['date', 'total_volumes']).set_index('date')

    # combine the separate dataframes
    df_market = pd.concat([prices, market_caps, total_volumes], axis=1)
    
    # convert the index to a datetime dtype
    df_market.index = pd.to_datetime(df_market.index, unit='ms')
    
    # add the market_data dataframe to the dict, with the coin_id as the key
    df_market_dict[coin_id] = df_market  
    
    display(df_market.head())  # use print if not in a notebook or spyder

• 显示来自df_market_dict的数据帧

df_market_dict['bitcoin'].head()

                               prices   market_caps  total_volumes
date                                                              
2021-01-07 01:05:05.955  37215.781816  6.929761e+11   7.262550e+10
2021-01-07 02:08:34.785  36981.672166  6.871691e+11   7.031503e+10
2021-01-07 03:02:56.008  37059.042450  6.890434e+11   7.341887e+10
2021-01-07 04:14:05.845  37493.615768  6.971249e+11   7.513032e+10
2021-01-07 05:21:11.600  37473.956686  6.967610e+11   6.715542e+10

从dict中绘制DataFrames

fig, ax = plt.subplots(figsize=(9, 7))
for k, v in df_market_dict.items():
    v.plot(y='prices', label=f'{k}', ax=ax)

ax.set_title('Prices')
ax.set_yscale('log')
ax.legend(title='Coin ID', bbox_to_anchor=(1.05, 1), loc='upper left')
plt.show()

Answer 2

使用pd.concat()汇总详细请求的所有结果。有>；在pd.json_normalize()输出之后的1.5k列只是一个示例

import requests
API_URL = 'https://api.coingecko.com/api/v3'

r_coins_d = requests.get(API_URL + '/coins/markets', params={"vs_currency":"usd"})
d_coins = r_coins_d.json()
df = pd.DataFrame(d_coins)

df2 = pd.DataFrame()
for id in df.loc[:10,"id"].values:
    df2 = pd.concat([df2, pd.json_normalize(requests.get(f"{API_URL}/coins/{id}").json())])


df2s = df2.loc[:,[c for c in df2.columns if "." not in c or ".usd" in c or ".en" in c]]

print(df2s.iloc[:,:10].to_markdown())

输出

^{tb1}$