Movies Scoring and Clustering

This example uses the filmtv_movies dataset to evaluate the quality of the movies and create clusters of similar movies.

The columns provided include:

• 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 vastorbit:

import vastorbit as vo

vo.__version__

Connect to VAST. This example uses an existing connection called VASTDSN. For details on how to create a connection, see the Connection tutorial. You can skip the below cell if you already have an established connection.

vo.connect("VASTDSN")

Let’s create a new schema and assign the data to a VastFrame object.

filmtv_movies = vo.read_csv("movies.csv")
filmtv_movies.head(5)

Let’s take a look at the first few entries in the dataset.

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 99%	Abc genre Varchar(50) 99%	123 duration Integer 100%	Abc country Varchar(154) 99%	Abc director Varchar(632) 99%	Abc actors Varchar(1196) 94%	123 avg_vote Double 100%	123 votes Integer 100%	Abc description Varchar(1213) 0%	Abc notes Varchar(626) 0%
1	2774	À bout de souffle	1960	Drama	90	France	Jean-Luc Godard	Henri Jacques Huet, Jean-Paul Belmondo, Jean Seberg, Van Doude	8.3	252	[null]	[null]
2	2775	To the Death	1992	Action	90	United States	[null]	John Barrett, Michel Quissi	4.0	1	[null]	[null]
3	2776	Il fiore delle Mille e una notte	1974	Drama	130	Italy	Pier Paolo Pasolini	Ninetto Davoli, Franco Citti, Tessa Bouché, Franco Merli	7.4	82	[null]	[null]
4	2777	Steel Magnolias	1989	Drama	119	United States	Herbert Ross	Shirley MacLaine, Sally Field, Julia Roberts, Olympia Dukakis, Daryl Hannah, Dolly Parton, Sam Shepard, Tom Skerritt, Dylan McDermott	6.2	62	[null]	[null]
5	2778	Kaagaz ke phool	1959	Drama	138	India	Guru Dutt	Guru Dutt, Waheeda Rehman, Baby Naaz, Mahesh Kaul, Mehmood	7.0	4	[null]	[null]

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.

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

	dtype	count	top	top_percent	unique
"filmtv_id"	integer	53497	43476	0.002	53497.0
"title"	varchar(255)	53497	Les Vampires	0.019	50681.0
"year"	integer	53485	2016	3.086	111.0
"genre"	varchar(50)	53294	Drama	30.106	27.0
"duration"	integer	53497	90	11.801	283.0
"country"	varchar(154)	53446	United States	41.148	2396.0
"director"	varchar(632)	53433	Mario Mattòli	0.136	19179.0
"actors"	varchar(1196)	50462	[null]	5.673	50208.0
"avg_vote"	double	53497	6.0	15.012	89.0
"votes"	integer	53497	1	22.99	588.0
"description"	varchar(1213)	439	[null]	99.179	434.0
"notes"	varchar(626)	170	[null]	99.682	169.0

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

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 99%	Abc genre Varchar(50) 99%	123 duration Integer 100%	Abc country Varchar(154) 99%	Abc director Varchar(632) 99%	Abc actors Varchar(1196) 94%	123 avg_vote Double 100%	123 votes Integer 100%
1	2774	À bout de souffle	1960	Drama	90	France	Jean-Luc Godard	Henri Jacques Huet, Jean-Paul Belmondo, Jean Seberg, Van Doude	8.3	252
2	2775	To the Death	1992	Action	90	United States	[null]	John Barrett, Michel Quissi	4.0	1
3	2776	Il fiore delle Mille e una notte	1974	Drama	130	Italy	Pier Paolo Pasolini	Ninetto Davoli, Franco Citti, Tessa Bouché, Franco Merli	7.4	82
4	2777	Steel Magnolias	1989	Drama	119	United States	Herbert Ross	Shirley MacLaine, Sally Field, Julia Roberts, Olympia Dukakis, Daryl Hannah, Dolly Parton, Sam Shepard, Tom Skerritt, Dylan McDermott	6.2	62
5	2778	Kaagaz ke phool	1959	Drama	138	India	Guru Dutt	Guru Dutt, Waheeda Rehman, Baby Naaz, Mahesh Kaul, Mehmood	7.0	4
6	2779	Firefox	1982	Action	135	United States	Clint Eastwood	Clint Eastwood, Freddie Jones, David Huffman, Warren Clarke	5.8	84
7	2780	Firenze d'allora	[null]	History	67	Italy	Sandro Sequi	A. Bianchini, Marisa Fabbri	6.0	1
8	2781	Il fischio al naso	1967	Grotesque	113	Italy	Ugo Tognazzi	Ugo Tognazzi, Tina Louise, Olga Villi, Franca Bettoja, Riccardo Garrone	6.8	56
9	2782	Body Waves	1992	Comedy	78	United States	P. J. Pesce	Bill Calvert, Leah Lail, Larry Linville, Dick Miller	5.0	2
10	2783	Fitzcarraldo	1982	Adventure	158	Germany	Werner Herzog	Klaus Kinski, Claudia Cardinale, José Lewgoy, Miguel Angel Fuentes	8.4	168
11	2784	Il fiume del grande caimano	1979	Adventure	90	Italy	Sergio Martino	Barbara Bach, Claudio Cassinelli, Mel Ferrer, Richard Johnson	5.1	24
12	2786	The River	1984	Drama	122	United States	Mark Rydell	Mel Gibson, Sissy Spacek, Scott Glenn, Shane Bailey	6.0	30
13	2787	River of Death	1989	Adventure	111	United States	Steve Carver	Michael Dudikoff	8.0	3
14	2789	Red River	1988	Western	100	United States	Richard Michaels	James Arness, Bruce Boxleitner, Ty Hardin, Guy Madison, Ray Walston	6.2	4
15	2790	The Flamingo Kid	1984	Comedy	99	United States	Garry Marshall	Matt Dillon, Hector Elizondo, Richard Crenna, Jessica Walter, Joe Grifasi, Janet Jones	5.5	6
16	2791	Flash Gordon	1980	Fantasy	100	Great Britain	Mike Hodges	Sam Jones, Max Von Sydow, Ornella Muti, Mariangela Melato	5.7	106
17	2792	Flashback	1990	Comedy	103	United States	Franco Amurri	Kiefer Sutherland, Dennis Hopper, Carol Kane	5.9	9
18	2793	Flashdance	1983	Musical	89	United States	Adrian Lyne	Jennifer Beals, Michael Nouri, Belinda Bauer, Lilia Skala	6.0	131
19	2794	La flûte à six schtroumpfs	1976	Animation	80	Belgium	Eddie Lateste, Peyo	[null]	6.4	7
20	2795	Trollflöjten	1974	Musical	135	Sweden	Ingmar Bergman	Ulrik Cold, Josef Köstlinger, Birgit Nordin, Håkan Hagegård	7.9	29

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

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 99%	Abc genre Varchar(50) 99%	123 duration Integer 100%	Abc country Varchar(154) 99%	Abc director Varchar(632) 99%	Abc actors Varchar(1196) 94%	123 avg_vote Double 100%	123 votes Integer 100%
1	162375	City of Joy	2018	Documentary	74	United States, Republic of the Congo	Madeleine Gavin	Christine Schuler-Deschryver, Denis Mukwege Mukengere, Eve Ensler, Jane Mukunilwa	10.0	1
2	163599	Degrees of Fear	2018	Thriller	90	United States	Damián Romay	Claire Blackwelder, Bryan Lillis, Tim Bensch, William DeAtley, Vergena Fields, Lacy Hartselle, Kelly Heyer, Denise Johnson, Jasmine Johnson, Airica Kraehmer	10.0	1
3	162377	Te lo dico pianissimo	2018	Comedy	90	Italy	Pasquale Marrazzo	Lucia Vasini, Stefano Chiodaroli, Pietro Pignatelli, Cinzia Marseglia, Corinna Agustoni, Tatiana Winteler, Luisa Vernelli, Jacopo Costantini, Renato Cortesi, Luca Torraca, Daniele Squassina, Giorgio Rosa	10.0	1
4	35311	Quem És Tu?	2001	Drama	112	Portugal	João Botelho	Patrícia Guerreiro, Suzana Borges, Rui Morisson, Rogério Samora	10.0	1
5	34206	Banovic Strahinja	1981	Drama	105	Yugoslavia, Germany	Vatroslav Mimica	Franco Nero, Dragan Nikolic, Rade Serbedzija, Sanja Vejnovic	10.0	2
6	162017	Love in Design	2018	Romantic	90	United States	Steven R. Monroe	Danica McKellar, Andrew W. Walker, Alvina August, Jan Skene, Eric Pollins, Brenda Gorlick, Paul Essiembre, Adam Hurtig	10.0	1
7	35014	Der Letzte Akt	1955	Drama	113	Germany	Georg Wilhelm Pabst	Albin Skoda, Oskar Werner, Lotte Tobisch, Willy Krause, Erich Stuckmann, Erland Erlandsen, Erik Frey	10.0	1
8	33964	Snow White and Three Stooges	1961	Comedy	107	United States	Walter Lang	Carol Heiss, Edson Stroll, Patricia Medina, Larry Fine, Joe DeRita	10.0	1
9	35277	Stiletto Dance	2001	Action	97	United States	Mario Azzopardi	Eric Roberts, Shawn Doyle, Romano Orzari, Brett Porter, Yaphet Kotto	10.0	1
10	34730	The Human Shield	1992	Action	88	United States	Ted Post	Michael Dudikoff, Tommy Hinkley, Hana Azoulay-Hasfari, Steve Inwood	10.0	1
11	35275	Expect No Mercy	1996	Action	91	United States	Zale Dalen	Anthony DeLongis, Billy Blanks, Jalal Merhi, Wolf Larson, Laurie Holden	10.0	1
12	35049	Project Shadowchaser II	1995	Fantasy	94	United States	John Eyres	Frank Zagarino, Bryan Genesse, Beth Toussaint, Danny Hill, Todd Jensen	10.0	1
13	34159	Donne proibite	1954	Drama	94	Italy	Giuseppe Amato	Lea Padovani, Valentina Cortese, Linda Darnell, Giulietta Masina, Anthony Quinn, Carlo Dapporto, Rossella Falk, Tino Buazzelli	10.0	1
14	33880	Angela	1955	Drama	81	United States, Italy	Edoardo Anton, Dennis O'Keefe	Dennis O'Keefe, Rossano Brazzi, Arnaldo Foà, Mara Lane	10.0	1
15	33954	Strafbataillon 999	1959	War	109	Germany	Harald Philipp	Ernst Schroeder, Werner Peters, Heinz Weiss, Sonja Ziemann	10.0	1
16	34645	Le défroqué	1954	Drama	107	France	Léo Jannon	Pierre Fresnay, Léo Jannon, René Blancard, Jacques Fabbri	10.0	1
17	164301	Camminando sull'acqua	2016	Documentary	50	Italy	Gianmarco D'Agostino	[null]	10.0	2
18	164063	Ostwind - Aris Ankunft	2019	Adventure	104	Germany	Theresa von Eltz	Luna Paiano, Hanna Binke, Marvin Linke, Amber Bongard, Tilo Prückner, Cornelia Froboess, Lili Epply, Sabin Tambrea, Meret Becker, Nina Kronjäger	10.0	1
19	164227	The Girl	1988	Drama	105	Sweden, Great Britain, Italy	Arne Mattsson	Franco Nero, Clare Powney, Bernice Stegers, Frank Brennan, Christopher Lee, Mark Robinson, Derek Benfield, Clifford Rose, Rosie Jauckens, Lenore Zann	10.0	1
20	162235	Les requins de la colère	2015	Documentary	90	France	Jérôme Delafosse	Romain Chabrol, Maria Damanaki, Claire Nouvian	10.0	1

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

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc genre Varchar(50) 99%	123 duration Integer 100%	Abc country Varchar(154) 99%	Abc director Varchar(632) 99%	Abc actors Varchar(1196) 96%	123 avg_vote Double 100%	123 votes Integer 100%
1	25980	The Godfather Trilogy: 1901-1980	1992	Drama	583	United States	Francis Ford Coppola	Marlon Brando, Al Pacino, Robert Duvall, James Caan, Diane Keaton	9.8	33
2	29136	Shoah	1985	Documentary	544	France	Claude Lanzmann	[null]	9.6	24
3	16567	Greed	1924	Drama	100	United States	Erich Von Stroheim	Gibson Gowland, Jean Hersholt, Chester Conklyn	9.6	58
4	7753	Sunset Boulevard	1950	Drama	100	United States	Billy Wilder	Gloria Swanson, William Holden, Erich Von Stroheim, Nancy Olson, Fred Clark, Cecil B. DeMille, Hedda Hopper, Buster Keaton, Lloyd Gough, Jack Webb	9.4	535
5	16584	La maman et la putain	1973	Comedy	208	France	Jean Eustache	Jean-Pierre Léaud, Bernadette Lafont, Francoise Lebrun, Isabelle Weingarten	9.4	30
6	5648	Citizen Kane	1941	Drama	119	United States	Orson Welles	Orson Welles, Joseph Cotten, Dorothy Comingore	9.4	588
7	23395	Shichi-nin no Samurai	1954	Adventure	200	Japan	Akira Kurosawa	Takashi Shimura, Toshiro Mifune, Yoshio Inaba, Seiji Miyaguchi	9.4	325
8	3831	Ladri di biciclette	1948	Drama	90	Italy	Vittorio De Sica	Lamberto Maggiorani, Enzo Staiola, Lianella Carell, Elena Altieri	9.4	477
9	27908	Seppuku	1962	Drama	135	Japan	Masaki Kobayashi	Tatsuya Nakadai, Akira Ishihama, Shima Iwashita, Akira Ishihama	9.4	68
10	12135	Sátántángo	1994	Drama	450	Hungary	Béla Tarr	Mihaly Vig, Putyi Horvath, János Derzsi, Miklós Székely B., László feLugossy, Éva Almássy Albert, Irén Szajki, Alfréd Járai, Erzsébet Gaál, Péter Dobai, Zoltán Kamondi, Barna Mihók	9.4	72
11	7042	Modern Times	1936	Comedy	83	United States	Charles Chaplin	Charles Chaplin, Paulette Goddard, Henry Bergman	9.4	427
12	10423	The Wind	1928	Drama	75	United States	Victor Sjöström	Lillian Gish, Lars Harson	9.3	29
13	26035	Zemlja	1930	Drama	84	Soviet Union	Aleksandr P. Dovzenko	Stepan Shkurat, Semen Svasenko	9.3	31
14	27984	Die Zweite Heimat - Chronik einer Jugend	1992	Drama	1525	Germany	Edgar Reitz	Henry Arnold, Salome Kammer, Anke Sevenich, Noemi Steuer	9.3	18
15	5580	Psycho	1960	Thriller	108	United States	Alfred Hitchcock	Anthony Perkins, Janet Leigh, Vera Miles, John Gavin, Martin Balsam	9.3	789
16	10559	Sherlock Junior	1924	Comedy	42	United States	Buster Keaton	Buster Keaton, Kathrin McGuire, Ward Crane	9.3	123
17	18173	Ordet	1955	Drama	119	Denmark	Carl Theodor Dreyer	Preben Lendorff Rye, Henrik Malberg, Birgitte Federspiel, Ann Elisabeth Rud	9.3	148
18	28382	Tasogare seibei	2002	Action	129	Japan	Yoji Yamada	Hiroyuki Sanada, Rie Miyazawa, Nenji Kobayashi, Ren Osugi	9.3	17
19	12730	Paths of Glory	1957	War	86	United States	Stanley Kubrick	George Macready, Timothy Carey, Kirk Douglas, Ralph Meeker, Adolphe Menjou	9.3	514
20	23023	Chikamatsu Monogatari	1954	Drama	110	Japan	Kenji Mizoguchi	Kazuo Hasegawa, Yoko Minamida, Eitaro Shindo, Kyôko Kagawa	9.3	50

