<jupyter_start><jupyter_text>Preperations Lets first import the relevant libraries and then load the data into the dataframe df<jupyter_code>import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt


from scipy import stats
import statsmodels.api as sm
import statsmodels.formula.api as smf
df = pd.read_csv("../data/King_County_House_prices_dataset.csv")<jupyter_output><empty_output><jupyter_text>Basic description of the data First, we want to take a first look at our data and analyse its contents<jupyter_code>df.head(5)
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 21597 entries, 0 to 21596
Data columns (total 21 columns):
 #   Column         Non-Null Count  Dtype  
---  ------         --------------  -----  
 0   id             21597 non-null  int64  
 1   date           21597 non-null  object 
 2   price          21597 non-null  float64
 3   bedrooms       21597 non-null  int64  
 4   bathrooms      21597 non-null  float64
 5   sqft_living    21597 non-null  int64  
 6   sqft_lot       21597 non-null  int64  
 7   floors         21597 non-null  float64
 8   waterfront     19221 non-null  float64
 9   view           21534 non-null  float64
 10  condition      21597 non-null  int64  
 11  grade          21597 non-null  int64  
 12  sqft_above     21597 non-null  int64  
 13  sqft_basement  21597 non-null  object 
 14  yr_built       21597 non-null  int64  
 15  yr_renovated   17755 non-null  float64
 16  zipcode        21597 non-null  int64  
 17  lat            21597 non-null  float64
 18  long  [...]<jupyter_text>1. Non-null datapoints: Except the columns 'waterfront' and 'yr_renovated' all the columns only contain non-null datapoints. 21597 datapoints exist. 1. Datatype: 'date' and 'sqft_basement' have a object datatype, the rest are either int 64 or float 64 Lets take a look at the distribution of prices since this is the independent value we are most interested in<jupyter_code>plt.figure(figsize=(6, 3))
fig = sns.distplot(df.price, color='blue', bins=100, hist_kws={'alpha':0.4})
fig.set(xlabel='Price', ylabel='Density')
plt.show
df.price.describe()<jupyter_output><empty_output><jupyter_text>The distribution of the price seems to follow a normal distribution. However, at  around 1,5 mil the distribution has a bump. the prices fo up to a maximum of 7.7 mil. Above 1 mill:<jupyter_code>plt.figure(figsize=(6, 3))

fig = sns.histplot(df.query('price > 1000000').price, color='blue', bins=100)
fig.set(xlabel='Price', ylabel='Density')
plt.show<jupyter_output><empty_output><jupyter_text>Above a price of 4 mill we can still see some datapoints. Since our client is more interested in cheaper houses, we should concentrate on houses below 1 mill. Later, we will exclude values above 1 mill for our model We will plot now every column of the dataframe df (excluding the id column)<jupyter_code>df_no_id = df.drop(labels='id', inplace=False, axis =1)
df_no_id.hist(bins =30, figsize = (15,19))<jupyter_output><empty_output><jupyter_text>bedrooms, bathrooms, sqft_living, sqft_lot, sqft_above, long, sqft_living15, sqft_lot15 seem to follow the same distribution like price*date and sqft_basement were not plotted since they have the datatype object. I was not too interested in these parameters, so I did not concentrate on them.*  I will check now which factors correlate with the price:<jupyter_code>df_no_id_corr = df_no_id.corr()['price'][1:]
best_correlators = df_no_id_corr[abs(df_no_id_corr)>0.5]
print ('correlations:\n' , df_no_id_corr, '\n\n')
print ('best correlators: \n' , best_correlators)<jupyter_output>correlations:
 bedrooms         0.308787
bathrooms        0.525906
sqft_living      0.701917
sqft_lot         0.089876
floors           0.256804
waterfront       0.276295
view             0.395734
condition        0.036056
grade            0.667951
sqft_above       0.605368
yr_built         0.053953
yr_renovated     0.129599
zipcode         -0.053402
lat              0.306692
long             0.022036
sqft_living15    0.585241
sqft_lot15       0.082845
Name: price, dtype: float64 


best correlators: 
 bathrooms        0.525906
