Summarize and analyze spatial data setsCalculate mean in ExcelCalculate standard deviation in Excelorganize data for analysisExplore functions in Excel Explore/Explain the relationship between quantitative variables
digital_mapping_lab__7_hybrid.docx
video_walk_through_for_lab.docx
digital_mapping_lab__6_excel_data.xls
Unformatted Attachment Preview
Lab 7: Working with Spatial Data, Part 2
Learning Outcomes for Lab:
1.
2.
3.
4.
5.
6.
Summarize and analyze spatial data sets
Calculate mean in Excel
Calculate standard deviation in Excel
organize data for analysis
Explore functions in Excel
Explore/Explain the relationship between quantitative variables
Learning Outcomes for Quantitative Literacy:
•
•
•
•
•
•
Understand quantitative models that describe real world phenomena and recognize
limitations of those models;
Perform simple mathematical computations associated with a quantitative model and make
conclusions based on the results;
Recognize, use, and appreciate mathematical thinking for solving problems that are part of
everyday life;
Understand the various sources of uncertainty and error in empirical data;
Retrieve, organize, and analyze data associated with a quantitative model; and
Communicate logical arguments and their conclusions.
1. Calculate the mean and standard deviation for the MINUTES variable.
Here, we will continue from last week’s lab to work with the survey data. If you haven’t
already, download the Data for Lab Digital Mapping excel from lab #5. Open the file in
Microsoft Excel. Recall that this file contains survey results.
Review the data table on the aata tab and the meanings and coding for the variables on the
Dictionary tab.
Calculating Mean
To calculate the mean, go to the data table, and in the row underneath the last row containing
data, in the cell under the MINUTES variable type the function
=AVERAGE(G2:G298)
Note that the columns are labeled with letters, e.g. ‘G’ at the top of the table which serves as an
identifier for that column/variable. The function ‘average’ indicates you are calculating the
average and the text “G2:G298” indicates the range of cells for which to apply the average
calculation.
Question 1: What is the mean of the MINUTES variable (to the nearest whole minute)?
To calculate the standard deviation, go to the data table, and in the row underneath the row
containing the mean, under the MINUTES variable type the function
=stdev(G2:G298)
Question 2? What is the standard deviation of the MINUTES variable (to the nearest whole
minute)?
Question? Standard Deviation and Mean are summary statistics – they tell us something
important about this complex array of numbers. Having calculated the mean and standard
deviation please analyze the data set using SD and mean. Explain what they numbers tell us
about the data set. Please recall our discussion from class to answer this question.
2. Calculate the mean and standard deviation of MINUTES for each value of WEALTH.
Here you will create a table that reports the mean and standard deviation (to the nearest whole
number) of MINUTES for each value of WEALTH. Select all and then under the sort & filter
tab select “custom sort.” Add a level, then select first to sort by WEALTH, then MINUTES.
Complete the table below:
Wealth Variable
1
2
3
4
5
Average for each wealth
category for Minutes
Avg minutes for all of the 1s
Avg minutes for all of the 2s
Avg minutes for all of the 3s
Avg minutes for all of the 4s
Avg minutes for all of the 5s
Standard Deviation for each
wealth category for Minutes
SD in minutes for all of the 1s
SD in minutes for all of the 2s
SD in minutes for all of the 3s
SD in minutes for all of the 4s
SD in minutes for all of the 5s
Question 3: Write a paragraph or two addressing the following questions: Is there a relationship
between the importance of wealth question and the amount of minutes it takes for folks to get to
work? Explain/analyze the relationship you showed in this table.
3. Investigate another relationship between variables in the data set
Question 4: Investigate whether a nominal or ordinal variable of your choosing in the data set
has a relationship with MILES. To do this, use the same approach you used to investigate
whether the mean and standard deviation of MINUTES differed for different values of
WEALTH. In your answer, be sure to include a table that expresses the mean and standard
deviation of MILES for different values of the nominal or ordinal variable you choose. Then,
explain in a paragraph if you think there is a relationship between MILES and the other variable,
the evidence for this relationship relating to the differences in mean and/or standard deviation,
and why you think the relationship exists (or doesn’t exist).
Wealth Variable
1
2
3
(4) Use only for ordinal
(5) Use only for ordinal
Average for each wealth
category for Miles
Avg miles for all of the 1s
Avg miles for all of the 2s
Avg miles for all of the 3s
Avg miles for all of the 4s
Avg miles for all of the 5s
Explain the relationship between the variables here…
Standard Deviation for each
wealth category for Miles
SD in miles for all of the 1s
SD in miles for all of the 2s
SD in miles for all of the 3s
SD in miles for all of the 4s
SD in miles for all of the 5s
Video Walkthrough for Lab https://www.youtube.com/watch?v=bLGLyTdvI68&feature=youtu.be
LON
LAT
-75.14747939.9725
-75.04966140.0216
-75.16118139.9699
-75.16879539.9705
-75.17212639.9628
-75.17242639.9485
-75.14934740.0008
-75.16668839.9149
-75.15030939.9384
-75.15900940.0442
-75.16293339.9696
-75.22291939.9544
-75.15207240.0000
-75.18339839.9119
-75.18842939.9381
-75.18537739.9879
-75.23299940.0455
-75.19925339.9584
-75.00921140.1000
-75.15638340.0503
-75.15144 40.0610
-75.15516640.0051
-75.17348639.9330
-75.16457939.9456
-75.15931839.9608
-75.20901140.0647
-75.18654439.9852
-75.15917539.9514
-75.20904239.9642
-75.17960339.9151
-75.23245839.9465
-75.12935139.9810
-75.18088139.9311
-75.13194740.0285
-75.15969139.9569
-75.22630839.9518
-75.12638 39.9834
-75.13321539.9695
-75.03066 40.0414
-75.13757139.9774
-75.12468 40.0005
-75.14372539.9516
-75.21876740.0278
-75.15900239.9200
-75.13927840.0062
-75.08464340.0666
COMMUTE
1
3
1
1
1
1
3
1
1
1
1
1
1
1
1
1
1
1
2
2
1
1
1
1
1
1
1
1
1
2
1
1
3
3
1
2
1
1
1
3
1
3
1
2
1
1
GROWUP
1
3
2
3
2
2
2
2
3
3
3
3
3
2
2
3
3
2
3
1
3
3
2
3
2
3
3
2
3
3
2
3
1
2
3
2
2
2
3
1
1
3
3
2
2
3
WEALTH
2
3
2
3
3
3
4
3
3
2
4
5
3
4
3
3
3
4
5
5
2
3
3
3
3
4
3
3
3
3
2
3
4
4
2
2
3
2
3
3
4
1
3
2
3
3
PHILLIES MINUTES
4
10
4
60
2
20
5
10
3
10
3
10
5
8
5
8
1
4
1
12
1
8
5
15
3
5
2
7
3
5
1
5
4
1
3
10
1
10
5
32
1
5
1
3
4
8
3
7
3
10
1
10
3
7
2
5
1
30
4
90
1
7
3
5
4
30
1
60
2
10
5
5
4
5
1
15
1
5
2
30
3
7
1
25
1
7
4
40
5
10
5
5
MILES
15
26
7
10
30
30
15
15
600
45
135
37
50
27
24
275
85
12
90
13
35
36
60
100
60
350
2
30
100
50
20
70
10
30
180
25
50
37
94
8
4
4
90
12
25
30
-75.07325440.0442
-75.13778639.9638
-75.25356339.9798
-75.16778239.9396
-75.18618540.0629
-75.20568439.9603
-75.12700439.9792
-75.16285439.9849
-75.17283939.9419
-75.01164840.1330
-75.03132240.1136
-75.15582939.9214
-75.15779339.9845
-75.15853639.9293
-75.15247840.0602
-75.14525839.9509
-75.01475740.0570
-75.14053439.9829
-75.12509740.0205
-75.16596839.9648
-75.15867139.9335
-75.12621239.9892
-75.13705639.9774
-75.09219639.9901
-75.18235140.0391
(-75.14569 39.9482
-75.15390939.9623
-75.14791240.0176
-75.16605539.9646
-75.15097939.9301
-75.22873940.0413
-75.03583240.0281
-75.17485639.9493
-75.13764839.9824
-75.16557140.0282
-75.16679839.9268
-75.16719239.9797
-75.16667939.9482
-75.23021639.9218
-75.16852939.9179
-75.18249139.9802
-75.11032240.0180
-75.18134239.9776
-75.10080240.0431
-75.15882139.9632
-75.02446340.0398
-75.16403440.0000
1
1
1
1
1
1
1
1
1
1
1
2
1
1
1
1
1
1
1
2
1
1
3
1
1
3
1
1
3
1
1
1
1
1
2
1
3
3
1
1
1
1
1
1
1
3
1
1
3
2
3
2
3
3
3
2
3
3
3
2
2
3
1
3
3
2
2
3
3
1
2
2
3
3
2
2
2
1
3
2
3
3
2
2
2
3
3
3
2
2
2
3
1
3
3
3
4
4
2
4
2
4
1
3
3
3
4
4
4
3
3
5
3
3
3
3
4
2
4
1
3
3
1
3
5
3
3
3
2
2
4
5
4
4
3
4
3
4
3
3
1
2
3
4
2
3
3
4
1
3
1
2
1
5
3
1
2
1
3
5
2
5
2
2
1
3
1
1
5
1
4
5
5
4
1
1
2
2
5
4
5
3
5
4
5
3
5
5
8
7
8
5
5
5
5
5
10
15
6
8
10
4
3
10
15
10
5
5
5
7
60
10
8
15
8
7
50
4
10
5
10
10
5
10
40
1
5
15
11
10
12
10
5
40
5
20
100
25
120
20
60
100
50
20
80
100
395
29
29
81
20
60
75
30
100
30
55
15
35
60
200
75
20
30
30
20
50
30
70
60
75
50
50
150
100
30
30
33
40
100
35
15
-75.16034739.9780
-74.96366140.1006
-75.15593939.9559
-75.09591740.0514
-75.04949140.0420
-75.11409239.9782
-75.17361 39.9333
-75.04674440.0261
-75.13049439.9790
-75.17511539.9394
-75.19453339.9566
-75.23464239.9614
-75.18115140.0304
-75.15944939.9473
-75.21577 39.9489
-75.17469639.9715
-75.13389340.0196
-75.17188939.9699
-75.17845239.9350
-75.17600639.9395
-75.17179739.9343
-75.16801439.9504
-75.16658939.9202
-74.97254640.0796
-75.17395839.9407
-75.14510639.9581
-75.05276940.0588
-75.17623240.0413
-75.17810339.9418
-75.15638339.9995
-75.17282639.9514
-75.19252739.9475
-75.20248140.0710
-75.14529139.9416
-75.16156739.9230
-75.14812539.9344
-75.13013140.0397
-75.14272539.9649
-75.06954239.9996
-75.15051739.9374
-75.16259839.9083
-75.14412239.9772
-75.23629840.0563
-75.14598840.0281
-75.14074839.9668
-75.17892939.9398
-75.16853 39.9686
1
2
1
1
1
1
3
3
3
3
3
3
2
1
1
2
1
2
1
2
1
1
2
3
3
3
3
3
3
3
3
3
3
3
1
1
1
1
1
1
1
1
1
1
1
1
1
2
1
2
3
2
2
2
2
3
3
3
1
1
3
3
3
3
1
1
2
1
2
3
1
2
2
3
1
2
3
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
3
3
2
5
2
3
5
2
2
4
5
4
2
3
2
1
2
3
4
1
3
2
3
4
5
1
2
3
4
5
4
3
2
1
2
3
2
1
2
3
4
3
2
3
4
5
3
2
2
5
3
2
2
5
2
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
3
3
3
3
3
3
3
3
3
15
30
8
3
12
20
15
10
5
5
5
7
60
10
8
15
5
30
7
25
7
40
10
5
8
7
8
5
5
5
5
7
60
10
8
60
20
10
10
10
8
8
4
12
8
15
5
45
15
50
28
60
30
30
33
40
100
35
15
45
15
50
28
60
50
50
150
100
30
30
33
40
100
35
15
45
24.5
17
9.5
60
75
50
50
150
100
30
30
33
40
100
57
56
54
52
-75.06728140.0515
-75.18090440.0286
-75.17061840.0658
-75.23992940.0695
-75.15778239.9359
-75.12771840.0041
-75.16347939.9690
-75.14449439.9431
-75.18411940.0422
-75.17464839.9533
-75.16435239.9443
-75.13664439.9816
-75.16404339.9620
-75.04234640.0325
-75.21448840.0057
-75.18925640.0648
-75.11816140.0399
-75.18165639.9325
-75.14862540.0053
-75.16206140.0226
-75.16153939.9231
-75.19299639.9360
-75.18649739.9947
-75.16083839.9245
-75.22063139.9479
-75.05203840.1039
-75.16007 39.9854
-75.17392239.9651
-74.99268940.0476
-75.23782639.9097
-75.18520440.0364
-75.18057939.9753
-75.18444 40.0506
-75.15965539.9580
-75.17332939.9415
-75.22080739.9553
-74.97051440.0874
-75.13740739.9757
-75.02538240.1230
-75.00829240.0585
-75.11059139.9948
-75.14241739.9743
-75.14766640.0236
-75.17060339.9835
-75.19264339.9661
-75.16143439.9712
-75.15551240.0297
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
2
5
5
4
4
4
4
4
4
4
5
5
4
3
4
5
2
3
4
4
5
5
4
3
4
5
6
3
4
4
5
4
5
6
2
3
2
1
2
3
5
2
1
2
2
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
7
5
5
8
15
5
30
7
25
7
40
10
5
8
7
8
5
5
5
5
7
60
10
8
60
20
10
10
8
8
4
12
8
15
5
7
5
60
10
8
60
34
33.8
33.6
33.4
33.2
33
51
7
10
30
30
15
15
600
45
135
205
437.5
670
902.5
1135
1367.5
342
32
344
566
432
23
21
15
4
2
33
21
23
45
332
11
23
34
21
55
56
76
54
32
45
43
21
34
21
21
45
-75.13910539.9952
-75.11242139.9967
-75.18006939.9459
-75.07471939.9955
-75.15752139.9268
-75.24220739.9906
-75.17783439.9449
-75.17830639.9406
-75.16774 40.0614
-75.12875139.9998
-75.16814 39.9507
-75.10725439.9941
-75.07650540.0718
-75.17130839.9670
-75.20412439.9618
-75.16658639.9320
-75.17102839.9623
-75.16473639.9361
-75.15801439.9246
-75.17841739.9914
-74.96147140.0954
-75.18422640.0067
-75.15413139.9183
-75.14990539.9645
-75.17042639.8954
-75.05044940.1093
-75.12925139.9801
-75.08011240.0374
-75.17750539.9694
-75.23281139.9910
-75.16551239.9654
-75.10691839.9884
-75.13392940.0345
-75.24800739.9474
-75.22792939.9416
-75.10814939.9833
-75.13096 40.0402
-75.15948140.0381
-75.22306 39.9544
-75.15774539.9258
-75.20613940.0753
-75.15976439.9761
-75.20373339.9592
-75.04487840.0350
-75.15446840.0011
-75.13984740.0117
-75.16755339.9261
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
1
2
3
2
2
1
2
1
3
2
1
2
3
3
3
3
1
2
2
3
2
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
4
4
5
5
4
3
3
4
5
4
3
2
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
5
4
4
4
4
4
4
4
4
4
4
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
32.8
60
10
8
60
20
10
10
10
55
64
54
32
23
23
12
10
8
15
5
30
7
25
7
40
10
5
8
7
8
34
32
12
45
54
33
33
21
13
15
43
32
22
23
24
25
23
33
67
64
879
643
45
32
21
12
5
6
8
5
3
3
4
5
6
7
23
34
21
55
56
76
54
32
45
43
21
34
21
21
45
33
5
3
3
4
5
6
7
23
34
21
55
56
-75.22151539.9758
-75.14081 39.9689
-75.13477239.9696
-75.12025140.0278
-75.17538 39.9394
-75.20271539.9631
-75.08983 40.0064
-75.24195839.9167
-75.14917339.9260
-75.11659139.9971
-75.18156739.9800
-75.15947 39.9639
-75.17599739.9668
-75.15549 39.9270
-75.08359340.0012
-75.15502839.9705
-75.17717239.9343
-75.01782840.1347
-75.17598739.9355
-75.13972140.0633
-75.17180639.9018
-75.11984839.9806
-75.17415840.0118
-75.15676 39.9391
-75.17601 39.9964
-75.17751439.9343
-75.14003239.9852
-75.14291240.0432
-75.22143440.0242
-75.08101140.0435
-75.18255639.9315
-75.22467 39.9624
-75.15473539.9379
-75.13761139.9744
-75.18536240.0311
-75.15314239.9379
-75.15923839.9299
-75.23818439.9243
-75.16857139.9282
-75.23506140.0499
-75.09238540.0367
-75.20266640.0777
-75.17434640.0399
-75.17321239.9140
-75.02676440.0389
-75.16117439.9701
1
3
1
2
2
2
1
3
3
1
2
2
1
2
2
2
1
1
1
1
1
1
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
4
4
4
4
4
4
4
4
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
1
1
1
1
1
1
1
1
1
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
3
34
32
12
45
54
33
33
21
13
15
10
8
8
4
12
8
15
5
7
5
5
8
12
8
15
5
7
5
5
8
12
8
15
5
7
5
5
8
32
22
34
12
11
54
45
32
76
62
67
73
79
85
91
96
102
107
113
119
124
32
5
6
7
8
45
33
456
21
45
342
32
344
566
432
23
21
15
4
2
50
50
150
100
30
30
33
40
100
35
15
45
24.5
-75.12239839.9828
-75.22523 40.0405
-75.16112639.9819
-75.00008740.1049
-75.16057939.9792
-75.17132239.9479
-75.23973639.9582
-75.22495239.9807
-75.15115639.9412
-75.17043839.9334
-75.14613440.0277
-75.17242639.9485
-75.21570440.0325
-75.15903539.9531
-75.21840840.0266
-75.15474739.9432
-75.15398 39.9392
2
2
2
2
2
2
2
1
1
3
3
1
1
2
3
1
2
3
3
3
3
3
3
3
3
3
3
3
3
3
2
3
1
2
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
3
3
2
2
2
2
2
2
23
34
32
56
56
32
32
34
56
57
58
45
43
41
40
33
26
17
34
55
67
68
53
43
233
12
44
2
5
7
98
11
3
23
Category
Question
Location
a. Lattitude
b. Longitude
Question 1 – Nominal Data
a.
What is your preferred mode of transportation (other than walking)
b.
Where are you from (did you grow up)?
Question 2 – Ordinal Data
a.
How important to you is being wealthy?
c.
How much of a Phillies fan are you?
Question 3 – Ratio Data
a.
How many minutes does it typically take you to get from
your current residence to work?
b. How many miles is it, approximately, from where you grew up
(or consider “home”) from where you currently are?
Field
Value
COMMUTE
1
2
3
GROWUP
1
2
3
WEALTH
{1, 2, …5}
PHILLIES
{1, 2, …5}
MINUTES
# minutes
MILES
# miles
Key
Bike
Drive
Public Transportation (train/bus/subway/trolley)
City of Philadelphia
Suburbs of Philadelphia
Outside the Philadelphia area
…
Purchase answer to see full
attachment
