<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Python Real Estate Analysis Project
May 2017
Written by Divya Sasidharan at NYU Stern 
Contact
Step1:  Data Soure
Downloaded the Philadelphia real estate dataset from Kaggle and import using Pandas lib. Please refer the following link. 
https
Step2:  Crime rate, Walks and School score in each postal code
Following are the observation based on analyzing average House price by Crime rate, walkscore and school score against postal code. The data that affected the price change most was crime for the city of Philadelphia. School rating also influenced the price of the property. Walkscore did not have a strong impact it was overridden by crime score but it had combined effect at the city center.
Step3:  House sales price by zipcode
Below analyis shows that most of the house price are below $150,000 in the city of Philadelphia.
Step4:  Calculating Average price of the property
Average price of the property = Zillow Estimate + Tax asssesment value + Average comps of similar property
Step5:  Average Price per sq. ft
Average price per sq.ft = Average price/finished square foot.
Step6:  Calculating rank based on Crime rate
Crime rate has been biffercated into various buckets based on the min, median and max. Please refer the below code for the buckets allocated.
Step7:  Impact of Crime over sales price
There are several factors that influences the house value and the most important is the crime for the city of Philadelphia. The same factor may not be the most influential factor in other cities for example, Manhattan, NY. The following chart shows that higher the rank (lower crime) higher the price of the property.
Step8:  Calculating rank based on Walk score
Walkscore has been biffercated into various buckets based on the min, median and max. Please refer the below code for the buckets allocated.
Step9:  Impact of Walkscore over sales price
Philadelphia has the 6th best walkscore after New York, Jersey City, San Francisco, Boston and Newark. Analysis carried out by John I. Gilderbloom, William W. Riggs and Wesley L. Meares shows that walkability has a positive impact not only on neighborhood housing valuation but also on reduction of crime and foreclosure[8]. Our research analysis of Philadelphia did not find strong correlation between Walkscore and property price except in the city center.
Step10:  Calculating rank based on School rating
School ratings has been biffercated into various buckets based on the min, median and max. Please refer the below code for the buckets allocated.
Step11:  Impact of School score over sales price
School rating is also influenced the price of the property. Better school score also improves the demand of the property.
Step12:  z1['Avg. price per sq. ft'] = z1['Avg. Price']/z1['finished \n(SqFt)']
<ASSISTANT_TASK:>
Python Code:
import pandas as pd                    # Importing necessary data package
import matplotlib.pyplot as plt        # pyplot module
import numpy as np
Explanation: Python Real Estate Analysis Project
May 2017
Written by Divya Sasidharan at NYU Stern 
Contact: ds5151@nyu.edu
Overview
Real estate is an active area in both investment market and academic research. Foreclosure auction properties are special properties that have different transaction and payment procedures from the regular sales, and the home buyers take much higher risk. In this project, we built a scalable analytics workflow to extract, clean and analyze foreclosure property data from multiple sources using Python tools and lib. We analyzed the correlation of foreclosure property price with crime score, walk score and school rating. We have that crime and school is major factor influencing the property price and demand.
End of explanation
Zillow = pd.ExcelFile("Properties_philly_Kraggle_v2.xlsx")
zz = Zillow.parse('Properties_philly_Kraggle_v2')
zz
print('Dimensions: ', zz.shape)  # looking at the categories I can work with
print('Column labels: ', zz.columns)   #Listing out the Column headings
print('Row labels: ', zz.index)
z = zz.dropna()       #Dropped empty rows that does have any data
print('Dimensions: ', z.shape)
Explanation: Data Soure
Downloaded the Philadelphia real estate dataset from Kaggle and import using Pandas lib. Please refer the following link. 
https://www.kaggle.com/harry007/philly-real-estate-data-set-sample/downloads/philly-real-estate-data-set-sample.zip
End of explanation
plt.scatter(z['Postal Code'], z[' Violent Crime Rate '])
plt.show()
plt.scatter(z['Postal Code'], z[' Avg Walk&Transit score  '])
plt.show()
plt.scatter(z['Postal Code'], z[' School Score  '])
plt.show()
Explanation: Crime rate, Walks and School score in each postal code
Following are the observation based on analyzing average House price by Crime rate, walkscore and school score against postal code. The data that affected the price change most was crime for the city of Philadelphia. School rating also influenced the price of the property. Walkscore did not have a strong impact it was overridden by crime score but it had combined effect at the city center.
End of explanation
plt.scatter(z['Postal Code'], z['Sale Price/bid price'])
plt.show()
z1 = pd.DataFrame(z, columns = ['Address',
 'Zillow Address',
 'Sale Date',
 'Opening Bid',
 'Sale Price/bid price',
 'Book/Writ',
 'OPA',
 'Postal Code',
 'Attorney',
 'Ward',
 'Seller',
 'Buyer',
 'Sheriff Cost',
 'Advertising',
 'Other',
 'Record Deed',
 'Water',
 'PGW',
 ' Avg Walk&Transit score  ',
 ' Violent Crime Rate ',
 ' School Score  ',
 'Zillow Estimate',
 'Rent Estimate',
 'taxAssessment',
 'yearBuilt',
 'finished \n(SqFt)',
 ' bathrooms ',
 ' bedrooms ',
 'PropType',
 'Average comps'])
