Manipulating Datasets

Download Rmd Version

If you wish to engage with this course content via Rmd, then please click the link below to download the Rmd file.

Download manipulating_data.Rmd

Often we need to manipulate or extract parts of our dataset prior to doing any analysis or plotting with it.

Learning Objectives

• Learn how to subset a dataset using conditional subsetting.

• Understand different methods for comparing and matching datasets in R.

• Learn how to combine one and two dimensional datasets using paste(), cbind(), and rbind().

• Learn how to rename, add and remove rows and/or columns in a two dimensional dataset.

• Learn how to use the seq() function to generate regular sequences of numbers.

Conditional Subsetting

We have already looked at slicing subsets, where we knew the indexes of the rows or columns of the entries we wanted. There may be times when, instead, we want to select rows based on a specific condition. This would require a conditional statement. Conditional commands check if criteria are met and return either TRUE or FALSE in response.

Let’s find which rows of iris have a Sepal.Length less than 7.

iris$Sepal.Length < 6

1. TRUE
2. TRUE
3. TRUE
4. TRUE
5. TRUE
6. TRUE
7. TRUE
8. TRUE
9. TRUE
10. TRUE
11. TRUE
12. TRUE
13. TRUE
14. TRUE
15. TRUE
16. TRUE
17. TRUE
18. TRUE
19. TRUE
20. TRUE
21. TRUE
22. TRUE
23. TRUE
24. TRUE
25. TRUE
26. TRUE
27. TRUE
28. TRUE
29. TRUE
30. TRUE
31. TRUE
32. TRUE
33. TRUE
34. TRUE
35. TRUE
36. TRUE
37. TRUE
38. TRUE
39. TRUE
40. TRUE
41. TRUE
42. TRUE
43. TRUE
44. TRUE
45. TRUE
46. TRUE
47. TRUE
48. TRUE
49. TRUE
50. TRUE
51. FALSE
52. FALSE
53. FALSE
54. TRUE
55. FALSE
56. TRUE
57. FALSE
58. TRUE
59. FALSE
60. TRUE
61. TRUE
62. TRUE
63. FALSE
64. FALSE
65. TRUE
66. FALSE
67. TRUE
68. TRUE
69. FALSE
70. TRUE
71. TRUE
72. FALSE
73. FALSE
74. FALSE
75. FALSE
76. FALSE
77. FALSE
78. FALSE
79. FALSE
80. TRUE
81. TRUE
82. TRUE
83. TRUE
84. FALSE
85. TRUE
86. FALSE
87. FALSE
88. FALSE
89. TRUE
90. TRUE
91. TRUE
92. FALSE
93. TRUE
94. TRUE
95. TRUE
96. TRUE
97. TRUE
98. FALSE
99. TRUE
100. TRUE
101. FALSE
102. TRUE
103. FALSE
104. FALSE
105. FALSE
106. FALSE
107. TRUE
108. FALSE
109. FALSE
110. FALSE
111. FALSE
112. FALSE
113. FALSE
114. TRUE
115. TRUE
116. FALSE
117. FALSE
118. FALSE
119. FALSE
120. FALSE
121. FALSE
122. TRUE
123. FALSE
124. FALSE
125. FALSE
126. FALSE
127. FALSE
128. FALSE
129. FALSE
130. FALSE
131. FALSE
132. FALSE
133. FALSE
134. FALSE
135. FALSE
136. FALSE
137. FALSE
138. FALSE
139. FALSE
140. FALSE
141. FALSE
142. FALSE
143. TRUE
144. FALSE
145. FALSE
146. FALSE
147. FALSE
148. FALSE
149. FALSE
150. TRUE

Where is says TRUE means the criteria have been met and FALSE not. We can use this to subset the rows of iris

iris[iris$Sepal.Length < 6,]

