SizzyNini SizzyNini - 1 month ago 7
JSON Question

How to read API JSON data and store as Python dictionary

I am pulling in info from an API. The returned data is in JSON format. I have to iterate through and get the same data for multiple inputs. I want to save the JSON data for each input in a python dictionary for easy access. This is what I have so far:

import pandas
import requests

ddict = {}

read_input = pandas.read_csv('input.csv')
for d in read_input.values:
print(d)
url = "https://api.xyz.com/v11/api.json?KEY=123&LOOKUP={}".format(d)
response = requests.get(url)
data = response.json()
ddict[d] = data

df = pandas.DataFrame.from_dict(ddict, orient='index')

with pandas.ExcelWriter('output.xlsx') as w:
df.to_excel(w, 'output')


With the above code, I get the following output:

a.com


I also get an excel output with the data only from this first line. My input csv file has close to 400 rows so I should be seeing more than 1 line in the output and in the output excel file.

If you have a better way of doing this, that would be appreciated. In addition, the excel output I get is very hard to understand. I want to read the JSON data using dictionaries and subdictionaries but I don't completely understand the format of the underlying data - I think it looks closest to a JSON array.

I have looked at numerous other posts including Parsing values from a JSON file in Python and How do I write JSON data to a file in Python? and Converting JSON String to Dictionary, Not List (Python) and How do I save results of a "for" loop into a single variable? but none of the techniques have worked so far. I would prefer not to pickle, if possible.

I'm new to Python so any help is appreciated!

Answer

I'm not going to address your challenges with JSON here as I'll need more information on the issues you're facing. However, with respect to reading from CSV using Pandas, here's a great resource: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html.

Now, your output is being read the way it is because a.com is being considered the header (undesirable). Your read statement should be:

read_input = pandas.read_csv('input.csv', header=None)

Now, read_input is a DataFrame (documentation). So, what you're really looking for is the values in the first column. You can easily get an array of values by read_input.values. This gives you a separate array for each row. So your for loop would be:

for d in read_input.values:
    print(d[0])
    get_info(d[0])

For JSON, I'd need to see a sample structure and your desired way of storing it.