Python JSON to CSV conversion

Question

I am working on an example but stuck with some points. I am a beginner for python and try to improve myself. I am trying to use openweather api and get some data from it and then write these data to a csv file. The aim of my code is input a txt file which contains city names, I want to get City name, Country code, lat long, Temperature, Wind speed, Wind direction. and then write them to a csv file. I can input the txt file or get the data with input from the command line but can not do both. And also I want to write the data to a csv file. Could you please help me? I can write it to the console, but I need to write them to the csv file. But, I can not convert my json object to csv

My input.txt

Los Angeles
San Francisco
...

My code:

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)

The problem after edit:

The CSV file just contains the last city data and data is not in a proper form if I open with excel

The data it outputs:

{"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}

When asking questions about Pandas please always include the [tag:pandas] tag, as described in the [tag:python] tag wiki. — ChrisGPT was on strike, Dec 26 '20 at 16:54
Can you post `weather_data`, assuming its not too large? (and then just a sample)? — tdelaney, Dec 26 '20 at 17:17
JSON is a serialized string representing data. Your `weather_data` isn't JSON - its a collection of python objects that have been created from JSON. `pd.from_json` is meant to deserialize JSON and pull a table out of it (and if the JSON doesn't look like a table to pandas, it fails). You'll likely be able to create a dataframe like `df = pd.DataFrame(weather_data)` - assuming `weather_data` is a dict holding lists. We need to see the data to know. — tdelaney, Dec 26 '20 at 17:22
Now you are getting `weather_data` twice - overwriting the first json with the response object on the second one. Your original code was doing `pprint(weather_data)` - that's the good stuff. — tdelaney, Dec 26 '20 at 17:23
That data will need to transformation to make it a CSV. Do you want all of the data? Since you have nested dicts you'll have to do some flattening and the `"weather"` value could be problematic as it is a list of values. I'll post a sketch of what I think will work. The `csv` module is likely a better fit than pandas here. — tdelaney, Dec 26 '20 at 17:40
Sir if you can give an working answer I will be very thankful, I tried a lot of answers and links but none of them worked, @tdelaney I need; City name, Country code, lat long, Temperature, Wind speed, Wind direction — , Dec 26 '20 at 17:43

Ahmad hassan · Answer 1 · 2020-12-26T17:08:42.490

3

To write your JSON file to a CSV file:

import pandas as pd

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

To write your JSON data from an API:

# 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()

edited Dec 26 '20 at 17:08

answered Dec 26 '20 at 16:47

Ahmad hassan

1,039
7
13

I tried your code but did not work. My json file name is weather_data, I can not see this variable in your code – Dec 26 '20 at 16:50
I just wrote a sample to let you get the idea of how to parse from JSON file to CSV file. You can manage/name files as you want :) – Ahmad hassan Dec 26 '20 at 16:52
I have written it like this but got tons of errors; df = pd.read_json(weather_data) df.to_csv (r'test.csv', index = None) – Dec 26 '20 at 16:53
Note that, weather_data should be a json file name. If yes as you did, please show me that error – Ahmad hassan Dec 26 '20 at 16:55
1

@daprogramma, "tons of errors" isn't very helpful. _Specific_ errors are better. Feel free to edit your question if it's easier to format. Please read [ask]. – ChrisGPT was on strike Dec 26 '20 at 16:55
It is just taking the last city data to the file and file is not in a proper form – Dec 26 '20 at 17:18
Review the following for your problem with dictionaries, https://stackoverflow.com/questions/10373247/how-do-i-write-a-python-dictionary-to-a-csv-file – Ahmad hassan Dec 26 '20 at 17:21
Could you please delete your answer @Ahmadhassan – Dec 26 '20 at 17:26

tdelaney · Accepted Answer · 2020-12-26T20:33:51.040

JavaScript Object Notation (JSON) is a serialized representation of data. pandas.read_json tries to decode the JSON and build a dataframe from it. After you've read the the data with requests and deserialized it into python with the .json() call, its not JSON anymore and pandas.read_json won't work.

Sometimes you can build a dataframe directly from the python object, but in this case you've got an additional problem. You are only asking for one row of data at a time (one city) and its information is nested in multiple dictionaries. You can use python to flatten the received city data into the subset of data you want. And since you are only working row by row anyway, use the csv module to write the rows as you go.

A solution is

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))

Hi, thank you a lot for your answer, I get the idea but have an error, could you please look; writer = csv.DictWriter(openweathermap_city_csv_header) TypeError: __init__() missing 1 required positional argument: 'fieldnames' — , Dec 26 '20 at 19:25
Thank you a lot, this helped me very much and also learned the subject :) — , Dec 26 '20 at 20:47

Python JSON to CSV conversion

2 Answers2