<jupyter_start><jupyter_text>We will take zipcodes and cluster them into neighborhoods.<jupyter_code>from google.colab import files
uploaded = files.upload()
import pandas as pd
import numpy as np
import matplotlib
import seaborn as sb
import matplotlib.pyplot as plt
import matplotlib.cm as cmx
import matplotlib.colors as colors
import math
%matplotlib inline
cols = [
    'id',
    'host_id',
    'zipcode',
    'property_type',
    'room_type',
    'accommodates',
    'bedrooms',
    'beds',
    'bed_type',
    'price',
    'number_of_reviews',
    'review_scores_rating',
    'host_listing_count',
    'availability_30',
    'minimum_nights',
    'bathrooms'
]

data = pd.read_csv('listings.csv', usecols=cols)
# remove NaN values from dataframe
data = data.dropna(how='any', subset=['zipcode', 'property_type', 'bedrooms', 'beds', 'bathrooms'])

# convert formatting for price
data['price'] = (data['price'].str.replace(r'[^-+\d.]', '').astype(float))

# drop any inconsistent values
data = data[data['accommodates'] != 0]
data = data[data['bedrooms'] != 0]
data = data[data['beds'] != 0]
data = data[data['price'] != 0.00]

# convert ZipCode
data['zipcode'] = data['zipcode'].str.replace(r'-\d+', '')
# turn NaN scores with 0 reviews into 'No Reviews'
idx_vals = data['review_scores_rating'][data['number_of_reviews'] == 0].index.values.tolist()
data.loc[idx_vals, ('review_scores_rating')] = data['review_scores_rating'][data['number_of_reviews'] == 0].replace('NaN', 'No Reviews')

# remove inconsistent NaN values
data = data[~data['review_scores_rating'].isnull()]
# convert review_scores_rating into buckets
def convert_scores_buckets(val):
    if val == 'No Reviews':
        return 'No Reviews'
    elif val >= 95.0:
        return '95-100'
    elif val >= 90.0 and val < 95.0:
        return '90-94'
    elif val >= 85.0 and val < 90.0:
        return '85-89'
    elif val >= 80.0 and val < 85.0:
        return '80-84'
    elif val >= 70.0 and val < 80.0:
        return '70-79'
    elif val >= 60.0 and val < 70.0:
        return '60-69'
    elif val >= 50.0 and val < 60.0:
        return '50-59'
    elif val >= 40.0 and val < 50.0:
        return '40-49'
    elif val >= 30.0 and val < 40.0:
        return '30-39'
    elif val >= 20.0 and val < 30.0:
        return '20-29'
    elif val >= 10.0 and val < 20.0:
        return '10-19'
    elif val < 10.0:
        return '0-9'
    
data['review_scores_rating'] = data['review_scores_rating'].apply(convert_scores_buckets)
# convert zipcodes into neighbrohoods

