Scraping Json to Excel Error，如何解决？

Question

试图将数据抓取并传输到 excel（csv），但不是我想要的。我的代码：import requestsimport jsonimport csvimport pandas as pdurl = 'https://www.reuters.com/companies/api/getFetchCompanyKeyMetrics/CIEL3.SA'headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36'}jsonData = requests.get(url, headers=headers).json()data = {    'Beta': [],    }for key in jsonData:    beta = jsonData['market_data']['beta']    data['Beta'].append(beta)table = pd.DataFrame(data, columns=['Beta'])table.index = table.index + 1table.to_csv('Ciel3.csv', sep=',', encoding='utf-8', index=False)print(table)这是结果：Beta0.777650.777650.777650.77765那么，它不应该只是一个值（而不是 4 个）吗？

Answer 1

您的错误是您正在遍历jsonData

如果您查看 Json 响应：

{"ts":1588131086,"market_data":{"company_name":"Cielo SA","fundamental_exchange_name":"Bolsa de Valores do Estado de Sao Paulo","ric":"CIEL3.SA","exchange_name":"Sao Paulo Stock Exchange","last":"4.230000","last_time":"2020-04-29 20:08:09","net_change":"0.050000","currency":"BRL","percent_change":"1.19617","modified":"2020-04-30 01:14:07","volume":"36291100","day_high":"4.34","day_low":"4.05","fiftytwo_wk_high":"9.07","fiftytwo_wk_low":"3.84","prev_day_close":"4.18","open":"4.11","local_name":null,"ratio_price_currency":"BRL","ratio_reporting_currency":"BRL","market_cap":"11166.11000","price_closing_or_last_bid":"4.11000","price_12_month_high":"9.03582","price_12_month_low":"3.84000","price_date":"2020-04-27T00:00:00","share_volume_10d":"18.17657","share_volume_3m":"379.72263","beta":"0.79832","fiftytwo_week_high_date":"2019-12-18T00:00:00","fiftytwo_week_low_date":"2020-04-24T00:00:00","one_day_price":"0.73529","thirteen_wk_price_return":"-41.21182","twenty_six_wk_price_return":"-47.06534","five_day_price_return":"-9.27152","fiftytwo_wk_price_return":"-45.04951","ytd_price_return":"-50.71029","mtd_price_return":"-7.43243","four_week_rel_sandp500":"-17.62747","thirteen_week_rel_sandp500":"-13.97879","twentysix_week_rel_sandp500":"-26.80281","fiftytwo_week_rel_sandp500":"-32.40910","ytd_rel_sandp500":"-27.14434","eps_excl_extra_ttm":"0.58219","eps_excl_extra_annual":"0.58166","eps_normalized_annual":"0.59728","revenue_share_FY":"4.17700","revenue_share_TTM":"4.17570","book_value_share_annual":"3.48999","book_value_share_quarterly":"3.48999","tangible_book_annual":"-0.55420","tangible_book_quarterly":"-0.55420","cash_share_annual":"0.17987","cash_share_quarterly":"0.17987","cf_share_annual":"1.05189","cf_share_ttm":"1.12846","div_share_annual":"0.25256","div_share_ttm":"0.25256","ebitd_share_ttm":"1.11631","eps_basic_excl_extra_annual":"0.58250","eps_basic_excl_extra_ttm":"0.58270","eps_incl_extra_annual":"0.58166","eps_incl_extra_ttm":"0.58219","fcf_share_ttm":"-0.33065","div_share_5y":"0.68218","pe_excl_extra_annual":"7.06598","pe_excl_extra_ttm":"7.05955","pe_normalized_annual":"6.88119","ps_annual":"0.98403","ps_ttm":"0.98403","ptbv_annual":"-99999.99000","ptbv_quarterly":"-99999.99000","pfcf_share_annual":"-99999.99000","pcf_share_ttm":"3.64126","pfcf_share_ttm":"-99999.99000","pb_annual":"1.17766","pb_quarterly":"1.17766","pe_basic_excl_extra_ttm":"14.31003","pe_excl_extra_high_ttm":"19.69764","pe_excl_low_ttm":"7.26080","pe_incl_extra_ttm":"7.05955","net_debt_interim":"8757.71000","net_debt_annual":"8757.71000","dividend_yield_5y":"4.56388","dividend_yield_indicated_annual":"8.64615","current_dividend_yield_ttm":"6.14501","fcf_annual":"-898.51600","current_ratio_annual":"1.13366","net_interest_coverage_annual":"5.37521","lt_debt_equity_annual":"87.95803","payout_ratio_annual":"37.29663","quick_ratio_annual":"-99999.99000","total_debt_equity_annual":"97.65807","current_ev_fcf_annual":"1011.01800","current_ev_fcf_ttm":"101.57140","current_ratio_quarterly":"1.13366","lt_debt_equity_quarterly":"87.95803","quick_ratio_quarterly":"-99999.99000","total_debt_equity_quarterly":"97.65807","fcf_ttm":"-898.51600","net_interest_coverage_ttm":"-99999.99000","payout_ratio_ttm":"37.29663","annual_gross_margin":"36.08872","gross_margin_ttm":"36.08872","net_profit_margin_annual":"15.73838","net_profit_margin_ttm":"15.73838","operating_margin_annual":"17.08003","operating_margin_ttm":"17.08003","pretax_margin_ttm":"22.21885","pretax_margin_annual":"22.21885","operating_margin_5y":"32.46751","pretax_margin_5y":"42.57876","focf_revenue_5y":"-5.42351","focf_revenue_ttm":"-7.91832","gross_margin_5y":"47.33752","net_profit_margin_5y":"29.70044","roa_rfy":"2.06460","roe_ttm":"2.04738","roe_rfy":"16.78189","roae_ttm":"15.28825","roi_annual":"8.91322","roi_ttm":"8.52219","roaa_5y":"6.04463","roae_5y":"37.26896","roi_5y":"18.39162","asset_turnover_annual":"0.13118","asset_turnover_ttm":"0.13009","inventory_turnover_annual":"-99999.99000","inventory_turnover_ttm":"-99999.99000","net_income_employee_annual":"-99999.99000","net_income_employee_ttm":"595294.00000","receivables_turnover_annual":"0.16722","receivables_turnover_ttm":"0.16539","revenue_employee_annual":"-99999.99000","revenue_employee_ttm":"3782435.00000","revenue_growth_quarterly_yoy":"-1.21241","revenue_growth_5y":"7.99219","eps_growth_quarterly_yoy":"-68.13928","eps_growth_ttm_yoy":"-49.60746","eps_growth_5y":"-15.54522","dividend_growth_rate_5y":"-21.23395","revenue_growth_ttm_yoy":"-2.89704","revenue_share_growth_5y":"5.15849","revenue_growth_3y":"-2.65358","eps_growth_3y":"-26.67314","book_value_share_growth_5y":"16.99827","tbv_cagr_5y":"-99999.99000","capital_spending_growth_5y":"10.94057","ebitda_cagr_5y":"-4.66895","ebitda_interim_cagr_5y":"-4.68817","focf_cagr_5y":"-99999.99000","total_debt_cagr_5y":"4.72520","net_margin_growth_5y":"-17.74423","revenue_annual":"11347.30000","revenue_ttm":"11347.30000","ebitd_annual":"3036.06800","ebitd_ttm":"3033.53400","ebt_annual":"2521.24100","ebt_ttm":"2521.24100","net_income_common_annual":"1580.15100","net_income_common_ttm":"1580.15100","ebt_normalized_annual":"2574.01700","net_income_common_normalized_annual":"1622.58700","diluted_eps_excl_extra_ttm":"0.58625"},"ric":"CIEL3.SA","status":{"code":200}}

您将获得 4 个主键

ts

market_data

ric

status

您不需要循环，因为您要提取的值存在于一个可以直接访问的对象中

beta = jsonData['market_data']['beta']

data = {'Beta': beta}

Answer 2

json 有 4 个键：ts、market_data、ric、status。这就是为什么结果中有 4 个相同的值。您正在循环键，然后将 beta 定义为 json 中单个值的直接路径。删除 for 循环并使用：

data = {

  'Beta': [],

}

beta = jsonData['market_data']['beta']

data['Beta'].append(beta)

table = pd.DataFrame(data, columns=['Beta'])