<jupyter_start><jupyter_text>Importing Packages<jupyter_code>! pip install geopandas
import param
import panel as pn

def pn_repr(self, include=None, exclude=None):
    pn.extension()
    return pn.viewable.Viewable._repr_mimebundle_(self, include, exclude)
pn.layout.Panel._repr_mimebundle_ = pn_repr
import pandas as pd
from datetime import datetime
import numpy as np
import geopandas as gpd

# settings to display all columns
pd.set_option("display.max_columns", None)
pd.set_option("display.max_rows", 20)

pn.extension(comms='colab')<jupyter_output><empty_output><jupyter_text>Importing Dataset<jupyter_code>df = pd.read_csv('/content/drive/MyDrive/Datasets/NYC Property Sales/df_2016_20.csv', index_col=0, low_memory=False)
df.head()
df = df.reset_index(drop=True)
df.info()
df.columns=['boro', 'NTA', 'bldg_cl_cat', 'tax_cl_pres', 'block', 'lot', 'easement', 'bldg_cl_pres', 'address', 'apt_no', 'zip', 'res_un', 'com_un', 'total_un', 'land_sf', 'gross_sf', 'yr_blt', 'tax_cl_TOS', 'bldg_cl_TOS', 'sale_price', 'sale_date']
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Sale Date<jupyter_code>df.sale_date.isna().sum()
df.sale_date = df.sale_date.astype(str)
df.sale_date = pd.to_datetime(df.sale_date, errors='coerce')
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Year Built<jupyter_code>df.yr_blt.isna().sum()
df.yr_blt = df.yr_blt.astype(str)
df.yr_blt = df.yr_blt.replace({'nan': '0'})
df.yr_blt = df.yr_blt.apply(lambda x: int(float(x)))
df.yr_blt = pd.to_datetime(df.yr_blt, format='%Y', errors='coerce')
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Zip Code<jupyter_code>df.zip.isna().sum()
df.zip = df.zip.replace({np.nan: 0})
df.zip = df.zip.apply(lambda x: int(x))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Building Class<jupyter_code>bldg_class = pd.read_excel('/content/drive/MyDrive/Datasets/NYC Property Sales/keys/building_code_nyc.xlsx')
bldg_class
list(set(df.bldg_cl_pres.values))
df.bldg_cl_pres.isna().sum()
# replace ' ', '  ', and nan
df.bldg_cl_pres = df.bldg_cl_pres.replace({' ': '', '  ': '', np.nan: ''})
df.bldg_cl_pres.value_counts()
list(set(df.bldg_cl_TOS.values))
df.bldg_cl_TOS.isna().sum()
df.bldg_cl_TOS = df.bldg_cl_TOS.replace({np.nan: ''})<jupyter_output><empty_output><jupyter_text>Building Class Categories<jupyter_code>df.bldg_cl_cat.value_counts()
df.bldg_cl_cat = df.bldg_cl_cat.apply(lambda x: str(x).title())
df.bldg_cl_cat.value_counts()
# df['bl_cl_cat_no'] = df.bldg_cl_cat.apply(lambda x: x[:2])
# df.head()
df.bldg_cl_cat = df.bldg_cl_cat.apply(lambda x: x.strip())
df.bldg_cl_cat.value_counts()
rows = ['05', '06', '07', '08', '11', '11A', '14', '16', '18', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49']
for row in rows:
    df.drop(index=df[df.bldg_cl_cat.str.startswith(row)].index, inplace=True)

df.bldg_cl_cat.value_counts()
list(set(df.bldg_cl_cat.values))
df.bldg_cl_cat = df.bldg_cl_cat.replace({'01  One Family Dwellings': '01 One Family Dwellings',
                                        '01  One Family Homes': '01 One Family Dwellings',
                                        '01 One Family Homes': '01 One Family Dwellings',
                                        '02  Two Family Dwellings': '02 Two Family Dwellings',
                                        '02  Two Family Homes': '02 Two Family Dwellings',
                                        '02 Two Family Homes': '02 Two Family Dwellings',
                                        '03  Three Family Dwellings': '03 Three Family Dwellings',
                                        '03  Three Family Homes': '03 Three Family Dwellings',
                                        '03 Three Family Homes': '03 Three Family Dwellings',
                                        '04  Tax Class 1 Condos': '04 Tax Class 1 Condos',
                                        '09  Coops - Walkup Apartments': '09 Coops - Walkup Apartments',
                                        '10  Coops - Elevator Apartments': '10 Coops - Elevator Apartments',
                                        '12  Condos - Walkup Apartments': '12 Condos - Walkup Apartments',
                                        '13  Condos - Elevator Apartments': '13 Condos - Elevator Apartments',
                                        '15  Condos - 2-10 Unit Residential': '15 Condos - 2-10 Unit Residential',
                                        '17  Condo Coops': '17 Condo Coops',
                                        '17  Condops': '17 Condo Coops',
                                        '17 Condops': '17 Condo Coops',
                                        'Nan': ''
                                        })
bldg_cl_cat_keys = {'01':  'One Family Dwellings',
                    '02':  'Two Family Dwellings',
                    '03':  'Three Family Dwellings',
                    '04':  'Tax Class 1 Condos',
                    '09':  'Coops - Walkup Apartments',
                    '10':  'Coops - Elevator Apartments',
                    '12':  'Condos - Walkup Apartments',
                    '13':  'Condos - Elevator Apartments',
                    '15':  'Condos - 2-10 Unit Residential',
                    '17':  'Condo Coops'
                    }

df = df.reset_index(drop=True)<jupyter_output><empty_output><jupyter_text>BBL (Borough-Block-Lot)- **Parcel represented by BBL can be:**    - one building, i.e. Empire State at 350 Fifth Ave.    - multiple buildings, i.e. Stuyvesant Town with BBL 1009720001 and many addresses incl. 240 First Ave.    - vacant lot or under-construction development site, i.e. 1241 Broadway    - in instance of Condominium building, i.e. 56 Leonard St., just portion of building    - to find BBL for city parcel, navigate to GIS site at http://maps.nyc.gov/doitt/nycitymap/- **10-digit unique property identifier:**    - first digit is numerical representation (1 to 5) of borough    - next five digits are city Block    - last four digits are Lot    - Often borough, block and lot written out like this: Manhattan, Block 972, Lot 1, but 10-digit number is 1009720001    - Leading zeroes added so that Block is five digits and lot is four digits: Manhattan, Block 00972, Lot 0001- **Borough Code:**    - 1: Manhattan, MN, New York County    - 2: Bronx, BX, Bronx County    - 3: Brooklyn, BK, Kings County    - 4: Queens, QN, Queens County    - 5: Staten Island, SI, Richmond County- **Block Code:**    - generally unique to actual city block, but many exceptions    - some Block numbers used over two adjacent city blocks, and few extend for multiple blocks, for example Brooklyn’s Block 7405    - block numbers generally start small at base of Manhattan and get larger as they head north    - in Brooklyn they follow similar pattern starting from Brooklyn Heights heading east and south    - anomaly is Battery Park City, which is on one tax block, 16, with many individually owned properties as well as condominium and cooperative buildings (the land is owned by the state and leased)    - Brooklyn Navy Yard is also one tax block and lot- **Lot Number:**    - condo lot number generally chronologically sequential starting with 7501, then 7502, etc. depending on number of condo buildings within tax Block    - for purposes of city, condo building simply building divided into multiple condominium tax lots, which can be retail, office, industrial or parking condo units, in addition to residential units  Borough<jupyter_code>df.boro.value_counts()
df.boro.isna().sum()
df.boro = df.boro.astype(str)
df.boro = df.boro.replace({'nan': '0'})
df.boro = df.boro.apply(lambda x: int(float(x)))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Block- tax block is sub-division of borough on which real properties are located- Dept. Finance uses BBL classification to label all real property in city- block and lot distinguished one unit of real property from another, such as different condominiums in single building- block and lots not subject to name changes based on whcih side of parcel building puts its entrance on<jupyter_code>df.block.isna().sum()
df.block = df.block.astype(str)
df.block = df.block.replace({'nan': '0'})
df.block = df.block.apply(lambda x: int(float(x)))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Lot- tax lot is subdivision of tax block and represents property unique location<jupyter_code>df.lot.isna().sum()
df.lot = df.lot.astype(str)
df.lot = df.lot.replace({'nan': '0'})
df.lot = df.lot.apply(lambda x: int(float(x)))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Residential, Commercial, and Total Units<jupyter_code>df.res_un.isna().sum()
df.res_un = df.res_un.astype(str)
df.res_un = df.res_un.replace({'nan': '-9999'})
df.res_un = df.res_un.apply(lambda x: int(float(x)))
df.head()
df.com_un.isna().sum()
df.com_un = df.com_un.astype(str)
df.com_un = df.com_un.replace({'nan': '-9999'})
df.com_un = df.com_un.apply(lambda x: int(float(x)))
df.head()
df.total_un.isna().sum()
df.total_un = df.total_un.astype(str)
df.total_un = df.total_un.replace({'nan': '-9999'})
df.total_un = df.total_un.apply(lambda x: int(float(x)))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Land and Gross Square Footage- **Land Square Feet:**  land area of property listed in square feet- **Gross Square Feet:**  total area of all the floors of building as measured from exterior surfaces of outside walls of building, incl. land area and space within any bulding or structure on property<jupyter_code>df.land_sf.isna().sum()
df.land_sf = df.land_sf.astype(str)
df.land_sf = df.land_sf.replace({'nan': '-9999'})
df.land_sf = df.land_sf.apply(lambda x: int(float(x)))
df.head()
df.gross_sf.isna().sum()
df.gross_sf = df.gross_sf.astype(str)
df.gross_sf = df.gross_sf.replace({'nan': '-9999'})
df.gross_sf = df.gross_sf.apply(lambda x: int(float(x)))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Tax ClassProperty in NYC is divided into 4 classes:- Class 1: Most residential property of up to three units (family homes and small stores or offices with one or two apartments attached), and most condominiums that are not more than three stories.- Class 2: All other property that is not in Class 1 and is primarily residential (rentals, cooperatives and condominiums). Class 2 includes:    - Sub-Class 2a  (4 -  6 unit rental building);    - Sub-Class 2b  (7 - 10 unit rental building);    - Sub-Class 2c  (2 - 10 unit cooperative or condominium); and    - Class 2  (11 units or more).- Class 3: Most utility property.- Class 4: All commercial and industrial properties, such as office, retail, factory buildings and all other properties not included in tax classes 1, 2 or 3.<jupyter_code>list(set(df.tax_cl_pres.values))
df.tax_cl_pres.value_counts()
df.tax_cl_pres = df.tax_cl_pres.replace({' ': '', '  ': '', np.nan: ''})
df.tax_cl_pres.value_counts()
df.tax_cl_TOS = df.tax_cl_TOS.astype(str)
df.tax_cl_TOS.value_counts()
df.tax_cl_TOS = df.tax_cl_TOS.replace({'nan': '0'})
df.tax_cl_TOS = df.tax_cl_TOS.apply(lambda x: int(float(x)))
df.tax_cl_TOS.value_counts()
df.tax_cl_TOS = df.tax_cl_TOS.replace({0: ''})
df.tax_cl_TOS = df.tax_cl_TOS.astype(str)
df.tax_cl_TOS.value_counts()<jupyter_output><empty_output><jupyter_text>NTA<jupyter_code>df.NTA.isna().sum()
df.NTA = df.NTA.apply(lambda x: str(x).title())
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Address<jupyter_code>df.address.isna().sum()
df.address = df.address.apply(lambda x: ' '.join(elem.capitalize() for elem in str(x).lower().split()))
df.head()<jupyter_output>Warning: Total number of columns (21) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Easement<jupyter_code>df.easement.value_counts()
df.drop(columns=['easement'], inplace=True)
df.head()<jupyter_output>Warning: Total number of columns (23) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Sale Price<jupyter_code>df.sale_price.value_counts()
df.sale_price.isna().sum()
df.sale_price = df.sale_price.astype(str)
df.sale_price = df.sale_price.replace({'nan': '-9999'})
df.sale_price = df.sale_price.apply(lambda x: int(float(x)))
df.head()<jupyter_output>Warning: Total number of columns (23) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Sale Age<jupyter_code>df.sale_date.isna().sum()
df['sale_yr'] = df.sale_date.apply(lambda x: x.year)
df.head()
df.yr_blt.isna().sum()
df['yr_built'] = df.yr_blt.apply(lambda x: x.year)
df.head()
df['sale_age'] = df['sale_yr'] - df['yr_built']
df.head()
df.sale_age.value_counts()
df.sale_age.isna().sum()
df.sale_age = df.sale_age.astype(str)
df.sale_age = df.sale_age.replace({'nan': '-9999'})
df.sale_age = df.sale_age.apply(lambda x: int(float(x)))
df.drop(columns=['sale_yr', 'yr_built'], inplace=True)
df.head()
df.head()<jupyter_output>Warning: Total number of columns (23) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>Comparing Building Class Before and After Sale<jupyter_code>df['bc_comp'] = np.where(df['bldg_cl_pres']==df['bldg_cl_TOS'], 1, 0)
df.head()<jupyter_output>Warning: Total number of columns (22) exceeds max_columns (20). Falling back to pandas display.<jupyter_text>10-digit COde<jupyter_code>df.block.values.min()
df.block.values.max()
conditions = [(df.block<100000)&(df.block>9999),
              (df.block<10000)&(df.block>999),
              (df.block<1000)&(df.block>99),
              (df.block<100)&(df.block>9),
              (df.block<10)&(df.block>0)]
choices = [str(df.loc[df.index]['block']),
           '0' + str(df.loc[df.index]['block']),
           '00' + str(df.loc[df.index]['block']),
           '000' + str(df.loc[df.index]['block']),
           '0000' + str(df.loc[df.index]['block'])]
df['BLOCK'] = np.select(conditions, choices, default = np.nan)
def conditions(s):
    if df.block > 9999:
        return str(df.block)
    elif (df.block < 10000) and (df.block > 999):
        return 0
df['BLOCK'] = np.where(df.block>9999, str(df.block),
                       np.where((df.block<10000) & (df.block>999), '0' + str(df.index),
                       np.where((df.block<1000) & (df.block>99), '00' + str(df.block),
                       np.where((df.block<100) & (df.block>9), '000' + str(df.block),
                       np.where((df.block<10) & (df.block>0), '0000' + str(df.block), np.nan)))))
df.BLOCK[0]
df.lot.values.min()
df.lot.values.max()
df['LOT'] = np.where((df.lot<10000) & (df.lot>999), str(df.lot),
                       np.where((df.lot<1000) & (df.lot>99), '0' + str(df.lot),
                       np.where((df.lot<100) & (df.lot>9), '00' + str(df.lot),
                       np.where((df.lot<10) & (df.lot>0), '000' + str(df.lot), np.nan))))
df.boro.value_counts()
df.boro = df.boro.astype(str)
df['BBL'] = str(df.boro) + str(df.BLOCK) + str(df.LOT)
df.head()
df.head()
df.drop(columns=['LOT', 'BLOCK', 'BBL'], inplace=True)
df.head()
df.to_csv('df_nyc_housing.csv')<jupyter_output><empty_output><jupyter_text>Visualizations<jupyter_code>df.head()
import seaborn as sns
import matplotlib.pyplot as plt

class RatingsDashboard(param.Parameterized):
    
    NTA = param.ObjectSelector(default='Alphabet City', objects=list(df.NTA.unique()))
    
    def get_data(self):
        class_df = df[(df.NTA==self.NTA)].copy()
        return class_df

    def box_view(self):
        data = self.get_data()
        ax = sns.boxplot(data['sale_price'])
        plt.close()
        return ax.figure

    def table_view(self):
        data = self.get_data()
        return data
rd = RatingsDashboard(name='')
dashboard_title = '# Sale Price Dashboard'
dashboard_desc = 'Description'
dashboard = pn.Column(dashboard_title,
                      dashboard_desc,
                      rd.param,
                      rd.box_view,
                      rd.table_view)
dashboard<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14144677795727875, 'fraction_numerical': 0.02991683120923832, 'mean_word_length': 3.435774946921444, 'pattern_counts': {'":': 0, '<': 84, '<?xml version=': 0, '>': 86, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 36, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '546640', 'license': 'MIT', 'n_tokens_mistral': 6126, 'n_tokens_neox': 5672, 'n_words': 1456, 'path': 'nyc_housing.ipynb', 'repo_name': 'datascisteven/NYC-Housing-Price-Prediction', 'size': '16713'}