"Selling California": Are House Prices Equivalent to House Values?

Background and Hypothesis

# importing libraries to be used in Deepnote (feel free to add more!) import pandas as pd import numpy as np import seaborn as sns import matplotlib.pyplot as plt from sklearn.linear_model import LogisticRegression from sklearn.model_selection import train_test_split

Data Collection

We started by obtaining data from Zillow covering the median prices of houses in ten California metropolitan statistical areas (MSA). We cleaned up the data for median prices by dropping irrelevant categories such as the region ID and size rank columns.

zillow_medianprices = pd.read_csv('ZillowMedianPrices.csv') zillow_medianprices_CA = zillow_medianprices.loc[zillow_medianprices['StateName'] == 'CA'].drop(['SizeRank', 'RegionID'], axis = 1) zillow_medianprices_CA

Next, we obtained house values from Zillow for houses for the same ten MSAs and cleaned the data by dropping the same columns we dropped in the median prices table.

zillow_housevalue = pd.read_csv('ZillowHouseValue.csv') zillow_housevalue_CA = zillow_housevalue.loc[zillow_housevalue['StateName'] == 'CA'].drop(['SizeRank', 'RegionID'], axis = 1) zillow_housevalue_CA

kaggle = pd.read_csv('RealEstate_California.csv') kaggle_dataset = kaggle.dropna() kaggle_dataset

Visualizations

We created two scatterplots to compare the trends of median house price and house value for the ten metropolitan statistical areas (MSA) in California. Based on the two visualizations, it can be noted that the MSAs with higher house values over the years also have higher median prices overall.

We converted the house value data values into lists for each MSA, and graphed the values for each MSA against months spanning from January 2000 to January 2022. From the housing values visualizations, we can observe that the house value trends are similar for all the MSAs, with the value increasing until about 2007, then decreasing until 2012, and then steadily increases after that.

# house value tbl_rows_list = zillow_housevalue_CA.values.tolist() los_angeles = tbl_rows_list[0][3:] san_francisco = tbl_rows_list[1][3:] riverside = tbl_rows_list[2][3:] san_diego = tbl_rows_list[3][3:] sacramento = tbl_rows_list[4][3:] san_jose = tbl_rows_list[5][3:] fresno = tbl_rows_list[6][3:] bakersfield = tbl_rows_list[7][3:] ventura = tbl_rows_list[8][3:] stockton = tbl_rows_list[9][3:] month_nums = range(len(los_angeles)) plt.scatter(x=month_nums, y=los_angeles, s=1, c='red') plt.scatter(x=month_nums, y=san_francisco, s=1, c='darkorange') plt.scatter(x=month_nums, y=san_jose, s=1, c='gold') plt.scatter(x=month_nums, y=san_diego, s=1, c='limegreen') plt.scatter(x=month_nums, y=fresno, s=1, c='turquoise') plt.scatter(x=month_nums, y=stockton, s=1, c='dodgerblue') plt.scatter(x=month_nums, y=riverside, s=1, c='blueviolet') plt.scatter(x=month_nums, y=sacramento, s=1, c='hotpink') plt.scatter(x=month_nums, y=bakersfield, s=1, c='sienna') plt.scatter(x=month_nums, y=ventura, s=1, c='dimgray') plt.title("CA Housing Values from 2000-2022") plt.xlabel("Months from 2000-2022") plt.ylabel("Price (in millions of USD)")

For the median prices, we followed the same steps by converting the median price data values into lists for each MSA, and graphed the values for each MSA against months spanning from January 2018 to January 2022.

# median prices med_rows_list = zillow_medianprices_CA.values.tolist() med_los_angeles = med_rows_list[0][3:] med_san_francisco = med_rows_list[1][3:] med_riverside = med_rows_list[2][3:] med_san_diego = med_rows_list[3][3:] med_sacramento = med_rows_list[4][3:] med_san_jose = med_rows_list[5][3:] med_fresno = med_rows_list[6][3:] med_bakersfield = med_rows_list[7][3:] med_ventura = med_rows_list[8][3:] med_stockton = med_rows_list[9][3:] med_month_nums = range(len(med_los_angeles)) plt.scatter(x=med_month_nums, y=med_los_angeles, s=5, c='red') plt.scatter(x=med_month_nums, y=med_san_francisco, s=5, c='darkorange') plt.scatter(x=med_month_nums, y=med_san_jose, s=5, c='gold') plt.scatter(x=med_month_nums, y=med_san_diego, s=5, c='limegreen') plt.scatter(x=med_month_nums, y=med_fresno, s=5, c='turquoise') plt.scatter(x=med_month_nums, y=med_stockton, s=5, c='dodgerblue') plt.scatter(x=med_month_nums, y=med_riverside, s=5, c='blueviolet') plt.scatter(x=med_month_nums, y=med_sacramento, s=5, c='hotpink') plt.scatter(x=med_month_nums, y=med_bakersfield, s=5, c='sienna') plt.scatter(x=med_month_nums, y=med_ventura, s=5, c='dimgray') plt.title("CA Housing Median Prices from 2018-2022") plt.xlabel("Months from 2018-2022") plt.ylabel("Price (in millions of USD)")

