<jupyter_start><jupyter_text>CEO Questions   Business question1. What are the properties that House Rocket should i buy it and at what price?2. Once the house is purchased, what is the best when to sell them and at what price?  Imports<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns
from matplotlib import gridspec
from matplotlib import pyplot as plt
import ipywidgets as widgets
import plotly.express as px
from ipywidgets import interact, interactive, fixed, interact_manual
from geopy.geocoders import Nominatim
import missingno                                   as msno<jupyter_output><empty_output><jupyter_text>Auxiliary functions<jupyter_code>## # Supress Scientific Notation

np.set_printoptions(suppress=True)
pd.set_option('display.float_format', '{:.2f}'.format)<jupyter_output><empty_output><jupyter_text>Loading data<jupyter_code>df=pd.read_csv('Datasets/kc_house_data.csv',parse_dates=[1,14])<jupyter_output><empty_output><jupyter_text>Dataset info<jupyter_code>df.head()
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 21613 entries, 0 to 21612
Data columns (total 21 columns):
 #   Column         Non-Null Count  Dtype         
---  ------         --------------  -----         
 0   id             21613 non-null  int64         
 1   date           21613 non-null  datetime64[ns]
 2   price          21613 non-null  float64       
 3   bedrooms       21613 non-null  int64         
 4   bathrooms      21613 non-null  float64       
 5   sqft_living    21613 non-null  int64         
 6   sqft_lot       21613 non-null  int64         
 7   floors         21613 non-null  float64       
 8   waterfront     21613 non-null  int64         
 9   view           21613 non-null  int64         
 10  condition      21613 non-null  int64         
 11  grade          21613 non-null  int64         
 12  sqft_above     21613 non-null  int64         
 13  sqft_basement  21613 non-null  int64         
 14  yr_built       21613 non-null  datetime64[ns]
 15  yr_renovated   2161[...]<jupyter_text>Change types<jupyter_code>df['yr_renovated'] = df['yr_renovated'].apply( lambda x: pd.to_datetime( x,format='%Y') if x > 0 else x )<jupyter_output><empty_output><jupyter_text>| Attributes     | Meaning                                                                                                                                                                                              ||----------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|| id             | Unique ID for each home sold                                                                                                                                                                         || date           | Date of the home sale                                                                                                                                                                                || price          | Price of each home sold                                                                                                                                                                              || bedrooms       | Number of bedrooms                                                                                                                                                                                   || bathrooms      | Number of bathrooms, where .5 accounts for a room with a toilet but no shower                                                                                                                        || sqft_living    | Square footage of the apartments interior living space                                                                                                                                               || sqft_lot       | Square footage of the land space                                                                                                                                                                     || floors         | Number of floors                                                                                                                                                                                     || waterfront     | A dummy variable for whether the apartment was overlooking the waterfront or not                                                                                                                     || view           | An index from 0 to 4 of how good the view of the property was                                                                                                                                        || condition      | An index from 1 to 5 on the condition of the apartment                                                                                                                                               || grade          | An index from 1 to 13, where 1-3 falls short of building construction and design, 7 has an average level of construction and design, and 11-13 have a high quality level of construction and design. || sqft_above     | The square footage of the interior housing space that is above ground level                                                                                                                          || sqft_basement  | The square footage of the interior housing space that is below ground level                                                                                                                          || yr_built       | The year the house was initially built                                                                                                                                                               || yr_renovated   | The year of the house’s last renovation                                                                                                                                                              || zipcode        | What zipcode area the house is in                                                                                                                                                                    || sqft_living15  | The square footage of interior housing living space for the nearest 15 neighbors                                                                                                                     || sqft_lot15     | The square footage of the land lots of the nearest 15 neighbors      Insights 1. How many homes are available for purchase?2. How many attributes do houses have?3. What are the attributes of the houses?4. What is the most expensive house (house with the highest sale value)?5. Which house has the most rooms?6. What is the sum total of quarters in the dataset?7. How many houses have 2 bathrooms?8. What is the average price of all houses in the dataset?9. What is the average price of 2 bathroom homes?10. What is the minimum price for 3 bedroom homes?11. How many houses have more than 300 square meters in the living room?Of being?12. How many houses have more than 2 floors?13. How many houses have a sea view?14. Of the houses with sea views, how many have 3 bedrooms?15. Of the houses with more than 300 square meters of living room,how many have more than 2 bathrooms?<jupyter_code>print( 'Number of houses available for purchase: {}'.format( df.shape[0] ) )
