Python JSON到CSV的转换

Question

我正在处理一个示例，但有一些问题。我是python的初学者，并努力提高自己。我正在尝试使用openweather api，从中获取一些数据，然后将这些数据写入csv文件。我的代码的目的是输入一个包含城市名称的txt文件，我想得到城市名称，国家代码，纬度，温度，风速，风向。然后将它们写入csv文件。我可以通过命令行输入txt文件或获取数据，但不能同时执行这两种操作。此外，我还想将数据写入csv文件。你能帮帮我吗？我可以将其写入控制台，但我需要将它们写入csv文件。但是，我无法将json对象转换为csv

我的输入.txt

Los Angeles
San Francisco
...

我的代码：

import requests
from pprint import pprint
import csv
import pandas as pd
file = input("Input the filepath: ")
with open(file) as f:
for line in f:
line = line.strip()
API_key = "MYAPIKEY"
base_url = "http://api.openweathermap.org/data/2.5/weather?"
headers = {'content-type': 'application/json'}
city_name = line

Final_url = base_url + "appid=" + API_key + "&q=" + city_name
weather_data = requests.get(Final_url).json()
print("nCurrent Weather" + city_name + ":n")
weather_data = requests.get(Final_url, headers=headers)
f = open('weather_data_file.csv', "w")
f.write(weather_data.text)
f.close()
print(f)

编辑后的问题：

CSV文件只包含最后一个城市数据，数据不在如果我用excel 打开，则为正确形式

它输出的数据：

{"coord":{"lon":-122.42,"lat":37.77},"weather":[{"id":802,"main":"Clouds","description":"scattered clouds","icon":"03d"}],"base":"stations","main":{"temp":284.74,"feels_like":280.59,"temp_min":283.15,"temp_max":286.48,"pressure":1024,"humidity":76},"visibility":10000,"wind":{"speed":5.1,"deg":260},"clouds":{"all":40},"dt":1609003065,"sys":{"type":1,"id":5817,"country":"US","sunrise":1608996226,"sunset":1609030632},"timezone":-28800,"id":5391959,"name":"San Francisco","cod":200}

Answer 1

将JSON文件写入CSV文件：

import pandas as pd
if __name__ == "__main__":
df = pd.read_json (r'ajsonfile.json')
df.to_csv (r'filename.csv', index = None)

从API编写JSON数据：

# Get JSON Data
weather_data = requests.get(yourURL, headers=yourHeaders)
# Write to .CSV
f = open('weather_data_file.csv', "w")
f.write(weather_data.text)
f.close()

Answer 2

JavaScript对象表示法(JSON(是数据的序列化表示。pandas.read_json尝试解码JSON并从中构建数据帧。在使用requests读取数据并使用.json()调用将其反序列化为python后，它不再是JSON，pandas.read_json也无法工作。

有时，您可以直接从python对象构建数据帧，但在这种情况下，您会遇到额外的问题。您一次只要求一行数据(一个城市(，其信息嵌套在多个字典中。您可以使用python将接收到的城市数据展平为所需的数据子集。由于您只能逐行工作，因此可以使用csv模块边写边写。

一种解决方案是

import requests
from pprint import pprint
import csv
openweathermap_city_csv_header = ["City Name", "Country Code", "Lat", "Long", "Temperature",
"Wind Speed", "Wind Direction"]
def openweathermap_city_flatten_record(record):
coord = record["coord"]
wind = record["wind"]
return { "City Name":record["name"], 
"Country Code":record["Code"],
"Lat":coord["lat"],
"Long":coord["lon"],
"Temperature":record["main"]["temp"],
"Wind Speed":wind["speed"],
"Wind Direction":wind["deg"] }

file = input("Input the filepath: ")
with open(file) as cities, open('weather_data_file.csv', "w") as outfile:
writer = csv.DictWriter(outfile, openweathermap_city_csv_header)
for line in f:
line = line.strip()
API_key = "MYAPIKEY"
base_url = "http://api.openweathermap.org/data/2.5/weather?"
headers = {'content-type': 'application/json'}
city_name = line
Final_url = base_url + "appid=" + API_key + "&q=" + city_name
weather_data = requests.get(Final_url, headers=headers).json()
print("nCurrent Weather" + city_name + ":n")
writer.writerow(openweathermap_city_flatten_record(weather_data))