<jupyter_start><jupyter_text>**Table of Contents**Clean housing dataRead data from all CSV filesMerge all CSV into a single sheetFind if there are duplicatesClean column namesDrop unnecessary columnsFind and fill missing valuesExplore distribution of numeric columnsBuild statistical filters to remove outliers6 sigma filterIQR filterFilter columns using both filters and compareWrite cleaned, appended table to disk  Clean housing dataIn this notebook, we download housing data from a popular MLS website, merge, clean, filter and prepare it for subsequent spatial and statistical analysis.<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt

%matplotlib inline<jupyter_output><empty_output><jupyter_text>Read data from all CSV files<jupyter_code>csv1_path = 'resources/file1_2018-09-20-14-42-11_4k.csv'
csv2_path = 'resources/file2_2018-09-20-15-04-20_g.csv'
csv3_path = 'resources/file3_2018-09-20-16-02-01_b.csv'

prop_df1 = pd.read_csv(csv1_path)
prop_df2 = pd.read_csv(csv2_path)
prop_df3 = pd.read_csv(csv3_path)
prop_df1.head(3)
(prop_df1.shape, prop_df2.shape, prop_df3.shape)<jupyter_output><empty_output><jupyter_text>Merge all CSV into a single sheet<jupyter_code>prop_df = prop_df1.append(prop_df2)
prop_df = prop_df.append(prop_df3)
prop_df.shape<jupyter_output><empty_output><jupyter_text>Find if there are duplicates<jupyter_code>prop_dup_index = prop_df.duplicated(['MLS#'])
prop_dup = prop_df[prop_dup_index]
prop_dup.shape
prop_df.drop_duplicates(subset=['MLS#'], inplace=True)
prop_df.shape<jupyter_output><empty_output><jupyter_text>Clean column namesColumn names contain illegal characters. Let us rename them<jupyter_code>prop_df.columns
prop_df.rename(index=str, columns={'$/SQUARE FEET':'PRICE PER SQFT',
                                  'HOA/MONTH':'HOA PER MONTH',
                                  'URL (SEE http://www.redfin.com/buy-a-home/comparative-market-analysis FOR INFO ON PRICING)':'URL',
                                  'MLS#':'MLS'}, inplace=True)
prop_df.columns<jupyter_output><empty_output><jupyter_text>Drop unnecessary columnsWhile it is beneficial to have a lot of features (columns) in our dataset, it is also important to drop those that are highly correlated to an existing field or a derivative of one or fields that we will never use. In the following, we drop fields that we will never use in this analysis.<jupyter_code>prop_df.drop(columns=['SOLD DATE','NEXT OPEN HOUSE START TIME', 'NEXT OPEN HOUSE END TIME',
                     'FAVORITE', 'INTERESTED'], inplace=True)
prop_df.columns<jupyter_output><empty_output><jupyter_text>Find and fill missing valuesMissing values in certain columns are critical - `beds`, `bath`, `price`, `latitude`, `longitude`. We will drop these rows. Missing values in remaining columns are not so critical, we will fill them with `0` or average values.Before we fill, let us explore the histograms of numerical columns. With pandas, this can be accomplished with a single command as shown below:<jupyter_code># explore distribution of numeric columns
ax_list = prop_df.hist(bins=25, layout=(4,4), figsize=(15,15))
# drop rows with missing values in critical columns
prop_df_nona = prop_df.dropna(axis=0, how='any', # if any of these cols are empty, remove row
                              subset=['BEDS','BATHS', 'PRICE', 'YEAR BUILT', 'LATITUDE','LONGITUDE'])
