Data Wrangling NYC Flights

Here I am installing the necessary packages and loading the required libraries.

# Load standard libraries
library(tidyverse)
library(nycflights13)
library(jsonlite)

Problem 1: Open Government data

Loading the Seattle Police Department Police Report Incidents data.

police_incidents <- fromJSON("https://data.seattle.gov/resource/hapq-73pk.json")

(a) Describing what the data represents.

head(police_incidents) #getting a glimpse of the first few rows of the data frame

tail(police_incidents) #getting a glimpse of the last few rows of the data frame

cat("\nDimensions of dataset:\nRows =",nrow(police_incidents),"\nColumns =", ncol(police_incidents))

## 
## Dimensions of dataset:
## Rows = 1000 
## Columns = 8

This dataset contains all the UCR reported crimes by police beat for first three months in 2008- January through March. It is to be noted that for all the three months only the incidents reported on the first day of the month are recorded in this data set.

(b) Describing each variable and what it measures.

#Getting the structure of the data frame
cat("\nOriginal structure of the data\n")

## 
## Original structure of the data

str(police_incidents)

## 'data.frame':    1000 obs. of  8 variables:
##  $ crime_description: chr  "Homicide" "Rape" "Robbery" "Assault" ...
##  $ crime_type       : chr  "Homicide" "Rape" "Robbery" "Assault" ...
##  $ police_beat      : chr  "B1" "B1" "B1" "B1" ...
##  $ precinct         : chr  "N" "N" "N" "N" ...
##  $ report_date      : chr  "2008-01-01T00:00:00.000" "2008-01-01T00:00:00.000" "2008-01-01T00:00:00.000" "2008-01-01T00:00:00.000" ...
##  $ row_value_id     : chr  "1" "2" "3" "4" ...
##  $ sector           : chr  "B" "B" "B" "B" ...
##  $ stat_value       : chr  "0" "0" "5" "1" ...

#Selecting column names that are to be coerced as factors
col_factor <- c("crime_description","crime_type", "police_beat", "precinct", "sector")

#Coercing the above columns into factors
police_incidents[col_factor] <- lapply(police_incidents[col_factor], factor)

#Selecting column names that are to be coerced as numbers
col_numeric <- c("row_value_id", "stat_value")

#Coercing the above columns into umeric
police_incidents[col_numeric] <- lapply(police_incidents[col_numeric], as.numeric)

#Coercing the eport_date columns into date
police_incidents$report_date <- as.Date(police_incidents$report_date)
  
#Getting the new structure
cat("\n\nChanged structure of the data\n")

## 
## 
## Changed structure of the data

str(police_incidents)

## 'data.frame':    1000 obs. of  8 variables:
##  $ crime_description: Factor w/ 7 levels "Assault","Burglary",..: 3 5 6 1 4 7 2 3 5 6 ...
##  $ crime_type       : Factor w/ 7 levels "Assault","Burglary",..: 3 6 7 1 4 5 2 3 6 7 ...
##  $ police_beat      : Factor w/ 51 levels "B1","B2","B3",..: 1 1 1 1 1 1 1 2 2 2 ...
##  $ precinct         : Factor w/ 5 levels "E","N","SE","SW",..: 2 2 2 2 2 2 2 2 2 2 ...
##  $ report_date      : Date, format: "2008-01-01" "2008-01-01" ...
##  $ row_value_id     : num  1 2 3 4 5 6 7 8 9 10 ...
##  $ sector           : Factor w/ 17 levels "B","C","D","E",..: 1 1 1 1 1 1 1 1 1 1 ...
##  $ stat_value       : num  0 0 5 1 35 4 15 0 0 2 ...

Description of each of the 8 variables:

crime_description: Description of the crime type | chaarcter | recasted to factor
crime_type : Type 1 Crime committed | chaarcter | recasted to factor
police_beat : The police beat for reported crimes | chaarcter | recasted to factor
precinct : Police Precinct | chaarcter | recasted to factor
report_date : The reported month of the crime | chaarcter | recasted to police_incidentse
row_value_id : Unique identifier | chaarcter | recasted to numeric
sector : Police Sector | chaarcter | recasted to factor
stat_value : Number of times crime occurred in a beat for reported month | chaarcter | recasted to numeric

(c) Producing a clean dataset, according to the rules of tidy data and exporting the data for future analysis using the Rdata format.

