将JSON API结果导出到CSV文件（垂直和带头）

Question

我正在尝试将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=', 'Records': [{'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', 'RecordID': '1', 'Results': 'AE07', 'State': '', 'StateName': 'TX ,TX ,TX ,TX ', 'Urbanization': ''}], 'TotalRecords': '1', 'TransmissionReference': '1353', 'TransmissionResults': '', 'Version': '4.0.4.48'} [Finished in 2.6s]

从这个结果中，我想在AddressLine2头中提取4个输出值。（我在API中输入4个值）例如

'Records':..............'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(','))

但是，它只提取不带标题和水平方向的值，如下图所示：

如何提取带有标题（AddressLine2）和垂直的值？在

谢谢你的帮助！在

Answer 1

我最近不得不做类似的事情。但最后我用了一种不同的方法，没有使用熊猫。在

我假设您正在使用python请求库、csv库、pandas和python3。在

假设您只尝试获取标题为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])

我知道我没有用熊猫来做这个。因此，如果您需要pandas数据帧来进行其他计算，这可能会有太多的额外工作。如果不是的话，那么希望这篇文章能给你带来那篇专栏文章！在