<jupyter_start><jupyter_text>Lambda School Data Science*Unit 2, Sprint 3, Module 2*--- Wrangle ML datasets- [ ] Continue to clean and explore your data. - [ ] For the evaluation metric you chose, what score would you get just by guessing?- [ ] Can you make a fast, first model that beats guessing?**We recommend that you use your portfolio project dataset for all assignments this sprint.****But if you aren't ready yet, or you want more practice, then use the New York City property sales dataset for today's assignment.** Follow the instructions below, to just keep a subset for the Tribeca neighborhood, and remove outliers or dirty data. [Here's a video walkthrough](https://youtu.be/pPWFw8UtBVg?t=584) you can refer to if you get stuck or want hints!- Data Source: [NYC OpenData: NYC Citywide Rolling Calendar Sales](https://data.cityofnewyork.us/dataset/NYC-Citywide-Rolling-Calendar-Sales/usep-8jbt)- Glossary: [NYC Department of Finance: Rolling Sales Data](https://www1.nyc.gov/site/finance/taxes/property-rolling-sales-data.page)<jupyter_code>%%capture
import sys

# If you're on Colab:
if 'google.colab' in sys.modules:
    DATA_PATH = 'https://raw.githubusercontent.com/LambdaSchool/DS-Unit-2-Applied-Modeling/master/data/'
    !pip install category_encoders==2.*
    !pip install pandas-profiling==2.*

# If you're working locally:
else:
    DATA_PATH = '../data/'
# Read New York City property sales data
import pandas as pd
df = pd.read_csv(DATA_PATH+'condos/NYC_Citywide_Rolling_Calendar_Sales.csv')<jupyter_output><empty_output><jupyter_text>Your code starts here:<jupyter_code># Change column names: replace spaces with underscores
df.columns = df.columns.str.replace(' ', '_')
df
# Get Pandas Profiling Report
from pandas_profiling import ProfileReport
prof = ProfileReport(df)
prof.to_file(output_file='output.html')
# Keep just the subset of data for the Tribeca neighborhood
# Check how many rows you have now. (Should go down from > 20k rows to 146)
tribeca = df[df['NEIGHBORHOOD'] == 'TRIBECA']
tribeca.shape
# Q. What's the date range of these property sales in Tribeca?
max = tribeca['SALE_DATE'].max()
min = tribeca['SALE_DATE'].min()
print('The dates range from',min,'to',max+'.')
# The Pandas Profiling Report showed that SALE_PRICE was read as strings
# Convert it to integers
tribeca['SALE_PRICE'] = tribeca['SALE_PRICE'].str.replace(' ', '')
tribeca['SALE_PRICE'] = tribeca['SALE_PRICE'].str.replace('$', '') 
tribeca['SALE_PRICE'] = tribeca['SALE_PRICE'].str.replace('-', '') 
tribeca['SALE_PRICE'] = tribeca['SALE_PRICE'].str.replace(',', '') 
tribeca['SALE_PRICE'] = tribeca['SALE_PRICE'].astype(int)
# Q. What is the maximum SALE_PRICE in this dataset?
sale_max = tribeca['SALE_PRICE'].max()
print('The highest sale was',sale_max)
# Look at the row with the max SALE_PRICE
max_row = tribeca[tribeca['SALE_PRICE'] == sale_max]
max_row
# Get value counts of TOTAL_UNITS
unit_val = tribeca['TOTAL_UNITS'].value_counts()
# Q. How many property sales were for multiple units?
multi_unit = tribeca[tribeca['TOTAL_UNITS']>1.0]
multi_unit_total = len(multi_unit)
print('There were',multi_unit_total,'sales of multi-unit properties.')
# Keep only the single units
single_unit = tribeca[tribeca['TOTAL_UNITS']==1]
single_unit.head()
# Q. Now what is the max sales price? How many square feet does it have?
single_max = single_unit['SALE_PRICE'].max()
single_max
single_max_unit = single_unit[single_unit['SALE_PRICE'] == single_max]
single_max_unit
max_single_sqft = single_max_unit['GROSS_SQUARE_FEET']
print('The most expenseive single unit sale had ',max_single_sqft[9236],'Sqft. of floor space.')
# Q. How often did $0 sales occur in this subset of the data?

single_zero = single_unit[single_unit['SALE_PRICE'] == 0]
number_zero_sale = len(single_zero)
print('There have been',number_zero_sale,'zero dollar sales. ')

# There's a glossary here: 
# https://www1.nyc.gov/site/finance/taxes/property-rolling-sales-data.page

# It says:
# A $0 sale indicates that there was a transfer of ownership without a 
# cash consideration. There can be a number of reasons for a $0 sale including 
# transfers of ownership from parents to children. 


# Look at property sales for > 5,000 square feet
fivek_sqft = tribeca[tribeca['GROSS_SQUARE_FEET'] > 5000]
# Q. What is the highest square footage you see?
fivek_sqft['GROSS_SQUARE_FEET'].max()
# What are the building class categories?
# How frequently does each occur?
fivek_sqft['BUILDING_CLASS_CATEGORY'].value_counts()
# Keep subset of rows:
# Sale price more than $0, 
# Building class category = Condos - Elevator Apartments
subset = tribeca[tribeca['SALE_PRICE'] > 0]
subset = tribeca[tribeca['BUILDING_CLASS_CATEGORY'] == '13 CONDOS - ELEVATOR APARTMENTS']
# Check how many rows you have now. (Should be 106 rows.)
subset.shape
# Make a Plotly Express scatter plot of GROSS_SQUARE_FEET vs SALE_PRICE
import plotly.express as px
fig = px.scatter(subset,x='GROSS_SQUARE_FEET', y='SALE_PRICE')
fig.show()
# Add an OLS (Ordinary Least Squares) trendline,
# to see how the outliers influence the "line of best fit"
fig = px.scatter(subset,x='GROSS_SQUARE_FEET', y='SALE_PRICE',trendline='ols')
fig.show()
# Look at sales for more than $35 million
thirtyfive_mil = subset[subset['SALE_PRICE']>35000000]
thirtyfive_mil.head()
# All are at 70 Vestry Street 
# All but one have the same SALE_PRICE & SALE_DATE
# Was the SALE_PRICE for each? Or in total?
# Is this dirty data?

# Make a judgment call:
# Keep rows where sale price was < $35 million
cleaned = subset[subset['SALE_PRICE']<35000000]
# Check how many rows you have now. (Should be down to 90 rows.)
cleaned
# Now that you've removed outliers,
# Look again at a scatter plot with OLS (Ordinary Least Squares) trendline
fig = px.scatter(cleaned,x='GROSS_SQUARE_FEET', y='SALE_PRICE',trendline='ols')
fig.show()
# Select these columns, then write to a csv file named tribeca.csv. Don't include the index.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1132170412613217, 'fraction_numerical': 0.011405568601140557, 'mean_word_length': 5.574421168687983, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 15, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, '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': '153963', 'license': 'MIT', 'n_tokens_mistral': 2066, 'n_tokens_neox': 1964, 'n_words': 633, 'path': 'module2-wrangle-ml-datasets/LS_DS_232_assignment.ipynb', 'repo_name': 'JoshuaHaga/DS-Unit-2-Applied-Modeling', 'size': '5962'}