# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# <h1 id="exercises">Exercises</h1>
# <p>Within your <code>codeup-data-science</code> directory, create a new directory named <code>clustering-exercises</code>. This will be where you do your work for this module. Create a repository on GitHub with the same name, and link your local repository to GitHub.</p>
# <p>Save your clustering work in your <code>clustering-exercises</code> repo. Then add, commit, and push your changes.</p>
# <p>For example, if the
# exercise directs you to create a file named <code>myfile.py</code>, you should have
# <code>clustering/myfile.py</code> in your repository.</p>
# <p>If a file extension is specified, you should create that specific file. If there
# is not file extension specified, you may either create a python script or a
# jupyter notebook for the exercise.</p>
# <div class="admonition tip">
# <p class="admonition-title">Workflow</p>
# <p>Throughout the exercises, you may wish to do your work in a notebook, then
# transfer any functions you've created to an external python script.</p>
# <p>Keep in mind this is not always a linear process! You will probably be
# cycling between a notebook and an external python script frequently.</p>
# <p>Remember to run your code often to check for correct output and/or errors.</p>
# </div>
#

# ---

#
# <h2 id="acquire-acquirepy">Acquire (acquire.py)</h2>
# <h3 id="zillow">Zillow</h3>
# <p>For the following, iterate through the steps you would take to create functions:
# Write the code to do the following in a jupyter notebook, test it, convert to functions, then create the file to house those functions. </p>
# <p>You will have a zillow.ipynb file and a helper file for each section in the pipeline. </p>
# <h4 id="acquire-summarize_1">acquire &amp; summarize</h4>
#

#
# <ol>
# <li>
# <p>Acquire data from mySQL using the python module to connect and query. You will want to end with a single dataframe. Make sure to include: the logerror, all fields related to the properties that are available. You will end up using all the tables in the database. </p>
# <ul>
# <li><strong><em>Be sure to do the correct join (inner, outer, etc.). We do not want to eliminate properties purely because they may have a null value for <code>airconditioningtypeid</code>.</em></strong>  </li>
# <li>Only include properties with a transaction in 2017, and include only the last transaction for each property (so no duplicate property ID's), along with zestimate error and date of transaction.  </li>
# <li>Only include properties that include a latitude and longitude value.  </li>
# </ul>
# </li>
#

# +
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

import warnings
warnings.filterwarnings('ignore')
import env

#import wrangle_zillow

# +
import warnings
warnings.filterwarnings("ignore")

# Wrangling
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split


# Visualizing
# %matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns

# default pandas decimal number display format
pd.options.display.float_format = '{:.2f}'.format


# -

def get_connection(database):
    '''
    Database: string; name of database that the url is being created for
    '''
    return f'mysql+pymysql://{env.user}:{env.password}@{env.host}/{database}'


sql_query = '''
SELECT prop.*, logerror, transactiondate, airconditioningdesc, architecturalstyledesc, buildingclassdesc, heatingorsystemdesc, propertylandusedesc, storydesc, typeconstructiondesc
FROM properties_2017 as prop 
INNER JOIN (
	SELECT id, p.parcelid, logerror, transactiondate
	FROM predictions_2017 AS p
	INNER JOIN (
		SELECT parcelid,  MAX(transactiondate) AS max_date
		FROM predictions_2017 
		GROUP BY (parcelid)) AS sub
			ON p.parcelid = sub.parcelid
		WHERE p.transactiondate = sub.max_date
		) AS subq
	ON prop.parcelid = subq.parcelid
LEFT JOIN airconditioningtype
	ON prop.airconditioningtypeid = airconditioningtype.airconditioningtypeid
LEFT JOIN architecturalstyletype
	ON prop.architecturalstyletypeid = architecturalstyletype.architecturalstyletypeid
LEFT JOIN buildingclasstype 
	ON prop.buildingclasstypeid = buildingclasstype.buildingclasstypeid
LEFT JOIN heatingorsystemtype
	ON prop.heatingorsystemtypeid = heatingorsystemtype.heatingorsystemtypeid
LEFT JOIN propertylandusetype
	ON prop.propertylandusetypeid = propertylandusetype.propertylandusetypeid
LEFT JOIN storytype
	ON prop.storytypeid = storytype.storytypeid
LEFT JOIN typeconstructiontype
	ON prop.typeconstructiontypeid = typeconstructiontype.typeconstructiontypeid
WHERE latitude IS NOT NULL 
	AND longitude IS NOT NULL
	AND transactiondate BETWEEN '2017-01-01' AND '2017-12-31';
'''

