0

I am making API calls and getting back nested JSON response for every ID.

If I run the API call for one ID the JSON looks like this. 

u'{"id":26509,"name":"ORD.00001","order_type":"sales","consumer_id":415372,"order_source":"in_store","is_submitted":0,"fulfillment_method":"in_store","order_total":150,"balance_due":150,"tax_total":0,"coupon_total":0,"order_status":"cancelled","payment_complete":null,"created_at":"2017-12-02 19:49:15","updated_at":"2017-12-02 20:07:25","products":[{"id":48479,"item_master_id":239687,"name":"QA_FacewreckHaze","quantity":1,"pricing_weight_id":null,"category_id":1,"subcategory_id":8,"unit_price":"150.00","original_unit_price":"150.00","discount_total":"0.00","created_at":"2017-12-02 19:49:45","sold_weight":10,"sold_weight_uom":"GR"}],"payments":[],"coupons":[],"taxes":[],"order_subtotal":150}'

I can successfully parse this one JSON string into a dataframe using this line of code:

order_detail = json.loads(r.text)
order_detail = json_normalize(order_detail_staging)

I can iterate all my IDs through the API using this code:

lists = []

for id in df.id:
       r = requests.get("URL/v1/orders/{id}".format(id=id), headers = headers_order)
       lists.append(r.text)

Now that all my JSON responses are stored in the list. How do I write all the elements into the list into a dataframe?

The code I have been trying is this:

for x in lists:
    order_detail = json.loads(x)
    order_detail = json_normalize(x)
    print(order_detail)

I get error:

AttributeError: 'unicode' object has no attribute 'itervalues'

I know this is happening at line:

order_detail = json_normalize(x)

Why does this line work for a single JSON string but not for the list? What can I do get the list of nested JSON into a dataframe?

Thank you in advance for the help.

edit:

Traceback (most recent call last):

  File "<ipython-input-108-5051d2ceb18b>", line 3, in <module>
    for id in df.id

  File "/Users/bob/anaconda/lib/python2.7/site-packages/requests/models.py", line 802, in json
    return json.loads(self.text, **kwargs)

  File "/Users/bob/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)

  File "/Users/bob/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())

  File "/Users/bob/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")

ValueError: No JSON object could be decoded

Traceback (most recent call last):

  File "<ipython-input-108-5051d2ceb18b>", line 3, in <module>
    for id in df.id

  File "/Users/bob/anaconda/lib/python2.7/site-packages/requests/models.py", line 802, in json
    return json.loads(self.text, **kwargs)

  File "/Users/bob/anaconda/lib/python2.7/json/__init__.py", line 339, in loads
    return _default_decoder.decode(s)

  File "/Users/bob/anaconda/lib/python2.7/json/decoder.py", line 364, in decode
    obj, end = self.raw_decode(s, idx=_w(s, 0).end())

  File "/Users/bob/anaconda/lib/python2.7/json/decoder.py", line 382, in raw_decode
    raise ValueError("No JSON object could be decoded")
Improve this question

2 Answers 2

Reset to default
1
  • use response .json() method
  • feed it directly to json_normalize

Example:

df = json_normalize([
    requests.get("URL/v1/orders/{id}".format(id=id), headers = headers_order).json()
    for id in df.id
])

UPD:

failsaife version to handle incorrect responses:

def gen():
    for id in df.id:
        try:
            yield requests.get("URL/v1/orders/{id}".format(id=id), headers = headers_order).json()
        except ValueError:  # incorrect API response
            pass

df = json_normalize(list(gen()))
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for the response @Marat. I tried your line and got the error. 'ValueError: No JSON object could be decoded'
is it raised by pandas or requests?
so it is raised by requests, because the API returns invalid JSON. I edited the answer to account for this, assuming it is safe to ignore such responses,
Wow that worked! Could you please tell me how you knew it was an issue by requests?
if you look at the stack trace, the first line after your code says is File "/Users/bob/anaconda/lib/python2.7/site-packages/requests/models.py" - i.e. everything below is inside requests
0

Try this:

In [28]: lst = list(set(order_detail) - set(['products','coupons','payments','taxes']))

In [29]: pd.io.json.json_normalize(order_detail, ['products'], lst, meta_prefix='p_')
Out[29]:
   category_id           created_at discount_total     id  item_master_id              name original_unit_price pricing_weight_id  \
0            1  2017-12-02 19:49:45           0.00  48479          239687  QA_FacewreckHaze              150.00              None

   quantity  sold_weight         ...          p_tax_total  p_order_source p_consumer_id p_payment_complete p_coupon_total  \
0         1           10         ...                    0        in_store        415372               None              0

   p_fulfillment_method  p_order_type p_is_submitted  p_balance_due         p_updated_at
0              in_store         sales              0            150  2017-12-02 20:07:25

[1 rows x 29 columns]
Improve this answer

1 Comment

Thank you for the response. I get the error, TypeError: sequence item 0: expected string, numpy.int64 found

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.