Movies Scoring and Clustering¶

This example uses the 'filmtv_movies' dataset to evaluate the quality of the movies and create clusters of similar movies. You can download the Jupyter Notebook of the study here.

year: Movie's release year
filmtv_id: Movie ID
title: Movie title
genre: Movie genre
country: Movie's country of origin
description: Movie description
notes: Information about the movie
duration: Movie duration
votes: Number of votes
avg_vote: Average score
director: Movie director
actors: Actors in the movie

We will follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem.

Initialization¶

This example uses the following version of VerticaPy:

In [1]:

import verticapy as vp
vp.__version__

Out[1]:

'0.9.0'

Connect to Vertica. This example uses an existing connection called "VerticaDSN." For details on how to create a connection, use see the connection tutorial.

In [2]:

vp.connect("VerticaDSN")

Let's create a Virtual DataFrame of the dataset. The dataset is available here.

In [3]:

filmtv_movies = vp.read_csv("data/filmtv_movies.csv")
display(filmtv_movies.head(5))

	123 filmtv_id Int	Abc title Varchar(486)	123 year Numeric(8,2)	Abc genre Varchar(22)	123 duration Int	Abc country Varchar(208)	Abc director Varchar(1066)	Abc Varchar(2218)	123 avg_vote Numeric(6,2)	123 votes Int	Abc Varchar(2232)	Abc Varchar(1052)
1	18	Diner	1982.0	Comedy	95	United States	Barry Levinson		7.2	15
2	22	A ciascuno il suo	1967.0	Drama	93	Italy	Elio Petri		7.8	102
3	24	A... come assassino	1966.0	Thriller	80	Italy	Ray Morrison (Angelo Dorigo)		6.5	3
4	30	A Ghentar si muore facile	1968.0	Adventure	101	Italy	Leon Klimovsky		5.5	2
5	31	Does This Mean We are Married?	1990.0	Comedy	90	United States	Carol Wiseman		4.0	1

Rows: 1-5 | Columns: 12

Data Exploration and Preparation¶

One of the biggest challenges for any streaming platform is to find a good catalog of movies.

First, let's explore the dataset.

In [4]:

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

Out[4]:

	dtype	count	top	top_percent	unique
"filmtv_id"	int	53397	18	0.002	53397.0
"title"	varchar(486)	53395	Les Vampires	0.019	50584.0
"year"	numeric(8,2)	53387	2016.0	3.092	111.0
"genre"	varchar(22)	53195	Drama	30.138	27.0
"duration"	int	53397	90	11.798	282.0
"country"	varchar(208)	53346	United States	41.141	2394.0
"director"	varchar(1066)	53335	Mario Mattòli	0.137	19160.0
"actors"	varchar(2218)	50372	[null]	5.665	50121.0
"avg_vote"	numeric(6,2)	53397	6.0	15.014	89.0
"votes"	int	53397	1	22.988	588.0
"description"	varchar(2232)	359	[null]	99.328	354.0
"notes"	varchar(1052)	106	[null]	99.801	105.0

Rows: 1-12 | Columns: 6

We can drop the 'description' and 'notes' columns since these fields are empty for most of our dataset.

In [5]:

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

Out[5]:

	123 filmtv_id Int	Abc Varchar(486)	123 year Numeric(8,2)	Abc genre Varchar(22)	123 duration Int	Abc country Varchar(208)	Abc director Varchar(1066)	Abc Varchar(2218)	123 avg_vote Numeric(6,2)	123 votes Int
