Cleaned Nashville Housing Dataset

June 21, 2022 by Marcella

Overview

This project was my hands-on practice ground for SQL data cleaning. The goal was simply to work through a real, messy dataset using SQL queries until I could see the improvement in structure and performance for myself. At the time, SQL Server Management Studio 2018 was brand new to me, so this project doubled as my introduction to the tool.

I followed AlexTheAnalyst's Data Analyst Portfolio Project tutorial on GitHub step by step, treating it like a sponge: soaking up every technique he demonstrated and reproducing it to the letter. "Done," for me, meant successfully applying every cleaning method from his walkthrough until my results matched what the tutorial produced.

The Original Dataset

When it arrived, the dataset lived entirely in Microsoft Excel: 18 general columns plus 1 date column, with all the column headers in ALL CAPS and month names in title case. After importing into SQL Server, it grew to 19 columns and 56,477 rows. The "SoldAsVacant" column alone had four different ways of saying the same two things: No, N, Yes, and Y. Beyond that, a long list of columns carried significant null values, including OwnerName, OwnerAddress, Acreage, TaxDistrict, LandValue, BuildingValue, TotalValue, YearBuilt, Bedrooms, FullBath, and HalfBath.

I chose this dataset specifically because it was "dirty." Working through real inconsistencies like these, rather than a tidy sample dataset, was the whole point.

The part that actually slowed me down wasn't the volume of nulls, it was the logic. This was my first time writing a CTE (Common Table Expression), and my first time chaining multiple SQL functions together, like using PARSENAME() and REPLACE() in the same statement to split a single PropertyAddress column into separate street and city columns.

Finding duplicates with a CTE:

-- View duplicates & create a CTE
with RowNumCTE as (
    select *,
        row_number() over(partition by
                            ParcelID,
                            PropertyAddress,
                            SalePrice,
                            SaleDate,
                            LegalReference
                        order by UniqueID) row_num
    from nashville_housing.dbo.nash_data
)
select *
from RowNumCTE
where row_num > 1;
            

Splitting PropertyAddress into street and city:

-- Separating 'PropertyAddress' col
select parsename(replace(PropertyAddress, ',', '.'), 2) as PropertyStreet,
        parsename(replace(PropertyAddress, ',', '.'), 1) as PropertyCity
from nashville_housing.dbo.nash_data;
            

The Cleaning Process

Beyond the CTE and the PARSENAME(REPLACE()) address split, the rest of the cleaning fell into a few categories: converting the date column into a real SQL date type, and replacing, renaming, and joining null values back together.

The SaleDate column arrived as a full datetime value, so I converted it with CONVERT(Date, SaleDate), added a new SaleDates column to hold the result, and updated the table to populate it.

For null values, my approach depended on the column. For PropertyAddress, I used ParcelID as an anchor, since it's unique to each property, and joined the table to itself: comparing rows with the same ParcelID but a different UniqueID, then filling in any null PropertyAddress with the address from its matching row using ISNULL(). OwnerAddress couldn't use the same trick since I didn't have a column tying it back to the property the same way, so I set that one aside to revisit later.

The moment I'm proudest of from this stretch is creating the CTE for the first time. I had to trust the process and work through the logic without knowing for sure it would work, and it did.

Converting SaleDate to a real date type:

-- Change 'SaleDate' col
select SaleDate,
        convert(Date, SaleDate)
from nashville_housing.dbo.nash_data;

-- Update 'SaleDate' col
    -- create a new column first
alter table nash_data
add SaleDates date;

update nash_data
set SaleDates = convert(Date, SaleDate);
            

Filling null PropertyAddress by joining the table to itself:

-- ParcelID is a good anchor because it's unique to the property
update nash1
set PropertyAddress = isnull(nash1.PropertyAddress,
                                nash2.PropertyAddress)
from nashville_housing.dbo.nash_data nash1
join nashville_housing.dbo.nash_data nash2
    on nash1.ParcelID = nash2.ParcelID
    and nash1.[UniqueID ] <> nash2.[UniqueID ]
where nash1.PropertyAddress is null;
            

Results

The final dataset ended up with 21 columns and 56,477 rows, built through a total of 26 queries.