Question 1: The Excel file Ca-Co Commute.xls contains census data on journey to

Question

Question 1: The Excel file Ca-Co Commute.xls contains census data on journey to work for nine selected counties across the state of California. Data shows mean travel times (2005 and 2015) and modal splits (i.e., proportion of commutes made by each mode) for employed residents of the nine counties (a) In which year (2005 or 2015) was there greater variation across the 9 counties in mean commute time? Compute and present the following statistics to answer this question: i. the 9-county means ii. the 9-county standard deviations [s] iii. Coefficient of Variation [ s /X] Hint: Treat the "mean" commute time for each county as a data point.J (b) Which county had the greatest diversity in Work Trip Modal Split in 2015? Rank-order the counties from the greatest to the least modal split diversity. Hint: Use IOVy

Explanation / Answer

a. the first part of the question deals with the following information
  
Mean Commute time
2005 2015
San Francisco 25.9 26.9
San Mateo 22.8 24
Sania Clara 22.8 23.3
Alameda 24.6 25.8
Contra costa 27.2 29.3
Solano 22.2 28.2
Napa 19.7 21.4
Sonoma 22.7 24.1
Marin 28.7 28.4
i. To find out the mean. we should add all the values and divide by the number of values

Mean for 2005 = (sum of all values in 2005)/9 = 24.06666667
Mean for 2015 = (sum of all values in 2015)/9 = 25.71111111

In excel use the formula =Average() to get the mean
ii. The standard deviation formula = square root of [ (1/N) times Sigma i=1 to N of (xi - mu)^2 ] (in other words squareroot of variance)
we can use the formula STDDEV to calculate standard deviationin excel
  
STD Dev for 2005 = 2.796426291
STD Dev for 2015 = 2.68442007
iii. Standard deviation is never a good measure of variance because it doesn't take in to account the means of the values. so we calculate coefficienr of variation
Co-effecient of variation = (Standard Deviation / Mean) * 100

Co-effecient of variation in 2005 = 11.61949982
Co-effecient of variation in 2015 = 10.44070036

Therefore,this concludes that variation is higher in 2005 than in 2015
b. To calculate which county had greatest diversity in Work Trip Model Split in 2015 we will use IQV (Index of Qualitative Variation)

IQV = number of observed differences / maximum possible differences
  
The following table will be used for the second part of the question
Work Trip Modal Split 2015
Drive Alone Carpool Transit Walk & Other No of Daily Commutes
San Francisco 38.50% 11.50% 33.50% 16.50% 286520
San Mateo 72.50% 13.00% 7.40% 7.10% 328444
Sania Clara 77.70% 12.30% 3.00% 7.00% 652132
Alameda 66.70% 12.80% 10.00% 10.50% 453980
Contra costa 71.60% 13.80% 7.80% 6.80% 412566
Solano 72.00% 18.40% 2.30% 7.30% 195097
Napa 75.20% 12.70% 1.10% 11.00% 98070
Sonoma 74.60% 13.00% 2.20% 10.20% 123933
Marin 66.10% 12.40% 10.30% 11.20% 275994
To get the number of observed differences we have to multiply the percentage values with no of daily commutes which will give the number of commuter using each mode. That will give the following table
H I J K
Drive Alone Carpool Transit Walk & Other
110310.2 32949.8 95984.2 47275.8
238121.9 42697.72 24304.856 23319.524
506706.564 80212.236 19563.96 45649.24
302804.66 58109.44 45398 47667.9
295397.256 56934.108 32180.148 28054.488
140469.84 35897.848 4487.231 14242.081
73748.64 12454.89 1078.77 10787.7
92454.018 16111.29 2726.526 12641.166
182432.034 34223.256 28427.382 30911.328
Now let us calculate Number of Observed Differences
No of Observed Differences = i=1..K, j=(i+1)..Kfifj
following thr formula no of observed differences for San Francisco = =H*I+I*J+J*K+K*L+L*H
The number of observed values for each county is as follows
  
Number of Observed Differences
San Francisco 56486629783
San Mateo 97640668885
Sania Clara 4.03315E+11
Alameda 1.81505E+11
Contra costa 1.52998E+11
Solano 35451386060
Napa 9234083471
Sonoma 14592709253
Marin 66976511755
Now, lets Calculatw the maximum number of possible differences
  
(K(K-1)/2)*(N/K)2
where K is the number of categories of the variable, and N is the sample size
Maximum Possible Differences
San Francisco 429780
San Mateo 492666
Sania Clara 978198
Alameda 680970
Contra costa 618849
Solano 292645.5
Napa 147105
Sonoma 185899.5
Marin 413991
Now calulate IQV by dividing Number of observed differences/Maximum Possible Differences
IQV
San Francisco 131431.4993
San Mateo 198188.3647
Sania Clara 412304.3704
Alameda 266539.527
Contra costa 247230.4505
Solano 121141.06
Napa 62772.05718
Sonoma 78497.84025
Marin 161782.5309
On sorting from largest to smallest values we get greatest to least modal split diversity which is
Sania Clara
Alameda
Contra costa
San Mateo
Marin
San Francisco
Solano
Sonoma
Napa

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.