sqft_living      0.701917
grade            0.667951
sqft_above       0.605368
sqft_living15    0.585241
Name: price, dtype: float64<jupyter_text>Now I want to plot the price against the different factors<jupyter_code>for i in range (0, len(df_no_id.columns), 5):
    sns.pairplot(data=df_no_id, y_vars=['price'], x_vars=df_no_id.columns[i:i+5])<jupyter_output><empty_output><jupyter_text>Bedrooms, bathrooms, sqft_living, sqft_lot, grade, sqft_above, sqft_living15, sqft_lot15 seem to correlate with the price. Since zipcode is more like a categorical factor, it won`t correlate here with the price, when we just plot its value  Hypotheses  Testing every factor Now I will test if any of the factors shows a positve result when making an OLS model<jupyter_code>smf.ols(formula='price ~  bedrooms + bathrooms + sqft_living + sqft_lot + floors + waterfront + view + condition + grade + sqft_above + yr_built + yr_renovated + zipcode + lat + long + sqft_living15 + sqft_lot15', data=df).fit().summary()<jupyter_output><empty_output><jupyter_text>The highest t-values are reached for bedrooms (-16.895), bathrooms (11.564), sqft_living (29.892), waterfront (29.679), view (20.696), grade (36.470), yr_built(-31.111), zipcode (-14.614), lat (47.578) and long (-13.964	)Since the zipcode are random numbers its correlation with the price does not make any sense here. Also the lat and long are most likely not good predictors for house prices, since cities can be complex with many different areas, regarding the housing prices.   `My hypotheses:`1. If there is a waterfront the price will be pricier1. The worse the grade and the condition the cheaper the house1. The price depends on the zip code*the longer the last renovation has been done the cheaper the price. year renovated: does not want frehsly renovated*  Old Hypothesis (not relevant)  Price dependency on Waterfront First, I want to check the price dependency by the waterfront. The waterfront factor is a categorical factor (0 meaning no waterfront, 1 meaning with waterfront). 2376 values are unknown (NaN). They will not be included in the analysis.  The mean value of the price already gives us a good hint that the waterfront has an influence on the price:<jupyter_code>df.groupby('waterfront').price.mean()<jupyter_output><empty_output><jupyter_text>The mean price of a house with a waterfront is around three times higher.  Now I want to plot the price and waterfront in a scatterplot with a logarithmic axis for price.<jupyter_code>fig = df.plot(kind='scatter', x='waterfront', y='price', alpha=0.4)
fig.set(xlabel='Waterfront', ylabel='Price')
fig.set_yscale('log')<jupyter_output><empty_output><jupyter_text>Here we can also see the shift in price depending on the waterfront value Next I want to test my hypothesis using the OLS model.<jupyter_code>smf.ols(formula='price ~ waterfront', data=df).fit().summary()<jupyter_output><empty_output><jupyter_text>price = 5.326 * 1005 + 1.185 * 1006 * waterfrontThe t-value of the coefficient and the slope seem quite high and the P>|t| quite low.<jupyter_code>sns.lmplot(data=df, x='waterfront', y='price')<jupyter_output><empty_output><jupyter_text>Interpretation: Usually an expensive house is not described by its location at a body of water. Of course there are many expensive houses that do not have a waterfront. That is probably the reason why the R2 has such a low value (0.076)  Price dependency on condition and grade Here, I want to analyse the dependence of the price on the condition and the grade. First, I want to get an overview over these factors. Therefore, I am grouping the dataset on the condition/grade and checking the mean price.<jupyter_code>df.groupby('condition').price.mean()
df.groupby('grade').price.mean()<jupyter_output><empty_output><jupyter_text>We can already see that the grade is a very stable predictor of the mean price. For the condition this relationship is not so strong, however, it still seems to exist.  Next I will plot these variables again:<jupyter_code>fig = df.plot(kind='scatter', x='condition', y='price', alpha=0.4)
fig = df.plot(kind='scatter', x='grade', y='price', alpha=0.4)
fig.set_yscale('log')<jupyter_output><empty_output><jupyter_text>The plots underline the previous finding.  Next I want to test my hypothesis using the OLS model.<jupyter_code>smf.ols(formula='price ~ condition', data=df).fit().summary()
smf.ols(formula='price ~ grade', data=df).fit().summary()<jupyter_output><empty_output><jupyter_text>The t value for condition is not quite as high. Also with an R2> of 0.001 this value is extremely low. However, the t value and its corresponding P>|t| are relatively high and low, respectively. Also the R2> value (0.446) is much higher compared to condition.   price = 4.709 x 105 +	2.036 x 104  x conditionprice  =  -1.061 x 106   +	2.092 x 105  x grade Interpretation:The price of the house seems to correlate with the grade of the house. This grade is based on King County grading system, which seems to be a much better predictor than the overall condition. It is not clear to me, how the overall condition was estimated.  Lets plot the residuals:<jupyter_code>...
sns.lmplot(data=df, x='condition', y='price')
sns.lmplot(data=df, x='grade', y='price')<jupyter_output><empty_output><jupyter_text>In the condition plot we can see that many pricy houses got a medium condition. It might be that these houses actually have a good grade but they need to get a renovation. I will exclude houses where the last rennovation was long ago.   First, I will look at the distribution of yr_renovated, which shows the year of the last rennovation.<jupyter_code>df.yr_renovated.plot(kind='hist',bins=100)<jupyter_output><empty_output><jupyter_text>Most of the values have a value of zero, which probably means that it is not known when they were renovated last, or they were never renovated. If we take a look of the distribution of the acutally renovated houses, we see this:<jupyter_code>df[df['yr_renovated']>1750].yr_renovated.plot(kind='hist', bins =50)<jupyter_output><empty_output><jupyter_text>Lets check the condition distribution of these houses:<jupyter_code>df_ren = df[df['yr_renovated']>1750]
df_ren.plot(kind='scatter',x='condition',y='price')<jupyter_output><empty_output><jupyter_text>If we make a regression model, we get the following:<jupyter_code>smf.ols(formula='price ~ condition', data=df).fit().summary()<jupyter_output><empty_output><jupyter_text>Again, the R2 value is extremly low. Lets look at the yr_renovated distribution again.  Now lets exclude houses that were not renovated after 1985<jupyter_code>df_ren = df[df['yr_renovated']>1985]
df_ren.plot(kind='scatter',x='condition',y='price')<jupyter_output><empty_output><jupyter_text>The distribution still looks very similar. excluding certain values in yr_renovated did not change the appearance of the condition-distribution.  I will quickly check with what the condition correlates:<jupyter_code>df_no_id_corr = df_no_id.corr()['condition']
df_no_id_corr<jupyter_output><empty_output><jupyter_text>Condtion seems to correlate with the year the house was built.<jupyter_code>df.yr_built.plot(kind='hist')<jupyter_output><empty_output><jupyter_text>lets exclude houses that were built before 1950 and plot the distribution of the condition again.<jupyter_code>df_built = df[df['yr_built']>=1950]
df_built.plot(kind='scatter',x='condition',y='price')<jupyter_output><empty_output><jupyter_text>The distribution still looks the same. I cannot figure out, why the condition distirbution looks like that...  Price Dependency on Zip Code Now I want to look if the price depends on the zip code. First I want to get an overview of the data by 1. grouping the houses with the same zip code together, calculating their mean value and sorting them on the price.1. Next, I am creating a new dataframe from this Series, and turning the zipcodes into strings1. Last, I want to plot this new dataframe as a scatterplot<jupyter_code>#1
sorted_df = df.groupby('zipcode').price.mean().sort_values()
#2
df_sorted_df = pd.DataFrame({'zipcode':sorted_df.index, 'price':sorted_df.values})
df_sorted_df['zipcode'] = df_sorted_df['zipcode'].apply(str)
#3
fig = df_sorted_df.plot(kind='scatter', x='zipcode', y='price')
for tick in fig.get_xticklabels():
    tick.set_rotation(90)
   
plt.show<jupyter_output><empty_output><jupyter_text>We can see here that different zip codes clearly have different average prices. Four zip codes seem to be exceptionally expensive. Furthermore, there seems to be one cluster of zip codes that are separated by the others by a jump in the price.  We have a good hint that the zip codes influences the price. Since the zipcode is a categorical value we have to use dummy variables in otder to make linear regression: 1. Copying the dataframe into a new dataframe to avoid changes in the original one1. create dummy variables for the zipcode1. Creating a new dataframe with only the price as one columns. Then adding the zip_dummies to this new dataframe<jupyter_code>#1
df_edit = df
#2
zip_dummies = pd.get_dummies(data=df_edit['zipcode'], prefix='zip', drop_first=True)
#3
df_price = df.price
df_price = pd.DataFrame({'price':df_price.values})
df_edit = pd.concat([df_price, zip_dummies], axis=1)

df_edit.head()<jupyter_output><empty_output><jupyter_text>As an example I will plot two of the zip codes:<jupyter_code>fig = df_edit.plot(kind='scatter', x='zip_98002', y='price', alpha=0.4)
fig = df_edit.plot(kind='scatter', x='zip_98039', y='price', alpha=0.4)
#die idee war alle auf einmal zu nehmen. 
X = df_edit.drop(['price'], axis=1)
y = df_edit.price
X = sm.add_constant(X)
model = sm.OLS(y, X)

results = model.fit()

results.summary()<jupyter_output><empty_output><jupyter_text>Lets re-plot the mean values of price sorted by the zipcode again:<jupyter_code>df_sorted_df.plot(kind='scatter', x='zipcode', y='price')<jupyter_output><empty_output><jupyter_text>Can we make a regression model with these data? I will assign each zipcode an index<jupyter_code>labels = pd.factorize(df_sorted_df.zipcode)

df_sorted_df['idx_zipcode']=labels[0]
df_sorted_df

smf.ols(formula='price ~ idx', data=df_sorted_df).fit().summary()<jupyter_output><empty_output><jupyter_text>In this model I found good values for t, P>|t|, and R2. However, I am not sure if this process is correct...<jupyter_code>sns.lmplot(data=df_sorted_df, x='idx_zipcode', y='price')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0912706576150312, 'fraction_numerical': 0.0395666186655695, 'mean_word_length': 4.914030819140308, 'pattern_counts': {'":': 0, '<': 120, '<?xml version=': 0, '>': 131, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, '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': '30', 'dir': 'jupyter-structured-clean-dedup', 'id': '514243', 'license': 'MIT', 'n_tokens_mistral': 5049, 'n_tokens_neox': 4600, 'n_words': 1640, 'path': 'notebooks/Data_analysis.ipynb', 'repo_name': 'HansSprungfeld/EDA_project', 'size': '14583'}