We can see classic movies like The Godfather and Greed. Let’s smooth the avg_vote using a linear regression to make it more representative.

To create our model we could use the votes, the category, the duration, etc. but let’s go with the director and main actors.

We can extract the five main actors for each movie with regular expressions.

for i in range(1, 5):
    filmtv_movies2 = vo.VastFrame("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"].topk(100)

	count	percent
Totò	100	0.047
Ciccio Ingrassia	91	0.043
John Wayne	85	0.04
Franco Franchi	83	0.039
Marcello Mastroianni	79	0.037
Alberto Sordi	79	0.037
Pierre Mondy	75	0.035
David Suchet	71	0.033
Gérard Depardieu	70	0.033
Ugo Tognazzi	70	0.033
Antonella Lualdi	67	0.031
Christopher Lee	66	0.031
Vittorio De Sica	66	0.031
Donald Sutherland	66	0.031
Franco Nero	65	0.03
Harvey Keitel	63	0.029
Nicolas Cage	61	0.029
Bruno Madinier	61	0.029
Sylva Koscina	61	0.029
Burt Lancaster	60	0.028
Christopher Plummer	59	0.028
Michael Caine	59	0.028
Ernest Borgnine	59	0.028
Jackie Chan	59	0.028
Peppino De Filippo	58	0.027
Philippe Leroy	57	0.027
Robert De Niro	57	0.027
Max Von Sydow	57	0.027
Gabriele Ferzetti	57	0.027
Stefania Sandrelli	57	0.027
Martin Sheen	56	0.026
Heide Keller	56	0.026
Adolfo Celi	54	0.025
Giancarlo Giannini	54	0.025
Enrico Maria Salerno	54	0.025
Steven Seagal	54	0.025
Mario Carotenuto	54	0.025
Andrea Checchi	53	0.025
Shelley Winters	53	0.025
Gary Cooper	52	0.024
Alain Delon	52	0.024
Vittorio Gassman	52	0.024
Susan Sarandon	52	0.024
Morgan Freeman	52	0.024
Paolo Stoppa	52	0.024
Gérard Depardieu	52	0.024
Donald Pleasence	51	0.024
Ugo Tognazzi	51	0.024
Carlo Campanini	51	0.024
Glenn Ford	51	0.024
Michel Piccoli	51	0.024
Kirk Douglas	50	0.023
George Sanders	50	0.023
Massimo Serato	50	0.023
Burt Reynolds	50	0.023
Christopher Walken	50	0.023
Mario Adorf	49	0.023
Michele Placido	49	0.023
Anthony Quinn	49	0.023
Isabelle Huppert	49	0.023
Luca Zingaretti	49	0.023
Samuel L. Jackson	49	0.023
Renzo Montagnani	48	0.022
Fernando Rey	48	0.022
Paolo Villaggio	48	0.022
James Mason	48	0.022
Robert Mitchum	48	0.022
Robert Duvall	48	0.022
Alessandro Haber	48	0.022
Vittorio Gassman	48	0.022
Nino Manfredi	47	0.022
James Stewart	47	0.022
Ornella Muti	47	0.022
Bernard Blier	47	0.022
Bruce Willis	47	0.022
Malcolm McDowell	47	0.022
Bruce Dern	47	0.022
Virna Lisi	46	0.021
John Malkovich	46	0.021
Rutger Hauer	46	0.021
Martin Balsam	46	0.021
Jean Gabin	46	0.021
Dennis Hopper	46	0.021
Charlton Heston	46	0.021
Walter Chiari	46	0.021
Ellen Burstyn	45	0.021
Christopher Lloyd	45	0.021
Paul Newman	45	0.021
Charles Bronson	45	0.021
Marcello Mastroianni	45	0.021
Lino Banfi	45	0.021
Charles Durning	44	0.021
Francisco Rabal	44	0.021
Willem Dafoe	44	0.021
Catherine Deneuve	44	0.021
Alberto Sordi	44	0.021
Rod Steiger	44	0.021
Franco Nero	44	0.021
Sophia Loren	44	0.021
Amedeo Nazzari	43	0.02