dict = {
    # Bronx Neighborhoods
    '10453': 'Central Bronx',
    '10457': 'Central Bronx',
    '10460': 'Central Bronx',
    '10458': 'Bronx Park and Fordham',
    '10467': 'Bronx Park and Fordham',
    '10468': 'Bronx Park and Fordham',
    '10451': 'High Bridge and Morrisania',
    '10452': 'High Bridge and Morrisania',
    '10456': 'High Bridge and Morrisania',
    '10454': 'Hunts Point and Mott Haven',
    '10455': 'Hunts Point and Mott Haven',
    '10459': 'Hunts Point and Mott Haven',
    '10474': 'Hunts Point and Mott Haven',
    '10045': 'Hunts Point and Mott Haven',
    '10463': 'Kingsbridge and Riverdale',
    '10471': 'Kingsbridge and Riverdale',
    '10466': 'Northeast Bronx',
    '10469': 'Northeast Bronx',
    '10470': 'Northeast Bronx',
    '10475': 'Northeast Bronx',
    '10704': 'Northeast Bronx',
    '10461': 'Southeast Bronx',
    '10462': 'Southeast Bronx',
    '10464': 'Southeast Bronx',
    '10465': 'Southeast Bronx',
    '10472': 'Southeast Bronx',
    '10473': 'Southeast Bronx',
    # Brooklyn Neighborhoods
    '11212': 'Central Brooklyn',
    '11213': 'Central Brooklyn',
    '11216': 'Central Brooklyn',
    '11233': 'Central Brooklyn',
    '11238': 'Central Brooklyn',
    '11209': 'Southwest Brooklyn',
    '11214': 'Southwest Brooklyn',
    '11228': 'Southwest Brooklyn',
    '11204': 'Borough Park',
    '11218': 'Borough Park',
    '11219': 'Borough Park',
    '11230': 'Borough Park',
    '11234': 'Canarsie and Flatlands',
    '11236': 'Canarsie and Flatlands',
    '11239': 'Canarsie and Flatlands',
    '11223': 'Southern Brooklyn',
    '11224': 'Southern Brooklyn',
    '11229': 'Southern Brooklyn',
    '11235': 'Southern Brooklyn',
    '11201': 'Northwest Brooklyn',
    '11205': 'Northwest Brooklyn',
    '11215': 'Northwest Brooklyn',
    '11217': 'Northwest Brooklyn',
    '11231': 'Northwest Brooklyn',
    '11203': 'Flatbush',
    '11210': 'Flatbush',
    '11225': 'Flatbush',
    '11226': 'Flatbush',
    '11126': 'Flatbush',
    '11207': 'East New York and New Lots',
    '11208': 'East New York and New Lots',
    '11211': 'Greenpoint',
    '11222': 'Greenpoint',
    '11220': 'Sunset Park',
    '11232': 'Sunset Park',
    '11206': 'Bushwick and Williamsburg',
    '11221': 'Bushwick and Williamsburg',
    '11237': 'Bushwick and Williamsburg',
    '11249': 'Bushwick and Williamsburg',
    # Staten Island Neighborhoods
    '10302': 'Port Richmond',
    '10303': 'Port Richmond',
    '10310': 'Port Richmond',
    '10306': 'South Shore',
    '10307': 'South Shore',
    '10308': 'South Shore',
    '10309': 'South Shore',
    '10312': 'South Shore',
    '10301': 'Stapleton and St. George',
    '10304': 'Stapleton and St. George',
    '10305': 'Stapleton and St. George',
    '10314': 'Mid-Island',
    # Manhattan Neighborhoods
    '10026': 'Central Harlem',
    '10027': 'Central Harlem',
    '10030': 'Central Harlem',
    '10037': 'Central Harlem',
    '10039': 'Central Harlem',
    '10001': 'Chelsea and Clinton',
    '10011': 'Chelsea and Clinton',
    '10018': 'Chelsea and Clinton',
    '10019': 'Chelsea and Clinton',
    '10020': 'Chelsea and Clinton',
    '10036': 'Chelsea and Clinton',
    '1001': 'Chelsea and Clinton',
    '10029': 'East Harlem',
    '10035': 'East Harlem',
    '10010': 'Gramercy Park and Murray Hill',
    '10016': 'Gramercy Park and Murray Hill',
    '10017': 'Gramercy Park and Murray Hill',
    '10022': 'Gramercy Park and Murray Hill',
    '10012': 'Greenwich Village and Soho',
    '10013': 'Greenwich Village and Soho',
    '10014': 'Greenwich Village and Soho',
    '10004': 'Lower Manhattan',
    '10005': 'Lower Manhattan',
    '10006': 'Lower Manhattan',
    '10007': 'Lower Manhattan',
    '10038': 'Lower Manhattan',
    '10280': 'Lower Manhattan',
    '10282': 'Lower Manhattan',
    '10080': 'Lower Manhattan',
    '10281': 'Lower Manhattan',
    '10002': 'Lower East Side',
    '10003': 'Lower East Side',
    '10009': 'Lower East Side',
    '10021': 'Upper East Side',
    '10028': 'Upper East Side',
    '10044': 'Upper East Side',
    '10065': 'Upper East Side',
    '10075': 'Upper East Side',
    '10128': 'Upper East Side',
    '10162': 'Upper East Side',
    '8456422473 call for more details': 'Upper East Side',
    '10023': 'Upper West Side',
    '10024': 'Upper West Side',
    '10025': 'Upper West Side',
    '10069': 'Upper West Side',
    '14072': 'Upper West Side',
    '10031': 'Inwood and Washington Heights',
    '10032': 'Inwood and Washington Heights',
    '10033': 'Inwood and Washington Heights',
    '10034': 'Inwood and Washington Heights',
    '10040': 'Inwood and Washington Heights',
    # Queens Neighborhoods
    '11361': 'Northeast Queens',
    '11362': 'Northeast Queens',
    '11363': 'Northeast Queens',
    '11364': 'Northeast Queens',
    '11354': 'North Queens',
    '11355': 'North Queens',
    '11356': 'North Queens',
    '11357': 'North Queens',
    '11358': 'North Queens',
    '11359': 'North Queens',
    '11360': 'North Queens',
    '11365': 'Central Queens',
    '11366': 'Central Queens',
    '11367': 'Central Queens',
    '11412': 'Jamaica',
    '11423': 'Jamaica',
    '11432': 'Jamaica',
    '11433': 'Jamaica',
    '11434': 'Jamaica',
    '11435': 'Jamaica',
    '11436': 'Jamaica',
    '11101': 'Northwest Queens',
    '11102': 'Northwest Queens',
    '11103': 'Northwest Queens',
    '11104': 'Northwest Queens',
    '11105': 'Northwest Queens',
    '11106': 'Northwest Queens',
    '111006': 'Northwest Queens',
    '11109': 'Northwest Queens',
    '11374': 'West Central Queens',
    '11375': 'West Central Queens',
    '11379': 'West Central Queens',
    '11385': 'West Central Queens',
    '11691': 'Rockaways',
    '11692': 'Rockaways',
    '11693': 'Rockaways',
    '11694': 'Rockaways',
    '11695': 'Rockaways',
    '11697': 'Rockaways',
    '11004': 'Southeast Queens',
    '11005': 'Southeast Queens',
    '11411': 'Southeast Queens',
    '11413': 'Southeast Queens',
    '11422': 'Southeast Queens',
    '11426': 'Southeast Queens',
    '11427': 'Southeast Queens',
    '11428': 'Southeast Queens',
    '11429': 'Southeast Queens',
    '11414': 'Southwest Queens',
    '11415': 'Southwest Queens',
    '11416': 'Southwest Queens',
    '11417': 'Southwest Queens',
    '11418': 'Southwest Queens',
    '11419': 'Southwest Queens',
    '11420': 'Southwest Queens',
    '11421': 'Southwest Queens',
    '11368': 'West Queens',
    '11369': 'West Queens',
    '11370': 'West Queens',
    '11372': 'West Queens',
    '11373': 'West Queens',
    '11377': 'West Queens',
    '11378': 'West Queens',
}

