# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: hcad_pred
#     language: python
#     name: hcad_pred
# ---

# + [markdown] papermill={"duration": 0.044661, "end_time": "2020-09-13T12:44:15.367203", "exception": false, "start_time": "2020-09-13T12:44:15.322542", "status": "completed"} tags=[]
# # Find the comparables: real_acc.txt
#
# The file `real_acc.txt` contains important property information like number total appraised value (the target on this exercise), neighborhood, school district, economic group, land value, and more. Let's load this file and grab a subset with the important columns to continue our study.

# + papermill={"duration": 0.049398, "end_time": "2020-09-13T12:44:15.447405", "exception": false, "start_time": "2020-09-13T12:44:15.398007", "status": "completed"} tags=[]
# %load_ext autoreload
# %autoreload 2

# + papermill={"duration": 0.478058, "end_time": "2020-09-13T12:44:15.973517", "exception": false, "start_time": "2020-09-13T12:44:15.495459", "status": "completed"} tags=[]
from pathlib import Path
import pickle

import numpy as np
import pandas as pd

from src.definitions import ROOT_DIR
from src.data.utils import Table, save_pickle

# + papermill={"duration": 0.053467, "end_time": "2020-09-13T12:44:16.058458", "exception": false, "start_time": "2020-09-13T12:44:16.004991", "status": "completed"} tags=[]
real_acct_fn = ROOT_DIR / 'data/external/2016/Real_acct_owner/real_acct.txt'
assert real_acct_fn.exists()

# + papermill={"duration": 0.047817, "end_time": "2020-09-13T12:44:16.165004", "exception": false, "start_time": "2020-09-13T12:44:16.117187", "status": "completed"} tags=[]
real_acct = Table(real_acct_fn, '2016')

# + papermill={"duration": 0.05578, "end_time": "2020-09-13T12:44:16.250909", "exception": false, "start_time": "2020-09-13T12:44:16.195129", "status": "completed"} tags=[]
real_acct.get_header()

# + [markdown] papermill={"duration": 0.030536, "end_time": "2020-09-13T12:44:16.336836", "exception": false, "start_time": "2020-09-13T12:44:16.306300", "status": "completed"} tags=[]
# # Load accounts and columns of interest
# Let's remove the account numbers that don't meet free-standing single-family home criteria that we found while processing the `building_res.txt` file.
#
# Also, the columns above show a lot of value information along property groups that might come in handy when predicting the appraised value. Now let's get a slice of some of the important columns.

# + papermill={"duration": 9.845532, "end_time": "2020-09-13T12:44:26.213431", "exception": false, "start_time": "2020-09-13T12:44:16.367899", "status": "completed"} tags=[]
skiprows = real_acct.get_skiprows()

# + papermill={"duration": 0.046552, "end_time": "2020-09-13T12:44:26.310241", "exception": false, "start_time": "2020-09-13T12:44:26.263689", "status": "completed"} tags=[]
cols = [
    'acct',
    'site_addr_3', # Zip
    'school_dist',
    'Neighborhood_Code',
    'Market_Area_1_Dscr',
    'Market_Area_2_Dscr',
    'center_code',
    'bld_ar',
    'land_ar',
    'acreage',
    'land_val',
    'tot_appr_val', # Target
    'prior_land_val',
    'prior_tot_appr_val',
    'new_own_dt',  # New owner date
]

# + papermill={"duration": 8.120532, "end_time": "2020-09-13T12:44:34.490774", "exception": false, "start_time": "2020-09-13T12:44:26.370242", "status": "completed"} tags=[]
real_acct_df = real_acct.get_df(skiprows=skiprows, usecols=cols)

# + papermill={"duration": 0.068312, "end_time": "2020-09-13T12:44:34.604541", "exception": false, "start_time": "2020-09-13T12:44:34.536229", "status": "completed"} tags=[]
real_acct_df.head()

# + [markdown] papermill={"duration": 0.032287, "end_time": "2020-09-13T12:44:34.694488", "exception": false, "start_time": "2020-09-13T12:44:34.662201", "status": "completed"} tags=[]
# Double check if the there is only one account number per row

