Business Data with Python and CSV

For this guide, we're going to assume you're interested in using Datafiniti's business data to do some marketing analysis on hotels. Let's say you're a data scientist that's been tasked with the following:

  1. Collect data on hotels.
  2. Sort the data by state.
  3. Find which states have the most hotels.

Your environment and data needs:

  1. You're working with Python.
  2. 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 your settings in the left navigation bar. From there, you'll see 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 security reasons, your API token will be automatically changed whenever you change your password.

📘

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 hotels, let's try a simple search that will just give us online listings for hotels.

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 Business Database for hotels.
import requests
import urllib.parse
import json

# Set your API parameters here.
API_token = 'AAAXXXXXXXXXXXX'
format = 'JSON'
query = 'categories:hotels'
num_records = 1
download = False

request_headers = {
	'Authorization': 'Bearer ' + API_token,
	'Content-Type': 'application/json',
}
request_data = {
    'query': query,
    'format': format,
    'num_records': num_records,
    'download': download
}

# Make the API call.
r = requests.post('https://api.datafiniti.co/v4/businesses/search',json=request_data,headers=request_headers);

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

You should get a response similar to this:

{
  "num_found": 139666,
  "total_cost": 1,
  "records": [
    {
      "address": "7030 Amin Dr",
      "categories": [
        "Hotels"
      ],
      "city": "Chattanooga",
      "country": "US",
      "dateAdded": "2016-11-04T23:50:14Z",
      "dateUpdated": "2016-11-04T23:50:14Z",
      "descriptions": [
        {
          "dateSeen": [
            "2016-11-09T19:26:53Z"
          ],
          "sourceURLs": [
            "http://www.hotels.com/ho141351/?locale=en_US&pos=HCOM_US"
          ],
          "value": "No-frills hotel in Hamilton Place with health club"
        }
      ],
      "features": [
        {
          "key": "Services",
          "value": [
            "24-hour front desk, Dry cleaning/laundry service, Laundry facilities, Free newspapers in lobby"
          ]
        },
        {
          "key": "Nearby Attractions",
          "value": [
            "[In Hamilton Place, Hamilton Place Mall (1.1 mi / 1.7 km), Dragon Dreams Museum (2.7 mi / 4.4 km), Tennessee Valley Railroad Museum (3.6 mi / 5.8 km), Concord Golf Club (3.8 mi / 6 km), Brown Acres Golf Course (4 mi / 6.5 km)]"
          ]
        }
      ],
      "imageURLs": [
        "https://exp.cdn-hotels.com/hotels/1000000/120000/117400/117332/117332_95_n.jpg",
        "https://exp.cdn-hotels.com/hotels/1000000/120000/117400/117332/117332_107_n.jpg"
      ],
      "keys": [
        "us/tn/chattanooga/7030amindr/1104338646"
      ],
      "latitude": "35.04281",
      "longitude": "-85.158",
      "name": "Mainstay Suites Chattanooga",
      "numRoom": 77,
      "phones": [
        "8004916126"
      ],
      "postalCode": "37421",
      "province": "TN",
      "reviews": [
        {
          "date": "2013-11-11T00:00:00Z",
          "dateAdded": "2016-11-04T23:50:14Z",
          "dateSeen": [
            "2016-11-06T00:00:00Z",
            "2016-08-12T00:00:00Z"
          ],
          "rating": 2,
          "sourceURLs": [
            "https://www.hotels.com/hotel/141351/reviews%20/"
          ],
          "text": "Hotel was ok. Room not as up to date as I expected.",
          "title": "Was ok",
          "username": "A Traveler"
        },
        {
          "date": "2014-11-03T00:00:00Z",
          "dateAdded": "2016-11-04T23:50:14Z",
          "dateSeen": [
            "2016-08-09T00:00:00Z",
            "2016-08-27T00:00:00Z",
            "2016-07-18T00:00:00Z"
          ],
          "rating": 3,
          "sourceURLs": [
            "https://www.hotels.com/hotel/141351/reviews%20/"
          ],
          "text": "It was adequate for our one night stay there. Staff was very friendly and the room was clean but not very big. I would recommend to someone for a very short stay.",
          "username": "scott"
        }
      ],
      "sourceURLs": [
        "http://www.hotels.com/ho141351/?locale=en_US&pos=HCOM_US",
        "https://www.hotels.com/hotel/141351/reviews%20/"
      ],
      "id": "AVwcsllU_7pvs4fzx-yW"
    }
  ]

Let's break down each of the parameters we sent in our request:

API Call ComponentDescription
"query": "categories:hotels"query tells the API what you want to search. In this case, you're telling the API you want to search by categories. Any business that has hotels listed in its categories field will be returned.
"num_records": 1num_records tells the API how many records to return in its response. In this case, you just want to see 1 matching record.

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

Response FieldDescription
"num_found"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.
"total_cost"The number of credits this request has cost you. Business records only cost 1 credit per record.
"records"The first available matches to your query. 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 business returned with multiple fields and the values associated with that business. The JSON response will show all fields that have a value. It won't show any fields that don't have a value.

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

4. Refine your search

If you think about the original query we made, you'll realize we didn't really specify which geography we're interested in. Since we only want US hotels, we should narrow our search appropriately. Modify your code to look like this:

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

# Set your API parameters here.
API_token = 'AAAXXXXXXXXXXXX'
format = 'JSON'
query = 'categories:hotels AND country:US'
num_records = 10
download = False

request_headers = {
	'Authorization': 'Bearer ' + API_token,
	'Content-Type': 'application/json',
}
request_data = {
    'query': query,
    'format': format,
    'num_records': num_records,
    'download': download
}

# Make the API call.
r = requests.post('https://api.datafiniti.co/v4/businesses/search',json=request_data,headers=request_headers);

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

This query is different in a couple ways:

  1. It adds AND country:US to narrow down results to just US hotels.
  2. It changes records=1 to records=10 so we can look at more sample matches.

Datafiniti lets you construct very refined boolean queries. If you wanted to do more complicated searches, you could OR operations, negation, and more.

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

If you would like to narrow your search to just exact matches you can place the search term in quotation marks.

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

# Set your API parameters here.
API_token = 'AAAXXXXXXXXXXXX'
format = 'JSON'
query = 'name:"Datafiniti LLC"'
num_records = 10
download = False

request_headers = {
	'Authorization': 'Bearer ' + API_token,
	'Content-Type': 'application/json',
}
request_data = {
    'query': query,
    'format': format,
    'num_records': num_records,
    'download': download
}

# Make the API call.
r = requests.post('https://api.datafiniti.co/v4/businesses/search',json=request_data,headers=request_headers);

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

The above query will only return businesses with the name Datafiniti LLC.

5. Initiate a download of the data

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

🚧

When using the API, you will not receive any warning if you are going past your monthly record limit. Keep a track on how many records you have left by checking your account. You are responsible for any overage fees if you go past your monthly limit.

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

# Set your API parameters here.
API_token = 'AAAXXXXXXXX'
view_name = 'business_basic'
format = 'CSV'
query = 'categories:hotels AND country:US'
num_records = 50
download = True

request_headers = {
	'Authorization': 'Bearer ' + API_token,
	'Content-Type': 'application/json',
}
request_data = {
    'query': query,
    'view': view_name,
    'records': num_records,
    'format': format,
    'num_records': num_records,
    'download': download
}

# Make the API call.
r = requests.post('https://api.datafiniti.co/v4/businesses/search',json=request_data,headers=request_headers);

# Do something with the response.
if r.status_code == 202:
	request_response = r.json()
	print(request_response)

	# Keep checking the request status until the download has completed
	download_id = request_response['id']
	download_status = request_response['status']

	while (download_status != 'completed'):
		time.sleep(5)
		download_r = requests.get('https://api.datafiniti.co/v4/downloads/' + str(download_id),headers=request_headers);
		download_response = download_r.json()
		download_status = download_response['status']
		print('Records downloaded: ' + str(download_response['num_downloaded']))

	# Once the download has completed, get the list of links to the result files and download each file
	if download_status == 'completed':
		result_list = download_response['results']
		i = 1;
		for result in result_list:
			filename = str(download_id) + '_' + str(i) + '.' + format
			urllib.request.urlretrieve(result,filename)
			print('File: ' + str(i) + ' out of ' + str(len(result_list)) + ' saved: ' + filename)
			i += 1

else:
	print('Request failed')
	print(r)

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

  1. We changed format from JSON to CSV. We'll want to view the files in CSV format, so it will be easier to look at.
  2. We change records from 10 to 50. This will download the first 50 matching records. If we wanted to download all matching records, we would remove records.
  3. We set view to business_basic. This will show a limited set of fields and nest fields like categories and features into a single cell (instead splitting them across multiple rows and columns).
  4. Change download=false to download=true.

🚧

If num_records is not specified, ALL of the records matching the query will be downloaded.

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 Business Data with Postman and CSV guide 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:

7. Analyze the results

Using Excel, we can easily count the total number of hotels on a state-by-state basis. The province column gives us where each hotel is located, so we can use it to tally up the numbers.