By aggregating the data, we can find the number of actors and the number of votes by actor. We can then normalize the data using the min-max method and quantify the notoriety of the actors.

import vastorbit.sql.functions as fun

actors_stats = filmtv_movies.groupby(
    columns = ["actor"],
    expr = [
        fun.sum(filmtv_movies["votes"])._as("notoriety_actors"),
        fun.count(filmtv_movies["actors"])._as("castings_actors"),
    ],
)
actors_stats["actor"].dropna()
actors_stats["notoriety_actors"].normalize(method = "minmax")

	Abc actor Varchar(1196) 100%	123 notoriety_actors Real 100%	123 castings_actors Bigint 100%
1	Lorraine Bracco	0.10989123	13
2	Erminio Macario	0.03456338	12
3	Eric Walker	0.00030497	1
4	Tim Thomerson	0.00630273	10
5	Forbes Murray	0.00426959	1
6	Annibale Betrone	0.00386297	3
7	Sergio Fantoni	0.02897225	17
8	Dean Jagger	0.02653248	26
9	Frank Baker	0.00010166	2
10	Sherry Buchanan	0.00752262	4
11	Jeff Cameron	0.00437125	3
12	Marisa Merlini	0.09413439	40
13	Barbara Turner	0.00010166	2
14	Daniel Gélin	0.04676222	19
15	Raul Julia	0.0891532	20
16	Katherine Helmond	0.00650605	6
17	Beau Bridges	0.04228932	34
18	Salvatore Borgese	0.01148724	1
19	Judge Reinhold	0.05530141	24
20	Klaus Kinski	0.10989123	39

Let’s look at the top ten actors by notoriety.

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

	Abc actor Varchar(1196) 100%	123 notoriety_actors Decimal(29,8) 100%	123 castings_actors Bigint 100%
1	Robert De Niro	1.0	57
2	Morgan Freeman	0.8614415	52
3	Clint Eastwood	0.8564603	43
4	Tom Cruise	0.82037206	34
5	Johnny Depp	0.81488259	34
6	Tom Hanks	0.77188167	37
7	Samuel L. Jackson	0.7425028	49
8	Brad Pitt	0.72776253	26
9	Leonardo DiCaprio	0.71546203	20
10	Al Pacino	0.64836841	40

As expected, we get a list of very popular actors like Robert De Niro, Morgan Freeman, and Clint Eastwood.

Let’s do the same for the directors.

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

	Abc director Varchar(632) 99%	123 notoriety_director Real 100%	123 castings_director Bigint 100%
1	David Lynch	0.367590988	60
2	Rosalia Polizzi	0.00017331	8
3	Gabriele Salvatores	0.218370884	80
4	Wisit Sasanatieng	0.002166378	16
5	Chen Kaige	0.012564991	40
6	Peter Docter	0.041421144	4
7	Penny Marshall	0.03812825	28
8	Tamra Davis	0.004419411	24
9	Steve Barron	0.010745234	36
10	Eric Hendershot	0.000866551	28
11	John McTiernan	0.141854419	44
12	Wes Anderson	0.203812825	36
13	David Elvin	0.0	4
14	Joris Ivens	0.000519931	12
15	Dennis Dugan	0.078942808	68
16	Andrew Dominik	0.041247834	16
17	Richard Wilson	0.004246101	20
18	Drummond Challis, David Wooster	8.6655e-05	4
19	Martin Scorsese	0.829289428	140
20	Roberta Torre	0.01152513	24

Now let’s look at the top 10 movie directors.

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

	Abc director Varchar(632) 99%	123 notoriety_director Decimal(30,9) 100%	123 castings_director Bigint 100%
1	Steven Spielberg	1.0	132
2	Woody Allen	0.962045061	192
3	Clint Eastwood	0.893067591	152
4	Martin Scorsese	0.829289428	140
5	Alfred Hitchcock	0.753379549	208
6	Ridley Scott	0.693500867	112
7	Quentin Tarantino	0.686741768	40
8	Stanley Kubrick	0.649913345	48
9	Tim Burton	0.588214905	72
10	David Cronenberg	0.513431542	84

Again, we get a list of popular directors like Steven Spielberg, Woody Allen, and Clint Eastwood.

Let’s join our notoriety metrics for actors and directors with the main dataset.

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",
    ],
)

As we did many operation, it can be nice to save the VastFrame as a table in the VAST DataBase.

vo.drop("filmtv_movies_director_actors", method = "table")
filmtv_movies_director_actors.to_db(
    name = "filmtv_movies_director_actors",
    relation_type = "table",
    inplace = True,
)

We can aggregate the data to get metrics on each movie.

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 = [
        fun.sum(filmtv_movies_director_actors["notoriety_actors"])._as("notoriety_actors"),
        fun.sum(filmtv_movies_director_actors["castings_actors"])._as("castings_actors"),
    ],
)

Let’s compute some statistics on our dataset.

filmtv_movies_complete.describe(method = "all")

	"filmtv_id"	"year"	"avg_vote"	"votes"	"duration"	"notoriety_director"	"castings_director"	"notoriety_actors"	"castings_actors"	"title"	"genre"	"country"	"director"
dtype	integer	integer	double	integer	integer	decimal(30,9)	bigint	decimal(38,8)	bigint	varchar(255)	varchar(50)	varchar(154)	varchar(632)
percent	100.0	99.978	100.0	100.0	100.0	99.88	99.88	94.327	94.327	100.0	99.621	99.905	99.88
count	53497	53485	53497	53497	53497	53433	53433	50462	50462	53497	53294	53446	53433
top	34124	2016	6.0	1	90	0.0	4	[null]	4	Les Vampires	Drama	United States	Mario Mattòli
top_percent	0.002	3.086	15.012	22.99	11.801	7.98	22.297	5.673	6.036	0.019	30.106	41.148	0.136
avg	44432.83972933062	1990.953762737216	5.8449127988485134	24.55935846869918	98.47202646877395	0.030358427	41.722905320681974	0.15177762	39.15629582656256	17.517169187057217	6.5144481555146925	11.498203794484152	14.98412965770217
stddev	42399.88543579693	22.90556774162335	1.524591659975186	61.02025611953502	35.32468299109015	0.08142434642150961	48.10052056794561	0.22910496031638788	34.193246464462405	9.683024479188294	1.7851413092342423	6.361542066807146	7.952945139332811
min	2	1897	0.5	1	40	0.0	4	0.0	1	1	3	4	3
approx_25%	15101	1975	4.911815686252396	2	89	0.0005772231	7	0.010034426	11	11	5	6	12
approx_50%	31445	1997	5.9984754086758185	5	95	0.0043295827	23	0.05898159	30	16	6	13	14
approx_75%	57312	2010	6.940050299994486	18	105	0.021892866	60	0.20034872	58	22	8	13	16
max	179937	2019	10.0	1222	5280	1.0	292	2.23147301	267	243	11	104	532
range	179935	122	9.5	1221	5240	1.0	288	2.23147301	266	242	8	100	529
empty	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	0	0	0	0