sqlquery2='''
SELECT prop.*, 
       pred.logerror, 
       pred.transactiondate, 
       air.airconditioningdesc, 
       arch.architecturalstyledesc, 
       build.buildingclassdesc, 
       heat.heatingorsystemdesc, 
       landuse.propertylandusedesc, 
       story.storydesc, 
       construct.typeconstructiondesc 

FROM   properties_2017 prop  
       INNER JOIN (SELECT parcelid,
                          logerror,
                          Max(transactiondate) transactiondate 
                   FROM   predictions_2017 
                   GROUP  BY parcelid, logerror) pred
               USING (parcelid) 
       LEFT JOIN airconditioningtype air USING (airconditioningtypeid) 
       LEFT JOIN architecturalstyletype arch USING (architecturalstyletypeid) 
       LEFT JOIN buildingclasstype build USING (buildingclasstypeid) 
       LEFT JOIN heatingorsystemtype heat USING (heatingorsystemtypeid) 
       LEFT JOIN propertylandusetype landuse USING (propertylandusetypeid) 
       LEFT JOIN storytype story USING (storytypeid) 
       LEFT JOIN typeconstructiontype construct USING (typeconstructiontypeid) 
WHERE  prop.latitude IS NOT NULL 
       AND prop.longitude IS NOT NULL
'''


def acquire_cache_zillow():
    url = get_connection('zillow')
    zillow = pd.read_sql(sql_query, url)
    zillow.to_csv('zillow.csv')
    zillow = pd.read_csv('zillow.csv')
    zillow.drop(columns=['Unnamed: 0'], inplace=True)
    return zillow


def acquire_cache_zillow2():
    url = get_connection('zillow')
    zillow2 = pd.read_sql(sqlquery2, url)
    zillow2.to_csv('zillow2.csv')
    zillow2 = pd.read_csv('zillow2.csv')
    zillow2.drop(columns=['Unnamed: 0'], inplace=True)
    return zillow2


# +
#zillow = acquire_cache_zillow()

# +
#Acquire data- commented out - just use saved CSV
#zillow2 = acquire_cache_zillow2()
# -

df = pd.read_csv('zillow2.csv')

#print(zillow.shape)
print(df.shape)

df.head()

# 2. Summarize your data (summary stats, info, dtypes, shape, distributions, value_counts, etc.)</p>
#
#

df.describe().T

df.hist();


df.info()

df.dtypes

shape1 = df.shape

# +
# How many nulls have in each column?

df.isnull().sum()

# +
# % values missing in each column

missing_pct = df.isnull().sum()/len(df)*100
missing_pct.sort_values(missing_pct[1], ascending=False).head(51)

# +
# Percent complete rach column

complete = (100 - (df.isnull().sum()/len(df)*100))
complete

# +
# How many nulls have in each row?

df.isnull().sum(axis =1).value_counts()
# -

columns = df.columns.to_list()


for col in columns:
    print(col.upper())
    print('\n')
    print(df[col].value_counts())
    print('\n')


# 3. Write a function that takes in a dataframe of observations and attributes and returns a dataframe where each row is an atttribute name, the first column is the number of rows with missing values for that attribute, and the second column is percent of total rows that have missing values for that attribute. Run the function and document takeaways from this on how you want to handle missing values. 
#
#

#
# <table>
# <thead>
# <tr>
# <th></th>
# <th align="right">num_rows_missing</th>
# <th align="right">pct_rows_missing</th>
# </tr>
# </thead>
# <tbody>
# <tr>
# <td>parcelid</td>
# <td align="right">0</td>
# <td align="right">0.000000</td>
# </tr>
# <tr>
# <td>airconditioningtypeid</td>
# <td align="right">29041</td>
# <td align="right">0.535486</td>
# </tr>
# <tr>
# <td>architecturalstyletypeid</td>
# <td align="right">54232</td>
# <td align="right">0.999982</td>
# </tr>
# </tbody>
# </table>
#
#

def describe_missing(df):
    columns = df.columns.to_list()
    num_rows_missing = []
    pct_rows_missing = []
    null_df = pd.DataFrame()
    for column in columns:
        num_rows_missing.append(df[column].isna().sum())
        pct_rows_missing.append(df[column].isna().sum()/df[column].shape[0]*100)
    null_df['attribute'] = columns
    null_df['num_rows_missing'] = num_rows_missing
    null_df['pct_rows_missing'] = pct_rows_missing
    return null_df