A/B Testing

We are testing to see if there is any statistically significant difference between housing value and median price of houses in 10 major regions of CA with a significance value of p = 0.05. We are using A/B Testing to determine if it is significant. A/B testing is done by creating a set of data with labels, and then randomizing the labels to create a distribution of the correlated values. Then, the original observed statistic is compared to the distribution of randomized labels.

The null hypothesis is that there is no statistically significant difference between the median prices and house value for the specified region, and the alternate hypothesis is that there is a statistically significant difference between the median prices and house value.

la_medians = zillow_medianprices_CA.loc[zillow_medianprices_CA['RegionName'] == 'Los Angeles-Long Beach-Anaheim, CA'] la_median_vals = la_medians.values.tolist()[0][-13:] median_labels = ['Median Price' for i in range(13)] la_median_data = {'Label': median_labels, 'Values': la_median_vals} la_median_df = pd.DataFrame(la_median_data) la_median_df

la_house = zillow_housevalue_CA.loc[zillow_housevalue_CA['RegionName'] == 'Los Angeles-Long Beach-Anaheim, CA'] la_house_vals = la_house.values.tolist()[0][-13:] house_labels = ['House Value' for i in range(13)] la_house_data = {'Label': house_labels, 'Values': la_house_vals} la_house_df = pd.DataFrame(la_house_data) la_house_df

# concatenating the tables (median_vs_house) and calculating the OBSERVED test statistic # we are plotting the OBSERVED test statistic (reality) against our simulated values to determine the p-value frames = [la_median_df, la_house_df] median_vs_house = pd.concat(frames) shuffled_labels = median_vs_house.sample(n = 13)['Label'] def test_stat(tbl): med_values = tbl.loc[(tbl['Label'] == 'Median Price')] house_values = tbl.loc[(tbl['Label'] == 'House Value')] return med_values['Values'].mean() - house_values['Values'].mean() observed_difference = test_stat(median_vs_house) print("The observed difference is", observed_difference)

# function that calculates one simulated difference def one_simulated_difference(): shuffled_labels = median_vs_house.sample(n = 26)['Label'] shuffled_table = median_vs_house.drop(columns = ['Label']) shuffled_table['Label'] = shuffled_labels.tolist() return test_stat(shuffled_table) print("This randomized sample had a difference of", one_simulated_difference())

differences = [] for i in range(1000): # this takes a LONG time to run, maybe consider reducing the number of simulations new_difference = one_simulated_difference() differences = np.append(differences, new_difference) differences_df = pd.DataFrame(differences, columns = ['Differences']) differences_df

# calculates the p-value by determining the proportion of simulated differences that are above the observed val above_observed = differences_df.loc[differences_df['Differences'] >= observed_difference] above_observed.astype(bool).sum(axis=0)/10000

def ab_testing(region_name): #Creates a dataframe of 13 median values from all the median prices of houses in the given region_name medians = zillow_medianprices_CA.loc[zillow_medianprices_CA['RegionName'] == region_name] median_vals = medians.values.tolist()[0][-13:] median_labels = ['Median Price' for i in range(13)] median_data = {'Label': median_labels, 'Values': median_vals} median_df = pd.DataFrame(median_data) #Creats a dataframe of 13 house values from the region house = zillow_housevalue_CA.loc[zillow_housevalue_CA['RegionName'] == region_name] house_vals = house.values.tolist()[0][-13:] house_labels = ['House Value' for i in range(13)] house_data = {'Label': house_labels, 'Values': house_vals} house_df = pd.DataFrame(house_data) #Combines the median and house value data frames frames = [median_df, house_df] median_vs_house = pd.concat(frames) observed_difference = test_stat(median_vs_house) # Creates an array of 1000 simulated test statistics differences = [] for i in range(1000): # this takes a LONG time to run, maybe consider reducing the number of simulations new_difference = one_simulated_difference() differences = np.append(differences, new_difference) differences_df = pd.DataFrame(differences, columns = ['Differences']) # Create histogram and plot the observed statistic as well differences_df.hist() plt.scatter(observed_difference, -0.002, color='red', s=40) plt.title(region_name) mean_sim_diff = np.mean(differences_df['Differences']) if observed_difference > mean_sim_diff: above_observed = differences_df.loc[differences_df['Differences'] >= observed_difference] else: above_observed = differences_df.loc[differences_df['Differences'] <= observed_difference] return len(above_observed)/1000

dict = {'Los Angeles': ab_testing('Los Angeles-Long Beach-Anaheim, CA'), 'San Francisco': ab_testing('San Francisco, CA'), 'Riverside': ab_testing('Riverside, CA'), 'San Diego': ab_testing('San Diego, CA'), 'Sacramento': ab_testing('Sacramento, CA'), 'San Jose': ab_testing('San Jose, CA'), 'Fresno':ab_testing('Fresno, CA'), 'Bakersfield':ab_testing('Bakersfield, CA'), 'Ventura':ab_testing('Ventura, CA'), 'Stockton': ab_testing('Stockton, CA')}

