Data Cleaning with Pandas

✕

Common Data Problems

Common Data Quality Issues in Real-World Datasets:
IssueResolution
Inconsistent Column NamesStandardize names using rename / load with names parameter.
Missing Values (NULL, Blanks)Use dropna to remove rows / fillna to fill them.
DuplicatesUse drop_duplicates to remove duplicate rows.
Inconsistent Categories. F, FeStandardize category names using replace or map.
Incorrect Data TypesUse astype to convert columns to correct data types.
OutliersIdentify outliers and remove with filter / clip.
Incorrect data format.melt, .pivot
Spelling MistakesFuzzy matching
Inconsistent UnitsUnit conversion to standardize measurements.
Case SensitivityConvert text to lower or upper case for consistency.
Business Rule. TxnDate > CreatedFix / Remove data to align with business rule
Leading and Trailing SpacesUse strip() to remove leading and trailing spaces.
Non Numeric Character in numeric columnUse regex to clean non-numeric characters.
Common data quality issues in real-world datasets, their descriptions, and potential resolutions

Tidy Data

  • Standard way of organizing data to facilitate analysis.
  • Each variable forms a column, each observation forms a row, and each type of observational unit forms a table.
Example of Untidy Data
productyear_2020_salesyear_2021_sales
Product A100150
Product B200250
Example of untidy data where sales for different years are in separate columns.
Example of Tidy Data
productyearsales
Product A2020100
Product A2021150
Product B2020200
Product B2021250
Example of tidy data where each variable (year, sales) forms a column and each observation forms a row.

melt

  • Tidy data is better for analysis, untidy is better for reporting.
  • melt converts wide format data to long format. i.e tidy format
  • Number of rows increases and number of columns decreases.
  • Syntax: pd.melt(df, id_vars=["id_1", "id_2"], value_vars=["val_1", "val_2"])
  • Example: import pandas as pd df = pd.DataFrame({ "product": ["A", "B"], "year_2020_sales": [100, 200], "year_2021_sales": [150, 250] }) melted_df = pd.melt(df, id_vars=["product"]) print(melted_df)

pivot_table

  • Pivoting is the reverse of melting.
  • It converts long format data to wide format.
  • Number of rows decreases and number of columns increases.
  • Aggregation for duplicate is average. Can be customized with aggfunc.
  • Syntax: df.pivot_table(index=["id_1", "id_2"], columns="variable", values="value")
  • Example: import pandas as pd df = pd.DataFrame({ "product": ["A", "A", "B", "B"], "year": [2020, 2021, 2020, 2021], "sales": [100, 150, 200, 250] }) p_df = df.pivot_table(index="product", columns="year", values="sales") print(p_df)

cut and qcut

  • cut: Segment and sort data values into bins.
  • qcut: Segment & sort data values into equal-sized bins based on quantiles.
    • Example
    1. bins = [0, 18, 35, 60] labels = ["Child", "Adult", "Senior"] df["age_group"] = pd.cut(df["age"], bins=bins, labels=labels) print(df) labels = ["Young", "Middle-aged", "Old"] df["age_quantile"] = pd.qcut(df["age"], q=3, labels=labels) print(df)

Handling Missing Value

    Common Strategies for Handling Missing Values
    1. Dropping rows/columns with missing values.
    2. Filling missing values with statistics (mean, median, mode).
    3. Use techniques like interpolation, prediction to estimate missing values.
  • Example: df["course_clean"] = df["course"].fillna("Unknown") df["age_clean"] = df["age"].fillna(df["age"].mean()) df["experience_clean"] = df["experience"].ffill() # or bfill filter_mask = (df["education"].isnull()) & (df["age"] < 25) df.loc[filter_mask, "education"] = "SLC" df.dropna(subset=["name"], inplace=True)

Handling Duplicates

  • Duplicate rows can be identified with duplicated() function.
  • Duplicate rows can be removed with drop_duplicates() function.
    • Example
    1. duplicate_mask = df.duplicated(subset=["name", "age"], keep=False) duplicates = df[duplicate_mask] print(duplicates) # Remove duplicates based on name and age columns unique_df = df.drop_duplicates(subset=["name", "age"], keep="first") print(unique_df.duplicated(subset=["name", "age"]).sum()