A data.frame: 83 × 5

	Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
	<dbl>	<dbl>	<dbl>	<dbl>	<fct>
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	setosa
7	4.6	3.4	1.4	0.3	setosa
8	5.0	3.4	1.5	0.2	setosa
9	4.4	2.9	1.4	0.2	setosa
10	4.9	3.1	1.5	0.1	setosa
11	5.4	3.7	1.5	0.2	setosa
12	4.8	3.4	1.6	0.2	setosa
13	4.8	3.0	1.4	0.1	setosa
14	4.3	3.0	1.1	0.1	setosa
15	5.8	4.0	1.2	0.2	setosa
16	5.7	4.4	1.5	0.4	setosa
17	5.4	3.9	1.3	0.4	setosa
18	5.1	3.5	1.4	0.3	setosa
19	5.7	3.8	1.7	0.3	setosa
20	5.1	3.8	1.5	0.3	setosa
21	5.4	3.4	1.7	0.2	setosa
22	5.1	3.7	1.5	0.4	setosa
23	4.6	3.6	1.0	0.2	setosa
24	5.1	3.3	1.7	0.5	setosa
25	4.8	3.4	1.9	0.2	setosa
26	5.0	3.0	1.6	0.2	setosa
27	5.0	3.4	1.6	0.4	setosa
28	5.2	3.5	1.5	0.2	setosa
29	5.2	3.4	1.4	0.2	setosa
30	4.7	3.2	1.6	0.2	setosa
⋮	⋮	⋮	⋮	⋮	⋮
60	5.2	2.7	3.9	1.4	versicolor
61	5.0	2.0	3.5	1.0	versicolor
62	5.9	3.0	4.2	1.5	versicolor
65	5.6	2.9	3.6	1.3	versicolor
67	5.6	3.0	4.5	1.5	versicolor
68	5.8	2.7	4.1	1.0	versicolor
70	5.6	2.5	3.9	1.1	versicolor
71	5.9	3.2	4.8	1.8	versicolor
80	5.7	2.6	3.5	1.0	versicolor
81	5.5	2.4	3.8	1.1	versicolor
82	5.5	2.4	3.7	1.0	versicolor
83	5.8	2.7	3.9	1.2	versicolor
85	5.4	3.0	4.5	1.5	versicolor
89	5.6	3.0	4.1	1.3	versicolor
90	5.5	2.5	4.0	1.3	versicolor
91	5.5	2.6	4.4	1.2	versicolor
93	5.8	2.6	4.0	1.2	versicolor
94	5.0	2.3	3.3	1.0	versicolor
95	5.6	2.7	4.2	1.3	versicolor
96	5.7	3.0	4.2	1.2	versicolor
97	5.7	2.9	4.2	1.3	versicolor
99	5.1	2.5	3.0	1.1	versicolor
100	5.7	2.8	4.1	1.3	versicolor
102	5.8	2.7	5.1	1.9	virginica
107	4.9	2.5	4.5	1.7	virginica
114	5.7	2.5	5.0	2.0	virginica
115	5.8	2.8	5.1	2.4	virginica
122	5.6	2.8	4.9	2.0	virginica
143	5.8	2.7	5.1	1.9	virginica
150	5.9	3.0	5.1	1.8	virginica

Matching

There are several circumstances we will need to check for matching and use that information. There are several ways we can do this using R depending on what we need.

Using identical(), we can check if values or collections of values are identical.

# Checking if the first and second row values in the "Species" column of iris are identical
identical(iris$Species[1], iris$Species[2])

TRUE

# Checking if the first and 51st row values in the "Species" column of iris are identical
identical(iris$Species[1], iris$Species[51])

FALSE

Using all.equal() is similar to identical(), but allows for some tolerance in how similar values can be. For example, we may want to check two numbers with lots of decimal places, but only need them to be within 0.01 of each other. Therefore, we can give a tolerance of 0.01.

x1 <- 1.232529
x2 <- 1.23366
all.equal(x1, x2, tolerance=0.01)

TRUE

all.equal(x1, x2, tolerance=0.0001)

'Mean relative difference: 0.0009176255'

We can use “==” as a selector to pull all matching entries. We can give a numeric value or a character in quotations.

iris[iris$Species == "setosa", ]

