NashVille(TN)_Housing

-- Let's first have a look into the data.

SELECT TOP (100) *
FROM Housing_Analysis_Project..Nashville_housing;


-- Let's start the cleaning of the data by standardizing the date format from the current datetime format.

ALTER TABLE Nashville_housing
ADD SaleDateConverted Date;

UPDATE Nashville_housing
SET SaleDateConverted = CONVERT(date, SaleDate);

SELECT SaleDateConverted, CONVERT(date, SaleDate)
FROM Housing_Analysis_Project..Nashville_housing;

ALTER TABLE Nashville_housing
DROP COLUMN SaleDate;

/* I realized that the there was missing data in the PropertyAddress field. I then learned that, for the same parcel ID, property addresses were identical. 
Therefore, I populated the PropertyAddress column by using the values where the null field had the same parcel ID as the PropertyAddress populated field*/


-- First, we join the Nashville-housing table to itself (self-join)

SELECT a.ParcelID,a.PropertyAddress,b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress, b.PropertyAddress) -- ISNULL does what I want here as it returns the value of b.PropertyAddress, if and only if a.PropertyAddress is null.
FROM Housing_Analysis_Project..Nashville_housing AS a
JOIN Housing_Analysis_Project..Nashville_housing AS b
	ON a.ParcelID=b.ParcelID
	AND a.[UniqueID ] <> b.[UniqueID ] 
WHERE a.PropertyAddress IS NULL;

--There are 35 null values that I will fill out with the new PropertyAddress values.

UPDATE a
SET PropertyAddress = ISNULL(a.PropertyAddress, b.PropertyAddress)
FROM Housing_Analysis_Project..Nashville_housing AS a
JOIN Housing_Analysis_Project..Nashville_housing AS b
	ON a.ParcelID=b.ParcelID
	AND a.[UniqueID ] <> b.[UniqueID ] 
WHERE a.PropertyAddress IS NULL;

--Let's check if it worked. No values returned, which indicates that it did!

SELECT a.ParcelID,a.PropertyAddress,b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress, b.PropertyAddress)
FROM Housing_Analysis_Project..Nashville_housing AS a
JOIN Housing_Analysis_Project..Nashville_housing AS b
	ON a.ParcelID=b.ParcelID
	AND a.[UniqueID ] <> b.[UniqueID ] 
WHERE a.PropertyAddress IS NULL;

SELECT TOP(100) *
from Nashville_housing


-- Let's now split the Address into individual columns for Address and City.



SELECT SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress)-1) AS Address,
SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress)+1, LEN(PropertyAddress)) AS City
FROM Housing_Analysis_Project..Nashville_housing;


-- Let's add the split values into the table and drop the duplicate column.


ALTER TABLE Nashville_housing
ADD PropertySplitAddress nvarchar(255);

UPDATE Nashville_housing
SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress)-1);

ALTER TABLE Nashville_housing
ADD PropertySplitCity nvarchar(255);

UPDATE Nashville_housing
SET PropertySplitCity = SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress)+1, LEN(PropertyAddress));


ALTER TABLE Nashville_housing
DROP COLUMN PropertyAddress;

SELECT TOP(5) *
FROM Nashville_housing;

-- Likewise, the same occurs with the OwnerAddress field. It needs split into Address, City, and State. Alternatively, I will use the PARSENAME function this time. 


SELECT
PARSENAME(REPLACE(OwnerAddress, ',','.'),3) AS Address, --PARSENAME needs a dot delimited string, for which I will use the REPLACE function to replace commas with dots.
PARSENAME(REPLACE(OwnerAddress, ',','.'),2) AS City,
PARSENAME(REPLACE(OwnerAddress, ',','.'),1) AS State
FROM Housing_Analysis_Project..Nashville_housing;

-- Let's add the split values into the Table


ALTER TABLE Nashville_housing
ADD OwnerSplitAddress nvarchar(255);

UPDATE Nashville_housing
SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress, ',','.'),3);

ALTER TABLE Nashville_housing
ADD OwnerSplitCity nvarchar(255);

UPDATE Nashville_housing
SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',','.'),2);

ALTER TABLE Nashville_housing
ADD OwnerSplitState nvarchar(255);

UPDATE Nashville_housing
SET OwnerSplitState = PARSENAME(REPLACE(OwnerAddress, ',','.'),1);

ALTER TABLE Nashville_housing
DROP COLUMN OwnerAddress;

SELECT TOP(5) *
FROM Nashville_housing;

/*In the "Sold as Vacant" field, there are 4 distinct values (Y, N,Yes and No), being the last two the most frequent (verified with a count). Let's change Y and N to the more frequent Yes and No */


SELECT DISTINCT SoldAsVacant, COUNT(SoldAsVacant)
From Housing_Analysis_Project..Nashville_housing
GROUP BY SoldAsVacant
ORDER BY 2 DESC;


UPDATE Nashville_housing
SET SoldAsVacant = CASE WHEN SoldAsVacant = 'Y' THEN 'Yes'
		 WHEN SoldAsVacant = 'N' THEN 'No'
		 ELSE SoldAsVacant
		 END  

-- Verifying that SoldAsVacant only has two possible values, Yes or No.

SELECT DISTINCT SoldAsVacant
FROM Nashville_housing;

-- Let's now remove duplicates, using a CTE and the ROW_NUMBER function to identify the duplicated values.

WITH RowNumCTE AS(
SELECT *, 
	ROW_NUMBER() OVER (PARTITION BY ParcelID, PropertySplitAddress, SalePrice, SaleDateConverted, LegalReference ORDER BY UniqueID) AS row_num  
	--The PARTITION BY statement includes all the fields I identified as unique, thus meaning that rows with the same values must be duplicates.
FROM Housing_Analysis_Project..Nashville_housing		
)


SELECT *
FROM RowNumCTE
WHERE row_num > 1
ORDER BY PropertySplitAddress;

--Found 104 duplicates whererow_num > 1)- let's get rid of them.

DELETE 
FROM RowNumCTE
WHERE row_num > 1;

--The Table has now been cleaned and is ready for any future further analysis.