End-to-End Machine Learning Solution with Vertica and Saagie Using VerticaPy

This video provides an end-to-end solution with Vertica and Saagie using VerticaPy. Analyze and model your data using Vertica's in-database machine learning capabilities and advance your ML pipeline with Saagie.

Saagie is a DataOps platform that brings together various technologies, so you can run data projects quickly, easily, and reliably. This platform allows you to use a combination of technologies to build and manage every step of a data project, from data extraction to visualization.

This document provides an end-to-end solution from loading your data into Vertica to connecting Vertica and Saagie using VerticaPy for performing data science operations.

VerticaPy is a Python library that has scikit-like functionality used in machine learning and advanced analytics for Vertica.

Vertica and Saagie High-Level Design

The following is a high-level design of how Saagie connects to Vertica using VerticaPy and other components for machine learning and model training. Saagie provides JOBS and APPS (Jupyter Notebook) from which you can connect to Vertica using VerticaPy as shown in the design diagram.

You can then explore and prepare data and train your models. The sections that follow provide step by step instructions for

Using the sample dataset
Loading data into Vertica
Creating a job and app (Jupyter notebook) to connect to Vertica using VerticaPy
Exploring and preparing data
Training and building a model to evaluate and create clusters

Environment

To begin you'll need to set up the following environment:

Saagie Cloud Instance
Vertica Analytical Database 12.0.4
VerticaPy Library installed on the Saagie environment
Jupyter Notebook or any other ETL tool to load data into Vertica

Assumptions and Prerequisites

Saagie is already setup either in the cloud or on-premises and the instance is up and running.
No firewall/connection issues exist from Saagie to the Vertica instance.

Step by Step Machine Learning Solution to Evaluate Movies Quality and Create Clusters

The goal of this solution is to build and train a model to analyze and evaluate the quality of movies and create clusters of similar movies.

The example explains how to get started with a dataset in Vertica and use data exploration, data preparation, and data modeling features in Saagie using the VerticaPy functionality. The example describes Normalization of numerical columns, categorizes the data, and create dummies to help the model understand the categorical variables. Finally, it will also show how to create regression and clustering models, and train models with the dataset to determine the quality of movies and create clusters of similar movies.

Note The following sections are collapsible/expandable. Ensure to click these topics to read more.

Loading Data into Vertica

Using Jupyter Notebook

We loaded the sample dataset into Vertica with Jupyter Notebook using VerticaPy.

Open Jupyter Notebook and install VerticaPy by running the following command:
```
pip install verticapy
```

Provide Vertica database connection details to connect to Vertica.

import verticapy as vp
# Creating a new connection
vp.new_connection({"host": "<Verticahost>", 
                   "port": "5433", 
                   "database": "<Verticadbname>", 
                   "password": <password>, 
                   "user": "dbadmin"},
                   name = "<Verticauser>")
# Connecting to the Database
vp.connect("MyVerticaConnection")

Let’s create a new schema.
```
vp.create_schema("filmtv_saagie")
```
Load the filmtv_movies dataset into Vertica using the below command where we pass path of the downloaded dataset, table name and the schema name
```
vp.read_csv('Downloads/ filmtv_movies.csv', table_name = ' filmtv_movies ', schema = filmtv_saagie ')
```

Using ETL Tool

You can load the sample dataset into Vertica using any ETL tool. Following are steps to load data into Vertica using DBVisualizer. You can use any ETL tool of your choice.

In the Import Table Data window, select the location of the downloaded filmtv_movies dataset and click Next.
In the Column delimiter section, select Auto Detect and provide values in the Options section as shown, and click Next.
Make sure that the data types are indicated correctly by verifying in the data preview section and click Next.
Verify the table and schema name the data is being imported to and click Next.
Click Import to import the data set into the filmtv_movies table.
After the import, close the Import table window, and verify if the data has been imported into the table in Vertica correctly.

In the Import Table Data window, select the location of the downloaded filmtv_movies dataset and click Next.

In the Column delimiter section, select Auto Detect and provide values in the Options section as shown, and click Next.

Make sure that the data types are indicated correctly by verifying in the data preview section and click Next.