A data.frame: 50 × 5

	Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
	<dbl>	<dbl>	<dbl>	<dbl>	<fct>
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	setosa
7	4.6	3.4	1.4	0.3	setosa
8	5.0	3.4	1.5	0.2	setosa
9	4.4	2.9	1.4	0.2	setosa
10	4.9	3.1	1.5	0.1	setosa
11	5.4	3.7	1.5	0.2	setosa
12	4.8	3.4	1.6	0.2	setosa
13	4.8	3.0	1.4	0.1	setosa
14	4.3	3.0	1.1	0.1	setosa
15	5.8	4.0	1.2	0.2	setosa
16	5.7	4.4	1.5	0.4	setosa
17	5.4	3.9	1.3	0.4	setosa
18	5.1	3.5	1.4	0.3	setosa
19	5.7	3.8	1.7	0.3	setosa
20	5.1	3.8	1.5	0.3	setosa
21	5.4	3.4	1.7	0.2	setosa
22	5.1	3.7	1.5	0.4	setosa
23	4.6	3.6	1.0	0.2	setosa
24	5.1	3.3	1.7	0.5	setosa
25	4.8	3.4	1.9	0.2	setosa
26	5.0	3.0	1.6	0.2	setosa
27	5.0	3.4	1.6	0.4	setosa
28	5.2	3.5	1.5	0.2	setosa
29	5.2	3.4	1.4	0.2	setosa
30	4.7	3.2	1.6	0.2	setosa
31	4.8	3.1	1.6	0.2	setosa
32	5.4	3.4	1.5	0.4	setosa
33	5.2	4.1	1.5	0.1	setosa
34	5.5	4.2	1.4	0.2	setosa
35	4.9	3.1	1.5	0.2	setosa
36	5.0	3.2	1.2	0.2	setosa
37	5.5	3.5	1.3	0.2	setosa
38	4.9	3.6	1.4	0.1	setosa
39	4.4	3.0	1.3	0.2	setosa
40	5.1	3.4	1.5	0.2	setosa
41	5.0	3.5	1.3	0.3	setosa
42	4.5	2.3	1.3	0.3	setosa
43	4.4	3.2	1.3	0.2	setosa
44	5.0	3.5	1.6	0.6	setosa
45	5.1	3.8	1.9	0.4	setosa
46	4.8	3.0	1.4	0.3	setosa
47	5.1	3.8	1.6	0.2	setosa
48	4.6	3.2	1.4	0.2	setosa
49	5.3	3.7	1.5	0.2	setosa
50	5.0	3.3	1.4	0.2	setosa

We can use objects or parts of objects to select rows and columns within [ ] using “%in%”.

select <- "versicolor"
iris[iris$Species %in% select, ]

A data.frame: 50 × 5

	Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
	<dbl>	<dbl>	<dbl>	<dbl>	<fct>
51	7.0	3.2	4.7	1.4	versicolor
52	6.4	3.2	4.5	1.5	versicolor
53	6.9	3.1	4.9	1.5	versicolor
54	5.5	2.3	4.0	1.3	versicolor
55	6.5	2.8	4.6	1.5	versicolor
56	5.7	2.8	4.5	1.3	versicolor
57	6.3	3.3	4.7	1.6	versicolor
58	4.9	2.4	3.3	1.0	versicolor
59	6.6	2.9	4.6	1.3	versicolor
60	5.2	2.7	3.9	1.4	versicolor
61	5.0	2.0	3.5	1.0	versicolor
62	5.9	3.0	4.2	1.5	versicolor
63	6.0	2.2	4.0	1.0	versicolor
64	6.1	2.9	4.7	1.4	versicolor
65	5.6	2.9	3.6	1.3	versicolor
66	6.7	3.1	4.4	1.4	versicolor
67	5.6	3.0	4.5	1.5	versicolor
68	5.8	2.7	4.1	1.0	versicolor
69	6.2	2.2	4.5	1.5	versicolor
70	5.6	2.5	3.9	1.1	versicolor
71	5.9	3.2	4.8	1.8	versicolor
72	6.1	2.8	4.0	1.3	versicolor
73	6.3	2.5	4.9	1.5	versicolor
74	6.1	2.8	4.7	1.2	versicolor
75	6.4	2.9	4.3	1.3	versicolor
76	6.6	3.0	4.4	1.4	versicolor
77	6.8	2.8	4.8	1.4	versicolor
78	6.7	3.0	5.0	1.7	versicolor
79	6.0	2.9	4.5	1.5	versicolor
80	5.7	2.6	3.5	1.0	versicolor
81	5.5	2.4	3.8	1.1	versicolor
82	5.5	2.4	3.7	1.0	versicolor
83	5.8	2.7	3.9	1.2	versicolor
84	6.0	2.7	5.1	1.6	versicolor
85	5.4	3.0	4.5	1.5	versicolor
86	6.0	3.4	4.5	1.6	versicolor
87	6.7	3.1	4.7	1.5	versicolor
88	6.3	2.3	4.4	1.3	versicolor
89	5.6	3.0	4.1	1.3	versicolor
90	5.5	2.5	4.0	1.3	versicolor
91	5.5	2.6	4.4	1.2	versicolor
92	6.1	3.0	4.6	1.4	versicolor
93	5.8	2.6	4.0	1.2	versicolor
94	5.0	2.3	3.3	1.0	versicolor
95	5.6	2.7	4.2	1.3	versicolor
96	5.7	3.0	4.2	1.2	versicolor
97	5.7	2.9	4.2	1.3	versicolor
98	6.2	2.9	4.3	1.3	versicolor
99	5.1	2.5	3.0	1.1	versicolor
100	5.7	2.8	4.1	1.3	versicolor

