<jupyter_start><jupyter_text>Housing Price Prediction - 2020  Introduction: Business problemAccording to U.S Census Bureau, the median home price in    * 1980  - $148,000       * 2000  - $180,000       * 2010  - $375,000       * 2020  - $1,500,000Bay Area Real Estate – most expensive market in CaliforniaThe average value of a home in the city is more than one-million dollars more than the median home value in the United States. How the COVID has affected the Bay Area real estate market?* Record Unemployment & Volatile Stock market* Uncertain economic times during COVID has affected the buyer’s & seller’s mindset	**- Buyer’s reluctant to invest during recession	**- Seller’s removed the house listing and the number of newly listed properties declined	**- Real Estate market frozen as soon as the pandemic startedBut..Due to the deadliest pandemic people now see their home not only as a place   to live, but as a shelter and workspace. Our aim is to understand, explore the performance of specific neighborhoods and answer few research questions.  Data The data was scraped from available results in zillow.com. The features scrapped are: * Address * List_price * Address.1 * City * Zipcode  * Zestimate  * Bed * Bath * House_size * House_type * Year_Built	 * Parking	 * Lot	 * HOA	 * Price_Sqft	 * walk_score	 * walk_desc	 * transit_score	 * transit_desc Importing Libraries WEB SCRAPPING<jupyter_code># importing all the required libraries
import urllib.request
import urllib.parse
import urllib.error
from bs4 import BeautifulSoup as BS
import ssl
import json
import ast
import os
from urllib.request import Request, urlopen
import pandas as pd
import requests
import re
import time
import math
# securing the connection 
ctx                = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode    = ssl.CERT_NONE
# initiatzing urls,zillow links
urls = []
zillow_links = []

zipcodes=[95119]

# created nested for loop to collect all the links related to mentioned zipcode above
for zip in zipcodes:
    url_count="https://www.zillow.com/homes/"+str(zip)+"_rb/"
    request_url_count = Request(url_count, headers={'User-Agent': 'Mozilla/5.0'})
    zillow_count = urlopen(request_url_count).read()
    soup = BS(zillow_count,'html.parser')
    result_count=soup.findAll('span',attrs={'class':'result-count'})
    resct=str(result_count)[28:30]
    res=math.floor(int(resct)/25)
    res=res+1
    for i in range(1,res):
        print(zip)
        print (i)
        url_1= "https://www.zillow.com/homes/"+str(zip)+"_rb/"+str(i)+"_p/"
        print (url_1)
        request_url     = Request(url_1, headers={'User-Agent': 'Mozilla/5.0'})
        zillow = urlopen(request_url).read()
        soup = BS(zillow,'html.parser')
        links = soup.findAll('a',attrs={'class':'list-card-link list-card-link-top-margin'},href=True)
        for houselink in links:
            #print(houselink['href'])
            houselink_cur=houselink['href']
            if "homedetails" in str(houselink_cur):
                print(houselink_cur)
                zillow_links.append(houselink['href'])
zillow_links
# defined a function for extracting information
def housedetails(url):
    ctx = ssl.create_default_context()
    ctx.check_hostname = False
    ctx.verify_mode    = ssl.CERT_NONE
    request     = Request(url, headers={'User-Agent': 'Mozilla/5.0'})
    zillow = urlopen(request).read()
    soup = BS(zillow,'html.parser')

    #         a_zestimate =soup.findAll('div',attrs={'display':'flex'}) 
    #flags are for corner cases
    flag=1
    rent_sale=soup.findAll('div',attrs = {'class':"ds-home-details-chip"})
    for i in rent_sale:
        if "rent" in i.text:
            flag=0
        elif "construction" in i.text:
            flag=0
    if(flag):
        zestimate =soup.findAll('div',attrs={'display':'flex'})
        zestimate = zestimate[4].text.replace(",","")

        house2=soup.findAll('li',attrs={'class':'ds-home-fact-list-item'})
        house_facts=[None]*6
        for i in house2:
            #print(i.text)
            #print(i.find_all(text="Type"))
            if "Type" in i.text:
                house_facts[0]=i.text.replace("Type:","")
                #print(house_facts[0])
            elif "Year" in i.text:
                house_facts[1]=i.text.replace("Year built:","")
            elif "Parking" in i.text:
                house_facts[2]=i.text.replace("Parking:","")
            elif "Lot" in i.text:
                lotsize=i.text.replace("Lot:","").replace(",","")
                house_facts[3]=lotsize
            elif "HOA" in i.text:
                house_facts[4]=i.text.replace("HOA:","")
            elif "Price" in i.text:
                house_facts[5]=i.text.replace("Price/sqft:","").replace(",","")

        # finding listprice paramter
        listprice = soup.findAll('span',attrs={'class':'ds-value'})
        listprice = listprice[0].text.replace(",","")
        
        #finding address
        address = soup.findAll('h1',attrs={'class':'ds-address-container'})
        address = address[0].text.replace(",",";").replace("\xa0","")
        
        # finding bed and bath
        bed_bath = soup.findAll('span',attrs = {'class':'ds-bed-bath-living-area'})
        bedinfo=[None]*3
        for i in bed_bath:
            if "bd" in i.text:
                bedinfo[0]=i.text.replace("bd","")
            elif "ba" in i.text:
                bedinfo[1]=i.text.replace("ba","")
            elif "Square Feet" in i.text:
                sqftt=i.text.replace("Square Feet","")
                bedinfo[2]=str(sqftt).replace(",","")
        house1_details=[]
        house1_details=[url,listprice,address,zestimate]
        house1_details.extend(bedinfo)
        house1_details.extend(house_facts)
        return house1_details
