Product Data with Python and CSV

For this guide, we're going to assume the following:

Your goal:
You're interested in using Datafiniti's product data to analyze trends in the women's luxury shoe market. You're a data scientist that's been tasked with the following:

Collect pricing data on women's luxury shoes from multiple online retailers.
Sort the data by brand.
Determine the average price of each brand.

Your environment and data needs:

You're working with Python.
You want to work with CSV data.

Here are the steps we'll take:

🚧
Note that we are using Python 3 for the examples below.

1. Install the requests module for Python

In your terminal, run the following to install the requests module for Python:

pip3 install requests

2. Get your API token

The next thing you'll need is your API token. The API token lets you authenticate with Datafiniti API and tells it who you are, what you have access to, and so on. Without it, you can't use the API.

To get your API token, go the Datafiniti Web Portal (https://portal.datafiniti.co), login, and click on your account name and the top-right. From there, you'll see a link to the "My Account" page, which will take you to a page showing your token. Your API token will be a long string of letters and numbers. Copy the API token or store it somewhere you can easily reference.

📘
For the rest of this document, we'll use AAAXXXXXXXXXXXX as a substitute example for your actual API token when showing example API calls.

3. Run your first search

The first thing we'll do is do write some code that will run a test search. This test search will give us a sense for what sort of data might be available. Eventually we'll refine our search so that we get back the most relevant data. Since we want women's luxury shoes, let's try a simple search that will just give us listings for shoes sold online.

Write the following code in your code editor (replace the dummy API token with your real API token):

# Illustrates an API call to Datafiniti's Product Database for shoes.
import requests
import urllib.parse
import json

# Set your API parameters here.
APIToken = 'AAAXXXXXXXXXXXX'
view = 'products_all'
format = 'JSON'
query = urllib.parse.quote_plus('categories:shoes')
records = '1'
download = 'false'

# Construct the API call.
APICall = 'https://' + APIToken + ':@api.datafiniti.co/v3/data/products?' \
			+ 'view=' + view \
			+ '&format=' + format \
			+ '&q=' + query \
			+ '&records=' + records \
			+ '&download=' + download

# Make the API call.
r = requests.get(APICall);

# Do something with the response.
if r.status_code == 200:
	print(json.loads(r.content))
else:
	print('Request failed')

You should get a response similar to this (although it may not be as pretty in your terminal):

{
  "estimated total": 884885,
  "records": [
    {
      "asins": [
        "B010XYTFM6"
      ],
      "brand": "inktastic",
      "categories": [
        "Novelty",
        "Tops & Tees",
        "Novelty & More",
        "Women",
        "Clothing, Shoes & Jewelry",
        "T-Shirts",
        "Clothing"
      ],
      "dateAdded": "2015-11-09T01:55:09Z",
      "dateUpdated": "2016-04-12T16:19:26Z",
      "imageURLs": [
        "http://ecx.images-amazon.com/images/I/41W6xSgsBbL._SX342_QL70_.jpg",
        "http://ecx.images-amazon.com/images/I/41StiamgorL._SR38,50_.jpg",
        "http://ecx.images-amazon.com/images/I/41StiamgorL._SX342_QL70_.jpg",
        "http://ecx.images-amazon.com/images/I/41W6xSgsBbL._SR38,50_.jpg"
      ],
      "keys": [
        "inktasticwomensbutterflybanjochickjuniorvnecktshirts/b010xytfm6"
      ],
      "name": "Inktastic Women's Butterfly Banjo Chick Junior V-neck T-shirts",
      "sourceURLs": [
        "http://www.amazon.com/Inktastic-Womens-Butterfly-Junior-T-Shirts/dp/B010XYTK1W",
        "http://www.amazon.com/Inktastic-Womens-Butterfly-Junior-T-Shirts/dp/B016HKYYN0",
        "http://www.amazon.com/Inktastic-Butterfly-T-Shirts-Athletic-Heather/dp/B016HKYPYS"
      ],
      "id": "AVkzMzokUmTPEltRlaJ_"
    }
  ]
}

Let's break down what the API call is all about:

API Call Component	Description
`https://`	This is the communication protocol used by the API. It's the same one used when you visit a secure website.
`AAAXXXXXXXXXXXX:@`	Your API token. You're telling the API who you are so it can respond to your request.
`api.datafiniti.co`	The location of the API.
`/v3`	You're telling the API which version to use. `v3` is our most recent and current API version.
`/data`	You're telling the API you're interested in querying data.
`/products`	Specifically, you're interested in product data.
`view=products_all`	The `view` tells the API in which fields you want your response. `products_all` will show all available fields in a record.
`format=JSON`	The `format` tells the API which data format you want to see. You can set it to `JSON` or `CSV`.
`q=categories:shoes`	The `q` tells the API what query you want to use. In this case, you're telling the API you want to search by `categories`. Any product that has `shoe` listed in its `categories` field will be returned.
`records=1`	The `records` tells the API how many records to return in the its response. In this case, you just want to see 1 matching record.
`download=false`	The `download` tells the API if you want to initiate a download request or not. Setting it to `false` means you don't, so it will show the matching records immediately in the response.

Now let's dive through the response the API returned:

Response Field	Description
`"estimated_total"`	The total number of available records in the database that match your query. If you end up downloading the entire data set, this is how many records you'll use.
`"records"`	The first available matches to your query. For most queries, you'll see 1 to 10 example records. If there are no matches, this field will be empty. Within each record returned, you'll see multiple fields shown. This is the data for each record.

Within the records field, you'll see a single product returned with multiple fields and their values associated with that product. The record shows all fields that have a value. It won't show any fields that don't have a value. E.g., the example product shown has no prices field showing, so that means the database doesn't have any pricing information for it (we can update our request later to make sure we only get products that do have prices).

Each product record will have multiple fields associated with it. You can see a full list of available fields in our Product Data Schema.

4. Refine your search

If you take a look at the sample record shown above, you'll notice that it's not actually a pair of shoes. It's actually a shirt. It was returned as a match because its category keywords included Clothing, Shoes & Jewelry. If we downloaded all matching records, we would find several products that really are shoes, but we'd also find other products like this one, which aren't.

We'll need to refine our search to make sure we're only getting shoes. To do that, we can add additional filters to the q parameter to narrow down the results. For example, we could change our query value to the following:

# Illustrates an API call to Datafiniti's Product Database for shoes.
import requests
import urllib.parse
import json

# Set your API parameters here.
APIToken = 'AAAXXXXXXXXXXXX'
view = 'products_all'
format = 'JSON'
query = urllib.parse.quote_plus('categories:shoes AND -categories:shirts AND categories:women AND (brand:* OR manufacturer:*) AND prices:*')
records = '1'
download = 'false'

# Construct the API call.
APICall = 'https://' + APIToken + ':@api.datafiniti.co/v3/data/products?' \
			+ 'view=' + view \
			+ '&format=' + format \
			+ '&q=' + query \
			+ '&records=' + records \
			+ '&download=' + download

# Make the API call.
r = requests.get(APICall);

# Do something with the response.
if r.status_code == 200:
	print(json.loads(r.content))
else:
	print('Request failed')

This query is different in a few ways:

It uses AND -categories:shirts to filter out any products that might be shirts. Note the - in front of categories.
It adds AND categories:women to narrow down results to just products for women. (We were interested in just women's shoes.)
It adds AND (brand:* OR manufacturer:*). This ensures the brand or manufacturer field is filled out in all the records I request. We call the * a "wildcard" value. Matching against a wildcard is a useful way to ensure the fields you're searching aren't empty.
It adds AND prices:*. Again, matching against a wildcard here means we're sure to only get products that have pricing information.
It changes records=1 to records=10 so we can look at more sample matches.

Notice how Datafiniti lets you construct very refined boolean queries. In the query above, we're using a mix of AND and OR to get exactly what we want.

You can run the code above to see the difference in the results.

5. Initiate a full download of the data

Once we like what we see from the sample matches, it's time to download the entire data set! To do this, we're going to update our code a fair bit (an explanation follows):

# Illustrates an API call to Datafiniti's Product Database for shoes.
import requests
import json
import urllib.parse
import time

# Set your API parameters here.
APIToken = 'AAAXXXXXXXXXXXX'
view = 'products_all'
format = 'csv'
query = urllib.parse.quote_plus('categories:shoes AND -categories:shirts AND categories:women AND (brand:* OR manufacturer:*) AND prices:*')
records = '1'
download = 'true'

# Construct the API call.
APICall = 'https://' + APIToken + ':@api.datafiniti.co/v3/data/products?' \
			+ 'view=' + view \
			+ '&format=' + format \
			+ '&q=' + query \
			+ '&download=' + download

# Make the initial API call.
r = requests.get(APICall);
if r.status_code == 200:
	# Get the request ID for the download
	requestAPICall = r.url

	# Keep checking the request status until the download has completed
	requestID = '';
	requestResponseCode = 200
	requestResponseStatus = 'STARTED';
	while (requestResponseCode == 200 and requestResponseStatus != 'COMPLETED'):
		time.sleep(5)
		print('Checking on status: ' + requestAPICall)
		request = requests.get(requestAPICall)
		requestResponse = json.loads(request.text)
		requestID = requestResponse[0]['id']
		requestResponseStatus = requestResponse[0]['status']
		print('Records downloaded: ' + str(requestResponse[0]['numDownloaded']))

	# Once the download has completed, get the list of links to the result files and download each file
	if requestResponseStatus == 'COMPLETED':
		result = requests.get('https://' + APIToken + ':@api.datafiniti.co/v3/results/' + str(requestID))
		resultResponse = json.loads(result.text)
		i = 1;
		for resultObj in resultResponse:
			filename = str(requestID) + '_' + str(i) + '.' + format
			urllib.request.urlretrieve(resultObj['url'],filename)
			print('Downloaded file: ' + filename)
			i += 1

else:
	print('Request failed')

A few things to pay attention to in the above code:

We changed format to CSV. We'll want to view the files in CSV format, so it will be easier to look at.
Remove &records=10. This tells the API to request all matching data.
Change download=false to download=true.
We've added code to (a) make the initial request, (b) check the status of the download, and (c) download the result files once they're ready.

Since we've handled multiple steps of the download process in this code, we won't go into the details here, but we do recommend you familiarize yourself with those steps. Checking them out in our Getting Started Guide for Product via a Web Browser will be helpful.

6. Open the result file(s) in Excel

The download code will save one or more result files to your project folder. Open one of those files in Excel. It will look something like:

A lot of the fields in a typical Datafiniti record will have multiple values. E.g., a typical product will have multiple keywords in the categories field. Our default CSV view (if you recall, we used products_all) will split out multi-valued fields into separate rows. So a single product record will have multiple rows. We have other views available that will show the data differently, but for now we'll stick with this one.

If you scroll over to the price columns (in Excel this should be columns AG - AY), you'll see entries for each product's prices. Products can have one or more price, and each price will have multiple values associated with it (e.g., amountMin, amountMax, currency, etc.). Each price entry will be on a separate row.

7. Analyze the results

If we want to find the average price of all these shoes, we just need to average column AG (prices.amountMin) or column AH (prices.amoundMax). When I do this in my file, I get 75.825 as the average price. Your average will probably be slightly different, since you're downloading the data at a different time than I did.