Movies Scoring and Clustering¶

import verticapy as vp
vp.__version__

vp.connect("VerticaDSN")

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

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 = vp.read_csv("data/filmtv_movies.csv")
    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')

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')

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

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