describe_missing(df).sort_values(by=['pct_rows_missing'], ascending=False).head(51)


# 4. Write a function that takes in a dataframe and returns a dataframe with 3 columns: the number of columns missing, percent of columns missing, and number of rows with n columns missing. Run the function and document takeaways from this on how you want to handle missing values. 
#
#

#
# <table>
# <thead>
# <tr>
# <th>num_cols_missing</th>
# <th align="right">pct_cols_missing</th>
# <th align="right">num_rows</th>
# </tr>
# </thead>
# <tbody>
# <tr>
# <td>23</td>
# <td align="right">38.333</td>
# <td align="right">108</td>
# </tr>
# <tr>
# <td>24</td>
# <td align="right">40.000</td>
# <td align="right">123</td>
# </tr>
# <tr>
# <td>25</td>
# <td align="right">41.667</td>
# <td align="right">5280</td>
# </tr>
# </tbody>
# </table>
#

def missing_columns(df):
    columns = df.columns.to_list()
    nulls_by_row = []
    for i in range(df.shape[0]):
        nulls_by_row.append(df.iloc[i].isna().sum())
    df['null_count'] = nulls_by_row
    df['pct_col_missing'] = df.null_count / len(columns)
    df2 = df.pct_col_missing.value_counts().rename_axis('pct_col_missing').reset_index(name='num_rows')
    df3 = df.null_count.value_counts().rename_axis('num_cols_missing').reset_index(name='num_rows').drop(columns=['num_rows'])
    df = pd.concat([df3, df2], axis = 1)
    return df     


missing_columns(df).sort_values(by=['num_cols_missing'], ascending=False).head(100)

#
# <h4 id="prepare">Prepare</h4>
# <ol>
# <li>
# <p>Remove any properties that are likely to be something other than single unit properties. (e.g. no duplexes, no land/lot, ...). There are multiple ways to estimate that a property is a single unit, and there is not a single "right" answer. But for this exercise, do not purely filter by unitcnt as we did previously. Add some new logic that will reduce the number of properties that are falsely removed. You might want to use # bedrooms, square feet, unit type or the like to then identify those with unitcnt not defined.  </p>
#
#
#

df.propertylandusedesc.isna().sum()


df.propertylandusedesc.value_counts()


df = df[
    (df.propertylandusedesc == 'Single Family Residential')|
    (df.propertylandusedesc == 'Condominium')|
    (df.propertylandusedesc == 'Planned Unit Development')|
    (df.propertylandusedesc == 'Mobile Home')|
    (df.propertylandusedesc == 'Manufactured, Modular, Prefabricated Homes')|
    (df.propertylandusedesc == 'Residential General')
       ]

shape2 = df.shape

# +
#rows_d = shape1[0]-shape2[0]
#columns_d = shape1[1]-shape2[1]

print(f'Original shape: {shape1}')
print(f'Current shape: {shape2}')
print(f'Rows dropped: {shape1[0]-shape2[0]}')
print(f'Columns dropped: {shape1[1]-shape2[1]}')
# -

df.calculatedfinishedsquarefeet.describe()


# Removing rows where bedroomcnt == 0
df = df[df.bedroomcnt > 0]

shape3 = df.shape
print(f'Original shape: {shape1}')
print(f'Last shape: {shape2}')
print(f'Current shape: {shape3}')
print()
print(f'Total Rows dropped: {shape1[0]-shape3[0]}')
print(f'Total Columns dropped: {shape1[1]-shape3[1]}')
print()
print(f'Rows dropped this step: {shape2[0]-shape3[0]}')
print(f'Columns dropped this step: {shape2[1]-shape3[1]}')

# Removing rows where unitcnt is greater than 1
df = df[~(df.unitcnt > 1)]

shape4 = df.shape
print(f'Original shape: {shape1}')
print(f'Last shape: {shape3}')
print(f'Current shape: {shape4}')
print()
print(f'Total Rows dropped: {shape1[0]-shape4[0]}')
print(f'Total Columns dropped: {shape1[1]-shape4[1]}')
print()
print(f'Rows dropped this step: {shape3[0]-shape4[0]}')
print(f'Columns dropped this step: {shape3[1]-shape4[1]}')

