[SOLVED] CIS 5450 Homework 1 Data Wrangling and Cleaning Fall 2024 Python

CIS 5450 Homework 1: Data Wrangling and Cleaning (Fall 2024)

Hello future data scientists and welcome to CIS 5450! In this homework, you will familiarize yourself with Pandas and Polars! Both are cute animals and essential libraries for Data Science. This homework is focused on one of the most important tasks in Data Science, preparing datasets so that they can be analyzed, plotted, used for machine learning models, etc…

This homework will be broken into analyzing several datasets across four sections!

1. Working with Amazon Prime Video Data to understand the details behind its movies

2. Working on merged/joined versions of the datasets (more on this later though).

3. Regex

4. Working with Used Cars Dataset and Polars to see performance between Pandas, eager execution in Polars, and lazy execution in Polars.

IMPORTANT NOTE: Before starting, you must click on the “Copy To Drive” option in the top bar. This is the master notebook so you will not be able to save your changes without copying it ! Once you click on that, make sure you are working on that version of the notebook so that your work is saved

Run the following 4 cells to setup the notebook

%set_env HW_ID=cis5450_fall24_HW1

%%capture

!pip install penngrader-client

from penngrader.grader import *

import pandas as pd

import numpy as np

import seaborn as sns

from string import ascii_letters

import matplotlib.pyplot as plt

import datetime as dt

import requests

from lxml import html

import math

import re

import json

import os

!wget -nc https://storage.googleapis.com/penn-cis5450/credits.csv

!wget -nc https://storage.googleapis.com/penn-cis5450/titles.csv

What is Pandas?

Apart from animals, Pandas is a Python library to aid with data manipulation/analysis. It is built with support from Numpy. Numpy is another Python package/library that provides effi cient calculations for matrices and other math problems.

Let’s also get familiarized with the PennGrader. It was developed specifi cally for 545 by a previous TA, Leonardo Murri.

PennGrader was developed to provide students with instant feedback on their answer. You can submit your answer and know whether it’s right or wrong instantly. We then record your most recent answer in our backend database. Let’s try it out! Fill in the cell below with your 8-digit Penn ID and then run the following cell to initialize the grader.

# PLEASE ENSURE YOUR PENN-ID IS ENTERED CORRECTLY.

# IF NOT, THE AUTOGRADER WON’T KNOW WHO TO ASSIGN POINTS TO YOU IN OUR BACKEND

# YOUR PENN-ID GOES HERE AS AN INTEGER

STUDENT_ID = 99999998

# You should also update this to a unique “secret” just for this homework, to

# authenticate this is YOUR submission

SECRET = STUDENT_ID

Leave this cell as-is…

%%writefile notebook-config.yaml

grader_api_url: ‘https://23whrwph9h.execute-api.us-east-1.amazonaws.com/default/Grader23’

grader_api_key: ‘flfkE736fA6Z8GxMDJe2q8Kfk8UDqjsG3GVqOFOa’

grader = PennGrader(‘notebook-config.yaml’, “cis5450_fall24_HW1”, STUDENT_ID, STUDENT_ID)

We will use scores from Penn Grader to determine your grade. You will still need to submit your notebook so we can check for cheating and plagarism. Do not cheat.

Note: If you run Penn Grader after the due date for any question, your assignment will be marked late, even if you already had full points for the question before the deadline. To remedy this, if you’re going to run your notebook after the deadline, either do not run the grading cells, or reinitialize the grader with an empty or clearly fake ID such as 999999999999 (please use 10+ digits to be clearly a fake STUDENT_ID )

Adding our data so that our code can find it

We can’t be data scientists without data! We provided code for you to download the data (the “wget” cell from earlier). If you go to the view on the left and click files, you should see something similar to this image

Part 1: Working with Amazon Prime Video Data [38 points]

In this part of the homework we will be working with a dataset focused on Amazon Prime Video Movie Data!

1.0 Loading in Titles data (2 points)

Let’s first load our dataset into a Pandas Dataframe. Use Pandas’s read_csv functionality, which you can find documentation for here:

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html

While reading documentation is hard at first, we strongly encourage you to get into the habit of doing this, since many times your questions will be answered directly by the documentation (ex: “why isn’t my dataframe. dropping duplicates” or “why didn’t this dataframe. update”).

TODO

Save the Credits dataframe. to a variable named: credits_df

Save the Titles dataframe. to a variable named: titles_df

#TODO: Import your two files to pandas dataframes — make sure the dataframes are named correctly!

Let’s focus on the titles_df for now and see what the dataframe. looks like. Display the first 10 rows of the dataframe. in the cell below (take a look at the documentation to find how to do this!)

#TODO: Display the first 10 rows of `titles_df`

Another thing that is often times helpful to do is inspect the types of each column in a dataframe. Output the types of titles_df in this cell below.

# TODO: Display the datatypes in `titles_df`

Save the types of the type , release_year , runtime , seasons , imdb_id , and tmdb_score columns to a series called titles_df_types (retaining the index names) and pass them into the autograder cell below.

# View the output here!

titles_df_types =

# TEST CASE: titles_df_types (2pt)

# [CIS 545 PennGrader Cell] – 2 points

