<jupyter_start><jupyter_text>Cleaning DC vs. Marvel IMDB Data  Import libarries and load data<jupyter_code># Import libaries
import re

import pandas as pd
import numpy as np
# Load data
df = pd.read_csv(r'C:\Users\nguye\Documents\GitHub\spark_shared_repo\group_02\Marvel_DC_imdb.csv')

# Drop unnamed column
df = df.drop(columns=['Unnamed: 0'])

# Show un-cleaned data
df<jupyter_output><empty_output><jupyter_text>Clean and Format Data  Fix columns that should be numericals but are strings instead

For example, the column `"RunTime"` has the words "min" in it.<jupyter_code># Create function to turn strings to float
def str_to_float(cell, remove = ["M", "min", "$", ","]):
    if isinstance(cell, str):
        for r in remove:
            cell = cell.replace(r, "")
        return float(cell)
    else:
        return cell

# Convert to numerical columns
df['RunTime'] = df['RunTime'].apply(str_to_float)
df['USA_Gross'] = df['USA_Gross'].apply(str_to_float)
df['Votes'] = df['Votes'].apply(str_to_float)

# Show data
df<jupyter_output><empty_output><jupyter_text>Year Data

Year data is a string instead of numerical. We need to extract the start and end dates from the string.<jupyter_code># Get start and end years as int

def get_start_end_year(s):
    if isinstance(s, (float, int)):
        return s, np.nan
    s = s.replace("(", "")
    s = s.replace(")", "")
    years = re.findall(r'\d+', s)
    if len(years) == 1:
        return int(years[0]), np.nan
    elif len(years) == 2:
        return int(years[0]), int(years[1])

df[["Year_Start", "Year_End"]] = pd.DataFrame(df['Year'].apply(get_start_end_year).tolist(), index=df.index)<jupyter_output><empty_output><jupyter_text>Strip leading and trailing spaces from string columns<jupyter_code># Strip leading and trailing spaces 

str_columns = ['Movie', 'Genre', 'Director', 'Actor', 'Description',]

def strip_spaces(cell):
    if isinstance(cell, str):
        return cell.strip()
    else:
        return cell

for col in str_columns:
    df[col] = df[col].apply(strip_spaces)<jupyter_output><empty_output><jupyter_text>Get information about what type of film it is<jupyter_code># Get information it a series or movie (repeat instances)
df[["Year", "Rating"]] = df[["Year", "Rating"]].astype(str)
count_data = df.groupby(['Movie', 'Year'])['Year_Start'].count()

movie_or_series = []

for _, row in df.iterrows():
    movie = row['Movie']
    year = row['Year']

    if "Video Game" in row["Year"]:
        movie_or_series.append('Video Game')
    elif "TV Movie" in row["Year"]:
        movie_or_series.append('TV Movie')
    elif "Video" in row["Year"]:
        movie_or_series.append('Video')
    elif "TV Special" in row["Year"]:
        movie_or_series.append('TV Special')
    elif "TV Short" in row["Year"]:
        movie_or_series.append('Short')
    elif "Short" in row["Genre"] or row["RunTime"] < 10:
        movie_or_series.append('Short')
    elif "Documentary" in row["Genre"]:
        movie_or_series.append('Documentary')
    elif "TV" in row["Rating"]:
        movie_or_series.append('TV Show')
    elif not pd.isna(row["USA_Gross"]):
        movie_or_series.append('Movie')
    else:
        if count_data.loc[movie, year] == 1:
            movie_or_series.append('Direct-to-Video Movie')
        else:
            movie_or_series.append('TV Show')

df["Type"] = movie_or_series



# Get the counts of each type
df.Type.value_counts()<jupyter_output><empty_output><jupyter_text>Exclude data that does not meet the following conditions

- Drop rows missing data in the following rows `["Movie", "Year", "IMDB_Score", "Director"]`
- Remove rows where the type is `['TV Special', 'Video Game', 'Video']`<jupyter_code># Drop data missing in columns, movie, year, IMDB_Score 
df = df.dropna(subset=["Movie", "Year", "IMDB_Score", "Director"])
# Only include Movies and TV Shows
df = df.query("Type.isin(['Movie', 'TV Show'])", engine="python")
# Get the counts of each type
df.Type.value_counts()<jupyter_output><empty_output><jupyter_text>Rename Columns

Rename the "Movie" column "Name"<jupyter_code>df = df.rename(columns={"Movie": "Name"})
df = df.set_index('Name')

df<jupyter_output><empty_output><jupyter_text>Export Data<jupyter_code>df.to_csv(r"Marvel_DC_imdb_cleaned.csv")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14214123006833712, 'fraction_numerical': 0.0025056947608200456, 'mean_word_length': 3.97281993204983, 'pattern_counts': {'":': 1, '<': 34, '<?xml version=': 0, '>': 33, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '606458', 'license': 'MIT', 'n_tokens_mistral': 1549, 'n_tokens_neox': 1483, 'n_words': 375, 'path': 'group_02/CleanData.ipynb', 'repo_name': 'nguyenjenny/spark_shared_repo', 'size': '4390'}