print( 'Features by house: {}'.format( df.shape[1] ) )
house_expensive_id = df[['id', 'price']].sort_values('price', ascending=False ).loc[0,'id']
print( 'Features by house: {}'.format( house_expensive_id) )
house_bedrooms_id = df[['id', 'bedrooms']].sort_values('bedrooms', ascending=False ).iloc[0,0]
print( 'Features by house: {}'.format( house_bedrooms_id) )
print( 'summ bedrooms: {}'.format( df['bedrooms'].sum() ))
data = df.loc[df['bathrooms'] == 2, :]
num_houses = len( data )
print( 'Total number of houses with 2 bathrooms: {}'.format( num_houses ) )
avg_price = np.round( df['price'].mean(), 2 )
print( 'Average Price: ${}'.format( avg_price ) )
avg_price = np.round( data.loc[data['bathrooms'] == 2, 'price'].mean(), 2 )
print( 'Average Price: ${}'.format( avg_price ) )
min_price = np.round( df.loc[df['bedrooms'] == 3, 'price'].min(), 2 )
print( 'Min Price: ${}'.format( min_price ))
houses = df.loc[df['sqft_living'] > 300, 'id'].shape[0]
print( 'There are {} Houses with living room grater than 300 square foot'.format( houses ) )
houses = df.loc[df['floors'] > 2, 'id'].shape[0]
print( 'There are {} Houses with the number of floors grater than 2'.format(houses ) )
houses = df.loc[df['floors'] == 1, 'id'].shape[0]
print( 'There are {} Houses with the number of floors grater than 2'.format(houses ) )
houses = df.loc[(df['waterfront'] == 1) & (df['bedrooms'] > 2), 'id'].shape[0]
print( 'There are {} Houses with waterfront and bedrooms greater then 3'.format( houses ) )
houses = df.loc[(df['sqft_living'] > 300) & (df['bathrooms'] > 2), 'id'].shape[0]
print( 'There are {} Houses with living room greater than 300 and bathrooms bigger than 2'.format( houses ) )<jupyter_output>Features by house: 7129300520
Features by house: 2402100895
summ bedrooms: 72854
Total number of houses with 2 bathrooms: 1930
Average Price: $540088.14
Average Price: $457889.72
Min Price: $82000.0
There are 21612 Houses with living room grater than 300 square foot
There are 782 Houses with the number of floors grater than 2
There are 10680 Houses with the number of floors grater than 2
There are 127 Houses with waterfront and bedrooms greater then 3
There are 11242 Houses with living room greater than 300 and bathrooms bigger than 2<jupyter_text>Feature engineering<jupyter_code>df['is_waterfront'] = df['waterfront'].apply( lambda x: 'yes' if x == 1 else 'no' )<jupyter_output><empty_output><jupyter_text>“house_age”- Se o valor da coluna “date” for maior que 2014-01-01 => ‘new_house’- Se o valor da coluna “date” for menor que 2014-01-01 => ‘old_house’<jupyter_code>df['house_age'] = 'NA'
df.loc[df['date'] > '2015-01-01', 'house_age'] = 'new_house'
df.loc[df['date'] < '2015-01-01', 'house_age'] = 'old_house'
df[['house_age','sqft_living','price']].groupby('house_age').mean().reset_index()<jupyter_output><empty_output><jupyter_text>“dormitory_type”- Se o valor da coluna “bedrooms” for igual à 1 => ‘studio’- Se o valor da coluna “bedrooms” for igual a 2 => ‘apartament’- Se o valor da coluna “bedrooms” for maior que 2 => ‘house’<jupyter_code>df['dormitory_type'] = 'NA'
for i in range( len( df ) ):
    if df.loc[i, 'bedrooms'] <= 1:
        df.loc[i, 'dormitory_type'] = 'studio'
        
    elif df.loc[i, 'bedrooms'] <= 2:
        df.loc[i, 'dormitory_type'] = 'apartment'
        
    elif df.loc[i, 'bedrooms'] > 2:
        df.loc[i, 'dormitory_type'] = 'house'