Explanation: House sales price by zipcode
Below analyis shows that most of the house price are below $150,000 in the city of Philadelphia.
End of explanation
z1['Avg. Price'] = z1[['Zillow Estimate', 'taxAssessment', 'Average comps']].mean(axis=1)
list(z1)
z1['Avg. Price'].head()
Explanation: Calculating Average price of the property
Average price of the property = Zillow Estimate + Tax asssesment value + Average comps of similar property
End of explanation
z1['Avg. price per sq. ft'] = z1['Avg. Price']/z1['finished \n(SqFt)']
z1['Avg. price per sq. ft'].head()
Explanation: Average Price per sq. ft
Average price per sq.ft = Average price/finished square foot.
End of explanation
z1[' Violent Crime Rate '].median()
z1[' Violent Crime Rate '].min()
z1[' Violent Crime Rate '].max()
crimerank = []
for row in z1[' Violent Crime Rate ']:
 if row<0.344:    
  crimerank.append(1)
 elif row>=0.344 and row<0.688:
  crimerank.append(2)
 elif row>=0.688 and row<1.032:
  crimerank.append(3)
 elif row>=1.032 and row<1.376:
  crimerank.append(4)
 else:
  crimerank.append(5)
      
z1['Crime Rank'] = crimerank
z1['Crime Rank'].head()
zcrime = z1.groupby(['Crime Rank'])['Avg. Price'].mean()
zcrime
Explanation: Calculating rank based on Crime rate
Crime rate has been biffercated into various buckets based on the min, median and max. Please refer the below code for the buckets allocated.
End of explanation
plt.figure(figsize = (16,7))  # plotting our data
plt.plot(zcrime, color = 'red', marker= '*')
plt.suptitle('Average price by crime', fontsize=18)
plt.xlabel('Crime Rank', fontsize=12)
plt.ylabel('Average price of houses', fontsize=12)
plt.show()
Explanation: Impact of Crime over sales price
There are several factors that influences the house value and the most important is the crime for the city of Philadelphia. The same factor may not be the most influential factor in other cities for example, Manhattan, NY. The following chart shows that higher the rank (lower crime) higher the price of the property.
End of explanation
Walkrank = []
for row in z1[' Avg Walk&Transit score  ']:
 if row>88:    
  Walkrank.append(1)
 elif row>77 and row<=88:
  Walkrank.append(2)
 elif row>66 and row<=77:
  Walkrank.append(3)
 elif row>55 and row<=66:
  Walkrank.append(4)
 else:
  Walkrank.append(5)
      
z1['Walk Rank'] = Walkrank
z1['Walk Rank'].head()
zwalk = z1.groupby(['Walk Rank'])['Avg. Price'].mean()
zwalk
Explanation: Calculating rank based on Walk score
Walkscore has been biffercated into various buckets based on the min, median and max. Please refer the below code for the buckets allocated.
End of explanation
plt.figure(figsize = (16,7))  # plotting our data
plt.plot(zwalk, color = 'blue', marker= '*')
plt.suptitle('Average price by walkrank', fontsize=18)
plt.xlabel('Walk Rank', fontsize=12)
plt.ylabel('Average price of houses', fontsize=12)
plt.show()
Explanation: Impact of Walkscore over sales price
Philadelphia has the 6th best walkscore after New York, Jersey City, San Francisco, Boston and Newark. Analysis carried out by John I. Gilderbloom, William W. Riggs and Wesley L. Meares shows that walkability has a positive impact not only on neighborhood housing valuation but also on reduction of crime and foreclosure[8]. Our research analysis of Philadelphia did not find strong correlation between Walkscore and property price except in the city center.
End of explanation
Schoolrank = []
for row in z1[' School Score  ']:
 if row>57.308:    
  Schoolrank.append(1)
 elif row>43.816 and row<=57.308:
  Schoolrank.append(2)
 elif row>30.324 and row<=43.816:
  Schoolrank.append(3)
 elif row>16.832 and row<=30.324:
  Schoolrank.append(4)
 else:
  Schoolrank.append(5)
      
z1['School Rank'] = Schoolrank
zschool = z1.groupby(['School Rank'])['Avg. Price'].mean()
zschool
Explanation: Calculating rank based on School rating
School ratings has been biffercated into various buckets based on the min, median and max. Please refer the below code for the buckets allocated.
End of explanation
plt.figure(figsize = (16,7))  # plotting our data
plt.plot(zschool, color = 'blue', marker= '*')
plt.suptitle('Average price by schoolrank', fontsize=18)
plt.xlabel('School Rank', fontsize=12)
plt.ylabel('Average price of houses', fontsize=12)
plt.show()
z1.head()
Explanation: Impact of School score over sales price
School rating is also influenced the price of the property. Better school score also improves the demand of the property.
End of explanation
z1['Closing Cost'] = z1['Avg. Price']*.085
 z1['Rehab Cost'] = z1['finished \n(SqFt)']*25
 z1['Estimated Max Bid Price']= z1['Avg. Price']-z1['Rehab Cost']
 z1.head()
Explanation: z1['Avg. price per sq. ft'] = z1['Avg. Price']/z1['finished \n(SqFt)']
End of explanation
<END_TASK>