# + papermill={"duration": 0.161511, "end_time": "2020-09-13T12:44:34.890245", "exception": false, "start_time": "2020-09-13T12:44:34.728734", "status": "completed"} tags=[]
assert real_acct_df['acct'].is_unique

# + [markdown] papermill={"duration": 0.045413, "end_time": "2020-09-13T12:44:34.970733", "exception": false, "start_time": "2020-09-13T12:44:34.925320", "status": "completed"} tags=[]
# # Describe and clean the columns
#
# Now we must describe each column by answering:
#
# * Meaning
# * Descriptive statistics or value counts
# * Data type
#
# There is no explicit document provided by HCAD explaining all the variables, but most are easy to guess for using their name.

# + [markdown] papermill={"duration": 0.031929, "end_time": "2020-09-13T12:44:35.036116", "exception": false, "start_time": "2020-09-13T12:44:35.004187", "status": "completed"} tags=[]
# ## Fix column names
# We would like the column names to be all lower case, with no spaces nor non-alphanumeric characters.

# + papermill={"duration": 0.054339, "end_time": "2020-09-13T12:44:35.122711", "exception": false, "start_time": "2020-09-13T12:44:35.068372", "status": "completed"} tags=[]
from src.data.utils import fix_column_names

# + papermill={"duration": 0.056537, "end_time": "2020-09-13T12:44:35.210342", "exception": false, "start_time": "2020-09-13T12:44:35.153805", "status": "completed"} tags=[]
real_acct_df.columns

# + papermill={"duration": 0.050233, "end_time": "2020-09-13T12:44:35.292064", "exception": false, "start_time": "2020-09-13T12:44:35.241831", "status": "completed"} tags=[]
real_acct_df = fix_column_names(real_acct_df)

# + papermill={"duration": 0.056703, "end_time": "2020-09-13T12:44:35.382049", "exception": false, "start_time": "2020-09-13T12:44:35.325346", "status": "completed"} tags=[]
real_acct_df.columns

# + [markdown] papermill={"duration": 0.031789, "end_time": "2020-09-13T12:44:35.445168", "exception": false, "start_time": "2020-09-13T12:44:35.413379", "status": "completed"} tags=[]
# ### Find duplicated rows

# + papermill={"duration": 1.618926, "end_time": "2020-09-13T12:44:37.096863", "exception": false, "start_time": "2020-09-13T12:44:35.477937", "status": "completed"} tags=[]
cond0 = real_acct_df.duplicated()
real_acct_df.loc[cond0, :]

# + [markdown] papermill={"duration": 0.034682, "end_time": "2020-09-13T12:44:37.167685", "exception": false, "start_time": "2020-09-13T12:44:37.133003", "status": "completed"} tags=[]
# ## Zip code: site_addr_3
# These are the property zip code. They should be non-zero integers. Most should start with 77.

# + papermill={"duration": 0.055076, "end_time": "2020-09-13T12:44:37.256889", "exception": false, "start_time": "2020-09-13T12:44:37.201813", "status": "completed"} tags=[]
# Let's change the column name
real_acct_df.rename({'site_addr_3': 'zip_code'}, axis=1, inplace=True)

# + papermill={"duration": 0.293237, "end_time": "2020-09-13T12:44:37.582175", "exception": false, "start_time": "2020-09-13T12:44:37.288938", "status": "completed"} tags=[]
# there were some empty zip code: ' '
real_acct_df['zip_code'] = real_acct_df['zip_code'].apply(lambda code: code if code.startswith('77') else np.nan )

# + papermill={"duration": 0.540348, "end_time": "2020-09-13T12:44:38.160290", "exception": false, "start_time": "2020-09-13T12:44:37.619942", "status": "completed"} tags=[]
real_acct_df['zip_code'] = pd.to_numeric(real_acct_df['zip_code'], downcast='unsigned')

# + papermill={"duration": 0.059985, "end_time": "2020-09-13T12:44:38.253380", "exception": false, "start_time": "2020-09-13T12:44:38.193395", "status": "completed"} tags=[]
real_acct_df['zip_code'].value_counts()

# + papermill={"duration": 0.088312, "end_time": "2020-09-13T12:44:38.374867", "exception": false, "start_time": "2020-09-13T12:44:38.286555", "status": "completed"} tags=[]
real_acct_df['zip_code'].describe()