grader.grade(test_case_id = ‘titles_df_types’, answer = titles_df_types)

1.1 Cleaning up Titles data (4 points)

When you work with data, you’ll have NaNs, duplicates or columns that don’t give much insight into the data. There are different ways to deal with missing values (i.e. imputation, which you can read into on your own), but for now, let’s drop some of these rows in titles_df to clean up our data. Note that there might be multiple ways to do each step. Also note that a lot of the columns in titles_df have all nulls. Thus, ensure to drop the unnecessary columns before filtering out rows with nulls

Refer to the documentation if you get stuck — it’s your best friend!

TODO: 1.1

Make a new data frame. titles_cleaned_df

Keep only the following columns: id , title , type , release_year , runtime , genres , production_countries , imdb_score , imdb_votes , tmdb_popularity , tmdb_score .

Drop rows that have NaNs in them. Use the info function to see the number of null rows in this DataFrame. before this, and afterward to sense check that your operation is correct

Reset the index and drop the index column which stores the original index prior to resetting the index. We recommend you print out the intermediate dataframe. prior to this to see that the indices are not consecutive!

Cast title , type to type string , and imdb_votes to type int .

Save the result to titles_cleaned_df .

Note: The affected string columns should appear as string datatype and not object (you can check using df.dtypes). If it is not, we recommend checking up on this documentation to see how to successfully convert object into strings (Hint: cast as ‘string’ and not str).

#TODO: Keep only the necessary columns

#TODO: Drop nulls

#TODO: Reset and drop the index

#TODO: Cast type

# TEST CASE: titles_cleaned_df (4pt)

# [CIS 545 PennGrader Cell] – 4 points

grader.grade(test_case_id = ‘titles_cleaned_df’, answer = titles_cleaned_df)

1.2 Data Wrangling with Titles Data (8 points)

Now, let’s process the data in an appropriate format so that we can answer some queries more easily. Make sure to use titles_cleaned_df for this part.

TODO: 1.2

Create a column called is_movie that contains a value of 1 if the type of content is MOVIE and a value of 0 if not.

Create the genres_expanded column in titles_cleaned_df to create individual rows for each genre of each movie. Hint: Make sure it is the correct type before doing this!

Similar to before, create a production_countries_expanded column to create individual rows for each country where the movie was produced.

Drop the redundant columns type , genres , and production_countries , as well as all null values, saving the result as titles_final_df . Make sure to reset and drop the index as well! (8 points)

Hint: See apply, explode, json.loads, and lambda in Python documentation.

Note: Feel free to reference this Geeks4Geeks link to better understand how to use the json.loads() function. You may not import ast or eval to do this.

Note: We recommend printing out the intermediate steps and testing your logic on singular values and getting the correct answer, before applying it to the entire dataframe!

Note: Include rows with the type SHOW, too.

# TODO

# TEST CASE: titles_final_df (8pt)

# [CIS 545 PennGrader Cell] – 8 points

grader.grade(test_case_id = ‘titles_final_df’, answer = titles_final_df)

1.3 Compute the Top Performing Genres

1.3.1 Compute the Best Genres By IMDb and TMDb Score (6 points)

In this section we will compute the top performing genres, and will use both data from the Internet Movie Database (IMDb) and The Movie Database (TMDb) to do so. We will use titles_final_df in this section.

TODO: 1.3.1

Use groupby() function

Create a dataframe. genres from titles_final_df with only the columns genres_expanded , tmdb_popularity , imdb_score and tmdb_score .

Filter genres to only keep those movies with tmdb_popularity greater than 2.0.

Create a dataframe. genres_imdb_df that contains the average imdb_score for each genre_expanded . Make sure to keep the resultant genres_expanded and imdb_score columns

Sort this in descending order, keeping only the top 10 values

Create a column called score that is the average score rounded to two decimal places

Reset the index and drop the index column

Have only score and genres_expanded as part of genres_imdb_df

Do the same steps for creating genres_imdb_df to create genres_tmdb_df with tmdb_score instead!

#TODO: Create genres

#TODO: Create genres_imdb_df

#TODO: Create genres_tmdb_df

# TEST CASE: genres_df (6pt)

# [CIS 545 PennGrader Cell] – 6 points

grader.grade(test_case_id = ‘genres_df’, answer = (genres_imdb_df, genres_tmdb_df))

1.3.2 Compute the Percentage Difference Between Genres (6 points)

In this section we will compute the differences in results between genres_imdb_df and genres_tmdb_df .

TODO: 1.3.2

Merge genres_imdb_df and genres_tmdb_df on genres_expanded to create merged_df .

Rename the score columns to score_imdb and score_tmdb respectively

Create a column difference in merged_df that is defi ned as the absolute value of the percentage difference between score_imdb and score_tmdb . Hint: Check out the abs function for help with this! Use the following formula for this:

Make sure do not use use Python iteration (e.g., for loop, while loop).

Sort merged_df in descending order by difference

Reset the index and drop the index column

merged_df should have score_imdb , genres_expanded , score_tmdb , and difference

# TODO

# TEST CASE: merged_df (6pt)

# [CIS 545 PennGrader Cell] – 6 points

grader.grade(test_case_id = ‘merged_df’, answer = merged_df)