python - 将 API 结果导出到 CSV 文件(分隔方式正确吗？)

Question

我在将下面的 API 结果导出到 csv 文件时遇到问题。在我看来，分隔符是“，”(3 个字符)。分隔结果并创建干净的 CSV 文件的最佳方法是什么？

注意:此 API 可以通过输入个人地址、名字、姓氏等来帮助确定个人地址是否是最新的。

CSV 文件:

例如，如下图所示，导出的 csv 文件有 4 个地址值，这些值分隔在 4 个不同的单元格中(还包含列名称 - AddressLine2)

(我最初输入 4 行。)

API 结果(来自 request.get):

{"CASSReportLink":"https://smartmover.melissadata.net/v3/Reports/CASSReport.aspx?tkenrpt=YvBDs39g52jKhLJyl5RgHKpuj5HwDMe1pE2lcQrczqRiG3/3y5yMlixj5S7lIvLJpDyAOkD8fE8vDCg56s3UogNuAkdTbS2aqoYF5FvyovUjnXzoQaHaL8TaQbwyCQ2RHVDSbt7tI8uGzEWwEIEwt6acz9MoXfDckO7OTUvVcuA=","NCOAReportLink":"https://smartmover.melissadata.net/v3/Reports/NCOAReport.aspx?tkenrpt=8anQa424W7NYg8ueROFirapuj5HwDMe1pE2lcQrczqRiG3/3y5yMlixj5S7lIvLJpDyAOkD8fE8vDCg56s3UogNuAkdTbS2aqoYF5FvyovUjnXzoQaHaL8TaQbwyCQ2RHVDSbt7tI8uGzEWwEIEwt6acz9MoXfDckO7OTUvVcuA=","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 1.1s]

我的代码:

import requests
import json
import pandas as pd
import numpy as np


df = pd.read_csv(r"C:\users\testu\documents\travis_test.csv",delimiter = ',',na_values="nan")

url = 'https://smartmover.melissadata.net/v3/WEB/SmartMover/doSmartMover' 
payload = {'t': '1353', 'id': '138fg88jr3r', 'jobid': '1', 'act': 'NCOA, CCOA', 'cols': 'AddressLine1,AddressLine2,City,PostalCode,Results,State', 'opt': 'ProcessingType: Standard', 'List': 'test', 'full': df["appr_owner_name"], 'a1': df["prop_year_addr_line1"], 'a2': df["prop_year_addr_line2"], 'city': df["prop_addr_city"], 'state': df["prop_addr_state"], 'postal': df["prop_addr_zip"], 'ctry': 'USA'}

response = requests.get(
    url, params=payload,
    headers={'Content-Type': 'application/json'} # Using JSON here for readability in the response
)


r = response.text
print(r)

with open(r'C:\users\testu\documents\travis3.csv', 'w+') as f:
    f.write(r)

Answer 1

您的API返回值不是csv格式，而是json格式。 Tou 不能只将其写入文件中。

您必须实际将其读取为 json，提取数据并按照您在 csv 文件中所需的方式对其进行格式化。示例:

r = response.json
with open(r'C:\users\testu\documents\travis3.csv', 'w+') as f:
    cf = csv.writer(f)
    cf.writerow(r['Records'][0]['AddressLine2'].split(','))