# creating excel file  and writing the extracted information into it
import time
zillow_milpitas=[]
timestr = time.strftime("%Y%m%d-%H%M%S")
city="city"+str(zipcodes[0])
filename=city+str(timestr)+str(".csv")
f1=open(filename,'w')
f1.write("zpidurl;List_price;Address;City;Zipcode;Zestimate;Bed;Bath;House_size;House_type;Year_Built;Parking;Lot;HOA;Price/Sqft\n")
for link in zillow_links:
    print(link)
    zillow_milpitas.append(housedetails(link))
    #print(housedetails(link))
    time.sleep(10)
    data_to_file=str(housedetails(link)).replace("'","").replace("]","").replace("[","").replace(",",";")
    f1.write(data_to_file+"\n")
f1.close()
    
    
import pandas as pd

df = pd.read_excel('pandas_to_excel.xlsx')

df['zpid_url'] = df.Address.str.split('/').str[5]
df['zpid'] = df.zpid_url.str.split('_').str[0]
df = df.drop(['zpid_url'],axis=1)
list_of_zpids = df['zpid'].to_list()
print(list_of_zpids)
#print(list_of_zpids)
with open('zpids.txt', "w") as myfile:
        for i in list_of_zpids:
                myfile.write("%s\n" % i)
import http.client
import mimetypes
import json

conn = http.client.HTTPSConnection("www.zillow.com")

def get_walk_and_transit(property_id):
	payload = "{\"query\":\"query WalkAndTransitScoreQuery($zpid: ID!) {\\n  property(zpid: $zpid) {\\n    id\\n    walkScore {\\n      walkscore\\n      description\\n      ws_link\\n    }\\n    transitScore {\\n      transit_score\\n      description\\n      ws_link\\n    }\\n  }\\n}\\n\",\"operationName\":\"WalkAndTransitScoreQuery\",\"variables\":{\"zpid\":" + str(property_id) + "},\"clientVersion\":\"home-details/6.0.11.0.0.hotfix-10-01-2020.48935c6\"}"
	headers = {
	  'content-type': 'text/plain'
	}
	conn.request("POST", "/graphql/?zpid=" + str(property_id) + "&operationName=WalkAndTransitScoreQuery", payload, headers)
	res = conn.getresponse()
	data = res.read()
	res_json = json.loads(data.decode("utf-8"))
	walk_score_obj = res_json['data']['property']['walkScore']
	transit_score_obj = res_json['data']['property']['transitScore']
	return walk_score_obj, transit_score_obj

def get_price_history(property_id):
	payload = "{\"query\":\"query HomeValueChartDataQuery($zpid: ID!, $metricType: HomeValueChartMetricType, $timePeriod: HomeValueChartTimePeriod) {\\n  property(zpid: $zpid) {\\n    homeValueChartData(metricType: $metricType, timePeriod: $timePeriod) {\\n      points {\\n        x\\n        y\\n      }\\n      name\\n    }\\n  }\\n}\\n\",\"operationName\":\"HomeValueChartDataQuery\",\"variables\":{\"zpid\": " + str(property_id) + ",\"timePeriod\":\"TEN_YEARS\",\"metricType\":\"LOCAL_HOME_VALUES\",\"forecast\":true},\"clientVersion\":\"home-details/6.0.11.0.0.hotfix-10-01-2020.48935c6\"}"
	headers = {
	  'content-type': 'text/plain'
	}
	conn.request("POST", "/graphql/?zpid=" + str(property_id) +"&timePeriod=TEN_YEARS&metricType=LOCAL_HOME_VALUES&forecast=true&operationName=HomeValueChartDataQuery", payload, headers)
	res = conn.getresponse()
	data = res.read()
	res_json = json.loads(data.decode("utf-8"))
	return res_json['data']['property']['homeValueChartData']

property_id_list = [19481361,19483503,19471816,19483974]

for zpid in property_id_list:
	walk, trans = get_walk_and_transit(zpid)
	price_history = get_price_history(zpid)

	print("zpid: {} walk score: {} description: {} transit score: {} description: {} zestimate chart history size: {}".format(zpid, walk['walkscore'], walk['description'], trans['transit_score'], trans['description'], len(price_history)))<jupyter_output>zpid: 19481361 walk score: 26 description: Car-Dependent transit score: 21 description: Minimal Transit zestimate chart history size: 4
