如何将API源中的数据组织为从Python输出到Excel中？

Question

我使用requests（见下文）调用API源并获取一些数据（见下文）。我不知道该怎么处理

它看起来像JSON和/或字典，但在导出到Excel之前，我不知道如何在Python中处理它。我知道，我在这个阶段没有导入JSON，但我可以

我的代码：

url = 'https://quoteapi.com/api/v5/symbols/hvn.asx?appID=af5f4d73c1a54a33&averages=1&fundamentals=1&liveness=delayed'
result = requests.get(url, headers=headers)

返回的数据：

{
  "symbol": "hvn.asx",
  "realSymbol": "hvn.asx",
  "units": "price",
  "currency": "AUD",
  "tz": "AEST",
  "tickTable": "0.0005<0.1,0.0025<2,0.005",
  "quote": {
    "pctChange": 0.556,
    "quoteBases": {
      
    },
    "low": 5.39,
    "open": 5.4,
    "vwap": 5.430336243,
    "value": 16256099.035,
    "time": "2021-07-07 16:49:15",
    "change": 0.03,
    "price": 5.43,
    "status": "ADJUST_ON",
    "close": 5.43,
    "bid": 5.43,
    "prevClose": 5.4,
    "lastTradeTime": "2021-07-07 16:49:15",
    "ask": 5.44,
    "volume": 2993572,
    "high": 5.48,
    "tradeCount": 5260
  },
  "sequence": 6502147,
  "desc": {
    "firstActive": "1990-08-06",
    "underlyingIssuerSymbol": "hvn.asx",
    "issuerName": "Harvey Norman Holdings Ltd",
    "isin": "AU000000HVN7",
    "securityType": "01",
    "shortDesc": "FPO",
    "lastActive": "2021-07-07",
    "abbrevDesc": "ORDINARY",
    "longDesc": "ORDINARY FULLY PAID",
    "shortName": "HARVEY",
    "underlyingIssuerNdividendPerShare": 0.38,
    "earningsPerShare": 0.5857
  },
  "averages": {
    "ytd": {
      "vwap": 5.395,
      "tradeCount": 4418,
      "high": 6.09,
      "value": 18583818.22,
      "volume": 3431631,
      "low": 4.7,
      "open": 4.69
    },
    "year": {
      "vwap": 4.884,
      "tradeCount": 4240,
      "high": 6.09,
      "value": 18055781.32,
      "volume": 3734356,
      "low": 3.49,
      "open": 3.59
    },
    "month": {
      "vwap": 5.293,
      "tradeCount": 4322,
      "high": 5.69,
      "value": 20414631.17,
      "volume": 3865196,
      "low": 5.07,
      "open": 5.36
    },
    "week": {
      "vwap": 5.508,
      "tradeCount": 3979,
      "high": 5.69,
      "value": 19188403.29,
      "volume": 3486067,
      "low": 5.37,
      "open": 5.4
    }
  }
}

Answer 1

继续您的代码，您可以使用requests方法直接解析json字符串：

obj = result.json()

现在有了一个名为obj的python对象，它是嵌套字典的复杂结构。在python中，这很容易操作，但它看起来肯定不是表格式的，所以尝试将其导出到Excel没有多大意义。此外，如评论中所述，您还没有指出结果应该是什么样子

但是，数据中的averages键看起来确实是表格，下面是如何从中创建数据框并将其导出到excel：

import json

with open("foobar.json", "r") as f:
    obj = json.load(f)

with open("averages.json", "w") as f:
    json.dump(obj['averages'], f)

df = pd.read_json('averages.json').T
df.to_excel('averages.xlsx')

注意json.dump调用中的obj['averages']。此外，我还使用了T函数来转置数据帧，它看起来更自然（但这是可选的

In [5]: df
Out[5]: 
        vwap  tradeCount  high        value     volume   low  open
ytd    5.395      4418.0  6.09  18583818.22  3431631.0  4.70  4.69
year   4.884      4240.0  6.09  18055781.32  3734356.0  3.49  3.59
month  5.293      4322.0  5.69  20414631.17  3865196.0  5.07  5.36
week   5.508      3979.0  5.69  19188403.29  3486067.0  5.37  5.40