Casualty By Accident in UK from 2006 to 2008

Assignment 3

Required packages

library(readr) # Useful for importing data
library(tidyr) # Useful for tidying up data
library(dplyr) # Useful for working with structured data
library(magrittr) # Useful for set of operations which promotes semantics
library(Hmisc) # Useful for data analysis and imputing missing valued
library(MVN) # Useful for analysing data
library(forecast) # Useful for normalizing graphs.

Executive Summary

• Aim: To demonstrate all the necessary skills essential in the data preprocessing stage of data analysis.

• Data Collection: The data used in this report was an Open Government License data opted from the government website of UK. The data used is about the injuries and casualty that people got in an accident from the year 2006 to 2008.

• Approach on Solving: First we imported the data using the readr function, as there were two datasets so then by using the tidyr function we join those two datasets. After that we checked the attributes and the structure of the data and factorize the variables where necessary. Then we again check if our data is completely tidy up, as there was one problem we tidy that up and then by using the same tidyr package we make a new variable using mutate function. Then we did the scanning process to find out if there are any NA values present in our data. After that we check for the outliers and as we found some we filter them out and then again plot the boxplot without outliers. At last where we have to perform transformation we take one variable and first plot its histogram as it was right-skewed we apply log10() function on it to make it normally distributed and then finally plot the normally distributed graph.

• Conclusion: We were able to complete all the tasks given in this assignment and tried our level best to showcase our skills properly. At last by looking at the combine boxplot we can say that the number of accidents and the number of casualties in all the three years are almost the same.

Data

• The following data is an open source data with Open Government License opted from the following website:
• https://data.gov.uk/dataset/73f4cd3e-92ed-4cf8-ad0b-0fe30042b626/reported-fatal-personal-injury-road-accident-and-casualty-data-gb-2006-2008
• https://www.nationalarchives.gov.uk/doc/open-government-licence/version/3/
• The following data provides the detail of everyone reported fatal injury by road accident in England from 2006 to 2008 also including the latitudes and longitudes of the accident.
• This data was last updated on 19 May 2010.
• There are two data sets on contains the detail information of which car was involved in accident and how many casualties.
• The second data hold the information on the type, sex and age of casualty.
• There are around 18 variables in the first dataset while 4 variables in the second one.
• We imported both of the datasets using the read_csv() function and then showed the header of those datasets.
• Then in the first dataset there were some variables which were of no use so we drop them and keep all variables which were necessary for us so now out of 18 variables only 7 variables in the first dataset were of use.
• Then we join both the datasets as they have one variable in common which was Fatal_Accident_Index so with the help of function left_join() wo combine them and make one table called fatalaccident_casualty.

accident <- read_csv("C:/Users/Syed Hassan Afsar/Downloads/RMIT 1st Semester/Data Preprocessing/Assignment 3/fatalaccidentdata.csv")

Parsed with column specification:
cols(
  Fatal_Accident_Index = col_character(),
  Month_of_Accident = col_character(),
  Hour_of_Accident = col_character(),
  Longitude = col_double(),
  Latitude = col_double(),
  Pedestrian_Casualties = col_integer(),
  Pedal_Cycles = col_integer(),
  Motor_Cycles = col_integer(),
  Cars = col_integer(),
  Buses_or_Coaches = col_integer(),
  Vans = col_integer(),
  HGVs = col_integer(),
  Other_Vehicles = col_integer(),
  Total_Vehicles_Involved = col_integer(),
  Fatal_Casualties = col_integer(),
  Serious_Casualties = col_integer(),
  Slight_Casualties = col_integer(),
  Total_Number_of_Casualties = col_integer()
)

head(accident)

accident <- accident[-c(6:13,15:17)]
head(accident)

casualty <- read_csv("C:/Users/Syed Hassan Afsar/Downloads/RMIT 1st Semester/Data Preprocessing/Assignment 3/fatalcasualtydata.csv")

Parsed with column specification:
cols(
  Fatal_Accident_Index = col_character(),
  Fatal_Casualty_Type = col_character(),
  Fatal_Casualty_Sex = col_character(),
  Fatal_Casualty_Age = col_character()
)

head(casualty)

fatalaccident_casualty <- left_join(accident,casualty, by = "Fatal_Accident_Index")
head(fatalaccident_casualty)

Understand