We can use the movie’s release year to get create three categories.

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 99%	Abc genre Varchar(50) 99%	Abc country Varchar(154) 99%	123 avg_vote Double 100%	123 votes Integer 100%	123 duration Integer 100%	Abc director Varchar(632) 99%	123 notoriety_director Decimal(30,9) 99%	123 castings_director Bigint 99%	123 notoriety_actors Decimal(38,8) 94%	123 castings_actors Bigint 94%	Abc period Varchar(6) 100%
1	3073	Dangerous Game	1987	Thriller	Australia	6.0	1	98	Stephen Hopkins, David Lewis	0.0	4	0.00772593	6	Old
2	3076	The Falcon and the Snowman	1984	Spy	United States	6.6	31	127	John Schlesinger	0.070710572	80	0.48012606	74	Old
3	3079	Il gioco delle spie	1966	Spy	Italy, France	3.5	2	86	Paolo Bianchini	0.005719237	48	0.09088137	64	Old
4	3080	Que les gros salaires lèvent le doigt	1982	Comedy	France	6.0	1	106	Denys Granier-Deferre	8.6655e-05	8	0.33841618	98	Old
5	3090	Days of Heaven	1978	Drama	United States	8.1	232	95	Terrence Malick	0.190987868	40	0.48348073	74	Old
6	3097	Giorni felici	1942	Comedy	Italy	6.3	4	85	Gianni Franciolini	0.017417678	60	0.24245197	131	Old
7	3131	Giovanna d'Arco al rogo	1954	Drama	Italy, France, Great Britain	6.0	26	70	Roberto Rossellini	0.141507799	136	0.18359257	36	Old
8	3152	Julia	1977	Drama	United States	7.7	53	118	Fred Zinnemann	0.065164645	72	0.37694418	117	Old
9	3153	Giulietta degli spiriti	1965	Drama	Italy	7.3	128	137	Federico Fellini	0.283968804	88	0.34248246	117	Old
10	3164	El Zorro cabalga otra vez	1965	Adventure	Italy, Spain	4.0	5	96	Ricardo Blasco	0.001213172	12	0.0049812	11	Old
11	3178	Il giustiziere dei mari	1961	Adventure	Italy	4.9	6	85	Domenico Paolella	0.025996534	128	0.10247027	91	Old
12	3180	Death Wish II	1982	Action	United States	5.5	36	92	Michael Winner	0.038734835	88	0.17393514	82	Old
13	3185	Gladiator	1986	Adventure	United States	7.3	11	120	Abel Ferrara	0.137868284	104	0.10338517	47	Old
14	3207	Sitting Pretty	1948	Comedy	United States	7.4	8	84	Walter Lang	0.015511265	92	0.0993189	66	Old
15	3259	The Great Scout & Cathouse Thursday	1976	Western	United States	6.3	3	102	Don Taylor	0.020450607	64	0.17373184	61	Old
16	3276	Grazie nonna	1975	Comedy	Italy	3.7	26	90	Franco Martinelli	0.011265165	20	0.1281895	38	Old
17	3280	Green Card	1991	Comedy	United States, France	7.0	96	108	Peter Weir	0.231975737	56	0.38456847	102	90s
18	3281	Gremlins	1984	Fantasy	United States	7.6	272	105	Joe Dante	0.127036395	60	0.14669107	20	Old
19	3283	A Cry for Love	1980	Drama	United States	6.0	1	100	Paul Wendkos	0.005979203	120	0.05763952	17	Old
20	3288	El aventurero de Guaynas	1967	Western	Italy, Spain	3.8	3	88	Joaquin Luis Romero Marchent	0.002859619	36	0.04808376	32	Old

Now, let’s look at the countries that made the most movies.

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

	Abc country Varchar(154) 99%	123 count Bigint 100%
1	United States	22013
2	Italy	9089
3	France	3051
4	Great Britain	2531
5	Germany	1705
6	Japan	1133
7	Canada	1054
8	Spain	563
9	Italy, France	403
10	Hong Kong	380

We can use this variable to create language groups.

# 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',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Arabic_Middle_Est))), 'Arabic_Middle_Est',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Chinese_Japan_Asian))), 'Chinese_Japan_Asian',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Indian))), 'Indian',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Hebrew))), 'Hebrew',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Spanish_Portuguese))), 'Spanish_Portuguese',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(English))), 'English',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(French))), 'French',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Italian))), 'Italian',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(German_North_Europe))), 'German_North_Europe',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Russian_Est_Europe))), 'Russian_Est_Europe',
    vo.StringSQL("REGEXP_LIKE(Country, '{}')".format("|".join(Grec_Balkan))), 'Grec_Balkan',
    'Others'
)

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 99%	Abc genre Varchar(50) 99%	Abc country Varchar(154) 99%	123 avg_vote Double 100%	123 votes Integer 100%	123 duration Integer 100%	Abc director Varchar(632) 99%	123 notoriety_director Decimal(30,9) 99%	123 castings_director Bigint 99%	123 notoriety_actors Decimal(38,8) 94%	123 castings_actors Bigint 94%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%
1	25132	Buongiorno, notte	2003	Drama	Italy	7.5	242	105	Marco Bellocchio	0.182062392	120	0.33668801	53	Recent	Italian
2	25136	Raja	2003	Comedy	France, Morocco	8.0	1	112	Jacques Doillon	0.002253033	44	0.0014232	6	Recent	Arabic_Middle_Est
3	25162	Um filme falado	2003	Drama	Portugal, France, Italy	7.3	75	96	Manoel de Oliveira	0.055199307	132	0.89620819	150	Recent	Spanish_Portuguese
4	25164	Vozvraschenie	2003	Drama	Russia	7.1	98	105	Andrej Zvyagintsev	0.025563258	20	0.03964623	6	Recent	Russian_Est_Europe
5	25181	Lost in Translation	2003	Comedy	United States	7.3	488	105	Sofia Coppola	0.147487002	28	1.09474433	75	Recent	English
6	25209	Ballo a tre passi	2003	Drama	Italy	6.7	27	106	Salvatore Mereu	0.011005199	16	0.02805733	10	Recent	Italian
7	25221	The Lizzie McGuire Movie	2003	Comedy	United States	4.3	37	93	Jim Fall	0.005805893	16	0.05072685	27	Recent	English
8	25234	Betty Fisher et autres histoires	2001	Drama	France, Canada	6.5	4	103	Claude Miller	0.006499133	48	0.02734573	30	Recent	French
9	25246	Rosamunde Pilcher - Mit den Augen der Liebe	2002	Romantic	Germany	6.0	1	88	Richard Engel	8.6655e-05	8	0.00792925	52	Recent	German_North_Europe
10	25251	Maa Bhoomi	1979	Drama	India	8.0	1	136	Goutam Ghose	0.00355286	52	0.0	4	Old	Indian
11	25340	The Mummy's Hand	1940	Horror	United States	6.5	5	67	Christy Cabanne	0.000953206	16	0.05265833	22	Old	English
12	25351	Hurlements en faveur de Sade	1952	Sperimental	France	7.0	7	75	Guy Debord	0.002946274	16	[null]	[null]	Old	French
13	25381	Tutto Totò - Premio Nobel	1967	Comedy	Italy	5.8	10	47	Daniele D'Anza	0.009878683	72	0.72227305	161	Old	Italian
14	25412	Cantando dietro i paraventi	2003	Adventure	Italy	7.0	84	100	Ermanno Olmi	0.123743501	124	0.24113042	26	Recent	Italian
15	25417	Deathline	1997	Action	Canada, Holland	4.8	3	93	Tibor Takacs	0.011265165	76	0.08762835	47	90s	French
16	25420	Jason and the Argonauts	2000	Mythology	United States	5.9	5	175	Nick Willing	0.007365685	32	0.25871709	56	Recent	English
17	25421	Zong heng si hai	1990	Action	Hong Kong	6.6	27	103	John Woo	0.149913345	80	0.10135204	27	90s	Chinese_Japan_Asian
18	25430	Daddy Day Care	2003	Comedy	United States	4.4	66	92	Steve Carr, Steve Carr	0.005632582	4	0.36942158	58	Recent	English
19	25433	Sabrina, Down Under	1999	Fantasy	United States	6.0	4	120	Kenneth R. Koch	0.000259965	4	0.00762427	15	90s	English
20	25435	Razzia sur la Chnouf	1954	Crime	France	7.1	20	105	Henri Decoin	0.004592721	56	0.1596015	69	Old	French

We can do the same for the genres.

filmtv_movies_complete.case_when(
    'Category',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Drama|Noir')"), 'Drama',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Comedy|Grotesque')"), 'Comedy',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Fantasy|Super-hero')"), 'Fantasy',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Romantic|Sperimental|Mélo')"), 'Romantic',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Thriller|Crime|Gangster')"), 'Thriller',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Action|Western|War|Spy')"), 'Action',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Adventure')"), 'Adventure',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Animation')"), 'Animation',
    vo.StringSQL("REGEXP_LIKE(Genre, 'Horror')"), 'Horror',
    'Others'
)

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 99%	Abc genre Varchar(50) 99%	Abc country Varchar(154) 99%	123 avg_vote Double 100%	123 votes Integer 100%	123 duration Integer 100%	Abc director Varchar(632) 99%	123 notoriety_director Decimal(30,9) 99%	123 castings_director Bigint 99%	123 notoriety_actors Decimal(38,8) 94%	123 castings_actors Bigint 94%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%	Abc Category Varchar(9) 100%
1	34799	Appetite	1998	Horror	Great Britain	4.2	9	97	George Milton	0.000693241	4	0.00843753	12	90s	English	Horror
2	34838	Deceit	2004	Drama	United States	5.0	3	90	John Sacret Young	0.00034662	12	0.0094541	21	Recent	English	Drama
3	34842	Epoch	2000	Fantasy	United States	4.0	4	93	Matt Codd	0.000779896	8	0.03862966	29	Recent	English	Fantasy
4	34847	Belle toujours	2006	Comedy	Portugal, France	6.9	45	68	Manoel de Oliveira	0.055199307	132	0.1631595	54	Recent	Spanish_Portuguese	Comedy
5	34934	A Good Woman	2004	Comedy	Spain, Italy, Great Britain, Luxembourg, United States	5.9	27	93	Mike Barker	0.009878683	28	0.78275897	63	Recent	Spanish_Portuguese	Comedy
6	35013	The Bribe	1949	Noir	United States	6.0	1	98	Robert Z. Leonard	0.01117851	120	0.26725627	117	Old	English	Drama
7	35024	The Virginian	1929	Western	United States	6.5	2	91	Victor Fleming	0.058925477	60	0.19111518	78	Old	English	Action
8	35106	Hell Is a City	1960	Thriller	Great Britain	4.0	1	88	Val Guest	0.012218371	72	0.47046864	103	Old	English	Thriller
9	35183	Vice Squad	1982	Crime	United States	5.8	4	97	Gary Sherman	0.006845754	28	0.00670936	12	Old	English	Thriller
10	35184	Tourist Trap	1979	Horror	United States	7.5	13	90	David Schmoeller	0.003032929	20	0.02714242	18	Old	English	Horror
11	35187	Haunted	1995	Horror	United States, Great Britain	4.9	7	108	Lewis Gilbert	0.043154246	84	0.28321642	59	90s	English	Horror
12	35231	The Spoilers	1930	Adventure	United States	6.0	1	86	Rex Beach	0.0	4	0.16132967	62	Old	English	Adventure
13	4887	Right to Kill?	1985	Drama	United States	6.0	1	100	John Erman	0.002253033	48	0.00935244	11	Old	English	Drama
14	4911	Diamond Skull	1989	Drama	Great Britain	6.0	1	84	Nick Broomfield	0.000259965	8	0.04015452	15	Old	English	Drama
15	4923	Orders Are Orders	1954	Comedy	Great Britain	6.0	1	78	David Paltenghi	0.0	4	0.17911965	33	Old	English	Comedy
16	4943	Caribbean Gold	1952	Adventure	United States	8.0	2	94	Edward Ludwig	0.00407279	48	0.05845278	64	Old	English	Adventure
17	4958	Oscar	1991	Comedy	United States	5.5	70	110	John Landis	0.255372617	80	0.85524042	111	90s	English	Comedy
18	4959	Young Doctors in Love	1982	Comedy	United States	4.5	16	95	Garry Marshall	0.076516464	68	0.33109687	82	Old	English	Comedy
19	4963	Banco à Bangkok pour Oss 117	1963	Spy	France	5.8	4	113	André Hunebelle	0.009098787	56	0.01728169	14	Old	French	Action
20	4974	The Osterman Weekend	1983	Thriller	United States	7.2	84	101	Sam Peckinpah	0.152599653	56	0.79882078	162	Old	English	Thriller