df[['dormitory_type','sqft_living','price']].groupby('dormitory_type').mean().reset_index()<jupyter_output><empty_output><jupyter_text>“condition_type”- Se o valor da coluna “condition” for menor ou igual à 2 => ‘bad’- Se o valor da coluna “condition” for igual à 3 ou 4 => ‘regular’- Se o valor da coluna “condition” for igual à 5 => ‘good’<jupyter_code>df['condition_type']=df['condition'].apply(lambda x:'bad' if x<= 2 else 
                                                  'regular' if (x==3 or x==4) else 
                                                  'good') 
df[['condition_type','sqft_living','price']].groupby('condition_type').mean().reset_index()
houses = df[df['condition_type'] == 'regular'].shape[0]
print( 'Number of Houses in "regular" condition is: {}'.format( houses ) )
houses = df[(df['condition_type'] == 'bad') & (df['waterfront'] == 1)].shape[0]
print( "Number of Houses with water view and bad condition: {}".format( houses) )
houses = df[(df['condition_type'] == 'good') & (df['house_age'] =="new_house")].shape[0]
print( "Number of new house with good conditional type is: {}".format( houses ))
max_studio_price = df[df['dormitory_type'] == 'studio']['price'].max()
print( "Most expensive studio house: ${}".format( max_studio_price ) )
houses = df[(df['house_age'] == 'new_house') &
(df['yr_renovated'] == 2014 )].shape[0]
print( "Number of reformed house in 2014: {}".format( houses ) )<jupyter_output>Number of reformed house in 2014: 0<jupyter_text>“Level”- Nível 0 -> Preço entre R$ 0 e R$ 321.950 - Nível 1 -> Preço entre R$ 321.950 e R$ 450.000 - Nível 2 -> Preço entre R$ 450.000 e R$ 645.000 - Nível 3 -> Acima de R$ 645.000<jupyter_code>df['level']=df['price'].apply(lambda x:'level_00' if (x >=0)&(x<321950) else
                                      'level_01' if (x >=321950)&(x<450000) else
                                      'level_02' if (x >=45000)&(x<645000) else 'level_03')
df[['level','sqft_living','price']].groupby('level').mean().reset_index()<jupyter_output><empty_output><jupyter_text>"Size"- Size 0 -> Tamanho entre 0 e 1427 sqft - Size 1 -> Tamanho entre 1427 e 1910 sqft - Size 2 -> Tamanho entre 1910 e 2550 sqft - Size 3 -> Tamanho acima de 2550 sqft<jupyter_code>df['size']=df['sqft_living'].apply(lambda x:'s00' if (x >=0)&(x<1427) else
                                      's01' if (x >=1427)&(x<1910) else
                                      's02' if (x >=1910)&(x<2550) else 's03')
df[['size','sqft_living','price']].groupby('size').mean().reset_index()<jupyter_output><empty_output><jupyter_text>Data Vizualization<jupyter_code>plt.figure( figsize=(24,12))
data = df[['price', 'bedrooms']].groupby( 'bedrooms' ).mean().reset_index()
sns.barplot( x='bedrooms', y='price', data=data );
plt.figure( figsize=(24,12))
data = df[['price', 'yr_built']].groupby( 'yr_built' ).mean().reset_index()
sns.lineplot( x='yr_built', y='price', data=data );
df=df.head(10)