Verify the table and schema name the data is being imported to and click Next.

Click Import to import the data set into the filmtv_movies table.

After the import, close the Import table window, and verify if the data has been imported into the table in Vertica correctly.

For more information , see Importing Table Data for steps to load CSV data into a new or existing table.

Creating Jobs in Saagie

You can use the Jobs page in Saagie to connect to Vertica. Let's create different jobs for data initialization, data exploration and preparation, data modeling to evaluate the quality of movies and create clusters of similar movies and create pipeline using these jobs.

Log in to the Saagie cloud instance from the link and credentials provided by Saagie.
The Saagie platform opens. It contains Projects, Monitoring, and Catalog Modules.

You can click the Projects module, to view All projects. In the All projects page, you can view all the existing projects and create a new project.
Here, we have a Vertica project. Let us open it and you will see the associated Jobs, Pipelines, Apps, Storage, Environment variables, External connections, and Docker credentials on the left pane.
Before you create a job, you must create a Python file with the actions you want your job to perform. Create a Python file with code that will enable you to connect to the Vertica database using Verticapy python library.

Initializing

Create a python file with details of the Vertica database to connect using Verticapy, After you connect, assign the filmtv_movies dataset that was loaded into Vertica as a table to a vDataFrame object(filmtv_movies) and then to display data from the Vertica database. We used environment variables to store the database details, see Creating Environment variables in Saagie section in this document.
```
import verticapy as vp
import os
vp.new_connection({"host":os.environ["host"],"port":"5433","database":os.environ["database"],"password": os.environ["pwd"],"user":os.environ["user"]},name="Vertica_Connection")
vp.connect("Vertica_Connection")

from verticapy import vDataFrame
film=os.environ["filmtv"]
filmtv_movies=vDataFrame(input_relation=film)
print(filmtv_movies.head(10))
```
To Create a Job, click Jobs > New job.

Provide a job name and add a description, click Continue.
Select your job technology. Click Continue.
Choose the Python version and you can then upload your Python file that was created.

Enter the shell command pip install Verticapy and python {file} as shown in the following. File here indicates the file that you just uploaded. This command will install VerticaPy in the environment to connect to Vertica. Click Continue.
You can manage resources, alerts, run type, release note as per your requirement. Click Create Job.
The Job is now created and the Overview page of your job opens. Click Run.
The status is displayed as queued > running > succeeded.

After job is Successful, the results are in the command line, top 10 rows from the dataset is displayed based on the Python code.

Note You can check the explanation of each line in the Python code in the section Creating an App in Saagie in this document.

For more information, see Creating a Connection.

Data Exploration and Preparation

Let's create a Python file again for exploring and preparing data by identifying top actors and directors using different VerticaPy functions, also normalize the data using the min-max method and quantify the notoriety of actors and directors. Lastly, as we did many operations, it will be nice to export the vDataFrame as a table in the Vertica database and we will change the value of environment variable with the name of a new table that we just exported so that this new table data will be used by the next job.

Since we are using these jobs to create a pipeline, the data should flow among the next jobs that are linked in the pipeline. So, for that reason, we are going to upload the dataframe that we are exploring and preparing in each job, as a table into the Vertica database and then retrieve that table data as a dataframe in the next job.

import verticapy as vp
import os
vp.new_connection({"host":os.environ["host"],"port":"5433","database":os.environ["database"],"password": os.environ["pwd"],"user":os.environ["user"]},name="Vertica_Connection")
vp.connect("Vertica_Connection")

from verticapy import vDataFrame
film=os.environ["filmtv"]
filmtv_movies=vDataFrame(input_relation=film)

filmtv_movies.describe(method = 'categorical', unique = True)

filmtv_movies.drop(['description','notes'])

filmtv_movies.sort({"avg_vote" : "desc"})

filmtv_movies.search(conditions = [filmtv_movies["votes"] > 10],order_by = {"avg_vote" : "desc" })