Since we’re more concerned with the Category at this point, we can drop genre.

filmtv_movies_complete.drop(columns = ["genre"])

Let’s look at the missing values.

filmtv_movies_complete.count_percent()

	count	percent
"filmtv_id"	53497.0	100.0
"title"	53497.0	100.0
"avg_vote"	53497.0	100.0
"votes"	53497.0	100.0
"duration"	53497.0	100.0
"period"	53497.0	100.0
"language_area"	53497.0	100.0
"Category"	53497.0	100.0
"year"	53485.0	99.978
"country"	53446.0	99.905
"director"	53433.0	99.88
"notoriety_director"	53433.0	99.88
"castings_director"	53433.0	99.88
"notoriety_actors"	50462.0	94.327
"castings_actors"	50462.0	94.327

Let’s impute the missing values for notoriety_actors and castings_actors using different techniques.

We can then drop the few remaining missing values.

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	123 avg_vote Double 100%	123 votes Integer 100%	123 duration Integer 100%	Abc director Varchar(632) 100%	123 notoriety_director Decimal(30,9) 100%	123 castings_director Bigint 100%	123 notoriety_actors Real 100%	123 castings_actors Real 100%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%	Abc Category Varchar(9) 100%
1	83903	Uncle Howard	2016	Great Britain, United States, Czech Republic, France, Germany	5.0	4	96	Aaron Brookner	0.000259965	4	0.08427366	21.0	Recent	English	Others
2	17196	City Limits	1985	United States	4.0	1	90	Aaron Lipstadt	0.001559792	16	0.196503	55.0	Old	English	Action
3	22704	Ten	2002	Iran, France	6.8	22	94	Abbas Kiarostami	0.034228769	64	0.0085392	4.0	Recent	Arabic_Middle_Est	Comedy
4	13720	Zire derakhatan zeyton	1994	Iran	8.0	25	103	Abbas Kiarostami	0.034228769	64	0.00731931	3.0	90s	Arabic_Middle_Est	Comedy
5	14151	Zendegi edame darad	1992	Iran	8.5	28	91	Abbas Kiarostami	0.034228769	64	0.01097896	4.0	90s	Arabic_Middle_Est	Comedy
6	42948	Vénus noire	2010	France	6.7	79	159	Abdellatif Kechiche	0.067590988	28	0.14201484	33.0	Recent	French	Others
7	73239	Yeti	2014	India	8.0	1	62	Abhijit Mazumdar	0.0	4	0.0	4.0	Recent	Indian	Drama
8	50405	Dabangg	2010	India	6.3	7	126	Abhinav Kashyap	0.000519931	4	0.00365965	10.0	Recent	Indian	Action
9	76112	Standoff	2015	United States	5.6	13	86	Adam Alleca	0.001039861	4	0.1125343	34.0	Recent	English	Thriller
10	85493	The Arbalest	2016	United States	6.0	2	73	Adam Pinney	8.6655e-05	4	0.0028464	6.0	Recent	English	Drama
11	137776	iBoy	2017	Great Britain	5.1	19	91	Adam Randall	0.001559792	4	0.19406324	37.0	Recent	English	Action
12	69139	The Nutt House	1992	United States	1.0	1	94	Adam Rifkin	0.005112652	28	0.06241741	19.0	90s	English	Comedy
13	20170	Detroit Rock City	1997	United States	5.6	27	94	Adam Rifkin	0.005112652	28	0.05286164	17.0	90s	English	Comedy
14	14252	The Chase	1994	United States	4.3	24	88	Adam Rifkin	0.005112652	28	0.11477077	48.0	90s	English	Comedy
15	39950	Homo Erectus	2007	United States	4.5	3	88	Adam Rifkin	0.005112652	28	0.29348378	42.0	Recent	English	Comedy
16	26677	Carnosaur	1993	United States	3.9	8	83	Adam Simon	0.002512998	12	0.0108773	16.0	90s	English	Horror
17	4200	The Kid from Left Field	1979	United States	6.0	1	92	Adell Aldrich	0.000259965	8	0.00589611	9.0	Old	English	Comedy
18	86411	My Name is Adil	2016	Italy, Morocco	7.5	2	74	Adil Azzab, Magda Rezene, Andrea Pellizzer	8.6655e-05	4	0.00040664	4.0	Recent	Arabic_Middle_Est	Drama
19	42618	Merlín	1991	Spain	5.7	5	58	Adolfo Arrieta	0.001993068	24	0.00213481	6.0	90s	Spanish_Portuguese	Fantasy
20	38001	Via del corso	2000	Italy	2.5	13	96	Adolfo Lippi	0.001039861	4	0.06038426	14.0	Recent	Italian	Comedy

Before we export the data, we should normalize the numerical columns to get the dummies of the different categories.

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].one_hot_encode(drop_first = True)

We can export the results to our VAST DataBase.

filmtv_movies_complete.to_db(
    name = "filmtv_movies_complete",
    relation_type = "table",
    inplace = True,
)
filmtv_movies_complete.to_db(
    name = "filmtv_movies_mco",
    relation_type = "view",
    db_filter = "votes > 0.02",
)

Machine Learning : Adjusting the Films Rates

Let’s create a model to evaluate an unbiased score for each different movie.

from vastorbit.machine_learning.vast.linear_model import LinearRegression

predictors = filmtv_movies_complete.get_columns(
    exclude_columns = [
        "avg_vote",
        "period",
        "director",
        "language_area",
        "title",
        "year",
        "country",
        "Category",
    ],
)
model = LinearRegression()
model.fit("filmtv_movies_mco", predictors, "avg_vote")

model.report()

	value
explained_variance	0.4648606660056842
max_error	4.907322219438494
median_absolute_error	0.6189516265121349
mean_absolute_error	0.7159402797457688
mean_squared_error	6.55535105679041
root_mean_squared_error	0.912400705676939
r2	0.4648606660056843
r2_adj	0.4633564457201521
aic	19515.629242198807
bic	19732.608010569173

