Subsetting data from Data Frame in R with references to SQL commands – Part 1

Hello there, welcome to my blog post. The reason behind this post is to practice R and R markdown and share my work with the audience. I would make references to SQL/relational database commands while working with R. If you are not familiar with SQL then please ignore SQL parts, SQL references are just for easy understanding. The primary focus of this blog post is to learn data selection from data frames. Here in this post, we will look at the following:

Prerequisites:

  1. R
  2. R Studio (for ease)

Assumption: Working directory is set and datasets are stored in the working directory. setwd() command is used to set the working directory. Supply the path of directory enclosed in double quotes to set it as a working directory.

Data

Data used in this exercise is downloaded from https://www.ucrdatatool.gov/ website and can be accessed from [https://www.ucrdatatool.gov/Search/Crime/State/OneYearofData.cfm] (CrimeOneYearofData.csv)

Reading data

Before we learn how to select data, we should learn that unlike relational databases, data in R resides in memory. All of the imported data will be loaded into the memory of your computer and hence before loading the data, you must check the capacity of your computer and whether it is sufficient enough to support the dataset.

Data can come from any source, it can be a flat file, database system, or handwritten notes. Usually, flat files are the most common source of the data. In this section, we will see how to load data from a CSV file.

crimeData <- read.csv("CrimeOneYearofData.csv")     

The command above will import the content of the CrimeOneYearofData.csv file into an object called the crimeData data frame. Object crimeData is a data frame that contains all the data from the CrimeOneYearofData.csv file. We can loosely compare this to a relational database object “table”. Data frames too, have rows and columns, and data is presented in structured form.

Tip: If you want to know the number of rows and columns of your Data Frame then dim() can do that for you.

