Data Cleaning and Manipulation

Data Cleaning Overview

• Purpose: Identifying and correcting inconsistencies and errors in datasets for accurate analysis.

• Importance: Ensures reliable data for decision-making.

Common Types of Errors

• Duplicate Values: Repeated entries in a column.

• Missing Information: Blank values in the dataset.

• Formatting Issues: Leading or trailing white spaces, special characters.

Steps for Data Cleaning

1. Identify Duplicates

   • Use filters to check for duplicates (e.g., House ID).

   • Utilize 'Remove Duplicates' function to eliminate duplicates in specific columns.

   • Example: House ID and Postal Code need to be unique.

2. Consistency in Entries

   • Ensure uniformity in text data to avoid inconsistency.

   • Example: Standardize country names (e.g., "United States" vs "USA").

3. Trimming White Spaces

   • Use the TRIM function for columns with leading/trailing spaces.

4. Fixing Special Characters

   • Identify special characters (like symbols or multiple question marks) using SUBSTITUTE function.

   • Clean up entries—replace unwanted characters with nothing.

5. Filling Missing Values

   • Determine a strategy to fill in missing values:

     • Numerical Data: Use MEDIAN or MEAN.

     • Categorical Data: Use MODE.

   • Examples: Fill blanks in Housing Area, Living Area, Airport Distance using median of respective columns.

6. Creating New Features

   • Develop new columns for deeper insights.

   • Example: Extract the month from the Sale Date column to analyze seasonal trends.

   • Categorize house size based on Living Area using IF statements to classify sizes (small, medium, large).

7. Final Review

   • Ensure all columns are cleaned and prepared for analysis.

   • Verify there are no blanks and duplicates in the cleaned dataset.