<jupyter_start><jupyter_text>Preprocessing Structured DataBefore we actually feed the data into any deep learning system we should look through it carefully. In addition to the kinds of big-picture problems that might arise in collecting data from a noisy world, we need to look out for missing values, strange outliers, and potential errors in the data. The data doesn't have to be completely error free, although obviously that would be best. Frequently, with the size of data we're dealing with, it is not realistic to completely scrub the data of any errors. Once we have a collection of data that has a tolerable amount of errors (ideally error free, though that does not HAVE to be the case) we have to transform it into a deep learning friendly format. There are a number of tricks that machine learning practitioners apply to get better results from the same data.It's also wise to explore the data and look for interesting outliers, correlation between different parts of the data, and other anomolies, oddities, and trends. Of course, we're hoping that our deep learning system can tease these out even better than we could—but that's not a good reason to shirk your own responsibility to understand the dataset. Sophisticated as they are, neural nets are still just tools, and understanding the data can help use hone our tools in the areas where they'll be most successful. For this lab we're going to use a public domain dataset from Kaggle. You can find the dataset here:https://www.kaggle.com/new-york-city/nyc-property-salesTo run this code you'll need to download and unzip that data.There is useful supporting information about this dataset as well at the following two URLs:https://www1.nyc.gov/assets/finance/downloads/pdf/07pdf/glossary_rsf071607.pdfhttps://www1.nyc.gov/assets/finance/jump/hlpbldgcode.htmlThe dataset is a record of every building/condo/appartment that was sold in New York City over a 12 month period.<jupyter_code># Pandas is a fantastic and powerful tool for working with structured data
# it's the best of spreadsheets + python, and it has quickly become a go to
# library for data scientists in python
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Your path may vary, change this appropriately
path_to_ny_sales = 'datasets/nyc-rolling-sales.csv'

# One of the things we love about pandas is that it's easy to load CSV data
# into a "data frame"
sales_df = pd.read_csv(path_to_ny_sales)

# And, it makes it easy to take a look at the first n items:
sales_df.head(5)
# And a summary with bundles of useful information on the numerical fields
sales_df.describe()
# Sometimes we get some unexpected datatypes when loading data
for col, dtype in zip(sales_df.columns, sales_df.dtypes):
    print(col, dtype)
# Look at these types... many are wrong or misleading. 

# The first two columns are ... kind of irrelelvant. The first is just the index for the data
# the second is named 'Unnamed: 0' which doesn't sound important. Lets look at it anyway: 

# We can check if there is ever a duplicate value in that column:
print(any(sales_df['Unnamed: 0'].duplicated()))

# And we can see which ones. This syntax often confuses Pandas newbs
# .duplicated() returns a parallel dataframe with one column set to
# True if the value in the 'Unnamed: 0' column is duplicated, false otherwise
# for every entry in the dataframe. Using the == comparison operator like this
# within the [] of a dataframe access acts as a filter.
multi_sale_units = sales_df[sales_df['Unnamed: 0'].duplicated() == True]

multi_sale_units
# So there were ~84,500 sales records, and 57,812 records where the 'Unnamed: 0' appeared 
# in more than one record. Lets look at ONE such value:
building_8413_records = sales_df[sales_df['Unnamed: 0'] == 8413]

building_8413_records
# Well... the duplicate values suggest it's not an ID, my first hypothosis. Lets plot a histogram
# and see if it's revealing.
sales_df.hist(column='Unnamed: 0', bins=100)
plt.show()
# weird. The data is undocumented, and has a strange distribution.
# Lets see if it correlates with anything?
sales_df.corrwith(sales_df['Unnamed: 0'])
# There is a very weak correlation with block and zip code... which is spurrious because those are both
# actually categorical columns, not numerical columns. Lets see if it has any correlation with what we
# CARE about specifically:
sales_df['SALE PRICE'].corr(sales_df['Unnamed: 0'])
# Uh oh — looks like we've got some problems in our sale amount data...
# Lets take a look:
sales_df['SALE PRICE']
# Looks like the data is a string type, and sometimes has a value of -
# The documentation suggests the - value means that there was no sale
# just a property transfer for nothing, such as an inheritance. 
# Lets try to coerce the data to numeric where possible:
coerced_sales = pd.to_numeric(sales_df['SALE PRICE'], errors='coerce')

# Values that cannot be coerced are changed to Not a Number (NaN). 
# We can use this code to examine those values:
only_non_numerics = sales_df['SALE PRICE'][coerced_sales.isna()]