for i in range(1, 5):
    filmtv_movies2 = vDataFrame(input_relation = '"filmtv"."filmtv_movies"')
    filmtv_movies2.regexp(column = "actors",
                          method = "substr",
                          pattern = '[^,]+',
                          occurrence = i,
                          name = "actor")
    if i == 1:
        filmtv_movies = filmtv_movies2.copy()
    else:
        filmtv_movies = filmtv_movies.append(filmtv_movies2)
filmtv_movies["actor"].describe()

import verticapy.stats as st
actors_stats = filmtv_movies.groupby(columns = ["actor"], 
                                     expr = [st.sum(filmtv_movies["votes"])._as("notoriety_actors"),
                                             st.count(filmtv_movies["actors"])._as("castings_actors")])
actors_stats["actor"].dropna()
actors_stats["notoriety_actors"].normalize(method = 'minmax')

print(actors_stats.search(order_by = {"notoriety_actors" : "desc","castings_actors" : "desc"}).head(10))


director_stats = filmtv_movies.groupby(columns = ["director"], 
                                       expr = [st.sum(filmtv_movies["votes"])._as("notoriety_director"),
                                               st.count(filmtv_movies["director"])._as("castings_director")])
director_stats["notoriety_director"].normalize(method = 'minmax')

print(director_stats.search(order_by = {"notoriety_director" : "desc","castings_director" : "desc" }).head(10))

filmtv_movies_director = filmtv_movies.join(
                                    director_stats,
                                    on = {'director': 'director'},
                                    how = "left",
                                    expr1 = ["*"],
                                    expr2 = ["notoriety_director", 
                                             "castings_director"])
filmtv_movies_director_actors = filmtv_movies_director.join(
                                    actors_stats,
                                    on = {'actor': 'actor'},
                                    how = "left",
                                    expr1 = ["*"],
                                    expr2 = ["notoriety_actors",
                                             "castings_actors" ])                                            

print(filmtv_movies_director_actors.head(5))

vp.drop('"filmtv_saagie"."filmtv_movies_director_actors"', relation_type = "table")
filmtv_movies_director_actors.to_db(name = '"filmtv_saagie"."filmtv_movies_director_actors"',relation_type = "table",inplace = True)

var_file = "/workdir/output-vars.properties"
with open(var_file, "a") as file:
    file.write(f"""filmtv = '"filmtv_saagie"."filmtv_movies_director_actors"' """)

We will follow the same steps to create a new job by uploading this Python file.

.
When the Job runs using the pipeline, the results in the command line display based on the code.

Let’s create one more job for preparing data using a different Python file which includes categorizing the data of multiple fields, normalizing the numerical data, creating dummies, and filling the missing values, and then export the vDataFrame as a table in the Vertica database. We will then change the value of environment variable with the name of a new table that we just uploaded so that this new table data will be used by the next job.

import verticapy as vp
import os
vp.new_connection({"host":os.environ["host"],"port":"5433","database":os.environ["database"],"password": os.environ["pwd"],"user":os.environ["user"]},name="Vertica_Connection")
vp.connect("Vertica_Connection")

from verticapy import vDataFrame
film=os.environ["filmtv"]
filmtv_movies_director_actors=vDataFrame(input_relation=film)

import verticapy.stats as st
filmtv_movies_complete = filmtv_movies_director_actors.groupby(
columns = ["filmtv_id",
"title",
"year",
"genre",
"country",
"avg_vote",
"votes",
"duration",
"director",
"notoriety_director",
"castings_director"],
expr = [st.sum(filmtv_movies_director_actors["notoriety_actors"])._as("notoriety_actors"),st.sum(filmtv_movies_director_actors["castings_actors"])._as("castings_actors")])

print(filmtv_movies_complete.describe(method = "all"))

filmtv_movies_complete.case_when('period',filmtv_movies_complete["year"] < 1990,'Old',filmtv_movies_complete["year"] >= 2000,'Recent', '90s')
print(filmtv_movies_complete.head(5))

print(filmtv_movies_complete.groupby(columns = ["country"],expr = ["COUNT(*)"]).sort({"count" : "desc"}).head(10))

