迭代多个URL以存储DataFrame的JSON响应
迭代多个URL以存储DataFrame的JSON响应
提问于 2019-03-05 18:11:33
我有一个动态API URL，每个URL使用JSON作为响应来获取数据，如下所示。
{
"@type":"connection",
"id":"001ZOZ0B00000000006Z",
"orgId":"001ZOZ",
"name":"WWW3",
"description":"Test connection2",
"createTime":"2018-07-20T18:28:05.000Z",
"updateTime":"2018-07-20T18:28:53.000Z",
"createdBy":"xx.xx@xx.com.dev",
"updatedBy":"xx.xx@xx.com.dev",
"agentId":"001ZOZ08000000000007",
"runtimeEnvironmentId":"001ZOZ25000000000007",
"instanceName":"ShareConsumer",
"shortDescription":"Test connection2",
"type":"TOOLKIT",
"port":0,
"majorUpdateTime":"2018-07-20T18:28:05.000Z",
"timeout":60,
"connParams":{
"WSDL URL":"https://xxxservices1.work.com/xxx/service/xxport2/n5/Integration%20System/API__Data?wsdl",
"Must Understand":"true",
"DOMAIN":"n5",
"agentId":"001ZOZ0800XXX0007",
"agentGroupId":"001ZOZ25000XXX0007",
"AUTHENTICATION_TYPE":"Auto",
"HTTP Password":"********",
"Encrypt password":"false",
"orgId":"001Z9Z",
"PRIVATE_KEY_FILE":"",
"KEY_FILE_TYPE":"PEM",
"mode":"UPDATE",
"CERTIFICATE_FILE_PASSWORD":null,
"CERTIFICATE_FILE":null,
"TRUST_CERTIFICATES_FILE":null,
"Username":"xxx@xxx",
"CERTIFICATE_FILE_TYPE":"PEM",
"KEY_PASSWORD":null,
"TIMEOUT":"60",
"Endpoint URL":"https://wxxservices1.xx.com/xxx/service/xxport2/n5/Integration%20System/API__Data",
"connectionTypes":"NOAUTH",
"HTTP Username":"API@n5",
"Password":"********"
}
}这里需要注意的是，我有大约50个URL，它们提供了这种类型的JSON数据。我使用下面的代码迭代它，但是我不能在Python pandas中存储来自每个URL的每个响应的dataframe。它将是仅存储在那里的最后一个响应。
我还想将整个数据帧转换为CSV。
将URL响应的每个结果的响应附加到dataframe，然后转换为CSV的最佳方法是什么？
Python代码如下：
import requests
from urllib.request import Request, urlopen
from urllib.request import urlopen, URLError, HTTPError
import urllib.error
import json
import pandas as pd
from pandas.io.json import json_normalize
import os
import csv
#This CSV file where we are getting ID and iterating over it for each url for get JSON data for the each URL
ConnID_data_read=pd.read_csv('ConnID.csv', delimiter = ',')
df = pd.DataFrame(ConnID_data_read)
user_iics_loginURL='https://xx-us.xxx.com/ma/api/v2/user/login'
headers = {
'Content-Type': "application/json",
'Accept': "application/json",
'cache-control': "no-cache"
}
payload = "{\r\n\"@type\": \"login\",\r\n\"username\": \"xx@xx.com.xx\",\r\n\"password\": \"xxxx\"\r\n}"
response = requests.request("POST", user_iics_loginURL, data=payload, headers=headers)
resp_obj = json.loads(response.text)
session_id = resp_obj['SessionId']
server_URL = resp_obj['serverUrl']
print(session_id)
Finaldf = pd.DataFrame()
for index, row in df.iterrows():
api_ver="/api/v2/connection/"+row['id']
#https://xx-us.xxx.com/saas/api/v2/connection/001ZOZ0B000000000066
conndetails_url = server_URL+api_ver
print(conndetails_url)
act_headers = {
'icSessionId': session_id,
'Content-Type': "application/json",
'cache-control': "no-cache",
}
act_response = requests.get(conndetails_url.strip(),headers=act_headers)
print(act_response.text)
print("Creating Data Frame on this***********************")
act_json_data= json.loads(act_response.text)
flat_json = json_normalize(act_json_data)
print(flat_json)
Conndf = pd.DataFrame(flat_json)
Finaldf.append(Conndf)
Finaldf.to_csv('NewTest.csv')回答 1
Stack Overflow用户
回答已采纳
发布于 2019-03-05 21:05:51
我首先注意到的是：
flat_json = json_normalize(act_json_data)
print(flat_json)
Conndf = pd.DataFrame(flat_json)当你使用flat_json = json_normalize(act_json_data)时，flat_json已经是一个数据帧了。做Conndf = pd.DataFrame(flat_json)是不必要的，也是多余的，虽然不会造成问题，但这只是你不需要的额外代码。
其次，这是问题所在。在追加数据帧时，需要将其设置为等于其自身。因此，改变：
Finaldf.append(Conndf)至
Finaldf = Finaldf.append(Conndf)我也只是休息索引，因为这只是我附加数据帧时的一个习惯：
Finaldf = Finaldf.append(Conndf).reset_index(drop=True)除了这一行之外，它看起来还不错，您应该可以使用Finaldf.to_csv('NewTest.csv')将完整的数据帧保存到csv
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55000334
复制相关文章
相似问题
腾讯云开发者