The model is good. Let’s add it in our VastFrame.

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	123 avg_vote Double 100%	123 votes Decimal(20,8) 100%	123 duration Decimal(20,8) 100%	Abc director Varchar(632) 100%	123 notoriety_director Decimal(36,13) 100%	123 castings_director Decimal(28,7) 100%	123 notoriety_actors Decimal(38,6) 100%	123 castings_actors Double 100%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%	Abc category Varchar(9) 100%	123 category_action Integer 100%	123 category_adventure Integer 100%	123 category_animation Integer 100%	123 category_comedy Integer 100%	123 category_drama Integer 100%	123 category_fantasy Integer 100%	123 category_horror Integer 100%	123 category_others Integer 100%	123 category_romantic Integer 100%	123 period_90s Integer 100%	123 period_old Integer 100%	123 language_area_arabic_middle_est Integer 100%	123 language_area_chinese_japan_asian Integer 100%	123 language_area_english Integer 100%	123 language_area_french Integer 100%	123 language_area_german_north_europe Integer 100%	123 language_area_grec_balkan Integer 100%	123 language_area_hebrew Integer 100%	123 language_area_indian Integer 100%	123 language_area_italian Integer 100%	123 language_area_others Integer 100%	123 language_area_russian_est_europe Integer 100%	123 unbiased_vote Double 100%
1	56292	Thuppakki	2012	India	8.0	0.0	0.02099237	A.R. Murugadoss	8.6655e-05	0.0138889	4.6e-05	0.015037593984962405	Recent	Indian	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	5.772842753487896
2	63738	Ghajini	2008	India	8.0	0.0	0.02729008	A.R. Murugadoss	8.6655e-05	0.0138889	0.001048	0.03007518796992481	Recent	Indian	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	5.869764070990654
3	128522	3-D Rarities	2015	United States	7.0	0.0	0.02041985	AA.VV.	0.002339688	0.1388889	0.013166	0.10526315789473684	Recent	English	Others	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	6.27590764358197
4	134196	Il racconto del reale - L'ultimo stadio	2016	Italy	6.0	0.0	0.0019084	AA.VV.	0.002339688	0.1388889	0.013166	0.10526315789473684	Recent	Italian	Others	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	5.450112055237488
5	37966	Paris, je t'aime	2006	France, Liechtenstein	7.2	0.00982801	0.01526718	AA.VV.	0.002339688	0.1388889	0.258303	0.33458646616541354	Recent	French	Romantic	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	5.854306366077978
6	76066	Urge	2016	United States	3.2	0.01556102	0.00935115	Aaron Kaufman	0.001646447	0.0	0.134709	0.14285714285714285	Recent	English	Thriller	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	5.435518318981543
7	78243	Walker, Texas Ranger: The Final Showdown	2001	United States	5.5	0.000819	0.00954198	Aaron Norris	0.008838821	0.1388889	0.022869	0.2781954887218045	Recent	English	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	5.309695603219519
8	77190	Walker, Texas Ranger: Sons of Thunder	1997	United States	5.5	0.000819	0.01087786	Aaron Norris	0.008838821	0.1388889	0.021685	0.2518796992481203	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.8431005860282
9	47566	Walker, Texas Ranger: Trial by Fire	2005	United States	6.0	0.002457	0.01526718	Aaron Norris	0.008838821	0.1388889	0.022869	0.2781954887218045	Recent	English	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	5.376466610095055
10	10345	Delta Force 2: The Colombian Connection	1990	United States	4.6	0.01474201	0.01335878	Aaron Norris	0.008838821	0.1388889	0.047333	0.2593984962406015	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.873247683652636
11	11387	Hellbound	1993	United States	3.7	0.01638002	0.01145038	Aaron Norris	0.008838821	0.1388889	0.022641	0.22180451127819548	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.880430908303579
12	1644	Platoon Leader	1988	United States	4.9	0.004914	0.01145038	Aaron Norris	0.008838821	0.1388889	0.007426	0.10526315789473684	Old	English	Action	1	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	6.564184814945444
13	51383	Sidekicks	1992	United States	6.0	0.000819	0.01164122	Aaron Norris	0.008838821	0.1388889	0.043506	0.34962406015037595	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.816466999368496
14	4882	On the Edge - The Hitman	1991	United States	5.1	0.00900901	0.01049618	Aaron Norris	0.008838821	0.1388889	0.035944	0.2744360902255639	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.809980836604728
15	23470	Top Dog	1995	United States	5.2	0.004914	0.00877863	Aaron Norris	0.008838821	0.1388889	0.019361	0.17293233082706766	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.788219840155414
16	15924	Forest Warrior	1996	United States	5.3	0.004914	0.0101145	Aaron Norris	0.008838821	0.1388889	0.026605	0.24436090225563908	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	5.798945511507094
17	33320	Date Movie	2006	United States	4.1	0.05978706	0.00820611	Aaron Seltzer	0.006325823	0.0	0.0246	0.03007518796992481	Recent	English	Comedy	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	5.587598422774075
18	61969	L’Armée du salut	2013	France, Morocco	7.0	0.001638	0.00839695	Abdellah Taïa	0.00017331	0.0	0.000273	0.007518796992481203	Recent	Arabic_Middle_Est	Drama	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	6.4490471028719005
19	4856	Cat Chaser	1989	United States	5.5	0.01638002	0.00877863	Abel Ferrara	0.137868284	0.3472222	0.192201	0.4398496240601504	Old	English	Action	1	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	6.449600447028341
20	47425	Fear City	1984	United States	5.1	0.00982801	0.01068702	Abel Ferrara	0.137868284	0.3472222	0.118947	0.3533834586466165	Old	English	Others	0	0	0	0	0	0	0	1	0	0	1	0	0	1	0	0	0	0	0	0	0	0	7.32730788962519

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

filmtv_movies_complete["unbiased_vote"] = fun.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)

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	123 avg_vote Double 100%	123 unbiased_vote Double 100%	123 votes Decimal(20,8) 100%	123 duration Decimal(20,8) 100%	Abc director Varchar(632) 100%	123 notoriety_director Decimal(36,13) 100%	123 castings_director Decimal(28,7) 100%	123 notoriety_actors Decimal(38,6) 100%	123 castings_actors Double 100%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%
1	5580	Psycho	1960	United States	9.3	10.0	0.64537265	0.0129771	Alfred Hitchcock	0.753379549	0.7083333	0.245501	0.33458646616541354	Old	English
2	27984	Die Zweite Heimat - Chronik einer Jugend	1992	Germany	9.3	10.0	0.01392301	0.28339695	Edgar Reitz	0.041074523	0.5416667	0.019179	0.20300751879699247	90s	German_North_Europe
3	17065	A Clockwork Orange	1971	Great Britain	9.2	10.0	0.88943489	0.01851145	Stanley Kubrick	0.649913345	0.1527778	0.254658	0.15037593984962405	Old	English
4	1963	2001: A Space Odyssey	1968	Great Britain	9.1	10.0	0.80671581	0.01927481	Stanley Kubrick	0.649913345	0.1527778	0.138809	0.041353383458646614	Old	English
5	4991	The Godfather	1972	United States	9.1	10.0	0.68468468	0.02633588	Francis Ford Coppola	0.434228769	0.3472222	0.566671	0.4924812030075188	Old	English
6	6476	The Shining	1980	United States, Great Britain	9.1	10.0	0.95577396	0.01507634	Stanley Kubrick	0.649913345	0.1527778	0.41579	0.16917293233082706	Old	English
7	7025	Taxi Driver	1976	United States	9.1	10.0	0.70925471	0.01335878	Martin Scorsese	0.829289428	0.4722222	0.832809	0.5864661654135338	Old	English
8	868	Barry Lyndon	1975	Great Britain, United States	9.0	10.0	0.48976249	0.02748092	Stanley Kubrick	0.649913345	0.1527778	0.192246	0.17669172932330826	Old	English
9	27983	Heimat - Eine Chronik in elf Teilen	1984	Germany	9.0	10.0	0.03357903	0.16870229	Edgar Reitz	0.041074523	0.5416667	0.011981	0.041353383458646614	Old	German_North_Europe
10	1152	Once Upon a Time in America	1984	United States	9.0	10.0	0.66502867	0.03435115	Sergio Leone	0.290467938	0.0833333	0.667304	0.42105263157894735	Old	English

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

Machine Learning : Creating Movie Clusters

Since KMeans clustering is sensitive to unnormalized data, let’s normalize our new predictors.

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	123 avg_vote Double 100%	123 votes Decimal(20,8) 100%	123 duration Decimal(20,8) 100%	Abc director Varchar(632) 100%	123 notoriety_director Decimal(36,13) 100%	123 castings_director Decimal(28,7) 100%	123 notoriety_actors Decimal(38,6) 100%	123 castings_actors Double 100%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%	Abc category Varchar(9) 100%	123 category_action Integer 100%	123 category_adventure Integer 100%	123 category_animation Integer 100%	123 category_comedy Integer 100%	123 category_drama Integer 100%	123 category_fantasy Integer 100%	123 category_horror Integer 100%	123 category_others Integer 100%	123 category_romantic Integer 100%	123 period_90s Integer 100%	123 period_old Integer 100%	123 language_area_arabic_middle_est Integer 100%	123 language_area_chinese_japan_asian Integer 100%	123 language_area_english Integer 100%	123 language_area_french Integer 100%	123 language_area_german_north_europe Integer 100%	123 language_area_grec_balkan Integer 100%	123 language_area_hebrew Integer 100%	123 language_area_indian Integer 100%	123 language_area_italian Integer 100%	123 language_area_others Integer 100%	123 language_area_russian_est_europe Integer 100%	123 unbiased_vote Real 100%
1	56292	Thuppakki	2012	India	8.0	0.0	0.02099237	A.R. Murugadoss	8.6655e-05	0.0138889	4.6e-05	0.015037593984962405	Recent	Indian	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0.2712328132171071
2	63738	Ghajini	2008	India	8.0	0.0	0.02729008	A.R. Murugadoss	8.6655e-05	0.0138889	0.001048	0.03007518796992481	Recent	Indian	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0.2879421693580591
3	128522	3-D Rarities	2015	United States	7.0	0.0	0.02041985	AA.VV.	0.002339688	0.1388889	0.013166	0.10526315789473684	Recent	English	Others	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.3579618283313675
4	134196	Il racconto del reale - L'ultimo stadio	2016	Italy	6.0	0.0	0.0019084	AA.VV.	0.002339688	0.1388889	0.013166	0.10526315789473684	Recent	Italian	Others	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0.21559363791874508
5	37966	Paris, je t'aime	2006	France, Liechtenstein	7.2	0.00982801	0.01526718	AA.VV.	0.002339688	0.1388889	0.258303	0.33458646616541354	Recent	French	Romantic	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0.2852772417326138
6	76066	Urge	2016	United States	3.2	0.01556102	0.00935115	Aaron Kaufman	0.001646447	0.0	0.134709	0.14285714285714285	Recent	English	Thriller	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.2130776595683602
7	78243	Walker, Texas Ranger: The Final Showdown	2001	United States	5.5	0.000819	0.00954198	Aaron Norris	0.008838821	0.1388889	0.022869	0.2781954887218045	Recent	English	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.1913856662849467
8	77190	Walker, Texas Ranger: Sons of Thunder	1997	United States	5.5	0.000819	0.01087786	Aaron Norris	0.008838821	0.1388889	0.021685	0.2518796992481203	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2833453512619381
9	47566	Walker, Texas Ranger: Trial by Fire	2005	United States	6.0	0.002457	0.01526718	Aaron Norris	0.008838821	0.1388889	0.022869	0.2781954887218045	Recent	English	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.20289707123197015
10	10345	Delta Force 2: The Colombian Connection	1990	United States	4.6	0.01474201	0.01335878	Aaron Norris	0.008838821	0.1388889	0.047333	0.2593984962406015	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2885427485301749
11	11387	Hellbound	1993	United States	3.7	0.01638002	0.01145038	Aaron Norris	0.008838821	0.1388889	0.022641	0.22180451127819548	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2897811454280468
12	1644	Platoon Leader	1988	United States	4.9	0.004914	0.01145038	Aaron Norris	0.008838821	0.1388889	0.007426	0.10526315789473684	Old	English	Action	1	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0.40766117258018497
13	51383	Sidekicks	1992	United States	6.0	0.000819	0.01164122	Aaron Norris	0.008838821	0.1388889	0.043506	0.34962406015037595	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.27875368767053843
14	4882	On the Edge - The Hitman	1991	United States	5.1	0.00900901	0.01049618	Aaron Norris	0.008838821	0.1388889	0.035944	0.2744360902255639	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.27763546511227727
15	23470	Top Dog	1995	United States	5.2	0.004914	0.00877863	Aaron Norris	0.008838821	0.1388889	0.019361	0.17293233082706766	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2738838421564376
16	15924	Forest Warrior	1996	United States	5.3	0.004914	0.0101145	Aaron Norris	0.008838821	0.1388889	0.026605	0.24436090225563908	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.27573296128815933
17	33320	Date Movie	2006	United States	4.1	0.05978706	0.00820611	Aaron Seltzer	0.006325823	0.0	0.0246	0.03007518796992481	Recent	English	Comedy	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.23929645932982702
18	61969	L’Armée du salut	2013	France, Morocco	7.0	0.001638	0.00839695	Abdellah Taïa	0.00017331	0.0	0.000273	0.007518796992481203	Recent	Arabic_Middle_Est	Drama	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0.3878112872725852
19	4856	Cat Chaser	1989	United States	5.5	0.01638002	0.00877863	Abel Ferrara	0.137868284	0.3472222	0.192201	0.4398496240601504	Old	English	Action	1	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0.38790668449598975
20	47425	Fear City	1984	United States	5.1	0.00982801	0.01068702	Abel Ferrara	0.137868284	0.3472222	0.118947	0.3533834586466165	Old	English	Others	0	0	0	0	0	0	0	1	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0.5392245433921767