1	18		1982.0	Comedy	95	United States	Barry Levinson		7.2	15
2	22		1967.0	Drama	93	Italy	Elio Petri		7.8	102
3	24		1966.0	Thriller	80	Italy	Ray Morrison (Angelo Dorigo)		6.5	3
4	30		1968.0	Adventure	101	Italy	Leon Klimovsky		5.5	2
5	31		1990.0	Comedy	90	United States	Carol Wiseman		4.0	1
6	32		1990.0	Drama	96	United States	Joseph Ruben		5.1	107
7	33		1991.0	Thriller	100	United States	Brian Grant		8.0	2
8	37		1975.0	Comedy	100	Italy	Marcello Fondato		5.9	42
9	41		1985.0	Drama	110	France	Vera Belmont		4.0	1
10	42		1988.0	Drama	110	Hungary	Géza Beremenyi		8.0	1
11	46		1992.0	Drama	88	France, Great Britain	Ian Sellar		6.0	1
12	49		1964.0	Drama	110	United States	Sidney Lumet		8.3	68
13	50		1990.0	Action	88	United States, China	Liu Chia Yung		7.0	2
14	53		1968.0	Adventure	94	Italy	Emilio Miraglia		4.0	6
15	55		1986.0	Comedy	100	United States	Alan Metter		6.6	16
16	56		1988.0	Comedy	89	United States	Nico Mastorakis		4.0	1
17	58		1989.0	Comedy	100	United States	Bruce Beresford		7.1	171
18	60		1987.0	Drama	98	Great Britain	Mark Rezyka		4.0	1
19	61		1966.0	Western	98	United States	Sidney J. Furie		7.0	24
20	63		1975.0	Crime	100	Italy	Mario Caiano		5.5	26
21	64		1991.0	Crime	85	Italy	Romolo Guerrieri		4.0	1
22	65		1981.0	Comedy	94	Canada	Les Rose		4.0	1
23	68		1945.0	Comedy	90	Italy	Gennaro Righelli		6.0	28
24	70		1986.0	Drama	120	United States	Michael Miller		6.0	1
25	74		1977.0	Adventure	130	United States	Peter Yates		5.2	28
26	75		1979.0	Drama	120	United States	Milton Katselas		8.0	2
27	77		1940.0	Biography	110	United States	John Cromwell		6.3	9
28	82		1943.0	Comedy	88	Italy, Spain	José Lopez Rubio, Primo Zeglio		3.5	3
29	85		1955.0	War	75	Germany	Georg Wilhelm Pabst		6.5	2
30	86		1962.0	Comedy	92	United States	Andrew Marton		6.5	3
31	88		1950.0	Comedy	104	United States	William Dieterle		7.2	5
32	91		1988.0	Comedy	90	United States	Michael Schroeder		6.0	1
33	93		1980.0	Comedy	97	United States	James Frawley		6.0	1
34	94		1987.0	Drama	77	France	Christine Allen		4.5	4
35	96		1933.0	Comedy	68	Italy	Gero Zambuto		8.0	1
36	100		1942.0	Drama	80	Italy	Nunzio Malasomma		5.5	3
37	101		1988.0	Crime	97	United States	Craig R. Baxley		5.1	32
38	102		1990.0	Drama	115	Japan	Koei Oguri		8.0	1
39	103		1977.0	Drama	92	Great Britain	Don Boyd		4.0	1
40	107		1961.0	Drama	109	United States	Daniel Mann		6.5	5
41	108		1949.0	Comedy	90	Italy	Mario Mattòli		5.0	7
42	109		1949.0	Comedy	92	Great Britain	Harold French		6.8	11
43	111		1932.0	War	78	United States	Frank Borzage		6.7	16
44	113		1990.0	Thriller	100	United States	Jerrold Freedman		6.0	1
45	114		1940.0	Drama	88	Italy	Ferdinando M. Poggioli		7.2	11
46	115		1956.0	Drama	95	United States	William A. Wellman		6.0	3
47	116		1969.0	Adventure	104	Great Britain	Jack Couffer		4.0	1
48	117		1939.0	Romantic	114	United States	Sam Wood		7.3	23
49	118		1951.0	Comedy	90	Great Britain	Anthony Asquith		8.3	14
50	119		1954.0	Drama	86	Italy	Roberto Bianchi Montero		3.0	2
51	120		1954.0	Drama	104	United States	Daniel Mann		8.0	1
52	122		1963.0	Comedy	108	France	Edouard Molinaro		5.3	10
53	123		1984.0	Comedy	100	United States	Howard Zieff		6.1	14
54	125		1943.0	Comedy	80	France	Fernandel		6.0	1
55	127		1971.0	Drama	93	Sweden, United States	Ingmar Bergman		6.0	16
56	128		1952.0	Drama	82	United States	William Spier, Roy Kellino		4.0	1
57	129		1966.0	Comedy	95	Italy	Pasquale Festa Campanile		5.5	33
58	130		1963.0	Comedy	90	Italy	Raffaello Matarazzo		5.0	4
59	136		1966.0	Spy	100	United States	Raoul Levy		4.8	4
60	137		1988.0	Comedy	92	United States	Jim Abrahams		5.2	20
61	138		1990.0	Crime	112	United States	Mike Figgis		6.5	65
62	139		1988.0	Drama	100	United States	Sharron Miller		6.0	1
63	140		1962.0	Comedy	98	United States	Richard Quine		7.1	32
64	141		1991.0	Drama	89	United States	Lou Antonio		6.0	1
65	145		1975.0	Adventure	95	Italy	Michele Lupo		5.5	20
66	146		1952.0	Adventure	95	Italy	Giovanni Roccardi		4.5	6
67	153		1969.0	Spy	140	Great Britain	Peter R. Hunt		7.3	145
68	155		1974.0	Spy	122	Great Britain	Guy Hamilton		6.0	107
69	158		1967.0	Spy	116	Great Britain	Lewis Gilbert		6.6	117
70	159		1965.0	Spy	132	Great Britain	Terence Young		7.0	130
71	162		1971.0	Spy	122	Great Britain	Guy Hamilton		6.1	116
72	163		1964.0	Spy	105	France	Maurice Labro		4.0	1
73	164		1954.0	Thriller	75	United States	Louis King		3.9	5
74	167		1965.0	Spy	90	Italy	Enrico Bomba		3.2	4
75	168		1966.0	Spy	90	Italy	Henry Bay		3.8	5
76	169		1972.0	Spy	73	Great Britain	David Greene		4.7	7
77	171		1966.0	Spy	97	France	Robert Vernay		4.0	1
78	172		1980.0	Spy	108	United States	Claudio Guzman		4.5	2
79	173		1916.0	Comedy	42	United States	Charles Chaplin		6.9	13
80	174		1984.0	Comedy	102	United States	Anthony Harvey		5.3	10
81	175		1988.0	Adventure	88	Spain	Gerardo Herrero		4.0	1
82	176		1957.0	Thriller	88	Italy	Riccardo Freda		4.9	6
83	178		1987.0	Crime	96	United States	Andy Sidaris		3.5	5
84	179		1967.0	Western	91	United States	R. G. Springsteen		5.0	2
85	180		1964.0	Adventure	91	United States	Ivan Tors		2.5	2
86	181		1964.0	Western	86	Germany	Jürgen Roland		4.5	2
87	183		1985.0	Drama	98	United States	Norman Jewison		6.1	43
88	185		1985.0	Fantasy	78	United States	Aaron Lipstadt		3.5	2
89	186		1953.0	Drama	95	Italy	Carlo Lizzani		5.4	9
90	187		1939.0	Comedy	70	Italy	Mario Mattòli		6.3	3
91	188		1953.0	Musical	97	Italy	Clemente Fracassi		6.0	7
92	189		1990.0	Adventure	106	United States	Roger Spottiswoode		5.3	56
93	193		1977.0	Action	110	United States	Jerry Jameson		4.7	36
94	194		1991.0	Comedy	100	United States	Francis Schaeffer		4.7	8
95	196		1989.0	Comedy	60	United States	Burt Brickerhoff		4.0	1
96	198		1961.0	Comedy	99	Italy	Luigi Filippo D'Amico		4.4	6
97	199		1983.0	Comedy	100	Italy	Francesco Massaro		5.1	103
98	200		1992.0	Thriller	90	Italy	Alessandro Lucidi		4.3	6
99	201		1959.0	Gangster	104	United States	Richard Wilson		6.5	11
100	202		1956.0	Drama	87	United States	Daniel Taradash		6.3	9