from verticapy import str_sql
# Language Discretization
Arabic_Middle_Est = ["Arab", "Iran", "Turkey", "Egypt", "Tunisia",
                     "Lebanon", "Palestine", "Morocco", "Iraq",
                     "Sudan", "Algeria", "Yemen", "Afghanistan",
                     "Azerbaijan", "Kazakhstan", "Kyrgyzstan",
                     "Kurdistan", "Syria", "Uzbekistan"]
Chinese_Japan_Asian = ["Japan", "Hong Kong", "China", "South Korea", 
                       "Thailand", "Philippines", "Taiwan", "Indonesia",
                       "Singapore", "Malaysia", "Vietnam", "Laos", "Cambodia",
                       "Bhutan"]
Indian = ["India", "Pakistan", "Nepal", "Sri Lanka", "Bangladesh"]
Hebrew = ["Israel"]
Spanish_Portuguese = ["Spain", "Portugal", "Mexico", "Brasil", "Chile",
                      "Argentina", "Colombia", "Cuba", "Venezuela", "Peru",
                      "Uruguay", "Dominican Republic", "Ecuador", "Guatemala",
                      "Costa Rica", "Paraguay", "Bolivia"]
English = ["United States", "England", "Great Britain", "Ireland",
           "Australia", "New Zealand", "South Africa"]
French = ["France", "Canada", "Belgium", "Switzerland", "Luxembourg"]
Italian = ["Italy"]
German_North_Europe = ["German", "Austria", "Holland", "Netherlands", "Denmark",
                       "Norway", "Iceland", "Finland", "Sweden", "Greenland"]
Russian_Est_Europe = ["Russia", "Soviet Union", "Yugoslavia", "Czechoslovakia",
                      "Poland", "Bulgaria", "Croatia", "Czech Republic", "Serbia",
                      "Ukraine", "Slovenia", "Lithuania", "Latvia", "Estonia", 
                      "Bosnia and Herzegovina", "Georgia"]
Grec_Balkan = ["Greece", "Macedonia", "Cyprus", "Romania", "Armenia", "Hungary",
               "Albania", "Malta"]

# Creation of the new feature
filmtv_movies_complete.case_when('language_area', 
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Arabic_Middle_Est))), 'Arabic_Middle_Est',
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Chinese_Japan_Asian))), 'Chinese_Japan_Asian', 
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Indian))), 'Indian', 
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Hebrew))), 'Hebrew', 
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Spanish_Portuguese))), 'Spanish_Portuguese', 
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(English))), 'English',
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(French))), 'French',
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Italian))), 'Italian',
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(German_North_Europe))), 'German_North_Europe',
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Russian_Est_Europe))), 'Russian_Est_Europe',
        str_sql("REGEXP_LIKE(Country, '{}')".format("|".join(Grec_Balkan))), 'Grec_Balkan', 
        'Others')
		
filmtv_movies_complete.case_when(
         'Category', 
         str_sql("REGEXP_LIKE(Genre, 'Drama|Noir')"), 'Drama', 
         str_sql("REGEXP_LIKE(Genre, 'Comedy|Grotesque')"), 'Comedy', 
         str_sql("REGEXP_LIKE(Genre, 'Fantasy|Super-hero')"), 'Fantasy', 
         str_sql("REGEXP_LIKE(Genre, 'Romantic|Sperimental|Mélo')"), 'Romantic', 
         str_sql("REGEXP_LIKE(Genre, 'Thriller|Crime|Gangster')"), 'Thriller', 
         str_sql("REGEXP_LIKE(Genre, 'Action|Western|War|Spy')"), 'Action', 
         str_sql("REGEXP_LIKE(Genre, 'Adventure')"), 'Adventure', 
         str_sql("REGEXP_LIKE(Genre, 'Animation')"), 'Animation', 
         str_sql("REGEXP_LIKE(Genre, 'Horror')"), 'Horror', 
         'Others')

print(filmtv_movies_complete.head(5))
		 
filmtv_movies_complete.drop(columns = ["genre"])

print(filmtv_movies_complete.count_percent())


filmtv_movies_complete["notoriety_actors"].fillna(method = "median",
                                                  by = ["director",
                                                        "Category"])
filmtv_movies_complete["castings_actors"].fillna(method = "median",
                                                 by = ["director",
                                                       "Category"])
filmtv_movies_complete.dropna()