# + papermill={"duration": 0.13045, "end_time": "2020-09-13T12:44:38.540407", "exception": false, "start_time": "2020-09-13T12:44:38.409957", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['zip_code'].isnull())}")

# + [markdown] papermill={"duration": 0.033488, "end_time": "2020-09-13T12:44:38.606904", "exception": false, "start_time": "2020-09-13T12:44:38.573416", "status": "completed"} tags=[]
# ## School district: school_dist
# The school district values come coded as two digit integers. There are a few numbers missing from the code sequence, i.e. 10, 11, 13, 14, and 22. This is consistent with the ISD codes in the `code_jur_list` table. Let's decode the school district values using this table, but first let's represent the missing data with NaNs.

# + papermill={"duration": 0.055, "end_time": "2020-09-13T12:44:38.696408", "exception": false, "start_time": "2020-09-13T12:44:38.641408", "status": "completed"} tags=[]
from src.data.utils import decode_isd

# + papermill={"duration": 0.059664, "end_time": "2020-09-13T12:44:38.789439", "exception": false, "start_time": "2020-09-13T12:44:38.729775", "status": "completed"} tags=[]
real_acct_df['school_dist'].head()

# + papermill={"duration": 1.690019, "end_time": "2020-09-13T12:44:40.543613", "exception": false, "start_time": "2020-09-13T12:44:38.853594", "status": "completed"} tags=[]
real_acct_df['school_dist'] = decode_isd(real_acct_df['school_dist'])

# + papermill={"duration": 0.148512, "end_time": "2020-09-13T12:44:40.741003", "exception": false, "start_time": "2020-09-13T12:44:40.592491", "status": "completed"} tags=[]
real_acct_df['school_dist'].value_counts(normalize=True)

# + papermill={"duration": 0.206561, "end_time": "2020-09-13T12:44:40.982137", "exception": false, "start_time": "2020-09-13T12:44:40.775576", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['school_dist'].isnull())}")

# + [markdown] papermill={"duration": 0.03551, "end_time": "2020-09-13T12:44:41.052232", "exception": false, "start_time": "2020-09-13T12:44:41.016722", "status": "completed"} tags=[]
# ## neighborhood_code
#
# From [Definition help](https://pdata.hcad.org/Desc/Definition_help.pdf) file:
# > Residential valuation neighborhoods are groups of comparable properties whose
# boundaries were developed based on location and similarity of property data
# characteristics. Each neighborhood in a school district has a unique identifier known as a
# residential valuation number. These neighborhood boundaries are maintained via an ongoing office and field review. As neighborhoods change, neighborhood lines are redrawn
# to reflect the changes, and maintain the homogeneity of the neighborhood. 

# + papermill={"duration": 0.050828, "end_time": "2020-09-13T12:44:41.139070", "exception": false, "start_time": "2020-09-13T12:44:41.088242", "status": "completed"} tags=[]
from src.data.utils import decode_nhood

# + papermill={"duration": 65.413156, "end_time": "2020-09-13T12:45:46.587627", "exception": false, "start_time": "2020-09-13T12:44:41.174471", "status": "completed"} tags=[]
real_acct_df['neighborhood_code'] = decode_nhood(real_acct_df['neighborhood_code'])

# + papermill={"duration": 0.172113, "end_time": "2020-09-13T12:45:46.799483", "exception": false, "start_time": "2020-09-13T12:45:46.627370", "status": "completed"} tags=[]
real_acct_df['neighborhood_code'].value_counts(normalize=True)

# + papermill={"duration": 0.208026, "end_time": "2020-09-13T12:45:47.043300", "exception": false, "start_time": "2020-09-13T12:45:46.835274", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['neighborhood_code'].isnull())}")

# + [markdown] papermill={"duration": 0.046268, "end_time": "2020-09-13T12:45:47.142657", "exception": false, "start_time": "2020-09-13T12:45:47.096389", "status": "completed"} tags=[]
# ## Market area description: market_area_1_dscr

# + papermill={"duration": 0.126174, "end_time": "2020-09-13T12:45:47.305823", "exception": false, "start_time": "2020-09-13T12:45:47.179649", "status": "completed"} tags=[]
real_acct_df['market_area_2_dscr'].value_counts(normalize=True)

