2

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!

Community
  • 1
  • 1
SizzyNini
  • 275
  • 1
  • 4
  • 11

2 Answers2

3

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.

robeat
  • 61
  • 3
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.

Laurent LAPORTE
  • 21,958
  • 6
  • 58
  • 103