# 2. <p>Create a function that will drop rows or columns based on the percent of values that are missing: handle_missing_values(df, prop_required_column, prop_required_row).  </p>
# <ul>
# <li>The input:<ul>
# <li>A dataframe</li>
# <li>A number between 0 and 1 that represents the proportion, for each column, of rows with non-missing values required to keep the column.  i.e. if prop_required_column = .6, then you are requiring a column to have at least 60% of values not-NA (no more than 40% missing).</li>
# <li>A number between 0 and 1 that represents the proportion, for each row, of columns/variables with non-missing values required to keep the row. For example, if prop_required_row = .75, then you are requiring a row to have at least 75% of variables with a non-missing value (no more that 25% missing). </li>
# </ul>
# </li>
# <li>The output:<ul>
# <li>The dataframe with the columns and rows dropped as indicated. <em>Be sure to drop the columns prior to the rows in your function.</em></li>
# </ul>
# </li>
# <li>hint:<ul>
# <li>Look up the dropna documentation. </li>
# <li>You will want to compute a threshold from your input values (prop_required) and total number of rows or columns.</li>
# <li>Make use of inplace, i.e. inplace=True/False.</li>
# </ul>
#

df.head()


def handle_missing_values(df, prop_required_column = .5, prop_required_row = .75):
    threshold = int(round(prop_required_column*len(df.index),0))
    df.dropna(axis=1, thresh=threshold, inplace=True)
    threshold = int(round(prop_required_row*len(df.columns),0))
    df.dropna(axis=0, thresh=threshold, inplace=True)
    return df


handle_missing_values(df)


shape5 = df.shape
print(f'Original shape: {shape1}')
print(f'Last shape: {shape4}')
print(f'Current shape: {shape5}')
print()
print(f'Total Rows dropped: {shape1[0]-shape5[0]}')
print(f'Total Columns dropped: {shape1[1]-shape5[1]}')
print()
print(f'Rows dropped this step: {shape4[0]-shape5[0]}')
print(f'Columns dropped this step: {shape4[1]-shape5[1]}')

# 3. Decide how to handle the remaining missing values: </p>
# <ul>
# <li>Fill with constant value.</li>
# <li>Impute with mean, median, mode.</li>
# <li>Drop row/column</li>
# </ul>
# </li>
# </ol>
#
#

describe_missing(df).sort_values(by=['pct_rows_missing'], ascending=False).head(17)


# Create a dictionary to reference later for the decision on each feature
null_plans = {}

# Building type id
df.buildingqualitytypeid.describe()


plt.hist(df.buildingqualitytypeid);


# Tight sd around mean, roughly symmetrical, use mean

null_plans['buildingqualitytypeid'] = 'mean'


bath_bed_df = df[['bathroomcnt','bedroomcnt','calculatedbathnbr']] 
bath_bed_df

bath_bed_df['diff']=bath_bed_df.bathroomcnt-bath_bed_df.calculatedbathnbr

bath_bed_df.describe()

print(df.bathroomcnt.isna().sum())
print(df.calculatedbathnbr.isna().sum())


# No difference between calculated bathroom bumber and bathroom count, but more nulls in `calculatedbathnbr` column, so can drop that columns

null_plans['calculatedbathnbr'] = 'drop_column'


df.head()

# +
# Calculated SF

area_df = df[['calculatedfinishedsquarefeet','finishedsquarefeet12']] 
area_df['difference']=area_df.calculatedfinishedsquarefeet-area_df.finishedsquarefeet12
area_df.describe()
# -

print(df.calculatedfinishedsquarefeet.isna().sum())
print(df.finishedsquarefeet12.isna().sum())

# no difference between calculated and finished sf, but only 10 nulls in `calculatedfinishedsquarefeet` so dropp `finishedsquarefeet12`

null_plans['finishedsquarefeet12'] = 'drop_column'


# calculatedfinishedsquarefeet sf
plt.hist(df.calculatedfinishedsquarefeet);

# Positive skew, use median to impute

null_plans['calculatedfinishedsquarefeet'] = 'median'


df.head()

# Full bath
print(df.fullbathcnt.isna().sum())


plt.hist(df.fullbathcnt);
plt.show();

null_plans['fullbathcnt'] = 'median'


df.heatingorsystemdesc.isna().sum()


len(df.heatingorsystemdesc)

# Percent missing
(df.heatingorsystemdesc.isna().sum()/len(df.heatingorsystemdesc))*100

df.heatingorsystemdesc.value_counts()


# Probably have AC in CA, so missing != `0`, use mode for imputing.

null_plans['heatingorsystemtypeid'] = 'mode'
null_plans['heatingorsystemdesc'] = 'mode'

plt.hist(df.lotsizesquarefeet);


df.lotsizesquarefeet.describe()


# Huge deviation. Skewed data, use median

