<jupyter_start><jupyter_text>Data cleaning The main goals for cleaning are:- remove duplicate movies- make titles unique by including the year and adding last name of director when necessary- extract genres to csv for web app- extract unique titles to csv for web app<jupyter_code>import pandas as pd
from imdb import IMDb
import sys
import re
import csv
from collections import defaultdict<jupyter_output><empty_output><jupyter_text>First read in the movies file with utf-8 encoding (for special characters in titles)<jupyter_code>movies= pd.read_csv("movies_original.dat",delimiter="::",encoding='utf8', header=None, engine='python')<jupyter_output><empty_output><jupyter_text>Find all the genres in the dataset and drop the adult and nan categories. Save to a csv for the html select menu in the web app.<jupyter_code>genres = set([])
for line in set(movies[2]):
   for g in str(line).split("|"):
    genres.add(g)
genre_list = list(genres)
genre_list.sort()
genre_list.remove('Adult')
genre_list.remove('nan')
genre_df = pd.DataFrame(genre_list)
genre_df.to_csv('genres.csv', index=False, header=False)<jupyter_output><empty_output><jupyter_text>Find movies that have the same title and year. This is confusing because users won't be able to differentiate movies if only the title and year are given. For example, two movies called Chaos came out in 2005, so a user would see "Chaos (2005)" for both.<jupyter_code>movies_grouped = movies.groupby(1).count().sort_values(by=0, ascending=False)[[0]]
movies_grouped = movies_grouped.reset_index()
movies_grouped.columns = ["title","counts"]
duplicate_set = set(movies_grouped[movies_grouped["counts"]>1]["title"])<jupyter_output><empty_output><jupyter_text>Go through titles with same name and year, and query imdb api to get the first director, then add last name of director to titles to differentiate them for the user.<jupyter_code>duplicate_dict = defaultdict(list) 
error_dict = defaultdict(list)
imdb_object = IMDb()
for idx, row in movies.iterrows():
    if (row[1] in duplicate_set):
        the_movie = imdb_object.get_movie(row[0])
        try:
            
            director = the_movie['directors'][0]['name'].split(" ")[-1] #grab first director last name
            movies.iat[idx,1] = row[1] + " " + director
            duplicate_dict[row[1] + " " + director].append(row[0])
            #print(row[1] + " " + director)
        except:
            error_dict[row[1]].append(row[0])
            print("ERROR: " + " " + str(row[1]) + str(row[0]) + " " +  str(idx))<jupyter_output>ERROR:  Big Eyes (2014)4317898 28877<jupyter_text>Only 1 missing director error on the film big eyes -- looking at the IMDB page it is a documentary written by Keane (no director listed)<jupyter_code>movies.iat[28877,1] = movies.iloc[28877][1]+" Keane"<jupyter_output><empty_output><jupyter_text>Here I assume entries in the duplicate dict with more than 1 entry are true duplicates since they have: same year, title, director. Those with 1 entry in the dict have been differentiated.<jupyter_code>id_dict = {} #id_dict shows which ids need to be changed, old ids to new ids
for key in duplicate_dict:
    curr_values = duplicate_dict[key]
    if len(curr_values)>1:
        #several movies have duplicates on imdb. I'll grab the first id that's listed and use that for all entries
        id_dict[curr_values[1]] = curr_values[0]<jupyter_output><empty_output><jupyter_text>Now I delete duplicate rows<jupyter_code>for key in id_dict:
    movies = movies[movies[0]!=key]<jupyter_output><empty_output><jupyter_text>Now I read in ratings file<jupyter_code>ratings = pd.read_csv("ratings_original.dat",delimiter="::",header=None,engine='python')
ratings.columns = ["rater_id","movie_id","rating", "time"]
ratings = ratings[["rater_id","movie_id","rating"]]<jupyter_output><empty_output><jupyter_text>Use the previous dictionary to update duplicate imdb ids in ratings to match the movies dataframe.<jupyter_code>for idx, row in ratings.iterrows():
    if row[1] in id_dict:
        ratings.iat[idx,1] = id_dict[row[1]]<jupyter_output><empty_output><jupyter_text>Calculate sum and counts of ratings for each movie to get mean rating. Use a single for loop so it's more efficient (than looping for each movie).<jupyter_code>sum_dict = defaultdict(int)
counts_dict = defaultdict(int)
for idx, row in ratings.iterrows():
    sum_dict[row[1]] += row[2]
    counts_dict[row[1]] += 1<jupyter_output><empty_output><jupyter_text>Get mean ratings (sum/counts)<jupyter_code>avg_dict = defaultdict(list)
for key in sum_dict:
    avg = float(sum_dict[key])/float(counts_dict[key])
    avg_dict["id"].append(key)
    avg_dict["avg_rating"].append(round(avg,2))
    avg_dict["num_ratings"].append(counts_dict[key])<jupyter_output><empty_output><jupyter_text>Make df of ratings and number of raters for each movie<jupyter_code>ratings_df = pd.DataFrame(avg_dict)<jupyter_output><empty_output><jupyter_text>Inner join on the movies and ratings dfs<jupyter_code>movies.columns = ["id", "title", "genres"]
mov_full_df = pd.merge(ratings_df, movies, how='inner', on="id" )<jupyter_output><empty_output><jupyter_text>Add a year column by parsing each movie title. Also, drop NAs<jupyter_code>year_list = []
for idx, row in mov_full_df.iterrows():
    st = str(row['title'])
    year = int(st[st.find("(")+1:st.rfind(")")])
    year_list.append(year)
mov_full_df['year'] = year_list
mov_full_df = mov_full_df.dropna()<jupyter_output><empty_output><jupyter_text>Sort by year, then save movies and ratings to csv<jupyter_code>mov_full_df = mov_full_df.sort_values(by="year", ascending=False)

mov_full_df.to_csv("movies_clean.csv", index=False, quoting=csv.QUOTE_NONNUMERIC)
ratings.to_csv("ratings_clean.csv", index=False, quoting=csv.QUOTE_NONNUMERIC)<jupyter_output><empty_output><jupyter_text>Save movie titles for javascript autocomplete function in the web app<jupyter_code>mov_full_df[["title"]].to_csv("movie_titles.csv", index=False, header=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11838161838161838, 'fraction_numerical': 0.011988011988011988, 'mean_word_length': 6.0093348891481915, 'pattern_counts': {'":': 2, '<': 68, '<?xml version=': 0, '>': 70, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '647530', 'license': 'MIT', 'n_tokens_mistral': 2084, 'n_tokens_neox': 2012, 'n_words': 523, 'path': 'data/data_cleaning.ipynb', 'repo_name': 'iafinn/twitter-mov-rec', 'size': '6006'}