Let’s compute the elbow() curve to find a suitable number of clusters.

predictors = filmtv_movies_complete.get_columns(
    exclude_columns = [
        "avg_vote",
        "period",
        "director",
        "language_area",
        "title",
        "year",
        "country",
        "Category",
        "filmtv_id",
    ],
)

from vastorbit.machine_learning.model_selection import elbow

elbow_chart = elbow(
    filmtv_movies_complete,
    predictors,
    n_clusters = (1, 60),
    show = True
)

elbow_chart

By looking at the elbow curve, we can choose 4 clusters. Let’s create a KMeans model.

from vastorbit.machine_learning.vast.cluster import KMeans

model_kmeans = KMeans(n_clusters = 4)
model_kmeans.fit(filmtv_movies_complete, predictors)
model_kmeans.clusters_

Let’s add the clusters in the VastFrame.

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

	123 filmtv_id Integer 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	123 avg_vote Double 100%	123 votes Decimal(20,8) 100%	123 duration Decimal(20,8) 100%	Abc director Varchar(632) 100%	123 notoriety_director Decimal(36,13) 100%	123 castings_director Decimal(28,7) 100%	123 notoriety_actors Decimal(38,6) 100%	123 castings_actors Double 100%	Abc period Varchar(6) 100%	Abc language_area Varchar(19) 100%	Abc category Varchar(9) 100%	123 category_action Integer 100%	123 category_adventure Integer 100%	123 category_animation Integer 100%	123 category_comedy Integer 100%	123 category_drama Integer 100%	123 category_fantasy Integer 100%	123 category_horror Integer 100%	123 category_others Integer 100%	123 category_romantic Integer 100%	123 period_90s Integer 100%	123 period_old Integer 100%	123 language_area_arabic_middle_est Integer 100%	123 language_area_chinese_japan_asian Integer 100%	123 language_area_english Integer 100%	123 language_area_french Integer 100%	123 language_area_german_north_europe Integer 100%	123 language_area_grec_balkan Integer 100%	123 language_area_hebrew Integer 100%	123 language_area_indian Integer 100%	123 language_area_italian Integer 100%	123 language_area_others Integer 100%	123 language_area_russian_est_europe Integer 100%	123 unbiased_vote Real 100%	123 movies_cluster Integer 100%
1	56292	Thuppakki	2012	India	8.0	0.0	0.02099237	A.R. Murugadoss	8.6655e-05	0.0138889	4.6e-05	0.015037593984962405	Recent	Indian	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0.2712328132171071	1
2	63738	Ghajini	2008	India	8.0	0.0	0.02729008	A.R. Murugadoss	8.6655e-05	0.0138889	0.001048	0.03007518796992481	Recent	Indian	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0.2879421693580591	1
3	128522	3-D Rarities	2015	United States	7.0	0.0	0.02041985	AA.VV.	0.002339688	0.1388889	0.013166	0.10526315789473684	Recent	English	Others	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.3579618283313675	2
4	134196	Il racconto del reale - L'ultimo stadio	2016	Italy	6.0	0.0	0.0019084	AA.VV.	0.002339688	0.1388889	0.013166	0.10526315789473684	Recent	Italian	Others	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0.21559363791874508	1
5	37966	Paris, je t'aime	2006	France, Liechtenstein	7.2	0.00982801	0.01526718	AA.VV.	0.002339688	0.1388889	0.258303	0.33458646616541354	Recent	French	Romantic	0	0	0	0	0	0	0	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0.2852772417326138	1
6	76066	Urge	2016	United States	3.2	0.01556102	0.00935115	Aaron Kaufman	0.001646447	0.0	0.134709	0.14285714285714285	Recent	English	Thriller	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.2130776595683602	2
7	78243	Walker, Texas Ranger: The Final Showdown	2001	United States	5.5	0.000819	0.00954198	Aaron Norris	0.008838821	0.1388889	0.022869	0.2781954887218045	Recent	English	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.1913856662849467	2
8	77190	Walker, Texas Ranger: Sons of Thunder	1997	United States	5.5	0.000819	0.01087786	Aaron Norris	0.008838821	0.1388889	0.021685	0.2518796992481203	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2833453512619381	2
9	47566	Walker, Texas Ranger: Trial by Fire	2005	United States	6.0	0.002457	0.01526718	Aaron Norris	0.008838821	0.1388889	0.022869	0.2781954887218045	Recent	English	Action	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.20289707123197015	2
10	10345	Delta Force 2: The Colombian Connection	1990	United States	4.6	0.01474201	0.01335878	Aaron Norris	0.008838821	0.1388889	0.047333	0.2593984962406015	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2885427485301749	2
11	11387	Hellbound	1993	United States	3.7	0.01638002	0.01145038	Aaron Norris	0.008838821	0.1388889	0.022641	0.22180451127819548	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2897811454280468	2
12	1644	Platoon Leader	1988	United States	4.9	0.004914	0.01145038	Aaron Norris	0.008838821	0.1388889	0.007426	0.10526315789473684	Old	English	Action	1	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0.40766117258018497	0
13	51383	Sidekicks	1992	United States	6.0	0.000819	0.01164122	Aaron Norris	0.008838821	0.1388889	0.043506	0.34962406015037595	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.27875368767053843	2
14	4882	On the Edge - The Hitman	1991	United States	5.1	0.00900901	0.01049618	Aaron Norris	0.008838821	0.1388889	0.035944	0.2744360902255639	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.27763546511227727	2
15	23470	Top Dog	1995	United States	5.2	0.004914	0.00877863	Aaron Norris	0.008838821	0.1388889	0.019361	0.17293233082706766	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.2738838421564376	2
16	15924	Forest Warrior	1996	United States	5.3	0.004914	0.0101145	Aaron Norris	0.008838821	0.1388889	0.026605	0.24436090225563908	90s	English	Action	1	0	0	0	0	0	0	0	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0.27573296128815933	2
17	33320	Date Movie	2006	United States	4.1	0.05978706	0.00820611	Aaron Seltzer	0.006325823	0.0	0.0246	0.03007518796992481	Recent	English	Comedy	0	0	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0.23929645932982702	2
18	61969	L’Armée du salut	2013	France, Morocco	7.0	0.001638	0.00839695	Abdellah Taïa	0.00017331	0.0	0.000273	0.007518796992481203	Recent	Arabic_Middle_Est	Drama	0	0	0	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0.3878112872725852	1
19	4856	Cat Chaser	1989	United States	5.5	0.01638002	0.00877863	Abel Ferrara	0.137868284	0.3472222	0.192201	0.4398496240601504	Old	English	Action	1	0	0	0	0	0	0	0	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0.38790668449598975	0
20	47425	Fear City	1984	United States	5.1	0.00982801	0.01068702	Abel Ferrara	0.137868284	0.3472222	0.118947	0.3533834586466165	Old	English	Others	0	0	0	0	0	0	0	1	0	0	1	0	0	1	0	0	0	0	0	0	0	0	0.5392245433921767	0

Let’s look at the different clusters.

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

	123 avg_vote Double 100%	Abc period Varchar(6) 100%	Abc director Varchar(632) 100%	Abc language_area Varchar(19) 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	Abc category Varchar(9) 100%