# + papermill={"duration": 0.691673, "end_time": "2020-09-13T12:45:48.033733", "exception": false, "start_time": "2020-09-13T12:45:47.342060", "status": "completed"} tags=[]
cond0 = real_acct_df['market_area_2_dscr'].str.strip() == ''
real_acct_df.loc[cond0, :]

# + papermill={"duration": 0.167605, "end_time": "2020-09-13T12:45:48.241933", "exception": false, "start_time": "2020-09-13T12:45:48.074328", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['market_area_2_dscr'].isnull())}")

# + [markdown] papermill={"duration": 0.038843, "end_time": "2020-09-13T12:45:48.317854", "exception": false, "start_time": "2020-09-13T12:45:48.279011", "status": "completed"} tags=[]
# ## center_code
# From [Definition help](https://pdata.hcad.org/Desc/Definition_help.pdf) file:
# > The center code indicates the individual (HCAD employee or contractor) that is
# responsible for working and valuing the property account.

# + papermill={"duration": 0.150791, "end_time": "2020-09-13T12:45:48.507195", "exception": false, "start_time": "2020-09-13T12:45:48.356404", "status": "completed"} tags=[]
real_acct_df['center_code'].value_counts()

# + papermill={"duration": 0.240193, "end_time": "2020-09-13T12:45:48.805699", "exception": false, "start_time": "2020-09-13T12:45:48.565506", "status": "completed"} tags=[]
real_acct_df['center_code'] = real_acct_df['center_code'].apply(lambda x: np.nan if x.isspace() else x)

# + papermill={"duration": 0.174209, "end_time": "2020-09-13T12:45:49.017737", "exception": false, "start_time": "2020-09-13T12:45:48.843528", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['center_code'].isnull())}")

# + [markdown] papermill={"duration": 0.03893, "end_time": "2020-09-13T12:45:49.094634", "exception": false, "start_time": "2020-09-13T12:45:49.055704", "status": "completed"} tags=[]
# ## Building area: 'bld_ar'
# This seems to be the total area occupied by buildings in the parcel. It possibly includes non-livable buildings like barns, sheds, and other outbuildings.

# + papermill={"duration": 0.059701, "end_time": "2020-09-13T12:45:49.194765", "exception": false, "start_time": "2020-09-13T12:45:49.135064", "status": "completed"} tags=[]
from src.data.utils import fix_area_column

# + papermill={"duration": 0.572448, "end_time": "2020-09-13T12:45:49.805217", "exception": false, "start_time": "2020-09-13T12:45:49.232769", "status": "completed"} tags=[]
real_acct_df = fix_area_column(real_acct_df, 'bld_ar')

# + [markdown] papermill={"duration": 0.040751, "end_time": "2020-09-13T12:45:49.890849", "exception": false, "start_time": "2020-09-13T12:45:49.850098", "status": "completed"} tags=[]
# ## Land area: land_ar
# Land area in square feet.

# + papermill={"duration": 0.581363, "end_time": "2020-09-13T12:45:50.512369", "exception": false, "start_time": "2020-09-13T12:45:49.931006", "status": "completed"} tags=[]
real_acct_df = fix_area_column(real_acct_df, 'land_ar')

# + [markdown] papermill={"duration": 0.053683, "end_time": "2020-09-13T12:45:50.611618", "exception": false, "start_time": "2020-09-13T12:45:50.557935", "status": "completed"} tags=[]
# ## Land appraised value: land_val
# Appraised value of the land. It is represented in US dollars, should integers unless there are NaNs. 

# + papermill={"duration": 0.075326, "end_time": "2020-09-13T12:45:50.757982", "exception": false, "start_time": "2020-09-13T12:45:50.682656", "status": "completed"} tags=[]
real_acct_df['land_val'].head()

# + papermill={"duration": 0.171761, "end_time": "2020-09-13T12:45:50.970415", "exception": false, "start_time": "2020-09-13T12:45:50.798654", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['land_val'].isnull())}")

# + papermill={"duration": 0.110843, "end_time": "2020-09-13T12:45:51.121394", "exception": false, "start_time": "2020-09-13T12:45:51.010551", "status": "completed"} tags=[]
real_acct_df['land_val'].describe().apply(lambda x: format(x, 'f'))