prop_df_nona.shape<jupyter_output><empty_output><jupyter_text>Let us impute for missing values using different strategies for different columns.<jupyter_code>prop_df_nona['HOA PER MONTH'].fillna(value=0, inplace=True)
prop_df_nona['LOT SIZE'].fillna(value=prop_df_nona['LOT SIZE'].median(), inplace=True)
prop_df_nona['PRICE PER SQFT'].fillna(value=prop_df_nona['PRICE PER SQFT'].median(), inplace=True)
prop_df_nona['SQUARE FEET'].fillna(value=prop_df_nona['SQUARE FEET'].median(), inplace=True)
prop_df_nona['YEAR BUILT'].fillna(value=prop_df_nona['YEAR BUILT'].mode(), inplace=True)
prop_df_nona['ZIP'].fillna(value=prop_df_nona['SQUARE FEET'].mode(), inplace=True)
# explore distribution of numeric columns
ax_list = prop_df_nona.hist(bins=25, layout=(4,4), figsize=(15,15))<jupyter_output><empty_output><jupyter_text>Explore distribution of numeric columns As can be seen from histogram, some numeric columns are heavily sqewed by outliers. Let us pull up statistics for each of these columns<jupyter_code>prop_df_nona.describe().round(3)<jupyter_output><empty_output><jupyter_text>Build statistical filters to remove outliersIn this segment, we build $6\sigma$ and Inter Quartile Range (`IQR`) filters to remove outliers from our dataset. 6 sigma filter![](https://cdn-images-1.medium.com/max/800/1*3tcjd64JtWcl37MSidL7Cg.png)Statistically, about `99.5%` of data falls within $\pm 3 \sigma$ from $\mu$ (mean). Thus, for fairly normally distributed data, $6\sigma$ filter yields a good resultant dataset.  IQR filter![](https://cdn-images-1.medium.com/max/800/1*igFam1Zx-r4boy1T_5LKlw.png)Howerver, certain columns such as `BEDS`, `LOT SIZE` are heavily sqewed. For such, the Inter Quartile Range filter (which uses median as a measure of centrality unlike 6sigma which uses mean) yields a robust filter as it is unaffected by outliers like the 6 sigma filter.<jupyter_code>def six_sigma_filter(df, column):
    sigma = df[column].std()
    mu = df[column].mean()
    three_sigma = [mu-(3*sigma), mu+(3*sigma)]
    print("Column:{}, Mean:{}, Sigma:{}, 3sigma_range: {}:{}".format(column,mu.round(3),
                                                                       sigma.round(3),
                                                                       three_sigma[0].round(2),
                                                                       three_sigma[1].round(2)))
    
    # filter
    df_to_keep = df[(df[column] > three_sigma[0]) & (df[column] < three_sigma[1])]
    
    # prints
    num_rows_dropped = prop_df.shape[0] - df_to_keep.shape[0]
    print("Number of rows dropped: " + str(num_rows_dropped))
    
    return df_to_keep
def iqr_filter(df, column):
    med = df[column].median()
    p_25 = df[column].quantile(q=0.25)
    p_75 = df[column].quantile(q=0.75)
    
    # find valid range
    iqr_range = [med-(2*p_25), med+(2*p_75)]
    print("Column: {}, Median:{}, 25%:{}, 75%:{}, IQR:{}:{}".format(column,med,
                                                                    p_25,p_75,
                                                                    iqr_range[0].round(2),
                                                                    iqr_range[1].round(2)))
    
    # filter
    df_to_keep = df[(df[column] > iqr_range[0]) & (df[column] < iqr_range[1])]
    
    #prints
    num_rows_dropped = prop_df.shape[0] - df_to_keep.shape[0]
    print("Number of rows dropped: " + str(num_rows_dropped))
    
    return df_to_keep<jupyter_output><empty_output><jupyter_text>Filter columns using both filters and compare<jupyter_code>prop_df2 = six_sigma_filter(prop_df_nona, 'BATHS')
prop_df2_iqr = iqr_filter(prop_df_nona, 'BATHS')
prop_df4 = six_sigma_filter(prop_df2, 'BEDS')
prop_df4_iqr = iqr_filter(prop_df2_iqr, 'BEDS')
(prop_df4.shape, prop_df4_iqr.shape)
ax_list = prop_df4.hist(bins=25, layout=(4,4), figsize=(15,15))
ax_list = prop_df4_iqr.hist(bins=25, layout=(4,4), figsize=(15,15))<jupyter_output><empty_output><jupyter_text>Write cleaned, appended table to disk The `IQR` filter yields a better result in our case as the resulting histograms of numeric columns show a nice normal distribution. We proceed with this dataset and write that to disk.<jupyter_code>prop_df4_iqr.to_csv('resources/houses_for_sale_filtered.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1107924921793535, 'fraction_numerical': 0.02411366006256517, 'mean_word_length': 4.044707429322814, 'pattern_counts': {'":': 0, '<': 51, '<?xml version=': 0, '>': 51, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 20, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '436133', 'license': 'Apache-2.0', 'n_tokens_mistral': 2627, 'n_tokens_neox': 2465, 'n_words': 628, 'path': 'talks/GeoDevPDX2018/01_clean-housing-data.ipynb', 'repo_name': 'nitz21/arcpy', 'size': '7672'}