Use Excel to analyse the Boston House Price dataset

The Boston House Price dataset was collected in the early 1970’s and published in 1978 by Harrison and Rubinfield in their paper entitled, “Hedonic Prices and the Demand for Clean Air”. This dataset has 506 entries that represent aggregated data about 14 features for homes in the various suburbs in Boston, Massachusetts.

I first became aware of this dataset when I was initially studying machine learning and using Python’s machine learning library, SciKit Learn. This dataset is no longer in the SciKit Learn website, and I believe that it is because it made reference to a certain demographic of the population.

I have been able to obtain a copy of the Boston House Prices dataset in Excel and feel that it would be a great idea to answer a few statistical questions concerning that datase.

Below is the Excel spreadsheet that I downloaded.

1. It does not have any suburb names so I had to include an index for each suburb, which I placed in column N of the worksheet.
2. On column P of the worksheet, I ranked the median values because that information will be crucial in the analysis.

Below is a legend that explains the contents of each column of the spreadsheet, with the exception of the index and rank because those were two columns that I added to the worksheet:-

The first question asks the question of the correlation between the number of rooms in a property and its median value. I used the CORREL() function to obtain the answer to that question, as seen below:-

The second question asks what value has the highest correlation with the median house value. I answered that question by using the CORREL() function for each relevant column of data and then ranking the answers, as seen in the code below. :-

The third question asked the average age of the home and I used the AVERAGE() function to answer that question:-

The fourth question asked what the distribution of the pupil/teacher ratio is and I answered that question by making a histogram of that column of data, as seen below:-

The fifth question asked what the highest median house value is and I answer that question by using the MAX() function, as seen below:-

The sixth question asked what the lowest median house value is, and I answered that question by using the MIN() function, as seen below:-

Below is a scatter plot of the median house values. As can be seen, there are several homes that are valued at £50,000:-

Even though the Boston House Price dataset is over five decades old, I still feel that it has value, which is why I like to use it.

I have created a video to accompany this blog post and it can be viewed here:- https://youtu.be/a6XPJ_kUj3k