<jupyter_start><jupyter_text>Find the comparables: exterior.txtThe file `exterior.txt` contains important property information about the areas of the property sections. Let's load this file and grab a subset with the important columns to continue our study.<jupyter_code>%load_ext autoreload
%autoreload 2
from pathlib import Path
import pickle

import pandas as pd

from src.definitions import ROOT_DIR
from src.data.utils import Table, save_pickle
exterior_fn = ROOT_DIR / 'data/external/2016/Real_building_land/exterior.txt'
assert exterior_fn.exists()
exterior = Table(exterior_fn, '2016')<jupyter_output><empty_output><jupyter_text>Load accounts of interestLet'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.<jupyter_code>skiprows = exterior.get_skiprows()
exterior_df = exterior.get_df(skiprows=skiprows)
exterior_df.head()
exterior_df.sar_dscr.value_counts().head(20)<jupyter_output><empty_output><jupyter_text>Grab slice of the exterior features of interestWith the value counts on the exterior features description performed above we can see that the majority of the features land in the top 10 categories. Let's filter out the rests of the columns.<jupyter_code>cols = exterior_df.sar_dscr.value_counts().head(10).index
cond0 = exterior_df['sar_dscr'].isin(cols)
exterior_df = exterior_df.loc[cond0, :]<jupyter_output><empty_output><jupyter_text>Build pivot tableLet's build a pivot table with the account number (`acct`) as index, surface area `sar_dscr` as column, and `area` as values. Since the areas are already split by primary (PRI) and upper (UPR) I'll assume there should be only one exterior feature category per property, and thus aggregate multiple occurrences by taking the maximum value.<jupyter_code>exterior_pivot = exterior_df.pivot_table(index='acct',
                                         columns='sar_dscr',
                                         values='area',
                                         fill_value=0,
                                         aggfunc='max')
exterior_pivot.head()<jupyter_output><empty_output><jupyter_text>add `acct` column to make easier the merging process ahead<jupyter_code>exterior_pivot.reset_index(inplace=True)
assert exterior_pivot['acct'].is_unique<jupyter_output><empty_output><jupyter_text>Fix column namesWe would like the column names to be all lower case, with no spaces nor non-alphanumeric characters.<jupyter_code>from src.data.utils import fix_column_names
exterior_pivot.columns
exterior_pivot = fix_column_names(exterior_pivot)
exterior_pivot.columns<jupyter_output><empty_output><jupyter_text>Find duplicated rows<jupyter_code>cond0 = exterior_pivot.duplicated()
exterior_pivot.loc[cond0, :]<jupyter_output><empty_output><jupyter_text>Describe<jupyter_code>exterior_pivot.info()
exterior_pivot.describe()<jupyter_output><empty_output><jupyter_text>Export real_acct<jupyter_code>save_fn = ROOT_DIR / 'data/raw/2016/exterior_comps.pickle'
save_pickle(exterior_pivot, save_fn)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08055645422193465, 'fraction_numerical': 0.007764477515367195, 'mean_word_length': 5.482180293501048, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '193522', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 968, 'n_tokens_neox': 931, 'n_words': 245, 'path': 'notebooks/01_Exploratory/output/1.4-rp-hcad-data-view-exterior_20200913.ipynb', 'repo_name': 'RafaelPinto/hcad_pred', 'size': '3091'}