Merging and Binding

We will often need to bring multiple two-dimensional objects together. We can do this multiple ways.

Using rbind() and cbind(), we can combine objects together. rbind() allows us to bind together rows.

# First we look at the dimension of "iris"
dim(iris)

1. 150
2. 5

# Using rbind() to put together two copies of  causing double rows
rbind(iris, iris) -> iris.r
dim(iris.r)

1. 300
2. 5

iris.r[ ,1]

1. 5.1
2. 4.9
3. 4.7
4. 4.6
5. 5
6. 5.4
7. 4.6
8. 5
9. 4.4
10. 4.9
11. 5.4
12. 4.8
13. 4.8
14. 4.3
15. 5.8
16. 5.7
17. 5.4
18. 5.1
19. 5.7
20. 5.1
21. 5.4
22. 5.1
23. 4.6
24. 5.1
25. 4.8
26. 5
27. 5
28. 5.2
29. 5.2
30. 4.7
31. 4.8
32. 5.4
33. 5.2
34. 5.5
35. 4.9
36. 5
37. 5.5
38. 4.9
39. 4.4
40. 5.1
41. 5
42. 4.5
43. 4.4
44. 5
45. 5.1
46. 4.8
47. 5.1
48. 4.6
49. 5.3
50. 5
51. 7
52. 6.4
53. 6.9
54. 5.5
55. 6.5
56. 5.7
57. 6.3
58. 4.9
59. 6.6
60. 5.2
61. 5
62. 5.9
63. 6
64. 6.1
65. 5.6
66. 6.7
67. 5.6
68. 5.8
69. 6.2
70. 5.6
71. 5.9
72. 6.1
73. 6.3
74. 6.1
75. 6.4
76. 6.6
77. 6.8
78. 6.7
79. 6
80. 5.7
81. 5.5
82. 5.5
83. 5.8
84. 6
85. 5.4
86. 6
87. 6.7
88. 6.3
89. 5.6
90. 5.5
91. 5.5
92. 6.1
93. 5.8
94. 5
95. 5.6
96. 5.7
97. 5.7
98. 6.2
99. 5.1
100. 5.7
101. 6.3
102. 5.8
103. 7.1
104. 6.3
105. 6.5
106. 7.6
107. 4.9
108. 7.3
109. 6.7
110. 7.2
111. 6.5
112. 6.4
113. 6.8
114. 5.7
115. 5.8
116. 6.4
117. 6.5
118. 7.7
119. 7.7
120. 6
121. 6.9
122. 5.6
123. 7.7
124. 6.3
125. 6.7
126. 7.2
127. 6.2
128. 6.1
129. 6.4
130. 7.2
131. 7.4
132. 7.9
133. 6.4
134. 6.3
135. 6.1
136. 7.7
137. 6.3
138. 6.4
139. 6
140. 6.9
141. 6.7
142. 6.9
143. 5.8
144. 6.8
145. 6.7
146. 6.7
147. 6.3
148. 6.5
149. 6.2
150. 5.9
151. 5.1
152. 4.9
153. 4.7
154. 4.6
155. 5
156. 5.4
157. 4.6
158. 5
159. 4.4
160. 4.9
161. 5.4
162. 4.8
163. 4.8
164. 4.3
165. 5.8
166. 5.7
167. 5.4
168. 5.1
169. 5.7
170. 5.1
171. 5.4
172. 5.1
173. 4.6
174. 5.1
175. 4.8
176. 5
177. 5
178. 5.2
179. 5.2
180. 4.7
181. 4.8
182. 5.4
183. 5.2
184. 5.5
185. 4.9
186. 5
187. 5.5
188. 4.9
189. 4.4
190. 5.1
191. 5
192. 4.5
193. 4.4
194. 5
195. 5.1
196. 4.8
197. 5.1
198. 4.6
199. 5.3
200. 5
201. 7
202. 6.4
203. 6.9
204. 5.5
205. 6.5
206. 5.7
207. 6.3
208. 4.9
209. 6.6
210. 5.2
211. 5
212. 5.9
213. 6
214. 6.1
215. 5.6
216. 6.7
217. 5.6
218. 5.8
219. 6.2
220. 5.6
221. 5.9
222. 6.1
223. 6.3
224. 6.1
225. 6.4
226. 6.6
227. 6.8
228. 6.7
229. 6
230. 5.7
231. 5.5
232. 5.5
233. 5.8
234. 6
235. 5.4
236. 6
237. 6.7
238. 6.3
239. 5.6
240. 5.5
241. 5.5
242. 6.1
243. 5.8
244. 5
245. 5.6
246. 5.7
247. 5.7
248. 6.2
249. 5.1
250. 5.7
251. 6.3
252. 5.8
253. 7.1
254. 6.3
255. 6.5
256. 7.6
257. 4.9
258. 7.3
259. 6.7
260. 7.2
261. 6.5
262. 6.4
263. 6.8
264. 5.7
265. 5.8
266. 6.4
267. 6.5
268. 7.7
269. 7.7
270. 6
271. 6.9
272. 5.6
273. 7.7
274. 6.3
275. 6.7
276. 7.2
277. 6.2
278. 6.1
279. 6.4
280. 7.2
281. 7.4
282. 7.9
283. 6.4
284. 6.3
285. 6.1
286. 7.7
287. 6.3
288. 6.4
289. 6
290. 6.9
291. 6.7
292. 6.9
293. 5.8
294. 6.8
295. 6.7
296. 6.7
297. 6.3
298. 6.5
299. 6.2
300. 5.9