def convert_zips_hoods(val):
     return dict[val]

data['neighborhood'] = data['zipcode'].apply(convert_zips_hoods)
data = data.drop('zipcode', 1)
# encode categorical variables
neighborhood_dummies = pd.get_dummies(data['neighborhood'])
property_dummies = pd.get_dummies(data['property_type'])
room_dummies = pd.get_dummies(data['room_type'])
bed_dummies = pd.get_dummies(data['bed_type'])
ratings_scores_dummies = pd.get_dummies(data['review_scores_rating'])

# replace the old columns with our new one-hot encoded ones
df = pd.concat((data.drop(['neighborhood', \
    'property_type', 'room_type', 'bed_type', 'review_scores_rating'], axis=1), \
    neighborhood_dummies.astype(str), property_dummies.astype(int), \
    room_dummies.astype(int), bed_dummies.astype(int), ratings_scores_dummies.astype(int)), \
    axis=1)

# move target predictor 'price' to the end of the dataframe
cols = list(df.columns.values)
idx = cols.index('price')
rearrange_cols = cols[:idx] + cols[idx+1:] + [cols[idx]]
df = df[rearrange_cols]
# convert non-categorical variables to floats and standardize
def standardize_col(col):
    mean = np.mean(col)
    std = np.std(col)
    return col.apply(lambda x: (x - mean) / std)

non_cat_vars = ['accommodates', 'bedrooms', 'beds', 'number_of_reviews', 'host_listing_count', 'availability_30', 'minimum_nights', 'bathrooms']
for col in non_cat_vars:
    df[col] = df[col].astype(float)
    df[col] = standardize_col(df[col])
    
df.head()
# log transform the response 'price'
df['price_log'] = df['price'].apply(lambda x: math.log(x))<jupyter_output><empty_output><jupyter_text>This data set is different than the original because it now has neighbourhood booleans as oppsed to zipcode booleans<jupyter_code># read to csv
df.to_csv('listings_neighborhood_clean.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1645662100456621, 'fraction_numerical': 0.1, 'mean_word_length': 3.7448006932409013, 'pattern_counts': {'":': 0, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '316563', 'license': 'MIT', 'n_tokens_mistral': 4605, 'n_tokens_neox': 3789, 'n_words': 1046, 'path': 'notebooks/Cleaning/optimal_airbnb_zipcode_clusters.ipynb', 'repo_name': 'optimal-airbnb/ds', 'size': '10950'}