Python JSON list to get data and retrieve column name

Question

I am a Python beginner. I'm accessing a historical metal prices api that returns a dictionary of lists. The first item in the list is the column names, and the rest is data that falls under those column headers.

I'm trying to write code that will return the Date and Cash Buyer values for all dates on record.

Here's what I've written so far. I've tried several dictionary and list methods and have not been able to get this to work.

The URL is purposefully restricted to 1 row (rows=1) for this posting and stripped of my API key. However, this URL will work for anyone but is rate limited without an API key:

import urllib
import json
url = "https://www.quandl.com/api/v1/datasets/LME/PR_CU.json?rows=1"
response = urllib.urlopen(url)
data = json.loads(response.read())

Here's a sample output as is:

{
"code": "PR_CU",
"column_names": [
    "Date",
    "Cash Buyer",
    "Cash Seller & Settlement",
    "3-months Buyer",
    "3-months Seller",
    "15-months Buyer",
    "15-months Seller",
    "Dec 1 Buyer",
    "Dec 1 Seller",
    "Dec 2 Buyer",
    "Dec 2 Seller",
    "Dec 3 Buyer",
    "Dec 3 Seller"
],
"data": [
    [
        "2016-10-14",
        4672.0,
        4672.5,
        4691.0,
        4692.0,
        4730.0,
        4740.0,
        null,
        null,
        null,
        null,
        null,
        null
    ]
],
"description": "LME Official Prices i
"display_url": null,
"errors": {},
"frequency": "daily",
"from_date": "2012-01-03",
"id": 19701916,
"name": "Copper Prices",
"premium": false,
"private": false,
"source_code": "LME",
"source_name": "London Metal Exchange"
"to_date": "2016-10-14",
"type": null,
"updated_at": "2016-10-17T07:05:00.54
"urlize_name": "Copper-Prices"
}

Answer 1

One way would be to get the position of 'Date' and 'Cache Buyer' columns, and then iterate through the data part of the JSON.

I will name the dictionary that you loaded from the JSON as dictionary in my example:

# Get the columns and the data part of the response dictionary
columns = dictionary['column_names']
data = dictionary['data']

# This is done in case that the columns are not always in the same order
# if they are, you can just hardcode the values to 0 and 1
index_of_date = columns.index('Date')
index_of_cash_buyer = columns.index('Cash Buyer')

# As data section is a list of lists we need to
# iterate through lists of data and collect the desired values
for piece_of_data in data:
    date = piece_of_data[index_of_date]
    cash_buyer = piece_of_data[index_of_cash_buyer]
    print date, cash_buyer