data cleaning

OBJECTIVE
Load CSV files in Python
Perform data cleaning
Manipulate DataFrames
Perform basic data manipulation
Use Python queries and condition to answer questions

PROGRAM OVERVIEW
You are being provided with a CSV file containing cell phone reviews listed on Amazon.com
The data file also has some problems in it and needs to be cleaned
You are expected to load the data file in Pandas and clean the data
After data cleaning, you are expected to answer questions based on querying and analysis using Pandas methods and general Python programming.
Video Overview of Project

REQUIREMENTS
On Line 1 of the first code cell (the top line), put your name, pitt-email, RECITATION class time, and assignment number in as a comment.
Every file you create must have your Pitt email id, e.g. jsmith23 (not the numeric student ID).
You MUST use primarily only the concepts learned in class.
Assignments are meant to be a demonstration of concepts learned in class
No credit will be awarded for implementations of code that have significantly deviated from class methods and concepts.
Do not copy overly complex code, complex methods, from libraries not covered in class.

INSTRUCTIONS
Start a new Jupyter Notebook project on Google Colab.

Click the default name to rename the project. Use the name structure “Project1_YourPittID.ipynb”. Final sample names are Project1_jde29.ipynb and Project1_adp7.ipynb.

PART 1. SETUP THE DATA FILE
Create a new code cell by pressing the “+ Code” button.

Place a comment “Part 1” at the top of the cell.

1.1 Use the following CSV URL and load it using Pandas.

https://raw.githubusercontent.com/profAltaf/busbis100/main/cellphone_reviews2023.csv

1.2. Use head(), tail(), info(), and describe() to look at the data.

OUTPUT: Once you are finished, your final output should show the info() for submission purposes.

PART 2. INITIAL INVESTIGATION
Create a new code cell by pressing the “+ Code” button.

Place a comment “Part 2” at the top of the cell.

2.1 List the unique brand names.

OUTPUT: Your final output should show the list of the unique brand names. This does not need to be a print statement.

You should study this list of brands for issues and think about what type of cleaning would be required.

PART 3. DATA CLEANING
Each of the columns may have some erroneous data within them. Your ultimate task is to clean the data, so that all the numeric columns do not have alphanumeric content, can be converted to numeric data, and you are able to view descriptive statistics. However, be mindful that you may not be able to see all the erroneous data initially.

Download the Cleaning Record Word template file Download Cleaning Record Word template file.
Update this file with information on all the change you have made.
Look at the template file for sample records showing you what kind of changes to record.

Create a new code cell For Data Cleaning by pressing the “+ Code” button
Place a comment “Part 3: Data Cleaning” at the top of the cell.

All the data cleaning MUST be done collectively in one place.
The objective is to have a list of code that is in one place, can be run together, and for you to be assured that after a certain point in your code, you will have a “good” and “clean” data file that you can use for querying.
You can and should have multiple code blocks for cleaning, so that you can run those parts individually as you work.
All code blocks should be marked with Part 3.x, e.g. Part 3.1, 3.2 and so on.

IMPORTANT NOTE:
If you find data problems while querying and need to fix/clean/delete data, you should come back to Part 3 and perform the change in this part.
The following is NOT a comprehensive list, or a detailed step-by-step list, or the list of the only issues in the file. This is to guide your actions so you go along the correct path. The specific actions you take on the path and the little detours it may need for you, are your own to decide!

Data cleaning is an iterative process. You may need to backtrack, fix data, rerun your code, find a bad value, fix the data, and so on.

Clean data with the following principles. You should create a new code block for each type of issue. Label each part with a comment, “Part 3.x [list the problem you are solving] ”

Do the following to uncover the initial set of problems.

Check if all numeric columns are showing with a numeric int or float type in info. If they are not, there is likely something that needs to be fixed.
Use describe() to look at descriptives. If values are not shown for individual numeric columns, there is likely something that needs to be fixed.

3.1 Remove rows where the brand has an na value.

3.2 Remove all rows which do not have a price listed for a cell phone.

This means removing rows which have a price of “0” listed

3.3 Fix Price Column

This is not the original price column (you will be asked to delete the original price column)

If the column has improper formatting -> fix the value in the cell as long as you can make out the correct value

For each modification of value you make, you must make sure to record the row index number, initial value, changed value, rationale or justification for new value in the Cleaning Record
Convert the column to numeric type. This is iterative and it will not succeed till major problems have been addressed.
Fix any extraneous or erroneous values that can be identified with relative confidence.
Do NOT try to guess if a price is valid in terms of it being realistic since there are really cheap phones and really expensive phones – the bounds for valid prices are large.
Restrict yourself to addressing values that looks clearly erroneous (e.g. $20 is not erroneous as there are some really cheap flip phones out there!)

3.4 Fix Total Reviews Column

If the column has improper formatting -> fix the value in the cell as long as you can make out the correct value
For each modification of value you make, you must make sure to record the row index number, initial value, changed value, rationale or justification for new value in the Cleaning Record
Convert the column to numeric type. This is iterative and it will not succeed till major problems have been addressed
Fix any extraneous or erroneous values that can be identified with relative confidence.

3.5 Eliminate all rows which have a duplicate ASIN.

Keep the first row in case of duplicates.

3.6 Fix the phone names

Look at the list of unique phone names. You may run into problems, which should be solved as follows
No brand name listed -> delete the row
Typos in the brand name -> fix the brand name either by fixing typos. Make sure all brand names have only the first letter capitalized unless you know that the brand name uses stylized lettering, e.g. OnePlus