print(filmtv_movies_complete.count_percent())

filmtv_movies_complete.normalize(method = "minmax",
                                 columns = ['votes', 
                                            'duration', 
                                            'notoriety_director',
                                            'castings_director',
                                            'notoriety_actors',
                                            'castings_actors'])
for elem in ['category', 'period', 'language_area']:
    filmtv_movies_complete[elem].get_dummies(drop_first = True)

print(filmtv_movies_complete.head(5))
	
vp.drop('"filmtv_saagie"."filmtv_movies_complete"', relation_type = "table")
filmtv_movies_complete.to_db(name = '"filmtv_saagie"."filmtv_movies_complete"',
                             relation_type = "table",
                             inplace = True)
vp.drop('"filmtv_saagie"."filmtv_movies_mco"', relation_type ="view")
filmtv_movies_complete.to_db(name = '"filmtv_saagie"."filmtv_movies_mco"',
                             relation_type = "view",
                             db_filter = "votes > 0.02")


var_file = "/workdir/output-vars.properties"
with open(var_file, "a") as file:
    file.write(f"""filmtv = '"filmtv_saagie"."filmtv_movies_complete"' """)

Upload the above Python file and create a job as shown.
When the Job runs using the pipeline, the results in the command line display data after performing different operations for preparing the data as per your code.

Machine Learning: Creating a Linear Regression Model

Let's create a Python file that creates a linear regression model using the data that we uploaded to the Vertica database in the previous job, train the model, generate model report, and evaluate top movies

After that we will export the vDataFrame as a table in the Vertica database and we will change the value of environment variable with the name of a new table that we exported

import verticapy as vp
import os
vp.new_connection({"host":os.environ["host"],"port":"5433","database":os.environ["database"],"password": os.environ["pwd"],"user":os.environ["user"]},name="Vertica_Connection")
vp.connect("Vertica_Connection")

from verticapy import vDataFrame
film=os.environ["filmtv"]
filmtv_movies_complete=vDataFrame(input_relation=film)

from verticapy.learn.linear_model import LinearRegression
predictors = filmtv_movies_complete.get_columns(
                            exclude_columns = ["avg_vote",
                                               "period",
                                               "director",
                                               "language_area",
                                               "title", 
                                               "year",
                                               "country",
                                               "Category"])
model = LinearRegression("filmtv_movies_lr",
                         max_iter = 1000,
                         solver = "BFGS")
model.fit('"filmtv_saagie"."filmtv_movies_mco"', predictors, "avg_vote")
print(model.report())

model.predict(filmtv_movies_complete,name = "unbiased_vote")

import verticapy.stats as st
filmtv_movies_complete["unbiased_vote"] = st.case_when(filmtv_movies_complete["unbiased_vote"] > 10, 10,
                                                       filmtv_movies_complete["unbiased_vote"] < 0, 0,
                                                       filmtv_movies_complete["unbiased_vote"])

print(filmtv_movies_complete.search(usecols = ['filmtv_id',
                                         'title',
                                         'year',
                                         'country',
                                         'avg_vote',
                                         'unbiased_vote',
                                         'votes',
                                         'duration',
                                         'director',
                                         'notoriety_director',
                                         'castings_director',
                                         'notoriety_actors',
                                         'castings_actors',
                                         'period',
                                         'language_area'],
                              order_by = {"unbiased_vote" : "desc", 
                                          "avg_vote" : "desc"}).head(10))

vp.drop('"filmtv_saagie"."filmtv_movies_complete_reg"', relation_type = "table")
filmtv_movies_complete.to_db(name = '"filmtv_saagie"."filmtv_movies_complete_reg"',
                             relation_type = "table",
                             inplace = True)

var_file = "/workdir/output-vars.properties"
with open(var_file, "a") as file:	
    file.write(f"""filmtv = '"filmtv_saagie"."filmtv_movies_complete_reg"' """)

You can now create a job for creating regression model using the above Python file.
After running the job using the pipeline, the results in the command line display the model report and display the top 10 movies from the data based on the code.

Machine Learning: Creating Movie Clusters