cbind() allows us to bind together columns.

# Using cbind() to put together two copies of iris causing double columns
cbind(iris, iris) -> iris.c
dim(iris.c)

1. 150
2. 10

iris.c[1,]

A data.frame: 1 × 10

	Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species	Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species
	<dbl>	<dbl>	<dbl>	<dbl>	<fct>	<dbl>	<dbl>	<dbl>	<dbl>	<fct>
1	5.1	3.5	1.4	0.2	setosa	5.1	3.5	1.4	0.2	setosa

Both rbind and cbind will work, if you try to combine vectors of different lengths. In this case it will recycle the shorted vector until it matches the length of the longer vector. You will get a warning in this situation.

Using merge(), we can merge objects together assigning what we bind by using “by =”. For example, we can bind using the rownames of our objects using “by = row.names”, we can merge by a specific column present in both objects (e.g. by = “Name”), or different columns in each object (by.x = “Species”, by.y = “Name”).

# Merging iris by row names
merge(iris, iris, by = "row.names") -> iris.double
dim(iris.double)

1. 150
2. 11

iris.double[1, ]

A data.frame: 1 × 11

	Row.names	Sepal.Length.x	Sepal.Width.x	Petal.Length.x	Petal.Width.x	Species.x	Sepal.Length.y	Sepal.Width.y	Petal.Length.y	Petal.Width.y	Species.y
	<I<chr>>	<dbl>	<dbl>	<dbl>	<dbl>	<fct>	<dbl>	<dbl>	<dbl>	<dbl>	<fct>
