<jupyter_start><jupyter_text>Respondendo novas perguntas do CEO Dataset disponível em: https://www.kaggle.com/harlfoxem/housesalesprediction 1. Qual a média do preço de compra dos imóveis por "Nível"?    - Nível 0 -> Preço entre 0 e 321.950    - Nível 1 -> Preço entre 321.950 e 450.000    - Nível 2 -> Preço entre 450.000 e 645.000    - Nível 3 -> Acima de 645.000<jupyter_code># Importando bibliotecas
import pandas as pd
pd.set_option('display.float_format', lambda x: '%.2f' % x)

# Carregando dataset
df = pd.read_csv('datasets/kc_house_data.csv')

df['level'] = df['price'].apply(lambda x: 0 if x <= 321950 else
                            1 if (x > 321950) & (x <= 450000) else
                            2 if (x > 450000) & (x <= 645000) else
                            3)
df['level'] = df['level'].astype(int)

df[['price', 'level']].groupby('level').mean().reset_index()<jupyter_output><empty_output><jupyter_text>2. Qual a média do tamanho da sala de estar dos imóveis por "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 -> Acima de 2550 sqft<jupyter_code>df['size'] = df['sqft_living'].apply(lambda x: 0 if x <= 1427 else
                                1 if(x > 1427) & (x <= 1910) else
                                2 if(x > 1910) & (x <= 2550) else
                                3)
df['size'] = df['size'].astype(int)

df[['sqft_living', 'size']].groupby('size').mean().reset_index()<jupyter_output><empty_output><jupyter_text>3. Adicione as seguintes informações ao conjunto de dados original:    - Place ID: Identificação da localização    - OSM Type: Open Street Map Type    - Country: Nome do País    - Country Code: Código do País<jupyter_code>from geopy.geocoders import Nominatim

# Initialize Nominatim API
geolocator = Nominatim(user_agent='geoapiExercises')

query='47.51,-122.26'
response = geolocator.reverse(query)
response.raw
import time
import defs
from multiprocessing import Pool

df['query'] = df[['lat', 'long']].apply(
    lambda x: str(x['lat']) + ',' + str(x['long']), axis=1
)


df1 = df[['id', 'query']].head()

p = Pool(2)

start = time.process_time()
df1[['place_id', 'osm_type', 'country', 'country_code']] = p.map(defs.get_data, df1.iterrows())
end = time.process_time()

print('Time Elapsed: {}', end - start)
df1.head()<jupyter_output><empty_output><jupyter_text>4. Adicione os seguintes filtros no Mapa:    - Tamanho mínimo da área da sala de estar    - Número mínimo de banheiros    - Valor máximo do preço    - Tamanho máximo da área do porão    - Filtro das condições do imóvel    - Filtro por ano de construção<jupyter_code>import plotly.express as px
import ipywidgets as widgets
from ipywidgets import fixed

style={'description_width': 'initial'}

df['is_waterfront'] = df['waterfront'].apply(
    lambda x: 'yes' if x == 1
        else  'no')

# Interative buttons
min_living_room = widgets.IntSlider(
    value=int(df['sqft_living'].mean()),
    min=df['sqft_living'].min(),
    max=df['sqft_living'].max(),
    step=1,
    description='Minimum Living Room Size',
    disabled=False,
    style=style
)


min_bathrooms = widgets.IntSlider(
    value=int(df['bathrooms'].mean()),
    min=df['bathrooms'].min(),
    max=10,
    step=1,
    description='Minimum Bathrooms Values',
    disabled=False,
    style=style
)


max_price = widgets.FloatSlider(
    value=int(df['price'].mean()),
    min=int(df['price'].min()),
    max=int(df['price'].max()),
    step=1,
    description='Maximum Price',
    disabled=False,
    style=style
)


max_basement = widgets.FloatSlider(
    value=int(df['sqft_basement'].mean()),
    min=df['sqft_basement'].min(),
    max=df['sqft_basement'].max(),
    step=1,
    description='Maximum Basement Size',
    disabled=False,
    style=style
)

condition_house = widgets.Dropdown(
    options=df['condition'].sort_values().unique().tolist(),
    value=int(df['condition'].mean()),
    description='Condition House',
    disable=False,
    style=style
)

year_built = widgets.Dropdown(
    options=df['yr_built'].sort_values().unique().tolist(),
    value=int(df['yr_built'].mean()),
    description='Year Built',
    disable=False,
    style=style
)