null_plans['lotsizesquarefeet'] = 'median'


df.propertyzoningdesc.isna().sum()


(df.propertyzoningdesc.isna().sum()/len(df.propertyzoningdesc))*100

df.propertyzoningdesc.value_counts()

df.head()

import scipy


def describe_column(df, col):
    na_count =df[col].isna().sum()
    na_percent = (na_count/len(df[col]))*100
    skew = scipy.stats.skew(df[col], nan_policy='omit')
    kurt = scipy.stats.kurtosis(df[col], axis=0, fisher=True, bias=False, nan_policy='omit')
    print(f'Number of missing values: {na_count}')
    print(f'Percent values missing: {na_percent}')
    print()
    print('Summary Statistics:')
    print(df[col].describe())
    print()
    print('Value counts:')
    print(df[col].value_counts())
    print()
    print(f'Sample skew: {skew}')
    print(f'Sample kurtosis: {kurt}')
    plt.hist(df[col])
    plt.show()
    #sns.distplot(df, x=df.col)



df.dtypes

df.head()

sns.distplot(df, x=df.bathroomcnt)

df.head()

describe_column(df,'buildingqualitytypeid')

# tight deviation about mean, roughly normal, use mean

null_plans['buildingqualitytypeid'] = 'mean'


df.head()

describe_column(df, 'assessmentyear')

# nothing missing

df.head()

describe_column(df, 'landtaxvaluedollarcnt')

# just one missing record, drop row

null_plans['buildingqualitytypeid'] = 'drop_row'


df.head()

describe_column(df, 'censustractandblock')

# Small number of values, drop

null_plans['censustractandblock'] = 'drop_row'


describe_column(df, 'propertyzoningdesc')

# Lots of categorical info. Many missing. Would have to one-hot encode, but way too many - drop columns

null_plans['propertyzoningdesc'] = 'drop_column'


describe_column(df, 'regionidcity')

# Categorical, we have other columns giving us location info, drop column

null_plans['regionidcity'] = 'drop_column'


describe_column(df, 'roomcnt')

df.roomcnt.value_counts()

# Lots of 0s for roomcount. Doesn't make sense. Drop column.

null_plans['roomcnt'] = 'drop_column'


describe_column(df, 'unitcnt')

# We got rid of all properties where this value != 1, so it can be dropped

null_plans['unitcnt'] = 'drop_column'


describe_column(df, 'yearbuilt')

null_plans['yearbuilt'] = 'mean'


describe_column(df, 'structuretaxvaluedollarcnt')

null_plans['structuretaxvaluedollarcnt'] = 'median'


describe_column(df, 'taxvaluedollarcnt')

null_plans['taxvaluedollarcnt'] = 'median'


describe_column(df, 'landtaxvaluedollarcnt')

null_plans['landtaxvaluedollarcnt'] = 'median'

describe_column(df, 'taxamount')

null_plans['taxamount'] = 'median'


df.head()

describe_column(df, 'logerror')

df.dtypes

describe_column(df, 'rawcensustractandblock')

# The calculated columns, `null_count` and `pct_col_missing` were useful for this step, but don't have predictive value for our model so they can be dropped

null_plans['null_count'] = 'drop_column'
null_plans['pct_col_missing'] = 'drop_column'

null_plans





# ---

#
# <h4 id="wrangle_zillowpy">wrangle_zillow.py</h4>
# <p>Functions of the work above needed to acquire and prepare a new sample of data. </p>
#







# ---

#
# <h3 id="mall-customers">Mall Customers</h3>
# <h4 id="notebook">notebook</h4>
# <ol>
# <li>Acquire data from mall_customers.customers in mysql database. </li>
#
#



#
# 2. Summarize data (include distributions and descriptive statistics).</li>
#
#





# 3. Detect outliers using IQR.</li>
#
#





# 4. Split data (train, validate, and test split).</li>
#
#





# 5. Encode categorical columns using a one hot encoder (pd.get_dummies).</li>
#
#







# 6. Handles missing values.</li>
#
#







# 7. Scaling</li>
#
#





# ---

#
# </ol>
# <h4 id="wrangle_mallpy">wrangle_mall.py</h4>
#

#
# <ol>
# <li>Acquire data from mall_customers.customers in mysql database. </li>
#
#





# 2. Split the data into train, validate, and split</li>
#







# 3. One-hot-encoding (pd.get_dummies)</li>
#
#





# 4. Missing values</li>
#
#





# 5. Scaling</li>
# </ol>







StarcoderdataJupyterScriptsDedupFiltered