1	7.2	Old	Aaron Lipstadt	English	Android	1982	United States	Fantasy
2	3.5	Old	Aaron Lipstadt	English	City Limits	1985	United States	Fantasy
3	9.4	Old	Akira Kurosawa	Chinese_Japan_Asian	Shichi-nin no Samurai	1954	Japan	Adventure
4	8.4	Old	Akira Kurosawa	Chinese_Japan_Asian	Dersu Uzala	1975	Soviet Union, Japan	Adventure
5	8.6	Old	Akira Kurosawa	Chinese_Japan_Asian	Yojimbo	1961	Japan	Adventure
6	8.3	Old	Akira Kurosawa	Chinese_Japan_Asian	Kakushi toride no san-akunin	1958	Japan	Adventure
7	7.7	Old	Akira Kurosawa	Chinese_Japan_Asian	Tsubaki Sanjuro	1962	Japan	Adventure
8	4.2	Old	Al Bagran (Alfonso Balcázar)	Spanish_Portuguese	Con la muerte a la espalda	1967	Spain, France, Italy	Action
9	4.5	Old	Al Bagran (Alfonso Balcázar)	Spanish_Portuguese	I bandoleros della dodicesima ora	1973	Spain, Italy	Action
10	6.1	Old	Alan Crosland	English	The Jazz Singer	1927	United States	Others
11	5.2	Old	Alan Johnson	English	Solarbabies	1986	United States	Fantasy
12	5.0	Old	Alan Le May	English	High Lonesome	1950	United States	Action
13	6.8	Old	Alexander Hall	English	My Sister Eileen	1942	United States	Comedy
14	6.0	Old	Alexander Hall	English	Goin' to Town	1935	United States	Comedy
15	6.0	Old	Alexander Hall	English	Down to Earth	1947	United States	Comedy
16	6.0	Old	Alexander Hall	English	Together Again	1953	United States	Comedy
17	6.0	Old	Alexander Hall	English	Good Girls Go to Paris	1939	United States	Comedy
18	5.5	Old	Alexander Hall	English	Because You're Mine	1952	United States	Comedy
19	6.0	Old	Alexander Hall	English	The Heavenly Body	1943	United States	Comedy
20	6.0	Old	Alexander Hall	English	Forever Darling	1956	United States	Comedy

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

	123 avg_vote Double 100%	Abc period Varchar(6) 100%	Abc director Varchar(632) 100%	Abc language_area Varchar(19) 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	Abc category Varchar(9) 100%
1	8.0	90s	Abdoulaye Ascofare	Others	Faraw! Une mere des sables	1997	Mali	Drama
2	5.6	Recent	Adam Brooks, Matthew Kennedy	French	The Editor	2014	Canada	Comedy
3	3.4	Recent	Adam Massey	French	The Intruders	2015	Canada	Thriller
4	4.0	Recent	Adam Weissman	French	Life in the Balance	2001	Canada	Thriller
5	6.2	Old	Aglauco Casadio	Italian	Un ettaro di cielo	1959	Italy	Comedy
6	7.0	Recent	Agustin Toscano	Spanish_Portuguese	El Motoarrebatador	2018	Argentina, Uruguay	Drama
7	2.0	Recent	Agustina Macri	Spanish_Portuguese	Soledad	2018	Argentina, Italy	Drama
8	7.5	Recent	Aitor Merino, Amaia Merino	Spanish_Portuguese	Asier ETA biok	2013	Spain, Ecuador	Others
9	6.0	Recent	Akira Ogata	Chinese_Japan_Asian	Dokuritsu shonen gasshoudan	2000	Japan	Drama
10	5.0	Recent	Akiz	German_North_Europe	Der Nachtmahr	2015	Germany	Drama
11	6.0	Recent	Aktan Arym Kubat	Arabic_Middle_Est	Maimil	2002	France, Kyrgyzstan, Japan	Others
12	6.0	Recent	Alanté Kavaïté	French	Sangaïlé	2014	France, Lithuania	Drama
13	5.0	90s	Albert Pyun	Chinese_Japan_Asian	Heatseeker	1995	United States, Philippines	Fantasy
14	7.5	90s	Alberto Bader	Italian	A casa di Irma	1999	Italy	Comedy
15	1.5	Old	Alberto Cavallone	Italian	Il padrone del mondo	1983	Italy	Drama
16	7.4	Old	Alberto Cavallone	Italian	Spell - Dolce mattatoio	1977	Italy	Drama
17	3.0	Old	Alberto Cavallone	Italian	Zelda	1974	Italy	Drama
18	6.7	Old	Alberto Cavallone	Italian	L'uomo, la donna e la bestia - Spell (Dolce mattatoio)	1977	Italy	Drama
19	6.5	Old	Alberto Cavallone	Italian	Blue Movie	1978	Italy	Thriller
20	7.2	Recent	Alberto Fasulo	Italian	Menocchio	2018	Italy, Romania	Drama

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

	123 avg_vote Double 100%	Abc period Varchar(6) 100%	Abc director Varchar(632) 100%	Abc language_area Varchar(19) 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	Abc category Varchar(9) 100%
1	7.0	Recent	AJ Schnack	English	Kurt Cobain About a Son	2006	United States	Others
2	6.0	Recent	Aaron Katz	English	Gemini	2017	United States	Thriller
3	5.5	90s	Abel Ferrara	English	New Rose Hotel	1998	United States	Thriller
4	6.0	Recent	Adam Ciancio	English	Vessel	2013	Australia	Fantasy
5	4.0	Recent	Adam Collis	English	Sunset Strip	2000	United States	Comedy
6	5.8	Recent	Adam Weissman	English	Henry Danger: One Henry, Three Girls: Part 1 & 2	2015	United States	Comedy
7	4.8	Recent	Adam Weissman	English	Henry Danger: Live & Dangerous	2017	United States	Comedy
8	5.0	90s	Alan Roberts	English	Save Me	1993	United States	Thriller
9	5.0	Recent	Alex Brewer, Benjamin Brewer	English	The Trust	2016	Great Britain	Thriller
10	4.5	Recent	Alex Helfrecht, Jörg Tittel	English	The White King	2016	Great Britain	Adventure
11	5.3	Recent	Alex Richanbach	English	Ibiza	2018	United States	Comedy
12	6.0	90s	Alexander Ramati	English	And the Violins Stopped Playing	1995	United States	Drama
13	5.3	Recent	Alexandre Bustillo, Julien Maury	English	Leatherface	2017	United States	Horror
14	5.3	90s	Alfonso Arau	English	A Walk in the Clouds	1994	United States	Romantic
15	7.1	Recent	Alfonso Cuarón	English	Children of Men	2006	Great Britain, United States	Fantasy
16	6.8	Recent	Alfonso Cuarón	English	Gravity	2013	United States	Fantasy
17	7.2	Recent	Alfonso Cuarón	English	Harry Potter and the Prisoner of Azkaban	2004	United States	Fantasy
18	6.0	Recent	Alin Bijan	English	Bells of Innocence	2003	United States	Horror
19	5.4	90s	Allan Moyle	English	The Gun in Betty Lou's Handbag	1992	United States	Thriller
20	4.9	90s	Allen Hughes, Albert Hughes	English	Menace II Society	1994	United States	Drama

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

	123 avg_vote Double 100%	Abc period Varchar(6) 100%	Abc director Varchar(632) 100%	Abc language_area Varchar(19) 100%	Abc title Varchar(255) 100%	123 year Integer 100%	Abc country Varchar(154) 100%	Abc category Varchar(9) 100%
1	5.2	Recent	Adrian Lyne	English	Unfaithful	2002	United States	Drama
2	6.1	Old	Adrian Lyne	English	Foxes	1980	United States	Drama
3	5.4	Old	Adrian Lyne	English	Nine And Half Weeks	1986	United States	Drama
4	6.9	Old	Adrian Lyne	English	Fatal Attraction	1987	United States	Drama
5	6.8	Old	Alain Resnais	French	Muriel ou le temps d'un retour	1963	France	Drama
6	7.5	Old	Alain Resnais	French	Mélo	1986	France	Drama
7	7.9	Old	Alain Resnais	French	L'amour à mort	1984	France	Drama
8	7.2	Old	Alain Resnais	French	Je t'aime, je t'aime	1968	France	Drama
9	8.4	Old	Alain Resnais	French	Providence	1977	France, Switzerland	Drama
10	6.1	Old	Alain Resnais	French	Stavisky	1974	France	Drama
11	7.0	Old	Alain Resnais	French	La guerre est finie	1966	France	Drama
12	8.8	Old	Alain Resnais	Chinese_Japan_Asian	Hiroshima, mon amour	1959	France, Japan	Drama
13	7.9	Old	Alain Resnais	French	L'année dernière à Marienbad	1961	France	Drama
14	7.4	Old	Alan Parker	English	Mississippi Burning	1988	United States	Drama
15	6.6	Old	Alan Parker	English	Birdy	1984	United States	Drama
16	7.7	Old	Alan Parker	English	Midnight Express	1978	Great Britain	Drama
17	3.7	Old	Alan Parker	English	Shoot the Moon	1982	United States	Drama
18	7.5	Recent	Alan Parker	English	The Life of David Gale	2003	United States	Drama
19	7.0	Old	Aleksandr Mitta	Russian_Est_Europe	Ekipazh	1980	Soviet Union	Drama
20	7.2	Old	Aleksandr Sokurov	Russian_Est_Europe	Spasi i sochrani	1989	Soviet Union	Drama

Each cluster consists of similar movies. These clusters can be used to give movie recommendations or help streaming platforms group movies together.

Conclusion

We’ve solved our problem in a pandas-like way, all without ever loading data into memory!