• First, we checked the attributes of our dataset.
• Then first we check the structure of our new data which gives the data type of each variable.
• Then we change the data type of Months_of_Accident from character to factor.
• We also change the data type of Fatal_Casualty_Sex from character to factor.
• Then we again check the structure of our data.

attributes(fatalaccident_casualty)

$`row.names`
   [1]    1    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17
  [18]   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34
  [35]   35   36   37   38   39   40   41   42   43   44   45   46   47   48   49   50   51
  [52]   52   53   54   55   56   57   58   59   60   61   62   63   64   65   66   67   68
  [69]   69   70   71   72   73   74   75   76   77   78   79   80   81   82   83   84   85
  [86]   86   87   88   89   90   91   92   93   94   95   96   97   98   99  100  101  102
 [103]  103  104  105  106  107  108  109  110  111  112  113  114  115  116  117  118  119
 [120]  120  121  122  123  124  125  126  127  128  129  130  131  132  133  134  135  136
 [137]  137  138  139  140  141  142  143  144  145  146  147  148  149  150  151  152  153
 [154]  154  155  156  157  158  159  160  161  162  163  164  165  166  167  168  169  170
 [171]  171  172  173  174  175  176  177  178  179  180  181  182  183  184  185  186  187
 [188]  188  189  190  191  192  193  194  195  196  197  198  199  200  201  202  203  204
 [205]  205  206  207  208  209  210  211  212  213  214  215  216  217  218  219  220  221
 [222]  222  223  224  225  226  227  228  229  230  231  232  233  234  235  236  237  238
 [239]  239  240  241  242  243  244  245  246  247  248  249  250  251  252  253  254  255
 [256]  256  257  258  259  260  261  262  263  264  265  266  267  268  269  270  271  272
 [273]  273  274  275  276  277  278  279  280  281  282  283  284  285  286  287  288  289
 [290]  290  291  292  293  294  295  296  297  298  299  300  301  302  303  304  305  306
 [307]  307  308  309  310  311  312  313  314  315  316  317  318  319  320  321  322  323
 [324]  324  325  326  327  328  329  330  331  332  333  334  335  336  337  338  339  340
 [341]  341  342  343  344  345  346  347  348  349  350  351  352  353  354  355  356  357
 [358]  358  359  360  361  362  363  364  365  366  367  368  369  370  371  372  373  374
 [375]  375  376  377  378  379  380  381  382  383  384  385  386  387  388  389  390  391
 [392]  392  393  394  395  396  397  398  399  400  401  402  403  404  405  406  407  408
 [409]  409  410  411  412  413  414  415  416  417  418  419  420  421  422  423  424  425
 [426]  426  427  428  429  430  431  432  433  434  435  436  437  438  439  440  441  442
 [443]  443  444  445  446  447  448  449  450  451  452  453  454  455  456  457  458  459
 [460]  460  461  462  463  464  465  466  467  468  469  470  471  472  473  474  475  476
 [477]  477  478  479  480  481  482  483  484  485  486  487  488  489  490  491  492  493
 [494]  494  495  496  497  498  499  500  501  502  503  504  505  506  507  508  509  510
 [511]  511  512  513  514  515  516  517  518  519  520  521  522  523  524  525  526  527
 [528]  528  529  530  531  532  533  534  535  536  537  538  539  540  541  542  543  544
 [545]  545  546  547  548  549  550  551  552  553  554  555  556  557  558  559  560  561
 [562]  562  563  564  565  566  567  568  569  570  571  572  573  574  575  576  577  578
 [579]  579  580  581  582  583  584  585  586  587  588  589  590  591  592  593  594  595
 [596]  596  597  598  599  600  601  602  603  604  605  606  607  608  609  610  611  612
 [613]  613  614  615  616  617  618  619  620  621  622  623  624  625  626  627  628  629
 [630]  630  631  632  633  634  635  636  637  638  639  640  641  642  643  644  645  646
 [647]  647  648  649  650  651  652  653  654  655  656  657  658  659  660  661  662  663
 [664]  664  665  666  667  668  669  670  671  672  673  674  675  676  677  678  679  680
 [681]  681  682  683  684  685  686  687  688  689  690  691  692  693  694  695  696  697
 [698]  698  699  700  701  702  703  704  705  706  707  708  709  710  711  712  713  714
 [715]  715  716  717  718  719  720  721  722  723  724  725  726  727  728  729  730  731
 [732]  732  733  734  735  736  737  738  739  740  741  742  743  744  745  746  747  748
 [749]  749  750  751  752  753  754  755  756  757  758  759  760  761  762  763  764  765
 [766]  766  767  768  769  770  771  772  773  774  775  776  777  778  779  780  781  782
 [783]  783  784  785  786  787  788  789  790  791  792  793  794  795  796  797  798  799
 [800]  800  801  802  803  804  805  806  807  808  809  810  811  812  813  814  815  816
 [817]  817  818  819  820  821  822  823  824  825  826  827  828  829  830  831  832  833
 [834]  834  835  836  837  838  839  840  841  842  843  844  845  846  847  848  849  850
 [851]  851  852  853  854  855  856  857  858  859  860  861  862  863  864  865  866  867
 [868]  868  869  870  871  872  873  874  875  876  877  878  879  880  881  882  883  884
 [885]  885  886  887  888  889  890  891  892  893  894  895  896  897  898  899  900  901
 [902]  902  903  904  905  906  907  908  909  910  911  912  913  914  915  916  917  918
 [919]  919  920  921  922  923  924  925  926  927  928  929  930  931  932  933  934  935
 [936]  936  937  938  939  940  941  942  943  944  945  946  947  948  949  950  951  952
 [953]  953  954  955  956  957  958  959  960  961  962  963  964  965  966  967  968  969
 [970]  970  971  972  973  974  975  976  977  978  979  980  981  982  983  984  985  986
 [987]  987  988  989  990  991  992  993  994  995  996  997  998  999 1000
 [ reached getOption("max.print") -- omitted 7656 entries ]

