Erie County Redfin Housing Sales

# import packages

import pandas as pd
import numpy as np

import time
from datetime import datetime

import matplotlib.pyplot as plt
import seaborn as sns

import os


plt.style.use('seaborn-v0_8-darkgrid')
# warnings ignore
import warnings
# set warnings to ignore
#warnings.filterwarnings('ignore')

pd.options.mode.chained_assignment = None  # default='warn'

pd.set_option('display.max_columns', None)

url = 'https://redfin-public-data.s3.us-west-2.amazonaws.com/redfin_market_tracker/zip_code_market_tracker.tsv000.gz'

# start time to read file
startTime = time.time()
df = pd.read_csv(url, compression='gzip', sep='\t', on_bad_lines='skip')
# end time
executionTime = (time.time() - startTime)
print('Execution time in minutes: ' + str(round(executionTime / 60, 2)))
print('Num of rows:', len(df))
print('Num of cols:', len(df.columns))
df.head()

# filter on state code
state_code = 'NY'
df_filter =df.loc[df['state_code'] == state_code]
print('Num of rows:', len(df_filter))
df_filter.head()

df_filter.describe()

geo_data_url = 'https://raw.githubusercontent.com/scpike/us-state-county-zip/master/geo-data.csv'
df_geo = pd.read_csv(geo_data_url)
print('Num of rows:' , len(df_geo))
print('Num of columns:' , len(df_geo.columns))
df_geo.head()

from IPython.display import Image, display
display(Image('https://www3.erie.gov/dhses/sites/www3.erie.gov.dhses/files/2022-06/psap_map.jpg'))

# filter on erie county
df_geo_county = df_geo.loc[(df_geo['state_abbr'] == 'NY') &
                           (df_geo['county'].isin(['Erie']))]

# create function to check if zip code is valid
df_geo_county['valid_zip_code'] = df_geo_county.apply(lambda x: x['zipcode'].isnumeric(), axis =1)
print('Dataset:')
print(df_geo_county.groupby(['valid_zip_code', 'county'])['zipcode'].count())
print(' ')
print('Invalid zip codes:', df_geo_county.loc[df_geo_county['valid_zip_code'] == False]['zipcode'].unique())

# filter on only valid zip codes
df_geo_county_valid = df_geo_county.loc[df_geo_county['valid_zip_code'] == True]
print('Num of valid zip codes:', len(df_geo_county_valid))
df_geo_county_valid.head(1)

# get zip code from region field
df_filter['zipcode'] = df_filter.apply(lambda x: x['region'].split(':')[1].strip(), axis=1)
# merge market data & geo zip codes
df_merge = pd.merge(
    df_filter,
    df_geo_county_valid[['zipcode', 'city', 'county']],
    how = 'inner', # only return zipcodes in both tables
    on = ['zipcode'] # column to join on
)

print('Num of rows:', len(df_merge))
df_merge.head()

# choose a zip code & property type
df_merge.loc[(df_merge['zipcode'] == '14213') &
             (df_merge['property_type'] == 'Single Family Residential')]\
             .sort_values(by=['period_begin']).tail(2)\
             [['period_begin', 'median_dom', 'median_dom_mom', 'median_dom_yoy']]

# test to correct the error
df_test = df_merge.copy()
df_test['median_dom_mom_correction'] = df_test.sort_values(by=['period_begin'])['median_dom'].pct_change()
df_test.loc[(df_test['zipcode'] == '14213') &
            (df_test['property_type'] == 'Single Family Residential')] \
            .sort_values(by=['period_begin']).tail(2)\
            [['period_begin', 'median_dom', 'median_dom_mom', 'median_dom_yoy', 'median_dom_mom_correction']]

# add features
df_features = df_merge.copy()
print('Max begin date:', df_features['period_begin'].max())
print('Max end date:', df_features['period_end'].max())
max_end_date = df_features['period_end'].max()

# flag the most recent date
df_features['latest_period'] = df_features.apply(
    lambda x: True if x['period_end'] == max_end_date else False, axis=1)

# remove Redfin city and keep Geo city
df_features = df_features.drop(columns=['city_x'])
df_features = df_features.rename(columns={'city_y': 'city'})

# fix median days on market
df_features['median_dom_mom'] = df_features.sort_values(by=['period_begin'])['median_dom'].pct_change()

df_features.head()

# Export new data set

date_str = datetime.today().strftime('%Y-%m')
print('Current year/month:', date_str)

from google.colab import files

# download file
df_features.to_csv('zip_realtor_{0}_{1}.csv'.format(state_code, date_str), index=False)
files.download('zip_realtor_{0}_{1}.csv'.format(state_code, date_str))