Look at the unique names again to verify that you do not have repeating brand names, incorrect brand names, malformed brand names, etc.
In addition, as you work, you might run into challenges such as repeating values, missing data, data in the wrong format, non-numeric data.

3.7 Delete the image and original price columns.

OUTPUT: for each of the cleaning parts, leave the output as the head() or info() you might use to verify that your code ran correctly. Be mindful that all the parts may NOT have an ideal output to see (e.g., dropping rows/cols doesn’t show the result easily).

During cleaning, it is the process and code that counts. You will be asked to show final state of your cleaned file in Part 5.

PART 4. ADDING COMPUTED COLUMNS
Create a new code cell for Computed Columns by pressing the “+ Code” button.
Place a comment “Part 4: Computed Columns” at the top of the cell.
4.1 Create a new column named “Review_URL”

For the value of the URL, create a string using string concatenation or using f-strings.

Join the string “http://www.amazon.com/reviews/” with the ASIN column value to get a working link to an Amazon product review.

e.g. if the asin is B0009N5L7K, the value for the review URL will be “http://www.amazon.com/reviews/B0009N5L7K”

Use the head() method to review your data and ensure the URL works by copying a couple of the and testing them in a browser.

4.2 Create a column named “Price (Euros)”.

For the value of this column, convert the price from US Dollars to Euros using the exchange rate of $ 1 USD = 0.97 Euros

OUTPUT: Your final output should show the head() of the data frame.

PART 5. FINALIZING CLEANED FILE
Create a new code cell for Computed Columns by pressing the “+ Code” button.
Place a comment “Part 5: Finalizing Cleaned File” at the top of the cell.

5.1 Save the file with all the corrected data to a new file with CSV format. Use the name “Project1_YourPittID_CleanedData.csv”, e.g. “Project1_jsmith24_CleanedData.csv”

5.2 Display a list of the unique brand names. This is your final check that the issues in your file have been resolved.

5.3 Display the Info() for your final data set. This info represents the final status of the number of rows and columns you ended up with after all the cleaning was done, the data types, etc.

5.4 Download the cleaned CSV file from Colab (you will turn it in with the project). This file will be deleted when Colab purges your project execution runtime.

OUTPUT: Your final output should show the unique brand names and the info() of the final data frame (in different code blocks)

IMPORTANT
If you change any data for an issue encountered after this point in your code, you must re-run this part!

PART 6. ANALYSIS
Create a new code cell for Computed Columns by pressing the “+ Code” button.
Place a comment “Part 6: Analysis” at the top of the cell.
Create a new code block for each question and add a comment.

IMPORTANT

Run queries only after ALL cleaning has been done.
If you need to delete a row because you find bad data, you must write/run that code in Part 3: Cleaning and then re-run ALL queries on fresh, clean data.

6.1. How many Apple or Motorola phones are there combined?

6.2 How many phones have the word “Prepaid” in their name (use Title column)?

6.3 What is the mean price for a Motorola phone.

OUTPUT: Your final output should for EACH question, a nicely formatted statement in proper English, which answers the question.

GRADING NOTE – READ THIS TO AVOID GETTING A ZERO.
Credit will be awarded for writing good, clean code consistent with how it was taught in class. Points will be associated with writing CLEAN and READABLE code
One function should be called in a line.
Return value of functions should be stored in variables.
Those variables should then be used in following lines.
Functions from the Pandas module need to be used instead of global Python functions (do what we do in class)
sum ( df [ ] ) is not okay
df[ ].sum() is okay

Points will NOT be awarded for writing code in the following manner:
object.method().method().method()
method ( object.method().method() )
df[ ].method().method().method()
Expect 50-100% point penalties for entire parts containing such code.

Queries must be used when selecting a subset of data based on a condition
Points will not be awarded for doing something like this:
df[ df [ df [ ] ] ]
Points will NOT be awarded for using functions, methods, techniques not covered in class
Expect 50-100% point penalties for entire parts containing such code.
INTERNAL CONSISTENCY
All work turned in must be internally consistent, i.e. the Python code in .py file, must show output in the Jupyter notebook, which results in the CSV, based on the justifications listed in the Word document.
Code/Data/Output not consistent with the other parts submitted will only minimal, if any, credit
This is a Python class and it is the Python code which will be assessed primarily.
In cases where the code does not support the answer, either in output or print statements, a zero may be assigned.

DUPLICATE CODE WILL BE CHECKED USING AI
All code turned in will tested for copying using AI and Pattern Detection software.
AI Pattern Detection tests for PATTERNS: it is not dependent on reading variable names, function names, comments, empty lines, etc.
If AI flags your submission, you will be asked to demonstrate authorship. You may be asked to rewrite code, in person, using a different data file which has different errors to prove authorship.

ACADEMIC INTEGRITY
All work submitted must be conceptualized and written by you.
Read the full section on academic integrity in the Syllabus, before you submit anything.
Minimum penalty for academic integrity violations for the Project will be a 0 on the Project.

SUBMISSION

Submit 4 files to Canvas. Canvas will allow you to attach multiple files

1) Colab file with extension .ipynb (Go to File menu -> Download -> Download .ipynb )

2) Colab file with extension .py (Go to File menu -> Download -> Download .py )

3) Cleaned CSV data file with extension .csv (Click on folder icon in Colab -> click on ellipsis (3 dots) -> Download )

4) Word file of data modification justifications, with extension .docx

Resubmitting? ALL files must be resubmitted again. Only your final submission will be looked at. If files are missing, only the ones submitted in the latest submission will be graded.

No .py file attached? 5 point deduction.

No .csv file attached? 5 point deduction.

Last Completed Projects