python - 将 JSON API 结果导出到 CSV 文件(垂直与标题)
问题描述
我正在尝试将 JSON API 结果导出为具有正确格式的 CSV。
注意:SmartMover API 可以通过输入个人地址、名字、姓氏等来帮助确定个人地址是否是最新的。
下面是我的 JSON 格式的 API 结果。
JSON API 结果
{'CASSReportLink': ' https://smartmover.melissadata.net/v3/Reports/CASSReport.aspx?tkenrpt=YvBDs39g52jKhLJyl5RgHKpuj5HwDMe1pE2lcQrczqRiG3/3y5yMlixj5S7lIvLJpDyAOkD8fE8vDCg56s3UogNuAkdTbS2aqoYF5FvyovUjnXzoQaHaL8TaQbwyCQ2RB7tIlszGy5+LqFnI7Xdr6sjYX93FDkSGei6Omck5OF4= ', 'NCOAReportLink': ' https://smartmover.melissadata.net/v3 /Reports/NCOAReport.aspx?tkenrpt=8anQa424W7NYg8ueROFirapuj5HwDMe1pE2lcQrczqRiG3/3y5yMlixj5S7lIvLJpDyAOkD8fE8vDCg56s3UogNuAkdTbS2aqoYF5FvyovUjnXzoQaHaL8TaQbwyCQ2RB7tIlszGy5+LqFnI7Xdr6sjYX93FDkSGei6Omck5OF4=', '记录': [{'AddressExtras': '', 'AddressKey': '78704,78704', 'AddressLine1': ' , , ,STE C-100 ', 'AddressLine2': '1009 W MONROE ST ,1600 S 5TH ST ,1008 W MILTON ST ,3939 BEE CAVES RD ', 'AddressTypeCode': '', 'BaseMelissaAddressKey': '', 'CarrierRoute': '', 'City': 'Austin ,Austin ,Austin ,Austin ', 'CityAbbreviation': 'Austin ,Austin ,Austin ,Austin', 'CompanyName': '', 'CountryCode': 'US', 'CountryName': 'United States', 'DeliveryIndicator': ' ','DeliveryPointCheckDigit':'','DeliveryPointCode':'','MelissaAddressKey':'','MoveEffectiveDate':'','MoveTypeCode':'','PostalCode':'78704,78704,78704,78746' , '记录 ID': '1','结果':'AE07','状态':'','StateName':'TX,TX,TX,TX','城市化':''}],'TotalRecords':'1','TransmissionReference': '1353', 'TransmissionResults': '', 'Version': '4.0.4.48'} [2.6s 完成]}], 'TotalRecords': '1', 'TransmissionReference': '1353', 'TransmissionResults': '', 'Version': '4.0.4.48'} [2.6s 完成]}], 'TotalRecords': '1', 'TransmissionReference': '1353', 'TransmissionResults': '', 'Version': '4.0.4.48'} [2.6s 完成]
从这个结果中,我想在 AddressLine2 标头中提取 4 个输出值。(我在 API 中输入了 4 个值)例如,
'记录':........'AddressLine2':'1009 W MONROE ST',......
所以我编写了以下 Python 查询:
r = response.json()
df = pd.read_csv(r"C:\users\testu\documents\travis_test.csv",delimiter = ',',na_values="nan")
with open(r'C:\users\testu\documents\travis3.csv', 'w+') as f:
cf = csv.writer(f)
cf.writerow(r['Records'][0]['AddressLine2'].split(','))
print(r['Records'][0]['AddressLine2'].split(','))
但是,它只提取了没有 Header AND 水平的值,如下图所示:
如何使用 Header (AddressLine2) AND 垂直提取值?
谢谢你的帮助!
解决方案
我最近不得不做类似的事情。不过,我最终使用了不同的方法,并没有使用 Pandas。
我假设您使用的是 python requests 库、csv 库、pandas 和 python 3。
假设您只是想获得带有标题 AddressLine2 的一列,那么这样的事情应该可以工作:
import requests
import unicodedata
import csv
url = "The API endpoint"
# Build an array to contain the Address values. Add the header AddressLine2
# by appending the string to output_1.
output_1 = []
output_1.append("AddressLine2")
# Get the information from the API using requests.
response = requests.get(url)
data = response.json()
# Collect only Address Line 2 from the JSON output. properly encode/decode the
# string and add it to output_1.
for record in data['records']:
addressline2 = record['addressline2']
decode_1 = unicodedata.normalize('NFKD', addressline2)
output_1.append(decode_1)
# Write the values to a column
with open(csvfile, 'w') as fp:
writer = csv.writer(fp, dialect = 'excel')
for val in output_1:
writer.writerow([val])
I realize I didn't use pandas for this. So this might be too much extra work if you need that pandas dataframe for other calculations. If not, though, then this will hopefully get you that column!
推荐阅读
- java - 前端不获取后端。AWS, Heroku
- swift - 从多个集合中为每个用户 firebase swift 获取文档
- c# - 从优化的角度来看,在 C# 统一中,我们应该直接将数据类型与数字相乘还是每个数字都应该存储在变量中
- oracle-sqldeveloper - Oracle SQL Developer Cloud Wallet Mac电脑报错:配置文件无效
- python - 如何获取编译成 python exe 的文件夹的路径?
- javascript - 如何限制可以在不使用验证的 ASP.NET 视图中写入的数字范围?
- c++ - 在 Dialog 初始化期间运行代码,在绘制 GUI 之后
- api - 您将使用 API 的过程称为什么?
- python - 用于识别图像何时出现在屏幕上的区域中的 python 脚本
- node.js - 下载
of a url with minimal data usage</h1> <div id="body"><p>For the purpose of generating links to another websites I need to download content of tag.</p> <p>But I would like to use as minimal bandwidth as possible. In