Question
Statistics and data management: Coursework
Statistics and Data Management Assignment Brief
Required: you are to use the dataset traffic data.xls to complete the following:
Explain why data collected in traffic surveys may give biased estimates of key statistics and choose a method for dealing with this problem. Sould we include the data from 8 until 9? Explain. (Hint: We dealt with this issue in workshop 2. You may choose one method that we used there or a method of your own choice. You must discuss the advantages and disadvantages of your chosen method) (20 marks)
Use your suggested method to create a second string of observations in your excel file (10 marks)
Use both datasets to provide a table of key descriptive statistics for the whole dataset and discuss relevant differences (15 marks)
Use both datasets to construct box plots and bar charts for the whole dataset and comment on differences (15 marks)
Use both datasets to provide key descriptive statistics for each hour of the survey and discuss relevant differences (20 marks)
Use both datasets to construct hourly box plots and comment on differences
(20 marks)
Step 1: Construct a dataset in the excel
You have been given the basic dataset. You will use this to construct all the statistics and diagrams that you use in your report. This excel file will be submitted with your word file.
Step 2: Analyse the dataset using statistical tools
Estimate various statistical measures for your data series:
Mean: Use the AVERAGE() function in excel.
Standard deviation: Use the STDV.S() function in excel.
Coefficient of variation: Divide the standard deviation by the mean.
Median: Use the MEDIAN() function in excel.
Upper and lower quartile: Use the QUARTILE() function in excel.
Interquartile range: Find the difference between the upper and the lower quartile.
Minimum: Use the MIN() function in excel.
Maximum: Use the MAX() function in excel.
You should present the estimations in excel in a table. You will need to present the results for both cases. You should look at overall means etc. and also investigate differences between times of the day. You can also use the descriptive statistics function in the excel data analysis tool.
Step 3: Use diagrammatic tools to analyse the data
Construct the box plots and bar charts for use in your assignment. These should be in your excel file as well as in the word document. If you do not show these in the excel file, you will lose marks.
Step 4: Present the results in a word file
Create a word file (maximum: 1500 words) with the required information outlined above
Note that:
Most of the diagrams and the tables with estimations in excel should be presented in the word file. Therefore, step 2 and step 3 should be reflected in both the excel and the word file. Otherwise, you will lose marks.
You need to use labels such as Table 1: Descriptive Statistics, Figure 3: Mean speeds by hour etc.
General instructions
You have to submit both the excel file created in steps 1-3 and the word file created in step 4. The limit for the word file is 1,500 words.
Statistics and data management: Coursework Statistics and Data Management Assignment Brief Required: you are to use the dataset traffic data.xls to complete the following:
1. Explain why data collected in traffic surveys may give biased estimates of key statistics and choose a method for dealing with this problem. Sould we include the data from 8 until 9? Explain. (Hint: We dealt with this issue in workshop 2. You may choose one method that we used there or a method of your own choice. You must discuss the advantages and disadvantages of your chosen method) (20 marks)
2. Use your suggested method to create a second string of observations in your excel file (10 marks)
3. Use both datasets to provide a table of key descriptive statistics for the whole dataset and discuss relevant differences (15 marks)
4. Use both datasets to construct box plots and bar charts for the whole dataset and comment on differences (15 marks)
5. Use both datasets to provide key descriptive statistics for each hour of the survey and discuss relevant differences (20 marks)
6. Use both datasets to construct hourly box plots and comment on differences (20 marks)
Step 1: Construct a dataset in the excel You have been given the basic dataset. You will use this to construct all the statistics and diagrams that you use in your report. This excel file will be submitted with your word file. Step 2: Analyse the dataset using statistical tools Estimate various statistical measures for your data series: ? Mean: Use the AVERAGE() function in excel. ? Standard deviation: Use the STDV.S() function in excel. ? Coefficient of variation: Divide the standard deviation by the mean. ? Median: Use the MEDIAN() function in excel. ? Upper and lower quartile: Use the QUARTILE() function in excel. ? Interquartile range: Find the difference between the upper and the lower quartile. ? Minimum: Use the MIN() function in excel. ? Maximum: Use the MAX() function in excel. You should present the estimations in excel in a table. You will need to present the results for both cases. You should look at overall means etc. and also investigate differences between times of the day. You can also use the descriptive statistics function in the excel data analysis tool. Step 3: Use diagrammatic tools to analyse the data Construct the box plots and bar charts for use in your assignment. These should be in your excel file as well as in the word document. If you do not show these in the excel file, you will lose marks. Step 4: Present the results in a word file Create a word file (maximum: 1500 words) with the required information outlined above Note that: ? Most of the diagrams and the tables with estimations in excel should be presented in the word file. Therefore, step 2 and step 3 should be reflected in both the excel and the word file. Otherwise, you will lose marks. ? You need to use labels such as Table 1: Descriptive Statistics, Figure 3: Mean speeds by hour etc. General instructions You have to submit both the excel file created in steps 1-3 and the word file created in step 4. The limit for the word file is 1,500 words.
Time Speed 8.15 12 8.15 12 8.15 12 8.15 12 8.15 12 8.15 12 8.15 12 8.15 12 8.15 12 8.16 14 8.16 14 8.16 14 8.16 14 8.16 14 8.16 14 8.16 14 8.16 14 8.17 28 8.18 28 8.19 28 8.2 14 8.2 15 8.2 15 8.2 15 8.2 15 8.2 15 8.21 21 8.22 31 8.23 36 8.23 26 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.23 18 8.24 14 8.24 14 8.24 14 8.24 14 8.24 14 8.24 14 8.25 23 8.26 35 8.27 42 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 24 8.28 34 8.28 38 8.29 35 8.29 35 8.29 35 8.29 35 8.29 35 8.3 37 8.31 45 8.36 51 8.39 35 8.45 37 8.45 37 8.45 37 8.45 37 8.45 37 8.45 37 8.45 37 8.45 37 8.45 37 8.45 37 8.52 31 8.53 36 8.53 36 8.53 36 8.53 36 8.53 36 8.53 36 8.56 42 8.57 41 8.58 27 8.58 27 8.59 34 9.15 28 9.15 28 9.15 28 9.15 28 9.15 28 9.15 28 9.15 28 9.15 28 9.15 28 9.16 34 9.16 34 9.16 34 9.16 34 9.16 34 9.16 34 9.16 34 9.16 34 9.17 45 9.18 44 9.19 38 9.2 34 9.2 29 9.2 39 9.2 37 9.2 34 9.2 40 9.21 43 9.22 40 9.23 30 9.23 31 9.23 34 9.23 33 9.23 35 9.23 26 9.23 42 9.23 35 9.23 40 9.23 45 9.23 44 9.23 27 9.23 28 9.23 26 9.23 41 9.23 28 9.23 28 9.23 30 9.24 26 9.24 42 9.24 28 9.24 30 9.24 39 9.24 27 9.25 27 9.26 30 9.27 45 9.28 26 9.28 32 9.28 36 9.28 37 9.28 38 9.28 38 9.28 27 9.28 40 9.28 41 9.28 32 9.28 40 9.28 39 9.28 38 9.28 27 9.29 35 9.29 38 9.29 33 9.29 38 9.29 44 9.3 37 9.31 27 9.36 42 9.39 39 9.45 40 9.45 29 9.45 26 9.45 32 9.45 37 9.45 40 9.45 35 9.45 35 9.45 43 9.45 34 9.52 27 9.53 38 9.53 26 9.53 33 9.53 41 9.53 45 9.53 39 9.56 28 9.57 27 9.58 39 9.58 39 9.59 27 10.15 34 10.15 36 10.15 30 10.15 41 10.15 36 10.15 31 10.15 29 10.15 35 10.15 39 10.16 42 10.16 35 10.16 36 10.16 42 10.16 33 10.16 39 10.16 35 10.16 33 10.17 44 10.18 29 10.19 29 10.2 37 10.2 30 10.2 45 10.2 45 10.2 29 10.2 37 10.21 29 10.22 44 10.23 36 10.23 45 10.23 35 10.23 30 10.23 34 10.23 45 10.23 28 10.23 36 10.23 41 10.23 39 10.23 29 10.23 37 10.23 36 10.23 32 10.23 33 10.23 41 10.23 28 10.23 33 10.24 43 10.24 39 10.24 33 10.24 44 10.24 31 10.24 34 10.25 29 10.26 37 10.27 44 10.28 32 10.28 43 10.28 40 10.28 34 10.28 29 10.28 37 10.28 41 10.28 34 10.28 30 10.28 39 10.28 32 10.28 35 10.28 41 10.28 38 10.29 37 10.29 36 10.29 38 10.29 30 10.29 38 10.3 40 10.31 45 10.36 34 10.39 44 10.45 28 10.45 28 10.45 44 10.45 40 10.45 39 10.45 39 10.45 33 10.45 45 10.45 29 10.45 41 10.52 43 10.53 29 10.53 39 10.53 43 10.53 41 10.53 41 10.53 28 10.56 33 10.57 43 10.58 34 10.58 37 10.59 43 11.15 48 11.15 47 11.15 40 11.15 30 11.15 53 11.15 51 11.15 30 11.15 50 11.15 31 11.16 31 11.16 48 11.16 40 11.16 35 11.16 43 11.16 36 11.16 36 11.16 55 11.17 34 11.18 52 11.19 50 11.2 35 11.2 29 11.2 37 11.2 47 11.2 34 11.2 54 11.21 30 11.22 41 11.23 37 11.23 43 11.23 35 11.23 46 11.23 41 11.23 30 11.23 54 11.23 54 11.23 44 11.23 47 11.23 39 11.23 33 11.23 41 11.23 29 11.23 44 11.23 28 11.23 43 11.23 49 11.24 38 11.24 37 11.24 39 11.24 49 11.24 46 11.24 48 11.25 55 11.26 48 11.27 39 11.28 42 11.28 46 11.28 55 11.28 52 11.28 54 11.28 29 11.28 37 11.28 34 11.28 33 11.28 41 11.28 46 11.28 49 11.28 50 11.28 54 11.29 40 11.29 34 11.29 43 11.29 53 11.29 44 11.3 52 11.31 28 11.36 44 11.39 41 11.45 43 11.45 44 11.45 46 11.45 51 11.45 30 11.45 28 11.45 50 11.45 40 11.45 49 11.45 34 11.52 38 11.53 51 11.53 39 11.53 34 11.53 54 11.53 54 11.53 42 11.56 45 11.57 46 11.58 48 11.58 36 11.59 45 12.15 32 12.15 30 12.15 28 12.15 39 12.15 44 12.15 34 12.15 37 12.15 30 12.15 30 12.16 41 12.16 31 12.16 28 12.16 34 12.16 28 12.16 36 12.16 39 12.16 36 12.17 31 12.18 41 12.19 39 12.2 30 12.2 35 12.2 37 12.2 42 12.2 44 12.2 33 12.21 36 12.22 42 12.23 35 12.23 41 12.23 29 12.23 35 12.23 30 12.23 37 12.23 32 12.23 37 12.23 29 12.23 40 12.23 29 12.23 30 12.23 42 12.23 33 12.23 44 12.23 42 12.23 41 12.23 35 12.24 37 12.24 41 12.24 28 12.24 37 12.24 30 12.24 31 12.25 37 12.26 28 12.27 31 12.28 43 12.28 44 12.28 32 12.28 36 12.28 31 12.28 40 12.28 30 12.28 44 12.28 31 12.28 28 12.28 39 12.28 35 12.28 33 12.28 29 12.29 32 12.29 31 12.29 32 12.29 38 12.29 29 12.3 37 12.31 43 12.36 41 12.39 31 12.45 33 12.45 44 12.45 43 12.45 43 12.45 37 12.45 38 12.45 35 12.45 39 12.45 39 12.45 28 12.52 36 12.53 40 12.53 36 12.53 33 12.53 44 12.53 29 12.53 31 12.56 31 12.57 43 12.58 44 12.58 38 12.59 38 13.15 31 13.15 33 13.15 40 13.15 40 13.15 29 13.15 36 13.15 36 13.15 30 13.15 35 13.16 29 13.16 34 13.16 35 13.16 31 13.16 31 13.16 37 13.16 38 13.16 28 13.17 29 13.18 38 13.19 36 13.2 35 13.2 36 13.2 36 13.2 28 13.2 30 13.2 32 13.21 32 13.22 36 13.23 36 13.23 30 13.23 31 13.23 36 13.23 31 13.23 38 13.23 35 13.23 30 13.23 38 13.23 30 13.23 36 13.23 29 13.23 38 13.23 38 13.23 34 13.23 38 13.23 37 13.23 35 13.24 34 13.24 39 13.24 38 13.24 40 13.24 36 13.24 37 13.25 33 13.26 36 13.27 40 13.28 39 13.28 29 13.28 28 13.28 40 13.28 34 13.28 37 13.28 28 13.28 33 13.28 36 13.28 40 13.28 28 13.28 33 13.28 34 13.28 34 13.29 38 13.29 35 13.29 35 13.29 30 13.29 28 13.3 34 13.31 33 13.36 30 13.39 36 13.45 40 13.45 40 13.45 37 13.45 39 13.45 33 13.45 39 13.45 31 13.45 30 13.45 32 13.45 36 13.52 31 13.53 31 13.53 32 13.53 39 13.53 32 13.53 35 13.53 38 13.56 34 13.57 35 13.58 34 13.58 29 13.59 29 14.15 44 14.15 42 14.15 32 14.15 45 14.15 46 14.15 40 14.15 34 14.15 36 14.15 27 14.16 42 14.16 31 14.16 31 14.16 44 14.16 45 14.16 27 14.16 28 14.16 32 14.17 37 14.18 30 14.19 35 14.2 31 14.2 30 14.2 36 14.2 38 14.2 45 14.2 27 14.21 33 14.22 46 14.23 43 14.23 41 14.23 47 14.23 26 14.23 28 14.23 41 14.23 32 14.23 47 14.23 34 14.23 32 14.23 42 14.23 39 14.23 43 14.23 35 14.23 47 14.23 46 14.23 42 14.23 45 14.24 26 14.24 29 14.24 47 14.24 30 14.24 41 14.24 46 14.25 44 14.26 40 14.27 48 14.28 48 14.28 45 14.28 37 14.28 29 14.28 43 14.28 31 14.28 45 14.28 38 14.28 32 14.28 48 14.28 29 14.28 45 14.28 46 14.28 45 14.29 28 14.29 37 14.29 45 14.29 42 14.29 32 14.3 36 14.31 32 14.36 28 14.39 43 14.45 41 14.45 42 14.45 47 14.45 44 14.45 48 14.45 32 14.45 35 14.45 48 14.45 26 14.45 36 14.52 36 14.53 42 14.53 42 14.53 36 14.53 38 14.53 47 14.53 33 14.56 38 14.57 34 14.58 33 14.58 30 14.59 48 15.15 38 15.15 34 15.15 31 15.15 28 15.15 30 15.15 24 15.15 32 15.15 32 15.15 41 15.16 42 15.16 42 15.16 37 15.16 39 15.16 43 15.16 44 15.16 42 15.16 31 15.17 32 15.18 26 15.19 44 15.2 43 15.2 32 15.2 23 15.2 34 15.2 43 15.2 24 15.21 43 15.22 34 15.23 24 15.23 24 15.23 32 15.23 41 15.23 38 15.23 38 15.23 26 15.23 26 15.23 38 15.23 41 15.23 31 15.23 24 15.23 36 15.23 35 15.23 44 15.23 29 15.23 42 15.23 25 15.24 40 15.24 44 15.24 38 15.24 28 15.24 27 15.24 37 15.25 25 15.26 35 15.27 29 15.28 34 15.28 25 15.28 43 15.28 24 15.28 24 15.28 33 15.28 30 15.28 41 15.28 37 15.28 23 15.28 24 15.28 32 15.28 29 15.28 44 15.29 39 15.29 35 15.29 42 15.29 42 15.29 26 15.3 30 15.31 36 15.36 43 15.39 39 15.45 31 15.45 27 15.45 37 15.45 23 15.45 36 15.45 42 15.45 32 15.45 25 15.45 30 15.45 42 15.52 39 15.53 34 15.53 27 15.53 35 15.53 26 15.53 42 15.53 40 15.56 42 15.57 41 15.58 31 15.58 31 15.59 35 16.15 30 16.15 33 16.15 37 16.15 33 16.15 35 16.15 35 16.15 30 16.15 31 16.15 35 16.16 33 16.16 29 16.16 32 16.16 36 16.16 28 16.16 30 16.16 33 16.16 36 16.17 34 16.18 31 16.19 28 16.2 30 16.2 32 16.2 36 16.2 30 16.2 34 16.2 37 16.21 29 16.22 31 16.23 30 16.23 29 16.23 34 16.23 36 16.23 30 16.23 32 16.23 33 16.23 34 16.23 34 16.23 30 16.23 32 16.23 31 16.23 36 16.23 29 16.23 36 16.23 30 16.23 32 16.23 33 16.24 30 16.24 28 16.24 35 16.24 28 16.24 34 16.24 33 16.25 36 16.26 29 16.27 29 16.28 36 16.28 28 16.28 33 16.28 29 16.28 33 16.28 32 16.28 37 16.28 36 16.28 37 16.28 30 16.28 29 16.28 31 16.28 33 16.28 29 16.29 36 16.29 31 16.29 28 16.29 36 16.29 37 16.3 30 16.31 32 16.36 34 16.39 30 16.45 30 16.45 32 16.45 33 16.45 30 16.45 36 16.45 29 16.45 32 16.45 33 16.45 31 16.45 37 16.52 37 16.53 32 16.53 37 16.53 32 16.53 32 16.53 28 16.53 30 16.56 32 16.57 34 16.58 34 16.58 31 16.59 31
Explanation / Answer
Time Speed Mean 12.2897 34.67444 Standard Error 0.086215587 0.258442 Median 12.24 35 Mode 8.23 36 Standard Deviation 2.586467611 7.753259 Sample Variance 6.689814704 60.11303 Kurtosis -1.224354626 0.718201 Skewness 0.000115938 -0.32242 Range 8.44 43 Minimum 8.15 12 Maximum 16.59 55 Sum 11060.73 31207 Count 900 900 Largest(1) 16.59 55 Smallest(1) 8.15 12