Command read.csv above take multiple other arguments other than just the name of the file. Information on additional arguments can be found [https://www.rdocumentation.org/packages/utils/versions/3.6.2/topics/read.table] (read.csv).

Subsetting data from data frame

Before we learn how to select data from data frame crimeData, I would recommend learning following commands using crimeData frame:

  names(crimeData)
  str(crimeData)
  dim(crimeData)

Command names(crimeData) above would return all the column names of the data frame. Checking column names just after loading the data is useful as this will make you familiar with the data frame. Following is the output of the names command. Do not worry about the numbers in the square brackets just yet, we will look at them in a subsequent blog. The names of the columns are listed next to the numbers in the brackets and there are a total of 22 columns in the crimeData data frame.

names(crimeData)
#>  [1] "State"                                    
#>  [2] "Population"                               
#>  [3] "Violent.crime.total"                      
#>  [4] "Murder.and.nonnegligent.Manslaughter"     
#>  [5] "Legacy.rape..1"                           
#>  [6] "Revised.rape..2"                          
#>  [7] "Robbery"                                  
#>  [8] "Aggravated.assault"                       
#>  [9] "Property.crime.total"                     
#> [10] "Burglary"                                 
#> [11] "Larceny.theft"                            
#> [12] "Motor.vehicle.theft"                      
#> [13] "Violent.Crime.rate"                       
#> [14] "Murder.and.nonnegligent.manslaughter.rate"
#> [15] "Legacy.rape.rate..1"                      
#> [16] "Revised.rape.rate..2"                     
#> [17] "Robbery.rate"                             
#> [18] "Aggravated.assault.rate"                  
#> [19] "Property.crime.rate"                      
#> [20] "Burglary.rate"                            
#> [21] "Larceny.theft.rate"                       
#> [22] "Motor.vehicle.theft.rate"

Command str(crimeData) would return the structure of the data frame. The command str() compactly displays the internal structure of the object be it data frame or any other. following is the structure of the crimeData data frame. The following result from str() command shows the data type of the columns crimeData data frame has, as well as sample data from the individual columns.

str(crimeData)
#> 'data.frame':    159 obs. of  22 variables:
#>  $ State                                    : chr  "Alabama" "Alaska" "Arizona" "Arkansas" ...
#>  $ Population                               : chr  "4849377" "736732" "6731484" "2966369" ...
#>  $ Violent.crime.total                      : chr  "20727" "4684" "26916" "14243" ...
#>  $ Murder.and.nonnegligent.Manslaughter     : int  276 41 319 165 1699 151 86 54 105 1149 ...
#>  $ Legacy.rape..1                           : int  1436 555 2464 1182 8398 2121 571 249 352 6051 ...
#>  $ Revised.rape..2                          : int  2005 771 3378 1763 11527 3039 782 386 472 8563 ...
#>  $ Robbery                                  : int  4701 629 6249 2050 48680 3039 3159 1269 3497 24914 ...
#>  $ Aggravated.assault                       : int  13745 3243 16970 10265 91803 10325 4495 2867 4125 72895 ...
#>  $ Property.crime.total                     : int  154094 20334 215240 99018 947192 135510 69070 27900 34147 679446 ...
#>  $ Burglary                                 : int  39715 3150 43562 24790 202670 23472 11955 5768 3466 143220 ...
#>  $ Larceny.theft                            : int  104238 15445 154091 68627 592670 99464 51005 20865 26898 493647 ...
#>  $ Motor.vehicle.theft                      : int  10141 1739 17587 5601 151852 12574 6110 1267 3783 42579 ...
#>  $ Violent.Crime.rate                       : num  427 636 400 480 396 ...
#>  $ Murder.and.nonnegligent.manslaughter.rate: num  5.7 5.6 4.7 5.6 4.4 2.8 2.4 5.8 15.9 5.8 ...
#>  $ Legacy.rape.rate..1                      : num  29.6 75.3 36.6 39.8 21.6 39.6 15.9 26.6 53.4 30.4 ...
#>  $ Revised.rape.rate..2                     : num  41.3 104.7 50.2 59.4 29.7 ...
#>  $ Robbery.rate                             : num  96.9 85.4 92.8 69.1 125.5 ...
#>  $ Aggravated.assault.rate                  : num  283 440 252 346 237 ...
#>  $ Property.crime.rate                      : num  3178 2760 3198 3338 2441 ...
#>  $ Burglary.rate                            : num  819 428 647 836 522 ...
#>  $ Larceny.theft.rate                       : num  2150 2096 2289 2314 1527 ...
#>  $ Motor.vehicle.theft.rate                 : num  209 236 261 189 391 ...

Command dim(crimeData) mentioned above will result in dimensions of the crimeData data frame or in other words total number of rows and columns this data frame has. In statistics terms, a column is a variable and row is an observation. Data frame crimeData has 159 observations and 22 variables.

dim(crimeData)
#> [1] 159  22

Select all data from a data frame

In base R, just putting the name of the data frame crimeData on the prompt will display all of the data for that data frame. It might be overwhelming if you have a large data frame. In such case, use of commands head(crimeData), head(crimeData, 10) (10 is just to show the parameter which limit the number of lines, you can choose any other number), tail(crimeData) or tail(crimeData, 10) will be helpful.

SQL Reference – select * from crimeData

Subset specific column from a data frame

In base R, you can specify the name of the column that you would like to select with $ sign along with the data frame. Command head(crimeData$Population, 10) would show first 10 observations from column Population from data frame crimeData:

head(crimeData$Population, 10)
#>  [1] "4849377"  "736732"   "6731484"  "2966369"  "38802500" "5355866" 
#>  [7] "3596677"  "935614"   "658893"   "19893297"

Above can be done using dplyr package as well. As per rdocumentation.org “dplyr is a grammar of data manipulation, providing a consistent set of verbs that help you solve the most common data manipulation challenges.” Here is a command using dplyr package which selects Population column from crimeData data frame:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
result <- select(crimeData, Population)
head(result, 10)
#>    Population
#> 1     4849377
#> 2      736732
#> 3     6731484
#> 4     2966369
#> 5    38802500
#> 6     5355866
#> 7     3596677
#> 8      935614
#> 9      658893
#> 10   19893297

You can see the presentation of the result between subsetting using $ sign and using dplyr package. dplyr package present result into a tabular format and more nearer to SQL select statement though two are completely different.

Subsetting multiple columns from a data frame

Using base R, the following command will help subset multiple columns. In the command below first two columns are selected from the data frame crimeData. If you see the result for command names(crimeData) above, you would find that State and Population are the first two columns.

SQL Reference – select State, Population from crimeData

  head(crimeData[,c(1,2)],10)
#>                   State Population
#> 1               Alabama    4849377
#> 2                Alaska     736732
#> 3               Arizona    6731484
#> 4              Arkansas    2966369
#> 5            California   38802500
#> 6              Colorado    5355866
#> 7           Connecticut    3596677
#> 8              Delaware     935614
#> 9  District of Columbia     658893
#> 10              Florida   19893297

Or we can supply the name of the columns and select them. Here is an example:

  head(crimeData[,c("State","Population")],10)
#>                   State Population
#> 1               Alabama    4849377
#> 2                Alaska     736732
#> 3               Arizona    6731484
#> 4              Arkansas    2966369
#> 5            California   38802500
#> 6              Colorado    5355866
#> 7           Connecticut    3596677
#> 8              Delaware     935614
#> 9  District of Columbia     658893
#> 10              Florida   19893297

Using dplyr package select clause

result <- select(crimeData, State, Population)
head(result, 10)
#>                   State Population
#> 1               Alabama    4849377
#> 2                Alaska     736732
#> 3               Arizona    6731484
#> 4              Arkansas    2966369
#> 5            California   38802500
#> 6              Colorado    5355866
#> 7           Connecticut    3596677
#> 8              Delaware     935614
#> 9  District of Columbia     658893
#> 10              Florida   19893297

Any number of columns can be selected this way by giving the number or the name of the column within a vector. Let’s find out the first, seventh, and tenth column from crimeData data frame. Remember, instead of the number you can give the name of the column enclosed in double-quotes:

SQL Reference – select State, Robbery, Burglary from crimeData

  head(crimeData[,c(1,7,10)],10)
#>                   State Robbery Burglary
#> 1               Alabama    4701    39715
#> 2                Alaska     629     3150
#> 3               Arizona    6249    43562
#> 4              Arkansas    2050    24790
#> 5            California   48680   202670
#> 6              Colorado    3039    23472
#> 7           Connecticut    3159    11955
#> 8              Delaware    1269     5768
#> 9  District of Columbia    3497     3466
#> 10              Florida   24914   143220

Using dplyr package select clause

result <- select(crimeData, State, Robbery, Burglary)
head(result, 10)
#>                   State Robbery Burglary
#> 1               Alabama    4701    39715
#> 2                Alaska     629     3150
#> 3               Arizona    6249    43562
#> 4              Arkansas    2050    24790
#> 5            California   48680   202670
#> 6              Colorado    3039    23472
#> 7           Connecticut    3159    11955
#> 8              Delaware    1269     5768
#> 9  District of Columbia    3497     3466
#> 10              Florida   24914   143220

Subsetting all columns but one from data frame

This approach is called subsetting by the deletion of entries. In base R you can specify which column you would like to exclude from the selection by putting a minus sign in from of it. Let’s try:

  result <- head(crimeData[,-6],10)
  dim(result)
#> [1] 10 21

Now if we analyse the result of the above command, we can see the dimension of result variable is showing 10 observations (rows) and 21 variables (columns). If you check the result of command dim(crimeData) above, you can see there were total 22 variables in the crimeData data frame but as we have excluded the sixth column using -6 in column section in command result <- head(crimeData[,-6],10) which returned result for all columns except sixth. A similar operation can be performed using dplyr package as well and instead of using the minus sign on the number of a column, you can use it directly on the name of the column. Here is the example where we would exclude column “Burglary” form the result set:

result <- select(crimeData, -Burglary)
dim(head(result,10))
#> [1] 10 21

SQL Reference – In SQL to avoid a column, we must specify the list of columns in the select clause. This may sometime be overwhelming in case of a large table when a few columns need elimination from a large data set.

Subsetting all columns which starts with a particular character or string

If you go back to the result of names(crimeData) command you would see that few column names start with the same string. Two columns we particularly interested in here are “Burglary” and “Burglary rate”. Following R command using dplyr package will help us subset these two columns by writing as little code as possible. Imagine a scenario when you have several columns which start with the same character or string and in such scenario following command will be helpful:

result <- select(crimeData, starts_with("Burglary"))
head(result, 10)
#>    Burglary Burglary.rate
#> 1     39715         819.0
#> 2      3150         427.6
#> 3     43562         647.1
#> 4     24790         835.7
#> 5    202670         522.3
#> 6     23472         438.2
#> 7     11955         332.4
#> 8      5768         616.5
#> 9      3466         526.0
#> 10   143220         719.9

I hope you enjoyed this post. If it helped you in any way then please do not forget to subscribe to mailers and share this post. Thank you, it means a lot!!

In the next post, we will look at subsetting observations or selecting rows. Stay tuned.