Clean and Merge Housing Data

Problem Statement

You are working with two datasets: a primary housing_data DataFrame containing listing information and a zipcode_reference DataFrame containing regional demographic data.

Datasets

1. housing_data (columns: listing_id, price, zip_code):

   • price: Contains numeric values stored as strings (e.g., "$1,200"), or sometimes null.
   • zip_code: Often missing leading zeros (e.g., 902 vs "00902") and contains inconsistent types.

2. zipcode_reference (columns: zip, median_income):

   • zip: Guaranteed to be a 5-digit string (e.g., "00902").

Task

Write a function clean_and_merge(housing_df, zip_df) that returns a cleaned DataFrame.

1. Price Cleaning: Convert the price column to a numeric float. If a price is null, impute it with the global mean of the non-null price values.
2. Zip Code Standardization: Standardize zip_code in the primary dataset to a 5-digit string, padding with leading zeros where necessary.
3. Merge: Perform a left join to include median_income from zip_df onto housing_df.

Example

Input housing_df:

   listing_id  price zip_code
0           1  $500       123
1           2  $700      0123
2           3   NaN      9999

Input zip_df:

    zip  median_income
0  00123          50000
1  09999          60000

Expected Output:

   listing_id  price zip_code  median_income
0           1    500    00123        50000.0
1           2    700    00123        50000.0
2           3    600    09999        60000.0