Python:Scraping.json，输入pandas数据帧并输出为ex

Question

大家早上好，下午好，晚上好。你知道吗

因此，正如您从下面的代码片段中看到的，我不是一名程序员，我真的很难让这段代码提供所需的输出。
快速运行：

• 试图从证券交易所获得详细的公司信息
• 我正在调用一个API来检索基于包含公司代码的pandas数据帧的数据

• 然后我使用这些代码调用每个单独的公司网页，并将它们保存到列表中

• 然后，我尝试使用.get和适当的关键字，例如（'direktur'），将相关信息保存到第二个列表（fdir）中。它被输入到另一个数据帧中，然后作为excel输出 使用df.to_excel()

现在，我希望得到的输出excel文件如下所示：

[

但是，我当前得到的输出如下所示：

这是fdir[0]返回的结果：

  [{'Nama': 'Santosa', 'Jabatan': 'PRESIDEN DIREKTUR', 'Afiliasi': False}, {'Nama': 'Joko Supriyono', 'Jabatan': 'WAKIL PRESIDEN DIREKTUR', 'Afiliasi': False}, {'Nama': 'M. Hadi Sugeng Wahyudiono', 'Jabatan': 'DIREKTUR', 'Afiliasi': False}, {'Nama': 'Bambang Wijanarko', 'Jabatan': 'DIREKTUR', 'Afiliasi': False}, {'Nama': 'Rujito Purnomo', 'Jabatan': 'DIREKTUR', 'Afiliasi': False}, {'Nama': 'Handoko Pranoto', 'Jabatan': 'DIREKTUR', 'Afiliasi': False}, {'Nama': 'Mario Casimirus Surung Gultom', 'Jabatan': 'DIREKTUR', 'Afiliasi': False}]

因此，如果有人知道如何从我所拥有的东西中获得预期的产出，我将非常感激！感谢您抽出时间，完整代码如下：

import requests
import pandas as pd ; import xlsxwriter
import json
import time
# gets broad data of main page of the stock exchange
sxov = requests.get('https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfiles?draw=1&columns%5B0%5D%5Bdata%5D=KodeEmiten&columns%5B0%5D%5Bname%5D&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=false&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=KodeEmiten&columns%5B1%5D%5Bname%5D&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=false&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=NamaEmiten&columns%5B2%5D%5Bname%5D&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=false&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=TanggalPencatatan&columns%5B3%5D%5Bname%5D&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=false&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&start=0&length=700&search%5Bvalue%5D&search%5Bregex%5D=false&_=155082600847')

data = sxov.json() # save the request as .json file
df = pd.DataFrame.from_dict(data['data']) #creates DataFrame based on the data (.json) file
# removes unecessary columns from df
df.drop(["BAE", "DataID",   "Divisi",   "EfekEmiten_EBA",   "EfekEmiten_ETF",   "EfekEmiten_Obligasi",  "EfekEmiten_SPEI",  "EfekEmiten_Saham",
"Fax",  "JenisEmiten", "KodeDivisi", "Logo",    "NPKP",     "NPWP", "PapanPencatatan", "Status", "TanggalPencatatan", "id"
], axis=1, inplace=True)

cdate = time.strftime ("%Y%m%d") # creating string-variable w/ current date year|month|day
df.to_excel(f"{cdate}StockExchange_Overview.xlsx") # outputs DataFrame as Excel file

list_of_json = []
for nested_json in data['data'] :
    list_of_json.append(requests.get('https://www.idx.co.id/umbraco/Surface/ListedCompany/GetCompanyProfilesDetail?emitenType=&kodeEmiten='+nested_json['KodeEmiten']).json())

#create empty lists for directors
fdir = []

for i in range (len(list_of_json)) :
    fdir.append(list_of_json[i].get('Direktur'))
    i += 1


print (fdir[0])
# create writer object that converts DataFrame to {currentdate}Firm Details.xlsx
writer = pd.ExcelWriter(f'{cdate}Firm Details.xlsx', engine = 'xlsxwriter')

#creating the dataframes
dfdir = pd.DataFrame([fdir])
dfdir = (dfdir.T)

dfdir.to_excel(writer, sheet_name = 'Directors')

writer.save()

Answer 1

我怀疑json\u normalize会有所帮助。它会将您的json文件传输到一个平面表结构。
documentation_link

例如：

from pandas.io.json import json_normalize
with open('example_1.json') as data_file:    
    d = json.load(data_file)
    df = json_normalize(d)