#To caluclate the number of rows which have missing data
cc <- complete.cases(police_incidents)
cat("\nNumber of rows which have missing data=",length(cc[cc==FALSE]))

## 
## Number of rows which have missing data= 0

#To check if each type of observational unit forms a table
sapply(police_incidents[,c(1:8)], function(x)table(x))

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

# Exporting the data for future analysis using the Rdata format
save(police_incidents, file = "clean_police_incidents.Rdata")

From above, it is clear that there are no missing values in the dataset.
As per the rules of tidy data and the code above:

Each variable has its own column.
Each observation has its own row.
Each value has its own cell.
Each type of observational unit forms a table.

(d) General comments about this data.

This data set doesn’t have any missing values
We should not consider this an exhaustive list of the crime incidents because many incidents might not have been reported
It is to be noted that these incidents are reported. They do not give us an idea about the number of poeple convicted. So, this data might contain a few illegitimate or falsely accused incidents
We can assume that the data is ethical as it doen’t contain any identifiable information of both the accused as well as the victim.

Wrangling the NYC Flights data

In this problem set we will use the police_incidents on all flights that departed NYC (i.e. JFK, LGA or EWR) in 2013.

(a) Importing police_incidents:

Load the data.

#flight data being loaded into flght data frame
flght <- nycflights13::flights

(b) Data Manipulation:

Using the flights data to answer each of the following questions.

How many flights were there from NYC airports to Minneapolis/St.Paul in 2013?

#We first need to find the airport code for Minneapolis/St. Paul
#importing the airports data
arpts <- nycflights13::airports

#Looking for airport code for Minneapolis/St. Paul airport using regular expressions
dest <- arpts[grepl(".*(Minneapolis)", arpts$name)==T,]$faa

#To find the number of flights where destination was Minneapolis/St. Paul or MSP
cat("\nNumber of flights from NYC airports to MSP in 2013: ", nrow(flght[flght$dest==dest,]))

## 
## Number of flights from NYC airports to MSP in 2013:  7185

How many airlines fly from NYC to Minneapolis/St.Paul?

#Getting the number of airlines that fly from all three NYC airports to Minneapolis based on carrier
cat("\nNumber of airlines that fly from NYC airports to MSP: ", 
    length(levels(as.factor(flght[flght$dest==dest,]$carrier))))

## 
## Number of airlines that fly from NYC airports to MSP:  6

#Getting the airlines that fly from all three NYC airports to Minneapolis
cat("\nThese airlines are:", levels(as.factor(flght[flght$dest==dest,]$carrier)))

## 
## These airlines are: 9E DL EV MQ OO UA

How many unique airplanes fly from NYC to Minneapolis/St.Paul?

#Getting the unique number of airplanes that fly from fly from NYC to Minneapolis/St.Paul
#based on their tail number
cat("\nNumber of unique airplanes that fly from NYC airports to MSP: ",
    length(levels(as.factor(flght[flght$dest==dest,]$tailnum))))

## 
## Number of unique airplanes that fly from NYC airports to MSP:  633

What is the average arrival delay for flights from NYC to Minneapolis/St.Paul?

#Calculating the average arrival delay for flights from NYC to Minneapolis/St.Paul
#in 2013
cat("\nAverage arrival delay for flights from NYC to Minneapolis/St.Paul: ", mean(flght[flght$dest==dest,]$arr_delay, na.rm = T))

## 
## Average arrival delay for flights from NYC to Minneapolis/St.Paul:  7.270169

What proportion of flights to Minneapolis/St.Paul come from each NYC airport?

# tabulating the number of flights that departed from each of the three NYC airports
#to Minneapolis/St.Paul in 2013
tab <- table(flght[flght$dest==dest,]$origin)

#Finding the proportion of the flights that departed from each of the three NYC airports
#to Minneapolis/St.Paul in 2013
cat("\n\nThe proportion of the flights that departed from each of the three NYC airports
to Minneapolis/St.Paul in 2013:\n")

## 
## 
## The proportion of the flights that departed from each of the three NYC airports
## to Minneapolis/St.Paul in 2013:

prop.table(tab)*100

## 
##      EWR      JFK      LGA 
## 33.08281 15.24008 51.67711

Thus, about 33% of the flights that departed to Minneapolis/St.Paul in 2013 orignated from the EWR airport in NYC; 15% originated from the JFK airport and about 52% originated from the LGA airport.