#Dataframe of the p-values for all the regions' p-values locations = list(dict.keys()) p_values = list(dict.values()) results = ["Fail to reject the null" if dict[loc] > 0.05 else "Reject the null" for loc in locations] ca_pvals = {"CA Region": locations, "P-Values": p_values, "Results": results} ca_pvals_df = pd.DataFrame(ca_pvals) ca_pvals_df

Machine Learning Model

fres_kaggle = kaggle.loc[kaggle['city'] == 'Fresno'] fres_kaggle = fres_kaggle.loc[0 != fres_kaggle['yearBuilt']] fres_kaggle = fres_kaggle.loc[fres_kaggle['price'] != 0] fres_kaggle[['price']] fres_kaggle = fres_kaggle.loc[fres_kaggle['price']<2000000] plt.scatter(x = fres_kaggle['yearBuilt'], y = fres_kaggle['price'])

sf_kaggle = kaggle.loc[kaggle['city'] == 'San Francisco'] sf_kaggle = sf_kaggle.loc[0 != sf_kaggle['yearBuilt']] sf_kaggle = sf_kaggle.loc[sf_kaggle['price'] != 0] sf_kaggle = sf_kaggle.loc[sf_kaggle['yearBuilt'] != 9999] sf_kaggle = sf_kaggle.loc[sf_kaggle['price']<2500000] sf_kaggle[['price']] plt.scatter(x = sf_kaggle['yearBuilt'], y = sf_kaggle['price'])

Then we combined the San Francisco and Fresno into one data frame so we could plot data for both cities on the same graph.

sf_fres_data = pd.concat([sf_kaggle, fres_kaggle]) sf_fres_data = sf_fres_data[["city","price","yearBuilt"]] sf_fres_data # contains data for both SF and Fresno

colors = {'San Francisco': 'LightSteelBlue', 'Fresno':'LightPink'} plt.scatter(x = sf_fres_data['yearBuilt'], y = sf_fres_data['price'], c=sf_fres_data['city'].map(colors))

x = sf_fres_data[['yearBuilt', 'price']] y = sf_fres_data[['city']]

x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=0.20)

from sklearn.preprocessing import StandardScaler scalar = StandardScaler() scalar.fit(x_train) x_train = scalar.transform(x_train) x_test = scalar.transform(x_test)

from sklearn.neighbors import KNeighborsClassifier classifier = KNeighborsClassifier(n_neighbors=7) classifier.fit(x_train, y_train)

y_pred = classifier.predict(x_test)

from sklearn import metrics metrics.accuracy_score(y_test, y_pred)

from sklearn.neighbors import NearestNeighbors neigh = NearestNeighbors(n_neighbors=7) neigh.fit(x)

neigh.kneighbors_graph(x)

Similar to our A/B testing function, we wrote a few functions to help us compare other cities to San Francisco. The function citydatacollect selects one city from the Kaggle data and cleans out any irrelevant values.

def citydatacollect(city1): kaggle_city1 = kaggle.loc[kaggle['city'] == city1] kaggle_city1 = kaggle_city1.loc[0 != kaggle_city1['yearBuilt']] kaggle_city1 = kaggle_city1.loc[kaggle_city1['price'] != 0] kaggle_city1 = kaggle_city1.loc[kaggle_city1['yearBuilt'] != 9999] kaggle_city1 = kaggle_city1.loc[kaggle_city1['price']<2000000] return kaggle_city1

The concatenate function takes two cities and makes a data frame with the city name, house price, and year built.

def concatenate(city1, city2): city1_kaggle = citydatacollect(city1) city2_kaggle = citydatacollect(city2) city_data = pd.concat([city1_kaggle, city2_kaggle]) city_data = city_data[["city","price","yearBuilt"]] return city_data

from sklearn.preprocessing import StandardScaler from sklearn.neighbors import KNeighborsClassifier from sklearn import metrics def kNearest(df_concat): x = df_concat[['yearBuilt', 'price']] y = df_concat['city'] x_train, x_test, y_train, y_test = train_test_split(x, y, test_size=0.20) scalar = StandardScaler() scalar.fit(x_train) x_train = scalar.transform(x_train) x_test = scalar.transform(x_test) classifier = KNeighborsClassifier(n_neighbors=7) classifier.fit(x_train, y_train) y_pred = classifier.predict(x_test) return metrics.accuracy_score(y_test, y_pred)

def CityPredict(city1): combined_df = concatenate("San Francisco",city1) colors = {"San Francisco": 'LightSteelBlue', city1:'LightPink'} plt.figure() plt.scatter(x = combined_df['yearBuilt'], y = combined_df['price'], c=combined_df['city'].map(colors)) plt.title("San Francisco vs." + " " + city1) plt.xlabel("Year Built") plt.ylabel("Price (in millions of USD)") return kNearest(combined_df)