def update_map(data, living_room, min_bathrooms, max_price, basement, condition_house, year_built):
    
    houses = data[(data['sqft_living'] > living_room) &                              (data['bathrooms'] > min_bathrooms) &                              (data['price'] < max_price) & 
                  (data['sqft_basement'] < basement) &                                (data['condition'] == condition_house) &                            (data['yr_built'] > year_built)][['id', 'lat', 'long', 'price', 'sqft_living', 'level']].copy()
    
    # plot map
    fig = px.scatter_mapbox(houses,
                    lat='lat',
                    lon='long',
                    color='level',
                    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=600, margin={'r':0, 't':0, 'l':0, 'b':0})
    fig.show()
    
# Plot map    
widgets.interactive(update_map, 
                    data = fixed(df), 
                    living_room = min_living_room, 
                    min_bathrooms = min_bathrooms, 
                    max_price = max_price, 
                    basement = max_basement, 
                    condition_house = condition_house, 
                    year_built = year_built)<jupyter_output><empty_output><jupyter_text>5. Adicione os seguintes filtros no Dashboard:    - Filtro por data disponível para compra    - Filtro por ano de renovação    - Filtro se possui vista para a água ou não<jupyter_code>import ipywidgets as widgets
from matplotlib import gridspec
from matplotlib import pyplot as plt
import seaborn as sns
import pandas as pd 

# convert object columns to date 
df['year'] = pd.to_datetime(df['date']).dt.strftime('%Y')
df['date'] = pd.to_datetime(df['date']).dt.strftime('%Y-%m-%d')
df['year_week'] = pd.to_datetime(df['date']).dt.strftime('%Y-%U')

date_limit = widgets.SelectionSlider(
    options = df['date'].sort_values().unique().tolist()
    ,value='2014-12-01'
    ,description = 'Max Available Date'
    ,disable = False
    ,continuous_update = False
    ,orientation='horizontal'
    ,style=style
    ,readout = True
)

yr_limit = widgets.SelectionSlider(            
    options = df['yr_renovated'].sort_values().unique().tolist()
    ,value=2000
    ,description='Max Year'
    ,disable=False
    ,continuous_update = False
    ,orientation='horizontal'
    ,style=style
    ,readout = True
)

water_front = widgets.Checkbox(
    value=False
    ,description='Is Water Front?'
    ,disable=False
    ,indent=False
)

def update_map(df, data_limit, yr_limit, is_water_front):
    
    house = df[(df['date'] <= data_limit) & 
              (df['yr_renovated'] >= yr_limit) & 
              (df['waterfront'] == is_water_front)].copy()
    
    fig = plt.figure(figsize=(24,12))
    specs = gridspec.GridSpec(ncols=2, nrows=2, figure=fig)
    
    ax1 = fig.add_subplot(specs[0,:])
    ax2 = fig.add_subplot(specs[1,0])
    ax3 = fig.add_subplot(specs[1,1])
    
    by_year = house[['price', 'year']].groupby('year').sum() \
 .reset_index()
    sns.barplot(x='year', y='price', data=by_year, ax=ax1)
    
    by_day = house[['price', 'date']].groupby('date').mean() \
 .reset_index()
    sns.lineplot(x='date', y='price', data=by_day, ax=ax2)
    plt.xticks(rotation=90);
    
    by_week =house[['price', 'year_week']].groupby('year_week') \
    .mean().reset_index()
    sns.barplot(x='year_week', y='price', data=by_week, ax=ax3)
    plt.xticks(rotation=90);
    
widgets.interactive(update_map, 
                    df=fixed(df), 
                    data_limit=date_limit, 
                    yr_limit=yr_limit, 
                    is_water_front=water_front )<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14463318562284588, 'fraction_numerical': 0.024618414574101428, 'mean_word_length': 2.84160756501182, 'pattern_counts': {'":': 0, '<': 30, '<?xml version=': 0, '>': 37, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '347858', 'license': 'MIT', 'n_tokens_mistral': 2929, 'n_tokens_neox': 2731, 'n_words': 570, 'path': '02-Python-Zero-DS/Mod-04/resolucao_exercicio_04.ipynb', 'repo_name': 'fgandrade/Estudos-Comunidade-DS', 'size': '8124'}