Rows: 1-100 of 53397 | Columns: 10

We have access to more than 50000 movies in 27 different genres. Let's organize our list by their average rating.

In [6]:

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

Out[6]:

	123 filmtv_id Int	Abc Varchar(486)	123 year Numeric(8,2)	Abc genre Varchar(22)	123 duration Int	Abc Varchar(208)	Abc Varchar(1066)	Abc Varchar(2218)	123 avg_vote Numeric(6,2)	123 votes Int
1	74376		2013.0	Documentary	193				10.0	1
2	70969		1969.0	Documentary	378				10.0	1
3	81715		2015.0	Documentary	99				10.0	1
4	74408		2013.0	Documentary	87				10.0	1
5	72721		1987.0	Fantasy	81				10.0	1
6	125083		2016.0	Drama	65				10.0	1
7	86266		2018.0	Drama	73				10.0	1
8	82280		2016.0	Comedy	90				10.0	1
9	79888		1976.0	Comedy	100				10.0	1
10	75394		1973.0	Drama	101				10.0	1
11	72947		2014.0	Comedy	101				10.0	1
12	139242		2006.0	Thriller	92				10.0	1
13	134051		2016.0	Thriller	90				10.0	2
14	127365		2016.0	Documentary	70				10.0	3
15	121261		2015.0	Drama	90				10.0	2
16	86945		2015.0	Comedy	86				10.0	1
17	84535		1974.0	Animation	43				10.0	1
18	83102		1994.0	Drama	81				10.0	1
19	81189		2015.0	Drama	115				10.0	1
20	80205		2008.0	Drama	200				10.0	1
21	77898		1975.0	Drama	177				10.0	1
22	75806		1948.0	Documentary	71				10.0	1
23	74238		2014.0	Drama	126				10.0	1
24	73445		1971.0	Comedy	85				10.0	1
25	140035		2001.0	Drama	91				10.0	1
26	139818		2017.0	Comedy	105				10.0	1
27	156059		2018.0	Comedy	83				10.0	1
28	134090		2016.0	Comedy	90				10.0	1
29	129914		2015.0	Comedy	89				10.0	1
30	128665		2016.0	Documentary	57				10.0	1
31	150959		2015.0	Romantic	90				10.0	1
32	144085		2000.0	Comedy	94				10.0	1
33	121147		2001.0	Drama	93				10.0	1
34	114615		2015.0	Documentary	110				10.0	1
35	150077		2017.0	Romantic	90				10.0	1
36	86042		2007.0	Drama	92				10.0	1
37	83342		2008.0	War	118				10.0	1
38	83152		1982.0	Horror	70				10.0	1
39	147257		2017.0	Drama	92				10.0	1
40	81431		2015.0	Drama	80				10.0	1
41	81147		2005.0	Adventure	104				10.0	1
42	80309		2015.0	Drama	280				10.0	1
43	145657		2017.0	Thriller	102				10.0	1
44	77950		2014.0	Drama	87				10.0	1
45	77078		2009.0	Animation	60				10.0	1
46	76646		1937.0	Adventure	92				10.0	1
47	141940		2017.0	Drama	88				10.0	3
48	122810		2015.0	Documentary	82				10.0	1
49	143356		2017.0	Thriller	90				10.0	1
50	155093		1945.0	Biography	138				10.0	1
51	172743		2019.0	Drama	77				10.0	1
52	160587		2018.0	Comedy	90				10.0	1
53	162017		2018.0	Romantic	90				10.0	1
54	139825		2017.0	Comedy	81				10.0	1
55	173489		1967.0	Comedy	86				10.0	1
56	158417		1982.0	Documentary	63				10.0	1
57	166907		2019.0	Comedy	84				10.0	2
58	136505		2016.0	Documentary	70				10.0	1
59	167259		1985.0	Animation	53				10.0	1
60	156113		2016.0	Documentary	60				10.0	1
61	156687		2018.0	Documentary	85				10.0	1
62	129161		2017.0	Comedy	90				10.0	1
63	179863		2017.0	Romantic	84				10.0	1
64	164227		1988.0	Drama	105				10.0	1
65	169427		2008.0	History	72				10.0	1
66	147663		2017.0	Drama	73				10.0	1
67	171099		2019.0	Documentary	107				10.0	1
68	152045		2017.0	Drama	92				10.0	1
69	162235		2015.0	Documentary	90				10.0	1
70	120668		2016.0	Thriller	90				10.0	2
71	176199		2019.0	Documentary	76				10.0	1
72	150431		2017.0	Romantic	90				10.0	1
73	164301		2016.0	Documentary	50				10.0	2
74	86071		2016.0	Documentary	70				10.0	1
75	166775		2019.0	Musical	130				10.0	1
76	150297		2017.0	Action	99				10.0	1
77	150351		2017.0	Documentary	62				10.0	2
78	83306		1976.0	Comedy	97				10.0	2
79	177553		2008.0	Animation	83				10.0	1
80	162375		2018.0	Documentary	74				10.0	1
81	164063		2019.0	Adventure	104				10.0	1
82	144421		2017.0	Documentary	90				10.0	1
83	169517		2019.0	Drama	108				10.0	1
84	147438		2005.0	Animation	67				10.0	1
85	147442		2016.0	Thriller	180				10.0	1
86	80637		2015.0	Comedy	83				10.0	1
87	170879		2019.0	Comedy	90				10.0	1
88	147231		2017.0	Documentary	60				10.0	1
89	162377		2018.0	Comedy	90				10.0	1
90	79207		2015.0	Romantic	90				10.0	1
91	163599		2018.0	Thriller	90				10.0	1
92	146073		2017.0	Comedy	115				10.0	1
93	147093		2017.0	Thriller	90				10.0	1
94	76714		1972.0	Comedy	90				10.0	1
95	179519		2018.0	Thriller	87				10.0	1
96	160563		2018.0	Documentary	78				10.0	1
97	166777		2017.0	Romantic	86				10.0	3
98	138920		2017.0	Drama	101				10.0	1
99	167391		2019.0	Documentary	75				10.0	1
100	70671		2014.0	Drama	80				10.0	1

Rows: 1-100 of 53397 | Columns: 10

Since we want properly averaged scores, let's just consider the top 10 movies that have at least 10 votes.

In [7]:

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

Out[7]:

	123 filmtv_id Integer	Abc Varchar(486)	123 year Numeric(8,2)	Abc genre Varchar(22)	123 duration Integer	Abc Varchar(208)	Abc Varchar(1066)	Abc Varchar(2218)	123 avg_vote Numeric(6,2)	123 votes Integer
1	25980		1992.0	Drama	583				9.8	33
2	29136		1985.0	Documentary	544