Lastly, we will create a Python file for creating kmeans clustering model to create clusters of similar movies using the data that we uploaded to the Vertica database after performing linear regression.

import verticapy as vp
import os
vp.new_connection({"host":os.environ["host"],"port":"5433","database":os.environ["database"],"password": os.environ["pwd"],"user":os.environ["user"]},name="Vertica_Connection")
vp.connect("Vertica_Connection")

from verticapy import vDataFrame
film=os.environ["filmtv"]
filmtv_movies_complete_reg=vDataFrame(input_relation=film)

filmtv_movies_complete_reg["unbiased_vote"].normalize(method = "minmax")

predictors = filmtv_movies_complete_reg.get_columns(
                        exclude_columns = ["avg_vote",
                                           "period",
                                           "director",
                                           "language_area",
                                           "title", 
                                           "year",
                                           "country",
                                           "Category",
                                           "filmtv_id"])
			  
from verticapy.learn.cluster import KMeans
model_kmeans = KMeans("filmtv_movies_clustering", n_cluster = 17)
model_kmeans.fit(filmtv_movies_complete_reg, predictors)
model_kmeans.cluster_centers_

model_kmeans.predict(filmtv_movies_complete_reg,name = "movies_cluster")
print(filmtv_movies_complete_reg.head(5))

print(filmtv_movies_complete_reg.search(filmtv_movies_complete_reg["movies_cluster"] == 5,
                              usecols = ["avg_vote",
                                         "period",
                                         "director",
                                         "language_area",
                                         "title", 
                                         "year",
                                         "country",
                                         "Category"]).head(10))
										 
										 
print(filmtv_movies_complete_reg.search(filmtv_movies_complete_reg["movies_cluster"] == 6,
                              usecols = ["avg_vote",
                                         "period",
                                         "director",
                                         "language_area",
                                         "title", 
                                         "year",
                                         "country",
                                         "Category"]).head(10))

import verticapy as vp
import os
vp.new_connection({"host":os.environ["host"],"port":"5433","database":os.environ["database"],"password": os.environ["pwd"],"user":os.environ["user"]},name="Vertica_Connection")
vp.connect("Vertica_Connection")

Let’s now create a job using the above Python file and create clusters.
Finally, when this job runs using the pipeline, the results show the different clusters of the data as per our code

Below are the 5 jobs which we created and will be used to create a pipeline that provides end to end machine learning solution.

Creating a Pipeline in Saagie

To create a pipeline, click Pipelines > New pipeline.
Provide a pipeline name. Construct your pipeline by adding jobs that were created earlier.
Select Add job and choose the jobs from the list that you want to add in your pipeline. Jobs containing basic information, such as name, technology, and job category, appears in the graph.
Link the jobs in order as per your requirement to create a pipeline. Click Save pipeline.
The pipeline is now created. Click Run.

After it succeeds, the jobs connected in the pipeline get executed. You can see the logs of each job by clicking the job.
You can see the results from the logs of each job by clicking the job.
Click see logs of the linear regression job, a new window opens which has the results that show the solution to get top movies. Our results are more consistent as you can see movies like Psycho, Pulp Fiction, and The Godfather are among the top movies.

Click see logs of the kmeans job, a new window opens which has the results that show the clusters of similar movies. You can see from the results that cluster 5 has drama category and cluster 6 has comedy category, similarly other clusters have different categories. This shows that each cluster consists of similar movies.

This pipeline now provides end to end machine learning solution to evaluate movies quality and create clusters of similar movies.

Creating Environment Variables in Saagie

Click Environment variables page within the project. The page opens with the list of existing environment variables that we created and used in the jobs.
To create a new variable, click Create variable for project. Enter the required information and click Save.

You can select the option is password if your value is a password and you want that to be hidden.

Within a Pipeline

Environment variables created at this level can be accessed within the selected pipeline and can also access project and global environment variables. You can always edit the variables for the pipeline by clicking the variable.

Click Environment variables within the pipeline. The page opens with a list of existing environment variables with the scope of project and pipeline. We created these variables to store db details, table name and used them in jobs
To create a new variable in pipeline, click Create variable for pipeline. Enter the required information and click Save