#Create empty rows
df['road']='NA'
df['house_number']='NA'
df['city']='NA'
df['county']='NA'
df['state']='NA'
df['neighbourhood']='NA'

geolocator=Nominatim(user_agent='geoapiExercises')

for i in range(len(df)):
    print('Loop:{}/{}'.format(i,len(df)))
    df
    query=str(df.loc[i,'lat'])+','+str(df.loc[i,'long'])

#API Request
    response=geolocator.reverse(query)

#populate df
    if 'house_number' in response.raw['address']:
        df.loc[i,'house_number']=response.raw['address']['house_number']
    if 'road' in response.raw['address']:
        df.loc[i,'road']        =response.raw['address']['road']
    if 'city' in response.raw['address']:
        df.loc[i,'city']        =response.raw['address']['city']
    if 'county' in response.raw['address']:
        df.loc[i,'county']      =response.raw['address']['county']
    if 'state' in response.raw['address']:
        df.loc[i,'state']       =response.raw['address']['state']
    if 'neighbourhood' in response.raw['address']:
        df.loc[i,'neighbourhood']=response.raw['address']['neighbourhood']


#df.to_csv('Datasets/df_complete_lat_long.csv',index=False)
df=pd.read_csv('Datasets/df_complete_lat_long.csv',parse_dates=[1,14,15])
df.head()
%matplotlib inline
msno.matrix(df);
df.dtypes
#data de compra
df['date']=pd.to_datetime(df['date']).dt.strftime('%Y-%m-%d')

#vista mar sim ou nao
df['is_waterfront']=df['waterfront'].apply(lambda x:'yes' if x==1 else 'no')

df['year']=pd.to_datetime(df['date']).dt.strftime('%Y')
df['year']=df['year'].astype(int)

df.head()

# Iteractive buttons

style={'description_width':'initial'}

living_limit=widgets.IntSlider(
    value=2080,
    min=290,
    max=13540,
    step=1,
    description='Minimum sqft living',
    disable=False,
    style=style
)

bathrooms_limit=widgets.IntSlider(
    value=2,
    min=0,
    max=8,
    step=1,
    description='Minimum bathrooms',
    disable=False,
    style=style
)

price_limit=widgets.IntSlider(
    value=540000,
    min=75000,
    max=77000000,
    step=1,
    description='Maximun price',
    disable=False,
    style=style
)

lot_limit=widgets.IntSlider(
    value=15100,
    min=520,
    max=1651359,
    step=1,
    description='Maximum sqft lot',
    disable=False,
    style=style
)

def update_map(df,living,bathrooms,price,lot):
    houses=df[(df['sqft_living']>=living)&(df['bathrooms']>=bathrooms)&(df['price']<=price)&(df['sqft_lot']<=lot)][['id','lat','long','price','sqft_living','bathrooms','sqft_lot']]


#5. Plotar o mapa(dentro da funcao)
    fig=px.scatter_mapbox(houses,
        lat='lat',
        lon='long',
        color='sqft_living',
        size='price',
        color_continuous_scale=px.colors.cyclical.IceFire,
        size_max=15,
        zoom=10)
    fig.update_layout(mapbox_style='open-street-map')
    fig.update_layout(height=300,margin={'r':0,'t':0,'l':0,'b':0})
    fig.show()
widgets.interactive(update_map,df=fixed(df),living=living_limit,bathrooms=bathrooms_limit,price=price_limit,lot=lot_limit)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13218934911242602, 'fraction_numerical': 0.03609467455621302, 'mean_word_length': 1.9159765355417528, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 83, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 137, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '39782', 'license': 'MIT', 'n_tokens_mistral': 5108, 'n_tokens_neox': 4597, 'n_words': 1324, 'path': 'Dashboard.ipynb', 'repo_name': 'alessandra-barbosa/House-Rocket-Insights', 'size': '16900'}