# + [markdown] papermill={"duration": 0.042066, "end_time": "2020-09-13T12:45:51.213840", "exception": false, "start_time": "2020-09-13T12:45:51.171774", "status": "completed"} tags=[]
# ## Total appraised value: tot_appr_val
# This is the target to be predicted. It is represented in US dollars, should integers unless there are NaNs. 

# + papermill={"duration": 0.063069, "end_time": "2020-09-13T12:45:51.318916", "exception": false, "start_time": "2020-09-13T12:45:51.255847", "status": "completed"} tags=[]
from src.data.utils import fix_appraised_values

# + papermill={"duration": 0.214078, "end_time": "2020-09-13T12:45:51.572873", "exception": false, "start_time": "2020-09-13T12:45:51.358795", "status": "completed"} tags=[]
real_acct_df = fix_appraised_values(real_acct_df, 'tot_appr_val')

# + [markdown] papermill={"duration": 0.043013, "end_time": "2020-09-13T12:45:51.658697", "exception": false, "start_time": "2020-09-13T12:45:51.615684", "status": "completed"} tags=[]
# ## Prior land value: prior_land_val
# Last year's appraised land value. It is represented in US dollars, should integers unless there are NaNs.

# + papermill={"duration": 0.177925, "end_time": "2020-09-13T12:45:51.879632", "exception": false, "start_time": "2020-09-13T12:45:51.701707", "status": "completed"} tags=[]
real_acct_df = fix_appraised_values(real_acct_df, 'prior_land_val')

# + [markdown] papermill={"duration": 0.043062, "end_time": "2020-09-13T12:45:51.999154", "exception": false, "start_time": "2020-09-13T12:45:51.956092", "status": "completed"} tags=[]
# ## Prior total appraised value: prior_tot_appr_val
# Last year's total appraised land value. It is represented in US dollars, should integers unless there are NaNs.

# + papermill={"duration": 0.18162, "end_time": "2020-09-13T12:45:52.222373", "exception": false, "start_time": "2020-09-13T12:45:52.040753", "status": "completed"} tags=[]
real_acct_df = fix_appraised_values(real_acct_df, 'prior_tot_appr_val')

# + [markdown] papermill={"duration": 0.042673, "end_time": "2020-09-13T12:45:52.308034", "exception": false, "start_time": "2020-09-13T12:45:52.265361", "status": "completed"} tags=[]
# ## New owner date: new_own_dt
# When was the last recorded ownership changed on the property. Should be datetime type.

# + papermill={"duration": 0.261477, "end_time": "2020-09-13T12:45:52.611532", "exception": false, "start_time": "2020-09-13T12:45:52.350055", "status": "completed"} tags=[]
real_acct_df['new_own_dt'] = pd.to_datetime(real_acct_df['new_own_dt'])

# + papermill={"duration": 0.137212, "end_time": "2020-09-13T12:45:52.791843", "exception": false, "start_time": "2020-09-13T12:45:52.654631", "status": "completed"} tags=[]
print(f"The number of missing values is: {sum(real_acct_df['new_own_dt'].isnull())}")

# + papermill={"duration": 0.063468, "end_time": "2020-09-13T12:45:52.897043", "exception": false, "start_time": "2020-09-13T12:45:52.833575", "status": "completed"} tags=[]
real_acct_df['new_own_dt']

# + papermill={"duration": 0.098105, "end_time": "2020-09-13T12:45:53.044625", "exception": false, "start_time": "2020-09-13T12:45:52.946520", "status": "completed"} tags=[]
real_acct_df['new_own_dt'].describe()

# + [markdown] papermill={"duration": 0.043733, "end_time": "2020-09-13T12:45:53.132974", "exception": false, "start_time": "2020-09-13T12:45:53.089241", "status": "completed"} tags=[]
# # Export real_acct

# + papermill={"duration": 0.433891, "end_time": "2020-09-13T12:45:53.610211", "exception": false, "start_time": "2020-09-13T12:45:53.176320", "status": "completed"} tags=[]
save_fn = ROOT_DIR / 'data/raw/2016/real_acct_comps.pickle'
save_pickle(real_acct_df, save_fn)

StarcoderdataJupyterScriptsDedupFiltered