1	1	5.1	3.5	1.4	0.2	setosa	5.1	3.5	1.4	0.2	setosa

Activity

Create two objects, one containing numbers 1-10, one containing numbers 11-20

• Bind them together to make an object of two rows, row 1 being 1:10, row 2 being 11-20

• Bind them together to make an object of two columns, columns 1 being 11-20, column 2 being 1-10

Paste

The function paste() is a way of concatenating vectors together. It can be applied to a characters or numbers, vector and column(s) of a data frame or matrix. You can define what you want the separator to be (sep =), or use paste0() or paste() with the argument sep = “” for no space. You can also provide a string as an argument to add the same component to a character or vector.

# Adding text to a value in iris
paste("Species", iris$Species[1])

'Species setosa'

# Pasting together two columns of iris
paste(iris$Species[1:10], iris$Sepal.Length[1:10], sep = ":")

1. 'setosa:5.1'
2. 'setosa:4.9'
3. 'setosa:4.7'
4. 'setosa:4.6'
5. 'setosa:5'
6. 'setosa:5.4'
7. 'setosa:4.6'
8. 'setosa:5'
9. 'setosa:4.4'
10. 'setosa:4.9'

Renaming columns and rows

By using rownames() and colnames(), we can look at what the rownames and colnames of an object are. We can also use this to replace the rownames and colnames of the object by assigning using <-.

# Renaming the colnames in iris
iris -> iris2
colnames(iris2)

1. 'Sepal.Length'
2. 'Sepal.Width'
3. 'Petal.Length'
4. 'Petal.Width'
5. 'Species'

colnames(iris2) <- c("S.Length", "S.Width", "P.Length", "P.Width", "Type")
colnames(iris2)

1. 'S.Length'
2. 'S.Width'
3. 'P.Length'
4. 'P.Width'
5. 'Type'

Adding and removing variables

Adding data to your objects can be very useful. Adding an extra column is very easy using the assignment operator and giving the new column a name.

# Adding a new column
iris -> iris2
iris2$new.column <- 1:nrow(iris2)

head(iris2)

A data.frame: 6 × 6

	Sepal.Length	Sepal.Width	Petal.Length	Petal.Width	Species	new.column
	<dbl>	<dbl>	<dbl>	<dbl>	<fct>	<int>
1	5.1	3.5	1.4	0.2	setosa	1
2	4.9	3.0	1.4	0.2	setosa	2
3	4.7	3.2	1.3	0.2	setosa	3
4	4.6	3.1	1.5	0.2	setosa	4
5	5.0	3.6	1.4	0.2	setosa	5
6	5.4	3.9	1.7	0.4	setosa	6

Removing a column can be done by assigning the relevant column the “NULL” value.

# Removing a column
iris2$new.column <- NULL

Generating a sequence of numbers

To generate regular sequences, we can use seq(). We provide it a value to start from (from =), and where to end (to =) and then a value to increase by (by =).

# Create a sequence from 0 to 100 increasing by 5 each time
seq(from = 0, to = 100, by = 5)

1. 0
2. 5
3. 10
4. 15
5. 20
6. 25
7. 30
8. 35
9. 40
10. 45
11. 50
12. 55
13. 60
14. 65
15. 70
16. 75
17. 80
18. 85
19. 90
20. 95
21. 100

Activity

Create a copy of iris

• Rename the columns of iris by prefixing with the word “flower” and the separator “_”

• In your copy, duplicate the Species column

• Add a column to your copy which contains the numbers from 4 to 600 increasing by 4 each time.

Summary Quiz

What is the purpose of conditional subsetting in R?

To merge two datasets To extract rows based on specific conditions To rename columns and rows To concatenate strings

Submit

---

Which function is used to concatenate strings in R?

merge() cbind() paste() subset()

Submit

---

How can you combine two datasets by rows in R?

merge() rbind() cbind() subset()

Submit

---