Group Paper Boston Housing Data

Topics: Pivot table, Spreadsheet, Microsoft Excel Pages: 7 (962 words) Published: February 28, 2015
﻿

Hands-on Group Excel Project
April 16, 2014
Camesha Vera, Nadia Ferrari, Shuo Sun, Jiashun Hu, Siddhant Dutt, YuXin Xing

Alliant University
IST 6010
Professor Kumar

The US Census Service is need of analysis for some recently released data, pertaining to housing in the Boston Massachusetts area. The reports generated from this analysis will be completed using an Excel spreadsheet. Excel features calculations, graphing tools, pivot tables, “what if “ scenarios, along with other data analysis functions. It has been a very widely applied spreadsheet for these platforms, and is considered the industry standard for spreadsheets. The US Census is looking to gather the results for the following scenarios. The function used in the Excel tool is known as a pivot table. In data analysis, a pivot table is a data summarization tool found in data visualization programs or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. In addition, we will use other functions of excel, such as charts, filters & statistics, all calculated from the given dataset. The dataset contains information collected from the StatLib Archive (http://lib.stat.cmu.edu/datasets/boston). The dataset contains 506 cases. The data was originally published by Harrison, D. & Rubinfeld, D.L ‘Hedonic prices & the demand for clean air”, J. Environ. Economics & Management, vol, 81-10, 1978 There are 14 attributes in each case of the dataset. They are:

CRIM     per capita crime rate by town

ZN         proportion of residential land zoned for lots over 25,000 sq.ft.

INDUS    proportion of non-retail business acres per town.

CHAS     Charles River dummy variable (1 if tract bounds river; 0 otherwise)

NOX       nitric oxides concentration (parts per 10 million)

AGE       proportion of owner-occupied units built prior to 1940  RM        average number of rooms per dwelling

DIS       weighted distances to five Boston employment centers

TAX      full-value property-tax rate per \$10,000

PTRATIO pupil-teacher ratio by town

B          1000(Bk - 0.63)^2 where Bk is the proportion of blacks by town

LSTAT   % lower status of the population
MEDV    Median value of owner-occupied homes in \$1000

TASK 1-The census department would like to display the neighborhoods that are bordering the Charles River with 6-7 bedrooms, that have an average house value of 25.92 (\$000). Using multiple criteria, # of rooms & location.

Average of MEDV
Column Labels

Row Labels
0
1
Grand Total

3-4
25.30

25.30

4-5
16.02

16.02

5-6
17.13
22.22
17.49

6-7
21.77
25.92
22.02

7-8
35.96
44.07
36.92

8-9
45.70
35.95
44.20

Grand Total
22.09
28.44
22.53

Solution:

Column labels
CHAS

Row Labels
RM

Values
average value of MEDV

TASK 2-The Boston police department is deciding what neighborhoods to allocate funds for public safety and decreasing crime. They would also like to know what effect employment centers have on the crime rate of a neighborhood, and if more should be built.

Row Labels
Average of MEDV
Average of DIS

0.005-5.005
24.49
4.29

5.005-10.005
18.05
2.15

10.005-15.005
14.53
1.84

15.005-20.005
12.02
1.70

20.005-25.005
10.07
1.51

25.005-30.005
10.77
1.61

35.005-40.005
7.95
1.68

40.005-45.005
8.50
1.61

45.005-50.005
7.00
1.66

50.005-55.005
15.00
1.41

65.005-70.005
5.00
1.43

70.005-75.005
8.80
1.80

85.005-90.005
10.40
1.42

Grand Total
22.53
3.80

Solution:

Column labels
values

Row Labels
CRIM...

References: (http://lib.stat.cmu.edu/datasets/boston).
Data Mining for Business Intelligence, 2nd Edition, Shmueli, Patel & Bruce