# And this to print all the unique values from only_non_numerics
only_non_numerics.unique()
# So, indeed, the only value that wasn't a number as a string was the ' - ' value. 
# good to know. Lets go ahead and coerce them all
sales_df['SALE PRICE'] = pd.to_numeric(sales_df['SALE PRICE'], errors='coerce')
sales_df['SALE PRICE'] = sales_df['SALE PRICE'].fillna(0)
# Now we should be able to check the correlation we wanted to originally:
sales_df['SALE PRICE'].corr(sales_df['Unnamed: 0'])
# So... I'm going to go out on a limb and say 'Unnamed: 0' is a junk column. Lets delete it
# along with a few others that we don't want to use.
sales_df.columns
sales_df = sales_df.drop(columns=[
    'Unnamed: 0',
    'ADDRESS',          # Hard to parse. Block/zip/borough/neighborhood capture all the value we need. 
    'APARTMENT NUMBER', # Likely irrelevent to the price. Ought to be categorical, which would make data large.
    'SALE DATE',        # Everything was within a 12 month period, likely irrelevant and hard to parse.
    'LOT'               # A lot is a unique identifier within a block, and categorical. Not worth it. 
])

# Look again with dropped columns
sales_df.head(5)
sales_df.describe()
# Two other columns should be numeric, but are objects. Lets look at them too:
convert_to_numeric = [
    'LAND SQUARE FEET',
    'GROSS SQUARE FEET'
]

for col in convert_to_numeric:
    coerced = pd.to_numeric(sales_df[col], errors='coerce')
    only_non_numerics = sales_df[col][coerced.isna()]

    # And this to print all the unique values from only_non_numerics
    print(col, only_non_numerics.unique())
# So... similarly there are missing values. But, unlike the sale data, we don't have 
# any clues about what this means, and it's hard to imagine that a building exists with
# but occupies zero square feet... We'll apply another common tactic called "imputation"
# We're just going to use the mean value when there is missing data, it's better than nothing
# even though it may be wrong.
from sklearn.impute import SimpleImputer

# First lets just coerce the values to nan
for col in convert_to_numeric:
    coerced = pd.to_numeric(sales_df[col], errors='coerce')
    sales_df[col] = coerced
    sales_df[col] = sales_df[col].astype('float')
    
# Then, we can use the Imputer to fill in any missing values
imputer = SimpleImputer(missing_values = np.nan, strategy = 'mean') 

# Only fit it on our two relevant columns, to save time
imputer.fit(sales_df[convert_to_numeric])
imputed_values = imputer.transform(sales_df[convert_to_numeric])

# Now replace our old Series with the new imputed values. 
sales_df['LAND SQUARE FEET'] = imputed_values[:, 0]
sales_df['GROSS SQUARE FEET'] = imputed_values[:, 1]

sales_df.describe()
# One really cool and helpful thing we can do in pandas is checkout the correlation matrix:
correlation_matrix = sales_df.corr()
plt.matshow(correlation_matrix)
plt.xticks(range(len(correlation_matrix.columns)), correlation_matrix.columns, rotation='vertical');
plt.yticks(range(len(correlation_matrix.columns)), correlation_matrix.columns);
plt.show()
# We can see that everything perfectly correlates with itself, obviously. 
# Some of this is still spurious, since for example ZIP CODE seems to correlate
# weakly with SALE PRICE But it's actually a categorical value, not a numeric one. 

# Lets inform pandas that these values ought to be considered categorical.
categorical_columns = [
    'BOROUGH',
    'BLOCK',
    'ZIP CODE',
    'TAX CLASS AT TIME OF SALE'
]

for c in categorical_columns:
    sales_df[c] = sales_df[c].astype('category')
# Try the matrix again:
correlation_matrix = sales_df.corr()
plt.matshow(correlation_matrix)
plt.xticks(range(len(correlation_matrix.columns)), correlation_matrix.columns, rotation='vertical');
plt.yticks(range(len(correlation_matrix.columns)), correlation_matrix.columns);
plt.show()
# Not surprising that total units seems to correlate most with price. 
# Interesting that residential units seems more correlated than commercial

# What haven't we looked at...
sales_df.columns
sales_df['EASE-MENT'].unique()
# It only has one value, junk it. 
sales_df = sales_df.drop(columns=['EASE-MENT'])
# Lets plot two interesting charts:
for col, dtype in zip(sales_df.columns, sales_df.dtypes):
    if dtype not in ['float', 'int', 'float64', 'int64']: continue

    print(col)
    sales_df.boxplot(column=[col])
    sales_df.hist(column=[col])

    plt.tight_layout()
    plt.show()<jupyter_output>RESIDENTIAL UNITS<jupyter_text>Something to note about these charts is that, all of our numerical data seems to have a handful of extreme outliers. This might not be a challenge, because they are likely correlated. As in, the building with 1000+ units is probably also one of the sale price outliers. But it does sort of make the histograms unhelpful. We could consider pruning these outliers before going ahead with the rest of this data processing. Lets use some rough and tumble outlier detection code from Stack Overflow and replot.<jupyter_code>from scipy import stats
import numpy as np

