Requests vs Pycurl

When looking for python online data collection libraries (a.k.a. Scraping libraries) the main result is requests , however there is a much better library in my opinion called PyCurl, it is the python interface for libcurl which is basically the native library for curl . In this project I get immediately blocked when using requests, moreover the interesting part of the project is that we won't scrape an html code that we then have to parse with beautifulsoup, we will instead scrape data directly in a Json format. This gives us many advantages because Json is basically a dictionary that we can easily navigate, without need to learn a new library. Please have a look to this stackoverflow page if you are more interested in PyCurl vs Requests performances.

Introduction

As Data Scientist we are probably used to query our data using SQL, I have noticed a very common problem in Junior but also Senior analytics professional, they think that a table in a database is at the very beginning of the process, this is because it is their process within a bigger one; sometimes it is just a matter of point of view, for a Data Engineer a table in a database is at the end of the process!

If we want to have a broader view we need to look out of the Analytics bubble, this is the reason why i started this project.

When scraping data, the most important part is the website structure analysis, in fact I have spent many hours navigating the website to get as much info as possible. I would like to guide you through some of the encountered problems.

Javascript generated content

For complex websites you cannot directly scrape related HTML because most of the time there will be a JavaScript script generating informations. I have started considering Selenium to simulate a webdriver which would have fully loaded HTML info and then parse all this info with beautiful soup.

Selenium

The first problem of vivino website using Selenium was to fully load the wine list scrolling to the bottom, this require a scroll call with selenium until the end is reached, however by using this strategy you have to perform multiple calls and there may be the risk of being banned.

Find the most important JSON

When navigating the website I was not able to find a Json file with most of the info, this is because you get such file only after you start scrolling to the end of the first page. We now have a huge dictionary containing all details about a wine, even if some information in such dictionary are not displayed in the page you are looking.

‍

Tables that will be created from this code

1. vintage (this is the main table)
2. region_most_used_grapes
3. flavor
   • flavor_primary_keywords
   • flavor_secondary_keywords
4. prices
5. food
6. grapes

Start of The Project

Import Libraries

import time
import pycurl
import certifi
from io import BytesIO
import json
import pandas as pd

Performing cURL Request

%%time
n = 0
pages_available = True
wines_collection = []

for i in range(100):
# Creating a buffer as the cURL is not allocating a buffer for the network response
    buffer = BytesIO()

# Initialising the curl object
    curl = pycurl.Curl()

# Setting the request URL, the user agent, and the buffer for the response
    url = f'https://www.vivino.com/api/explore/explore?country_code=IT¤cy_code=EUR&grape_filter=varietal&min_rating=1&order_by=price&order=desc&price_range_max=500+&price_range_min=0&wine_type_ids%5B%5D=1&wine_type_ids%5B%5D=2&wine_type_ids%5B%5D=3&wine_type_ids%5B%5D=24&wine_type_ids%5B%5D=7&wine_type_ids%5B%5D=4&page={n}&language=en'
    curl.setopt(pycurl.URL, url)
    curl.setopt(pycurl.USERAGENT, "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:120.0) Gecko/20100101 Firefox/120.0")
    curl.setopt(pycurl.WRITEDATA, buffer)
    curl.setopt(pycurl.CAINFO, certifi.where())

# Performing the actual request
    curl.perform()

# Accessing the response code and adding 1 to n only if successful, this step is not necessary it allows you to check each step
    if curl.getinfo(pycurl.RESPONSE_CODE) == 200:
        #print(f'Success : {n} {curl.getinfo(pycurl.RESPONSE_CODE)}', )
        n +=1
    else:
        print(f'Not Success{curl.getinfo(pycurl.RESPONSE_CODE)}')

# Ending the session and freeing the resources
    curl.close()
    
#Convert Buffer from Byte to string to Dict
    page_dict = json.loads(buffer.getvalue().decode())

#page_dict is now a dictionaire containing three main dictionaries, the one we want is "explore_vintage", it contains another dict "matches"
#matches is a list of dict
    for i in page_dict['explore_vintage']['matches']:
        wines_collection.append(i)
    
    if n%10 == 0:
        print(len(wines_collection))

Output:
CPU times: user 4.74 s, sys: 541 ms, total: 5.28 s
Wall time: 1min 23s

Check Buffer and Navigate the Tree

This step is purely to show how to transformin buffer into a dictionary from Bytes -> String -> Dict and print each result

#buffer getvalue() now store informations we need in a bytes format
print(type(buffer.getvalue()))

#hence we will decode bytes into string
string = buffer.getvalue().decode()
print(type(string))

#at this stage we can  convert our string into a dictionary using json library
wines_info = json.loads(string)
print(type(wines_info))

Output:
<class 'bytes'>
<class 'str'>
<class 'dict'>

We can now have a look at our newly created dictionary structure

#We have three main keys
print(wines_info.keys())
#explore vintage has more keys
print(wines_info['explore_vintage'].keys())
#matches provides a list of values, let's see keys of the first value of this list
print(wines_info['explore_vintage']['matches'][0].keys())
print(wines_info['explore_vintage']['matches'][0]['vintage'].keys())

Output:
dict_keys(['explore_vintage', 'selected_filters', 'e'])
dict_keys(['market', 'records_matched', 'matches', 'bottle_type_errors', 'records'])
dict_keys(['vintage', 'price', 'prices'])
dict_keys(['id', 'seo_name', 'name', 'statistics', 'image', 'wine', 'year', 'grapes', 'has_valid_ratings'])

Build Pandas Dataframe

We will build a dataframe using one main pandas function, json_normalize, the first json flttening will be the easiest one, some other will require to add some parameter to json_normalize function, while at the very end we will use pure python code to manipulate our json.

Once you flatten Json and create a Dataframe with json_normalize, you can notice that there are still some columns containing a dictionary, this is because the json we just downloaded has many layers with which we can build multiple tables, each table can the be connected with primary keys like in a relational database.

vintage = pd.json_normalize(wines_collection)
vintage.head(2)

Nested JSON

From the table above, you may have noticed that the scraped Json is not fully transformed into a table, indeed we still have some list of dictionaries nested in our created dataframe. Check the columns "Prices", each wine may have multiple prices offered by different vendor, this is the reason why such variable is more complex than the others. There are other variables like this, from such variables we can create other tables that can be interpeted as dimensional tables.

It seems that from our simple Json we are able to build a proper schema.

Before we have used json_normalize just by giving json file as imput parameter, when dealing with nested json we can pass a list to the record_ path argument, such list represents nested json keys in order until the last one (where what you need is store) is reached.

Another parameter called meta can be provided, in this case we specify the location of a single value related to that record, in this case i will use the wine id so that we will be able in the future to join back all the information related to a wine.

Transform: vintage.wine.region.country.most_used_grapes

note how most_used_grapes is the last value of the list in record_ path

region_most_used_grapes = pd.json_normalize(wines_collection,
                                     record_path = ['vintage', 'wine', 'region', 'country', 'most_used_grapes'],
                                     record_prefix = 'vintage_wine_region_country_MostUsedGrapes_',
                                     meta = [['vintage', 'id']])
region_most_used_grapes.head(10)

‍

Transform: vintage.wine.tase.flavor

flavor contains another dictionary that we will transform later without using json_normalize but pure python

flavor = pd.json_normalize(wines_collection,
                                     record_path = ['vintage', 'wine', 'taste', 'flavor'], 
                                     meta = [['vintage', 'id']])
flavor.head(10)

‍

Transform: prices

prices = pd.json_normalize(wines_collection,
                                     record_path = 'prices',
                                     record_prefix = 'prices_', meta = [['vintage', 'id']])
prices.head(10)

‍

Nested JSON without json_normalize

what happen if in some cases and entire index is missing on your json? you can't use json_normalize, this is a great news because we can start thinking a little bit more to build our own json_ normalize function, I called it dict_ to_ table a very original name.

data is the dataframe where nested values are stored and specified with the parameter column, key is where the primary key of the wine is stored.

the following function loop through the nested dictionary and associate the wine key with function zip. the results is a list of dictionaries that can be easily read by pandas.DataFrame.

def dict_to_table(data, column, key):
    collect = []
    for i,j in zip(data[column], data[key]):
        try:
            for n in i:
                n[key] = j
                collect.append(n)
        except:
            pass
    return collect

flavor_primary_keywords = pd.DataFrame(dict_to_table( data = flavor, 
		column = 'primary_keywords', 
		key = 'vintage.id' ))
flavor_primary_keywords.head(10)

‍

If you are reading this, it means that you went thorough the entire project, do no hesitate to contact me or schedule a meeting if you have any advice or interesting problems we can try to solve together!

‍

‍