zpid: 19483503 walk score: 12 description: Car-Dependent transit score: 19 description: Minimal Transit zestimate chart history size: 3
zpid: 19471816 walk score: 84 description: Very Walkable transit score: 35 description: Some Transit zestimate chart history size: 3
zpid: 19483974 walk score: 58 description: Somewhat Walkable transit score: 32 description: Some Transit zestimate chart history size: 3<jupyter_text>Importing Libraries<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Import data<jupyter_code>zillow_dataframe = pd.read_excel('zillow_final.xlsx')
zillow_dataframe.head()
zillow_dataframe.info()
print('Number of listings: ', zillow_dataframe.shape[0])
print('Number of features in dataset: ', zillow_dataframe.shape[1])<jupyter_output>Number of listings:  500
Number of features in dataset:  19<jupyter_text>Data cleaning & wrangling : housing dataframe<jupyter_code># Removing "CA" characters from zipcode.
spec_chars = ["CA"]
for char in spec_chars:
    zillow_dataframe['Zipcode'] = zillow_dataframe['Zipcode'].str.replace(char, '')
#Removing sqft from lot column.
spec_chars = ["sqft","acres", "Acres", "acre"]
for char in spec_chars:
    zillow_dataframe['Lot'] = zillow_dataframe['Lot'].str.replace(char, '')
# Removing $ /month or /mo from HOA and leeping only price.
spec_chars = ["$","/","month","mo","/month","/mo"]
for char in spec_chars:
    zillow_dataframe['HOA'] = zillow_dataframe['HOA'].str.replace(char, '')
# Removing None value, special character with O value.
zillow_dataframe = zillow_dataframe.replace("None",0)
zillow_dataframe = zillow_dataframe.fillna(0)
zillow_dataframe=zillow_dataframe.replace("--",0)
zillow_dataframe['Parking'] = zillow_dataframe['Parking'].replace("No Data","0")

np.count_nonzero(zillow_dataframe["Zestimate"])<jupyter_output><empty_output><jupyter_text>Missing zestimate values account for about 7.4%. Fill the missing Zestimate values with List price of the listing.<jupyter_code>zillow_dataframe["Zestimate"] = zillow_dataframe["Zestimate"].mask(zillow_dataframe["Zestimate"] ==0).fillna(zillow_dataframe["List_price"])
np.count_nonzero(zillow_dataframe["Zestimate"])<jupyter_output><empty_output><jupyter_text>Imputing missing Year_Built values with 0000 value.<jupyter_code>zillow_dataframe["Year_Built"] = zillow_dataframe["Year_Built"].replace(0,"0000")
zillow_dataframe["Year_Built"] = zillow_dataframe["Year_Built"].replace("No Data","0000")

# Converting data type of Lot,HOA,Price/Sqft from object to integer/float for analysis.
zillow_dataframe['Lot'] = pd.to_numeric(zillow_dataframe['Lot'])

zillow_dataframe['HOA'] = zillow_dataframe['HOA'].astype(str).astype(float)
zillow_dataframe['Price_Sqft'] = zillow_dataframe['Price_Sqft'].astype(str).astype(float)
zillow_dataframe.describe()
zillow_dataframe.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 500 entries, 0 to 499
Data columns (total 19 columns):
Address          500 non-null object
List_price       500 non-null int64
Address.1        500 non-null object
City             500 non-null object
Zipcode          500 non-null object
Zestimate        500 non-null float64
Bed              500 non-null int64
Bath             500 non-null float64
House_size       500 non-null int64
House_type       500 non-null object
Year_Built       500 non-null object
Parking          500 non-null object
Lot              500 non-null float64
HOA              500 non-null float64
Price_Sqft       500 non-null float64
walk_score       500 non-null object
walk_desc        500 non-null object
transit_score    500 non-null float64
transit_desc     500 non-null object
dtypes: float64(6), int64(3), object(10)
memory usage: 74.3+ KB<jupyter_text>Heat map to visualize any missing values<jupyter_code>import seaborn as sns
sns.heatmap(zillow_dataframe.isnull(), cbar=False)

zillow_dataframe.to_excel('pandas_to_excel.xlsx', sheet_name='Zillow')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1398033666021153, 'fraction_numerical': 0.02644123342767764, 'mean_word_length': 3.991449814126394, 'pattern_counts': {'":': 13, '<': 27, '<?xml version=': 0, '>': 27, 'https://': 2, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 2, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '198917', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 4761, 'n_tokens_neox': 4479, 'n_words': 967, 'path': 'BAN612_scrapping_preprocess_G3.ipynb', 'repo_name': 'Chief-Ut/Zillow-Project', 'size': '13426'}