# Lets plot two interesting charts:
for col, dtype in zip(sales_df.columns, sales_df.dtypes):
    if dtype not in ['float', 'int', 'float64', 'int64']: continue

    print(col)
    
    # Quick and dirty outlier filtering, anything over 2 std deviations from the mean 
    # filtered out. 
    filtered_col = sales_df[col][np.abs(stats.zscore(sales_df[col])) < 2]
    
    filtered_col.plot.box()
    plt.show()
    
    filtered_col.hist(bins=10)
    plt.show()<jupyter_output>RESIDENTIAL UNITS<jupyter_text>Cleaning vs PreparingWhat we've done above is mostly just cleaning the data. We looked for missing values, and did some spot/sanity checks on our data. We did one thing that you might consider preparing: making some columns categorical. In addition to make sure the data is clean and error free, it's common practice to prepare the data so that it plays nice with neural networks. Two common examples are centering the mean about 0, and normalize the range to be between (0 to 1) or (-1 to 1).Why? Consider this: year built, square feet, and total units are all going to impact the sale price. One of those might be more impactful than the other, but in the end our neural network is doing a bunch of complex addition and multiplication with those values, but year is always going to be in a range between basically 1900-2017, and units are almost always between 0-50 or so. 1900, when used as a multiplicitive scalar, is going to have a bigger impact than 50. For this, and other reasons, it's common to normalize the data so that every datapoint is reduced to it's place within the distribution and to center that distribution between -1 and 1 or 0 and 1. Lets normalize all our numeric values to be between 0 and 1. Note that there are other scaling choices we could make, see the reading resources for this section.<jupyter_code>from sklearn.preprocessing import MinMaxScaler

scaler = MinMaxScaler()

# Note that we are NOT going to scale "sale price" because
# ultimately that will be our target value. WE still need the
# label to be in the format we wish to predict. 
cols_to_scale = [
    'RESIDENTIAL UNITS',
    'COMMERCIAL UNITS',
    'TOTAL UNITS',
    'LAND SQUARE FEET',
    'GROSS SQUARE FEET',
    'YEAR BUILT'
]

scaled_cols = scaler.fit_transform(sales_df[cols_to_scale])

# Wow, was it really that easy?
scaled_cols
# So, we just got back an NDArray, and we need to put these
# columns back into a dataframe.
scaled_df = sales_df.copy(deep=True)

for i, col  in enumerate(cols_to_scale):
    scaled_df[col] = scaled_cols[:, i]

scaled_df.head(5)
scaled_df.describe()
# Even though we labeled some columns as "category" we still need to one-hot 
# encode them. Pandas makes this super easy too:
scaled_dummy_df = pd.get_dummies(scaled_df)
scaled_dummy_df.head(1)

# Note that this takes awhile, it's procecssing a lot of data. 
# Note also that pandas automatically looks for columns with
# a categorical type, so being explicit above was important 
# to making this part easy.
# Holy crap, 12,413 columns!

# Note that all our numeric columns are between 0 and 1, except SALE PRICE
# All that's left to do here is to separate the labels from the features.

x_train = scaled_dummy_df.drop(columns=['SALE PRICE'])
y_train = scaled_dummy_df['SALE PRICE']

x_train.head(1)
y_train.head(1)
# Sweet, lets make a simple neural net with keras to make sure we can run the data
# through it. We don't expect great predictions out of this simple model we just
# want to be sure that we can :
from tensorflow.keras.models import Sequential
from tensorflow.keras.layers import Dense



model = Sequential()

# Sigmoid and other functions that squash the output might not be
# very appropriate for this task, because our target values are 
# quite large!
print(len(x_train.columns))

model.add(Dense(units=32, activation='relu', input_shape=(len(x_train.columns),)))

# For regression it's common to use a linear activation function
# since our output could be anything. In our case, it would never
# make sense to guess less than 0, so I'm using relu
model.add(Dense(units=1, activation='relu'))

# This function provides useful text data for our network
model.summary()
# MSE is pretty common for regression tasks
model.compile(optimizer="adam", loss='mean_squared_error')
history = model.fit(x_train, y_train, batch_size=128, epochs=5, verbose=True, validation_split=.2)

plt.plot(history.history['loss'])
plt.plot(history.history['val_loss'])
plt.title('model loss')
plt.ylabel('loss')
plt.xlabel('epoch')
plt.legend(['training', 'validation'], loc='best')

plt.show()
# So... our model didn't do so great. Okay, it did terribly. 
# It's off by a lot and is clearly overfitting the training data.

# Why might we be getting such poor performance? 

# How could we improve?

# What should we do to the data?

# What about to the model?
# Two things I would look at:

# Consider discritizing and building a classifier instead of a regressor! 
  # Neural networks tend to be much better at classification tasks.
  # Plus, it's just easier to predict 1 of 10 values compared to a 
  # continious space $0-$100,000,000 or so

# Consider dropping the values with 0 SALE PRICE or any very low value
  # those are not representative of actual sale prices!<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup