assignment excel

Data Mining 1

Variables That Were Dropped: The following are the variables that were dropped from the data with the explanation of why they were dropped:

1) County: This variable was dropped, because everything was in Chester.

2) High School: This variable was dropped, because there were missing values in it. In fact, there were 76 values missing for high school, which could impact the data.

3) Middle School: This variable was deleted because there where 44 missing values.

4) Elementary School: This variable was also removed for the same reason where 63 values were missing.

Categorical Variables:

1) School District: School district is a categorical type of data, where it was also coded as binary. Unionville-Chadds was coded as 0 and West Chester was coded as 1. Below is a frequency statistics table for this variable that was done manually by looking at the total numbers of houses in each school district and then calculating their percentages:

Frequency Statistics Table:

Therefore, by looking at the table, we can see that there are more than half of the houses are in West Chester.

2) Type: There are 4 types of houses in the data. Single, Single/detac, Twin/Semi-De, Row/Townhouse. To make things easier, I chose to code this variable as a binary one, where Single and Single/detac houses were coded 0 and the other 2 houses (since they are not single) were coded 1. Below is a frequency statistics table for this variable that was done manually by looking at the total numbers of single houses in contrast to the “others”:

Frequency Statistics Table:

Therefore, by analyzing the data we can see how close the percentages of single and Single/detac houses compared to other types are. In addition, by observing the data clients can get a clearer picture of what their houses should sell based on

Continuous Variables:

Continuous variables are variables that cannot be coded due to the fact that we can take the average of them. In this data, the continuous variables are: DOM, PRICE, BEDS, BATHS, ½ BATH, AGE, SQ FEET, RETAXES, ASSESS, ACRE, and GARAGE. Therefore, to analyze these variables independently and compare their means and standard deviations, a descriptive statistics table was created. Below is a descriptive statistics table consisting of all the continuous variables:

Using the table above, we can see that the average price of a house (highlighted above) in Chester county is $555820٫94. In addition, the standard deviation of price is 308037٫41, which is the largest out of all the data meaning that there is no consistency in the data and that it is spread also, that's mean the scores will fall in the range between 555820+308037 or 555820-308037. Furthermore, the average number of bedrooms in the houses was 3.91.

However, there are some of missing and bad variables such as the data in RETAXES and SQ FEET; I used the Pivot table to get the average based on the Beds to fill out the missing values

Descriptive Statistics Table:

Correlation Table:

Below is a correlation table that includes all variables (categorical and continuous) in order to see if there are correlations in the data

· There is a strong positive correlation between ASSESS and RETAXES (0.83).

and that’s means whenever the number of assess increases the retaxes increases either.

· There is a negative correlation between BATH and AGE (-0.24) as well as between ½ BATH and AGE (-0.28)

· As it shown in the table there are negative correlation between SQ FOOT, RETAXES, ASSESS GARAGE and AGE.

Categorical variables

· The negative correlation is between Type, Style and school district and between Type and Design.