Data Value issues

    Inconsistent Categories (map/replace)
    1. df["gender"] = df["gender"].replace({"m": "Male", "f": "Female"}) mapping_dict = {"m": "Male", "f": "Female"} df["gender"] = df["gender"].map(mapping_dict)
    Outliers (clip/drop)
    1. df["age"] = df["age"].clip(lower=0, upper=120) outlier_mask = (df["age"] < 0) | (df["age"] > 120) df = df[~outlier_mask]
    Leading and Trailing Spaces (strip)
    1. df["name"] = df["name"].str.strip()
    Inconsistent Case
    1. df["name"] = df["name"].str.upper() # or df["name"] = df["name"].str.lower()
    Non Numeric Character in numeric column (regex)
    1. df["age"] = df["age"].str.replace(r"\D", "", regex=True).astype(int)

Fuzzy Matching to clean Spelling issue

  • Technique to find strings that are approximately equal instead of exact.
  • Match result is based on similarity score.
  • The fuzzywuzzy library can be used for fuzzy matching in Python.
    • Example
    1. from fuzzywuzzy import process choices = ["New York", "Kathmandu", "Delhi"] query = "New yolk" best_match = process.extractOne(query, choices) print(best_match)

Practice QuestionsNot started

  1. Data Transformation

    Question 1 of 4

    • Load corona_data.csv into a pandas DataFrame. Transform this data to have columns Date, Country, TotalCases, TotalDeaths. Save result in csv file corona_transformed.csv.
    • Load lab_reading.csv into a pandas DataFrame. Transform this data to have columns Date, CO2, Rain and Methane. Fill Reading in appropriate column and save result in csv file lab_reading_transformed.csv. Note: Incase of duplicate take min value.
    • Load treatment_info.csv into a pandas DataFrame. Transform this data to have columns Date, Treatment Type and Dosage. Save result in csv file treatment_info_transformed.csv.

  2. Data Cleaning - Heart Disease Dataset

    Question 2 of 4

    • Load heart_disease_raw.csv into a DataFrame. Perform the following operations: 1. Display number of rows and columns in dataset. 2. Display column names and their datatypes. 3. Rename column Heart_ stroke to Heart_Stroke. 4. Display sample 15 records. 5. Adjust Gender column to have only M, F and null. 6. Adjust education to have only: Uneducate,Primary School,Graduate,Post Graduate,null 7. Adjust Exercise to have only: null, daily, weekly and monthly 8. Fill missing value in numeric column with their mean. 9. Fill missing value in categorical column with most frequent value. 10. Remove duplicate records. 11. Replace outliers in numeric column with mean value. 12. Ensure Gender, education, Exercise, prevalentStroke, Heart_Stroke is category dtype 13. Ensure other columns also have appropriate datatypes. 14. Save cleaned data as heart_disease_cleaned.csv.

  3. Data Cleaning - Baseball Player Dataset

    Question 3 of 4

    • Load Baseball player.txt into a DataFrame. Perform the following operations: 1. During Load assign header from file Baseball Player - Clean.txt. 2. Display sample 15 records to understand data. 3. Handle missing value in numeric column with mean & categorical column with mode 4. Remove duplicate records. 5. Handle outliers in numeric column by clipping to 1st & 99th percentile. 6. Drop duplicate records. 7. Create new column height_cm converting existing inch value 1 inch = 2.54 cm 8. Bin players into age group as: 10-15, 15-20, and so on till last value is included. 9. Replace underscore (_) of position column by space 10. Split the Name as FirstName,MiddleName, LastName & store in three columns. 11. For non-numeric value check if there is casing and spelling mistake. If so fix it. 12. Ensure all columns have appropriate datatypes. 13. Save cleaned data as tab separated values in baseball_player_cleaned.tsv.

  4. Answering from Baseball Player Dataset

    Question 4 of 4

    • Load baseball_player_cleaned.tsv into a DataFrame. Answer the following questions: 1. Which player has the highest BMI? (BMI = weight / height_cm^2) 2. Which team has the highest average player age? 3. How many players are in each position category? 4. What is the average weight of players in each age group? 5. Which player has the longest name (in terms of characters)? 6. How many players have age above 30 and weight below 70? 7. What is the distribution of players across different age groups? 8. Which player has the highest number of characters in their name? 9. How many players have Center as their position and are in 20-25 age group? 10. What is the average height of players in each position category? 11. What is average Height & Weight of U-23 baseball players? 12. Display the most common position hired by each team. 13.Display average age of each team and Position 14. Display MinHeight, MaxHeight, AverageHeight, MinWeight, MaxWeight & AverageWeight for each Position.