subset commandmerge command
table commandstr(), head(), and
dim() commands to explore your data setsThere is already a lot of data in the R library. The
data() command displays all available data sets.
We are going to use the airquality data here:
# data()
str(airquality)## 'data.frame': 153 obs. of 6 variables:
## $ Ozone : int 41 36 12 18 NA 28 23 19 8 NA ...
## $ Solar.R: int 190 118 149 313 NA NA 299 99 19 194 ...
## $ Wind : num 7.4 8 12.6 11.5 14.3 14.9 8.6 13.8 20.1 8.6 ...
## $ Temp : int 67 72 74 62 56 66 65 59 61 69 ...
## $ Month : int 5 5 5 5 5 5 5 5 5 5 ...
## $ Day : int 1 2 3 4 5 6 7 8 9 10 ...dim(airquality)## [1] 153 6head(airquality)## Ozone Solar.R Wind Temp Month Day
## 1 41 190 7.4 67 5 1
## 2 36 118 8.0 72 5 2
## 3 12 149 12.6 74 5 3
## 4 18 313 11.5 62 5 4
## 5 NA NA 14.3 56 5 5
## 6 28 NA 14.9 66 5 6subset commandThe subset function returns subsets of vectors, matrices or data frames which meet specified conditions. The following examples are from the subset function description (slightly modified).
In the following example, we extract data rows with temperature above 70 degrees, and keep only two columns (Ozone and Temp) of the original data frame:
sub1 = subset(airquality, Temp > 70, select = c(Ozone, Temp))
head(sub1)## Ozone Temp
## 2 36 72
## 3 12 74
## 11 7 74
## 22 11 73
## 29 45 81
## 30 115 79Next, we keep only day1 rows, and skip the Temp column:
sub2 = subset(airquality, Day == 1, select = -Temp)
head(sub2)## Ozone Solar.R Wind Month Day
## 1 41 190 7.4 5 1
## 32 NA 286 8.6 6 1
## 62 135 269 4.1 7 1
## 93 39 83 6.9 8 1
## 124 96 167 6.9 9 1We select all columns from Ozone to Wind (using the colon):
sub3 = subset(airquality, select = Ozone:Wind)
head(sub3)## Ozone Solar.R Wind
## 1 41 190 7.4
## 2 36 118 8.0
## 3 12 149 12.6
## 4 18 313 11.5
## 5 NA NA 14.3
## 6 28 NA 14.9The logical drop parameter tells the function if the
original data structure should be preserved or not.
The default value is FALSE. We give an example where only
one column is selected:
sub4 = subset(airquality, Day == 1, select = Temp, drop = FALSE) # drop = FALSE must not be stated explicitely
str(sub4)## 'data.frame': 5 obs. of 1 variable:
## $ Temp: int 67 78 84 81 91We see that sub4 is still a data frame, even though it consists of one vector only (Temp).
Now, different from above, we declare drop = TRUE:
sub5 = subset(airquality, Day == 1, select = Temp, drop = TRUE) # drop = TRUE !
str(sub5)## int [1:5] 67 78 84 81 91As a result, the command keeps only a vector. The data frame structure has been dropped.
merge commandThe merge command can join two data frames by common columns or row names.
Let us create some example data sets:
df1 <- data.frame( firstname = c("hans", "peter", "mary", "steve", "katie"),
surname = c("lund", "mueller", "kaminski", "smith", "brendel"),
profession = c("dentist", "trucker", "midwife", "dentist", "physician"))
df1## firstname surname profession
## 1 hans lund dentist
## 2 peter mueller trucker
## 3 mary kaminski midwife
## 4 steve smith dentist
## 5 katie brendel physicianWe create a slightly different data frame df2 by changing the column name surname to name:
df2 = df1
colnames(df2)[2] = "name"
# alternative : df2 <- within(df1, { name <- surname; rm(surname) })
df2## firstname name profession
## 1 hans lund dentist
## 2 peter mueller trucker
## 3 mary kaminski midwife
## 4 steve smith dentist
## 5 katie brendel physicianA third data frame:
cars <- data.frame(
name = c("lund", "mueller", "kaminski", "smith", "brendel", "weller", "cooper"),
type = c("kia", "vw", "ford", "bmw", "bmw", "fiat", "audi"),
color = c("pink", "red", "green", "blue", "white", "black", "white"),
miles = c(1200, 400, 650, 1590, 8420, 5000, 7200))
cars## name type color miles
## 1 lund kia pink 1200
## 2 mueller vw red 400
## 3 kaminski ford green 650
## 4 smith bmw blue 1590
## 5 brendel bmw white 8420
## 6 weller fiat black 5000
## 7 cooper audi white 7200By default, the data frames are merged on columns with identical names. If we merge df2 and cars, the common column name is name. We have 5 common entries of both frames in the name column:
intersect(df2$name, cars$name)## [1] "lund" "mueller" "kaminski" "smith" "brendel"Consequently, we should see 5 rows in the merged data set:
merge1 <- merge(df2, cars)
merge1## name firstname profession type color miles
## 1 brendel katie physician bmw white 8420
## 2 kaminski mary midwife ford green 650
## 3 lund hans dentist kia pink 1200
## 4 mueller peter trucker vw red 400
## 5 smith steve dentist bmw blue 1590However, I suggest to specify the column for joining explicitely, because it feels safer. The parameter by declares the column to be used for joining:
merge(df2, cars, by = "name")## name firstname profession type color miles
## 1 brendel katie physician bmw white 8420
## 2 kaminski mary midwife ford green 650
## 3 lund hans dentist kia pink 1200
## 4 mueller peter trucker vw red 400
## 5 smith steve dentist bmw blue 1590Of course, this requires that there is a column with that name in both data frames.
In the cases above, the profession columns of the merged data frames had identical entries, so that we need only one profession column in the output. What if we have profession columns in the datasets which are not identical?
df3 = df2
df3$profession[3] = "director"
df3$profession## [1] "dentist" "trucker" "director" "dentist" "physician"Let us have a look at df1 and df3 once more:
df1## firstname surname profession
## 1 hans lund dentist
## 2 peter mueller trucker
## 3 mary kaminski midwife
## 4 steve smith dentist
## 5 katie brendel physiciandf3## firstname name profession
## 1 hans lund dentist
## 2 peter mueller trucker
## 3 mary kaminski director
## 4 steve smith dentist
## 5 katie brendel physicianmerge(df1, df3, by = "firstname")## firstname surname profession.x name profession.y
## 1 hans lund dentist lund dentist
## 2 katie brendel physician brendel physician
## 3 mary kaminski midwife kaminski director
## 4 peter mueller trucker mueller trucker
## 5 steve smith dentist smith dentistNow, because the original profession columns are not
identical, merge has created two profession
columns with the suffixes .x and .y, respectively.
What if we want to join on columns with different names in the two data sets? For instance, df1 and cars have no common column names:
intersect(colnames(df1), colnames(cars))## character(0)…. but the entries in name and surname are overlapping, so we could join based on them:
df1$surname## [1] "lund" "mueller" "kaminski" "smith" "brendel"cars$name## [1] "lund" "mueller" "kaminski" "smith" "brendel" "weller" "cooper"Using the parameters by.x and by.y, we can specify which columns to use for merging in the corresponding datasets:
merge(cars, df1, by.x = "name", by.y = "surname")## name type color miles firstname profession
## 1 brendel bmw white 8420 katie physician
## 2 kaminski ford green 650 mary midwife
## 3 lund kia pink 1200 hans dentist
## 4 mueller vw red 400 peter trucker
## 5 smith bmw blue 1590 steve dentistWhen using surname in df1 and name in cars for joining, we have only 5 entries in the output, the common entries for these columns.
If we want all names/surnames in the merged dataset, we can specify all = TRUE:
m2 = merge(cars, df1, by.x = "name", by.y = "surname", all = TRUE)
m2## name type color miles firstname profession
## 1 brendel bmw white 8420 katie physician
## 2 cooper audi white 7200 <NA> <NA>
## 3 kaminski ford green 650 mary midwife
## 4 lund kia pink 1200 hans dentist
## 5 mueller vw red 400 peter trucker
## 6 smith bmw blue 1590 steve dentist
## 7 weller fiat black 5000 <NA> <NA>If we do so, we have to tolerate unassigned fields (NA) in the output, because there is no cooper and weller in the surname column of df1.
We have seen that the frame m2 contains unassigned fields, marked by NA. If we have to remove rows with such fields, we can use the complete.cases command:
complete.cases(m2)## [1] TRUE FALSE TRUE TRUE TRUE TRUE FALSEThe result of complete.cases() is a logical vector
indicating rows with complete data (TRUE), and rows containing
NA (FALSE). We can utilize this function to clean a
data frame from rows with unassigned fields:
m3 = m2[complete.cases(m2), ]
m3## name type color miles firstname profession
## 1 brendel bmw white 8420 katie physician
## 3 kaminski ford green 650 mary midwife
## 4 lund kia pink 1200 hans dentist
## 5 mueller vw red 400 peter trucker
## 6 smith bmw blue 1590 steve dentistWe have used the []-operator to select rows with
TRUE-values.
dfa = data.frame(eins = c("a", "b", "c", "d"), zwei = 6:9, drei = 3:6, vier = c("s", "t", "u", "v"))
dfb = data.frame(eins = c("c", "a", "d", "b"), zwei = 5:8, drei = 2:5, vier = 3:6)
rownames(dfa) = c("one", "two", "three", "four")
rownames(dfb) = rownames(dfa)
dfa## eins zwei drei vier
## one a 6 3 s
## two b 7 4 t
## three c 8 5 u
## four d 9 6 vdfb## eins zwei drei vier
## one c 5 2 3
## two a 6 3 4
## three d 7 4 5
## four b 8 5 6Some examples:
dfa = dfa[-1,] # remove first row
dfa## eins zwei drei vier
## two b 7 4 t
## three c 8 5 u
## four d 9 6 vdfa$eins <- NULL # remove column named "eins"
dfa## zwei drei vier
## two 7 4 t
## three 8 5 u
## four 9 6 vdfb[,2:3] <- NULL # remove two columns by index
dfb## eins vier
## one c 3
## two a 4
## three d 5
## four b 6match commandIn some situations, it might be necessary to reorder the rows or columns of a data frame or matrix. This can be accomplished by using the match function. Let us create some example data:
dfa = data.frame(eins = c("a", "b", "c", "d"), zwei = 6:9, drei = 3:6, vier = c("s", "t", "u", "v"))
rownames(dfa) = c("one", "two", "three", "four")
dfa## eins zwei drei vier
## one a 6 3 s
## two b 7 4 t
## three c 8 5 u
## four d 9 6 vWe store the desired order in a target vector with the goal to reorder dfa so that dfa$eins is in this order. The match command finds the positions of target in the dfa$eins column:
target <- c("b", "c", "a", "d") # desired order of the rows
order = match(target, dfa$eins)
order ## [1] 2 3 1 4The match
function takes two vectors as arguments. It yields the positions of the
first vector in the second vector (here saved in the integer vector
order). We see that b comes second in
dfa$eins, c comes third in dfa$eins,
a comes first in dfa$eins, and d comes forth
in dfa$eins.
Using this information, we can now rearrange the rows of the frame
dfa:
dfa_ordered = dfa[order,]
dfa_ordered## eins zwei drei vier
## two b 7 4 t
## three c 8 5 u
## one a 6 3 s
## four d 9 6 vAs it happens, this is the desired order of the data frame stored in target above.
We can of course shorten the procedure:
target <- c("b", "c", "a", "d") # desired order of the rows
dfa_ordered = dfa[match(target, dfa$eins),]
dfa_ordered## eins zwei drei vier
## two b 7 4 t
## three c 8 5 u
## one a 6 3 s
## four d 9 6 vSummary: The code above rearranges dfa$eins according to target.
A second frame:
dfb = data.frame(zwei = c("c", "a", "d", "b"), vier = 5:8, eins = 2:5, drei = 3:6)
rownames(dfb) = c("two", "four", "three", "one")
dfb## zwei vier eins drei
## two c 5 2 3
## four a 6 3 4
## three d 7 4 5
## one b 8 5 6Now, we order dfb$zwei according to dfa$eins, i.e. dfa$eins is the target vector.
intersect(dfb$zwei, dfa$eins) # must have the same entries !## [1] "c" "a" "d" "b"dfa$eins## [1] "a" "b" "c" "d"target = dfa$eins
order = match(target, dfb$zwei)
dfb_neu = dfb[order,]
dfb_neu## zwei vier eins drei
## four a 6 3 4
## one b 8 5 6
## two c 5 2 3
## three d 7 4 5If we want to have this very concise, we can write:
dfb_neu = dfb[match(dfa$eins, dfb$zwei),]
dfb_neu## zwei vier eins drei
## four a 6 3 4
## one b 8 5 6
## two c 5 2 3
## three d 7 4 5Summary: The above command rearranges dfb$zwei according to dfa$eins.
We can also carry out this operation based on the row names, if there are any.
rownames(dfa) ## [1] "one" "two" "three" "four"rownames(dfb) ## [1] "two" "four" "three" "one"We reorder the rows of dfb so that dfb has the same order of row names as dfa:
match(rownames(dfa), rownames(dfb)) # rownames(dfa) is the target vector !## [1] 4 1 3 2dfb[match(rownames(dfa), rownames(dfb)), ]## zwei vier eins drei
## one b 8 5 6
## two c 5 2 3
## three d 7 4 5
## four a 6 3 4Summary: The above command reorders dfb so that it has the same order of row names as dfa.
In a similar way, we can also adapt the order of the columns. Let’s have a look at an example:
colnames(dfa) ## [1] "eins" "zwei" "drei" "vier"colnames(dfb)## [1] "zwei" "vier" "eins" "drei"Our goul is to reorder the columns of dfb, so that their column names agree with those of dfa.
match(colnames(dfa), colnames(dfb)) # colnames(dfa) is the target## [1] 3 1 4 2Note, that the match command comes after the comma because we reorder columns:
dfb[, match(colnames(dfa), colnames(dfb))] # match command after comma## eins zwei drei vier
## two 2 c 3 5
## four 3 a 4 6
## three 4 d 5 7
## one 5 b 6 8Summary: The above command reorders the column names of dfb so that they have the same order as in dfa.
The grep command (or one of its flavours) can be used to search words in character vectors.
v0 = c("A", "B", "C", "D", "A", "a", "B", "B", "D", "E", "E","X", "XX", "DDD", "A", "XX", "FFFF", "0000", "12", "000")
grep("B", v0)## [1] 2 7 8We see that the command outputs the indices of the matches. Using this information, we can create a new vector containing only the matches:
v1 = v0[grep("B", v0)]
v1## [1] "B" "B" "B"The above grep command works only for vectors. If we
want to work on every row or column of a frame or matrix, we can use the
apply
command.
Some example data:
v0 = c("A", "B", "C", "D", "W", "B")
v1 = c("X", "B", "C", "BB", "A", "B")
v2 = c("A", "B", "C", "D", "W", "B")
v3 = c("X", "T", "X", "CC", "S", "U")
Vall = data.frame(one = v0, two = v1, three = v2, four = v3)
rownames(Vall) = c("eins", "zwei", "drei", "vier", "fuenf", "sechs")
Vall## one two three four
## eins A X A X
## zwei B B B T
## drei C C C X
## vier D BB D CC
## fuenf W A W S
## sechs B B B Ugrep("B", Vall)## [1] 1 2 3The output of grep shows only which columns contain the
“B”. If we want the positions in each column of the frame, we
can use the apply command:
apply(Vall, 2, grep, pattern = "B") # "pattern" is a formal parameter of the grep function ## $one
## [1] 2 6
##
## $two
## [1] 2 4 6
##
## $three
## [1] 2 6
##
## $four
## integer(0)We can also search by row. Therefore, we invoke a 1 as second parameter of the command:
apply(Vall, 1, grep, pattern = "B") # "pattern" is a formal parameter of the grep function!## $eins
## integer(0)
##
## $zwei
## [1] 1 2 3
##
## $drei
## integer(0)
##
## $vier
## [1] 2
##
## $fuenf
## integer(0)
##
## $sechs
## [1] 1 2 3We can also run commands on both rows and columns. Moreover, we can use own functions to tell what to do with the entries:
a = matrix(seq(1,16), nrow=4)
a## [,1] [,2] [,3] [,4]
## [1,] 1 5 9 13
## [2,] 2 6 10 14
## [3,] 3 7 11 15
## [4,] 4 8 12 16apply(a, c(1,2), function(x) {x^2}) # square of all elements!## [,1] [,2] [,3] [,4]
## [1,] 1 25 81 169
## [2,] 4 36 100 196
## [3,] 9 49 121 225
## [4,] 16 64 144 256The function definition can also be outside the apply
command:
expo <- function(x, lambda = 1) {exp(-lambda*x)} # function definition
ex = apply(a, c(1,2), expo, lambda = 2) # "lambda" is a formal parameter of "expo"
ex## [,1] [,2] [,3] [,4]
## [1,] 0.1353352832 4.539993e-05 1.522998e-08 5.109089e-12
## [2,] 0.0183156389 6.144212e-06 2.061154e-09 6.914400e-13
## [3,] 0.0024787522 8.315287e-07 2.789468e-10 9.357623e-14
## [4,] 0.0003354626 1.125352e-07 3.775135e-11 1.266417e-14There are more flavours of the apply function, all
listed here.
Did you notice that the command also considered “BB” as a match? (See the row named vier.)
If we only want matches with a single “B”, we have to modify the search pattern. We enclose the B with the begin (^) and end ($) token:
apply(Vall, 1, grep, pattern = "^B$")## $eins
## integer(0)
##
## $zwei
## [1] 1 2 3
##
## $drei
## integer(0)
##
## $vier
## integer(0)
##
## $fuenf
## integer(0)
##
## $sechs
## [1] 1 2 3Now, there isn’t any match in the row named vier.
Construction of such search patterns, called regular expressions, can be quite complicated. Explanation of regular expressions is out of the scope of this document. However, there are lots of manuals and cheatsheets on the web, for instance here or here.
To replace entries in data, we can use the sub or gsub command. We show a few examples:
v = c("a", "b", "aa", "d")
sub("a", "x", v)## [1] "x" "b" "xa" "d"This converts “a” to “x”, and “aa” to
“xa” in the vector v. The second is possibly not what
we want. To also change “aa” to “xx”, we can use
gsub:
v = c("a", "b", "aa", "d")
gsub("a", "x", v)## [1] "x" "b" "xx" "d"If we want to make changes in arrays, we can again facilitate the
apply command:
dfc = data.frame(eins = c("a", "b", "aa", "d"), zwei = 6:9, drei = 3:6, vier = c("a", "aaa", "u", "v"))
dfc## eins zwei drei vier
## 1 a 6 3 a
## 2 b 7 4 aaa
## 3 aa 8 5 u
## 4 d 9 6 vapply(dfc, 1, sub, pattern = "a", replacement = "x") # "pattern" and "replacement" are formal parameters of "sub"## [,1] [,2] [,3] [,4]
## eins "x" "b" "xa" "d"
## zwei "6" "7" "8" "9"
## drei "3" "4" "5" "6"
## vier "x" "xaa" "u" "v"This converts “aa” to “xa”. For some reason, the output matrix is transposed. We can reverse this with the t() function:
dfc_neu = apply(dfc, 1, sub, pattern = "a", replacement = "x") # pattern and replacement are formal parameters of sub
dfc_neu = t(dfc_neu) # transpose
dfc_neu## eins zwei drei vier
## [1,] "x" "6" "3" "x"
## [2,] "b" "7" "4" "xaa"
## [3,] "xa" "8" "5" "u"
## [4,] "d" "9" "6" "v"Again, we use gsub to completely convert all
“a” characters:
t(apply(dfc, 1, gsub, pattern = "a", replacement = "x")) # # pattern and replacement are formal parameters of sub## eins zwei drei vier
## [1,] "x" "6" "3" "x"
## [2,] "b" "7" "4" "xxx"
## [3,] "xx" "8" "5" "u"
## [4,] "d" "9" "6" "v"Here, we took care of transposing the output at the same time.
To identify duplicates in data, the duplicated command is useful. Let’s have a look at an example:
v1 = c("a", "b", "c", "a", "f", "c", "c", "f", "g")
duplicated(v1)## [1] FALSE FALSE FALSE TRUE FALSE TRUE TRUE TRUE FALSEThe output is a logical vector with TRUE values at redundant locations. We can use this information to create a vector without the duplicated items:
v1_cleaned = v1[!duplicated(v1)] # "!" stands for logical NOT !
v1_cleaned## [1] "a" "b" "c" "f" "g"Do not overlook the logical NOT brought about by the !-operator! in the code above.
We can also use the unique command:
v2 = c(1,2,3,2,5,6,8,4,4,3)
unique(v2)## [1] 1 2 3 5 6 8 4We can use the unique command to remove identical rows in data frames or arrays:
dfa = data.frame(eins = c("a", "a", "aa", "a"), zwei = c("b", "b", "aa", "a"), drei = c("c", "c", "aa", "a"), vier = c("d", "d", "u", "v"))
dfa## eins zwei drei vier
## 1 a b c d
## 2 a b c d
## 3 aa aa aa u
## 4 a a a vunique(dfa)## eins zwei drei vier
## 1 a b c d
## 3 aa aa aa u
## 4 a a a vtable commandThe table command can be very useful to gain insight into large data sets. Here comes an example (with a small dataset):
dat = c(rep(3,4), rep(8,3), rep(12,8))
dat## [1] 3 3 3 3 8 8 8 12 12 12 12 12 12 12 12table(dat)## dat
## 3 8 12
## 4 3 8The output says that “dat” includes:
The following example is from the table help page:
set.seed(77)
values = rpois(20, 2) # Poisson distribution with mean = 2; 20 randomly generated values The above call of the function rpois randomly generates 20 outcomes of a Poisson distribution with mean 2.
We can look at the output in tabular form:
values## [1] 1 3 4 5 3 2 3 3 2 2 4 6 1 0 2 1 2 3 4 0table(values)## values
## 0 1 2 3 4 5 6
## 2 3 5 5 3 1 1The summary command can be used to get information about different kinds of data sets.
We start with pure numerical data. The call of rnorm
below randomly creates 100 outcomes of a normal distribution with mean 2
and standard deviation 4. We immediately calculate the summary by
wrapping the summary() around:
summary(rnorm(100, 2, 4))## Min. 1st Qu. Median Mean 3rd Qu. Max.
## -11.6382 -0.2989 1.8385 1.7130 4.0587 11.7403This works also for data frames, for instance for the iris dataset:
head(iris)## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosasummary(iris)## Sepal.Length Sepal.Width Petal.Length Petal.Width
## Min. :4.300 Min. :2.000 Min. :1.000 Min. :0.100
## 1st Qu.:5.100 1st Qu.:2.800 1st Qu.:1.600 1st Qu.:0.300
## Median :5.800 Median :3.000 Median :4.350 Median :1.300
## Mean :5.843 Mean :3.057 Mean :3.758 Mean :1.199
## 3rd Qu.:6.400 3rd Qu.:3.300 3rd Qu.:5.100 3rd Qu.:1.800
## Max. :7.900 Max. :4.400 Max. :6.900 Max. :2.500
## Species
## setosa :50
## versicolor:50
## virginica :50
##
##
## More examples are listed on the summary manual page.
Note that all code snippets are designed for comprehensibility, not for efficiency!