$class
[1] "tbl_df"     "tbl"        "data.frame"

$names
 [1] "Fatal_Accident_Index"       "Month_of_Accident"          "Hour_of_Accident"          
 [4] "Longitude"                  "Latitude"                   "Total_Vehicles_Involved"   
 [7] "Total_Number_of_Casualties" "Fatal_Casualty_Type"        "Fatal_Casualty_Sex"        
[10] "Fatal_Casualty_Age"        

str(fatalaccident_casualty)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   8656 obs. of  10 variables:
 $ Fatal_Accident_Index      : chr  "200601CP00117" "200601TA00014" "200601TA00032" "200601TA00055" ...
 $ Month_of_Accident         : chr  "May" "January" "February" "January" ...
 $ Hour_of_Accident          : chr  "16" "22" "09" "16" ...
 $ Longitude                 : num  -0.0882 -0.1303 -0.0678 -0.0827 -0.1412 ...
 $ Latitude                  : num  51.5 51.5 51.6 51.6 51.5 ...
 $ Total_Vehicles_Involved   : int  2 1 1 2 2 1 1 1 1 3 ...
 $ Total_Number_of_Casualties: int  1 2 1 1 1 1 1 1 1 3 ...
 $ Fatal_Casualty_Type       : chr  "Pedestrian" "Pedestrian" "Pedestrian" "Motor_Cycle_Rider" ...
 $ Fatal_Casualty_Sex        : chr  "Male" "Male" "Male" "Male" ...
 $ Fatal_Casualty_Age        : chr  "33" "64" "2" "41" ...

fatalaccident_casualty$Month_of_Accident <- fatalaccident_casualty$Month_of_Accident %>% factor(levels = c("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"), labels = c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), ordered = TRUE)
fatalaccident_casualty$Fatal_Casualty_Sex <- fatalaccident_casualty$Fatal_Casualty_Sex %>% factor(levels = c("Male", "Female", "Not_Reported"), labels = c("Male", "Female","Not_Reported"))
str(fatalaccident_casualty)

