How to read API JSON data and store as Python dictionary

Question

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 using Python? and How do I write JSON data to a file in Python? and Converting JSON String to Dictionary Not List 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 1

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.

Answer 2

I think there is a awkwardness in you program.

Try with this:

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

Edit: iterate the read_input.values.

• SizzyNini: I made that edit but the code is still not iterating through the csv file.