You can select the option is password if your value is a password and you want that to be hidden.
You can select the option is password if your value is a password and you want that to be hidden.

Machine Learning: Creaitng a Linear Regression Model

We can create a model to evaluate an unbiased score for each different movie. Let’s use a Linear regression to evaluate and get the report.

from verticapy.learn.linear_model import LinearRegression
predictors = filmtv_movies_complete.get_columns(
                            exclude_columns = ["avg_vote",
                                               "period",
                                               "director",
                                               "language_area",
                                               "title", 
                                               "year",
                                               "country",
                                               "Category"])
model = LinearRegression("filmtv_movies_lr",
                         max_iter = 1000,
                         solver = "BFGS")
model.fit('"filmtv"."filmtv_movies_mco"', predictors, "avg_vote")
model.report()

The model looks good. Let's add it in our vDataFrame.

model.predict(filmtv_movies_complete,
              name = "unbiased_vote")

Since a score can't be greater than 10 or less than 0, we need to adjust the 'unbiased_vote' between 0 and 10.

filmtv_movies_complete["unbiased_vote"] = st.case_when(filmtv_movies_complete["unbiased_vote"] > 10, 10,
                                                       filmtv_movies_complete["unbiased_vote"] < 0, 0,
                                                       filmtv_movies_complete["unbiased_vote"])

Let's look at the top movies.

filmtv_movies_complete.search(usecols = ['filmtv_id',
                                         'title',
                                         'year',
                                         'country',
                                         'avg_vote',
                                         'unbiased_vote',
                                         'votes',
                                         'duration',
                                         'director',
                                         'notoriety_director',
                                         'castings_director',
                                         'notoriety_actors',
                                         'castings_actors',
                                         'period',
                                         'language_area'],
                              order_by = {"unbiased_vote" : "desc", 
                                          "avg_vote" : "desc"}).head(10)

Great, our results are more consistent. Psycho, Pulp Fiction, and The Godfather are among the top movies.

Machine Learning: Creating Movie Clusters

Let’s use K-means clustering to create clusters of similar movies.

Since k-means clustering is sensitive to unnormalized data, let's normalize our new predictors.

filmtv_movies_complete["unbiased_vote"].normalize(method = "minmax")

We can compute the elbow curve to find a suitable number of clusters to create a K-means model.

%matplotlib inline
predictors = filmtv_movies_complete.get_columns(
                        exclude_columns = ["avg_vote",
                                           "period",
                                           "director",
                                           "language_area",
                                           "title", 
                                           "year",
                                           "country",
                                           "Category",
                                           "filmtv_id"])
from verticapy.learn.model_selection import elbow
elbow = elbow(filmtv_movies_complete,
              predictors,
              n_cluster = (1, 60))

By looking at the elbow curve, we can choose 17 clusters. Let's create a k-means model.

from verticapy.learn.cluster import KMeans
model_kmeans = KMeans("filmtv_movies_clustering", n_cluster = 17)
model_kmeans.fit(filmtv_movies_complete, predictors)
model_kmeans.cluster_centers_

Now we will add the clusters in the vDataFrame.

model_kmeans.predict(filmtv_movies_complete, 
                     name = "movies_cluster")

Let's look at the different clusters of the movies.

filmtv_movies_complete.search(filmtv_movies_complete["movies_cluster"] == 0,
                              usecols = ["avg_vote",
                                         "period",
                                         "director",
                                         "language_area",
                                         "title", 
                                         "year",
                                         "country",
                                         "Category"])

filmtv_movies_complete.search(filmtv_movies_complete["movies_cluster"] == 6,
                              usecols = ["avg_vote",
                                         "period",
                                         "director",
                                         "language_area",
                                         "title", 
                                         "year",
                                         "country",
                                         "Category"])

We can see from the results that each cluster consists of similar movies. These clusters can be used to give movie recommendations or help streaming platforms group movies together.

End-to-End Machine Learning Solution with Vertica and Saagie Using VerticaPy

Vertica and Saagie High-Level Design

Environment

Assumptions and Prerequisites

Step by Step Machine Learning Solution to Evaluate Movies Quality and Create Clusters

For More Information