Classes ‘tbl_df’, ‘tbl’ and 'data.frame':   8656 obs. of  10 variables:
 $ Fatal_Accident_Index      : chr  "200601CP00117" "200601TA00014" "200601TA00032" "200601TA00055" ...
 $ Month_of_Accident         : Ord.factor w/ 12 levels "Jan"<"Feb"<"Mar"<..: 5 1 2 1 4 4 4 5 5 5 ...
 $ Hour_of_Accident          : chr  "16" "22" "09" "16" ...
 $ Longitude                 : num  -0.0882 -0.1303 -0.0678 -0.0827 -0.1412 ...
 $ Latitude                  : num  51.5 51.5 51.6 51.6 51.5 ...
 $ Total_Vehicles_Involved   : int  2 1 1 2 2 1 1 1 1 3 ...
 $ Total_Number_of_Casualties: int  1 2 1 1 1 1 1 1 1 3 ...
 $ Fatal_Casualty_Type       : chr  "Pedestrian" "Pedestrian" "Pedestrian" "Motor_Cycle_Rider" ...
 $ Fatal_Casualty_Sex        : Factor w/ 3 levels "Male","Female",..: 1 1 1 1 1 1 1 2 2 1 ...
 $ Fatal_Casualty_Age        : chr  "33" "64" "2" "41" ...

Tidy & Manipulate Data I

• The first variable with Fatal_Accident_Index also have Year of accident in it so my data was not in a tidy format so we have to separate Year from Fatal_Accident_Index by using the function separate().
• Now my data is in tidy format.
• Then showed the output of how my data looks.
• Then we factorize the new variable Year.

fatalaccident_casualty <- fatalaccident_casualty %>% separate(Fatal_Accident_Index, c("Year","Index"), sep = 4)
fatalaccident_casualty

fatalaccident_casualty$Year <- fatalaccident_casualty$Year %>% factor(levels = c("2006", "2007", "2008"), labels = c("2006", "2007","2008"), ordered = TRUE)

Tidy & Manipulate Data II

• We created a new variable named Casualty_Per_Car with the help of mutate() function and we stored the ratio of Totla_Number_of_Casualties and Totel_Vehicles_Involved.
• Then showed the output of the new dataset.
• Now our data is completely tidyup.

fatalaccident_casualty <- mutate(fatalaccident_casualty,
       Casualty_Per_Car = Total_Number_of_Casualties / Total_Vehicles_Involved)
fatalaccident_casualty

Scan I

• We check the total sum of Na values in our data with sum() function with is.na() inside it.
• Anda then we check the column sum of each variable so see any possible NA present in any of the variable using the function colSums() with is.na() inside it.
• As we saw that there were no NA values present in our dataset so no need of further investigation or processing at this stage.

sum(is.na(fatalaccident_casualty))

[1] 0

colSums(is.na(fatalaccident_casualty))

                      Year                      Index          Month_of_Accident 
                         0                          0                          0 
          Hour_of_Accident                  Longitude                   Latitude 
                         0                          0                          0 
   Total_Vehicles_Involved Total_Number_of_Casualties        Fatal_Casualty_Type 
                         0                          0                          0 
        Fatal_Casualty_Sex         Fatal_Casualty_Age           Casualty_Per_Car 
                         0                          0                          0 

Scan II

• In our data there are two main numerical values Totla_Vehicle_Involved and Total_Number_of_Casualty so instead of making separate boxplots we made a new variable called as Casualty_Per_Car in the Tidy & Manipulate Data II Task, and now we use this variable with the Year variable to show the comparison of the Casualty_Per_Car in all three years.
• First, we plot the boxplot of Casualty_Per_Car vs Year in which we saw that there were some outliers.
• So, to remove those outliers we filter out those values.
• And then again plot the boxplot without any possible outliers.
• Outliers which were far away from the max value were removed because there might be some error while entering data so giving it a benefit of doubt the most maximum values were removed other values were kept as it is because there is a chance that if an accident occurs between two cars 6 to 7 people can die or can get serious injuries.

boxplot(fatalaccident_casualty$Casualty_Per_Car ~ fatalaccident_casualty$Year)

length_outliers_filter <- fatalaccident_casualty %>% filter(Casualty_Per_Car < 10)
boxplot(length_outliers_filter$Casualty_Per_Car ~ length_outliers_filter$Year)

Transform

• First, we plot the histogram of Casualty_Per_Car and saw the output, it was right skewed.
• So, to deal with it we apply some transformation on it by using BoxCox() function on that variable and then store that values in boxcox.
• Then we plot the histogram of that new list boxcox which seems to be almost normally distributed.

hist(fatalaccident_casualty$Casualty_Per_Car, main = "Casualty per Car")

boxcox <- BoxCox(fatalaccident_casualty$Casualty_Per_Car, lambda = "auto")
hist(boxcox, main = "Casualty per Car")