Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 39 ยท Data Frames and Data Wrangling

Subsetting Data Frames: Rows, Columns and Filters

Select rows and columns from R data frames with df[rows, cols], $, [[ ]], logical filters, %in%, subset(), which() and na.omit(), and avoid the NA filtering trap.

Most data work is selection: keep only the customers from one country, take the three columns you care about, drop rows with missing values. R has a compact way to do all of this using square brackets, and it is built from ideas you already know from vectors and matrices. Once df[rows, columns] makes sense, the rest follows.

Here is the small table used in the examples:

R
people <- data.frame(
  name = c("Ana", "Ben", "Cleo", "Dev"),
  age  = c(28, 35, 41, 22),
  city = c("Oslo", "Rome", "Oslo", "Lima")
)

Selecting columns

Four common ways, with different results:

R
people$age                 # a vector
#> [1] 28 35 41 22

people[["age"]]            # a vector (works with a variable name)
#> [1] 28 35 41 22

people["age"]              # a one-column data frame
#>   age
#> 1  28
#> 2  35
#> 3  41
#> 4  22

people[, "age"]            # a vector (rows empty, column age)
#> [1] 28 35 41 22

The distinction mirrors lists: [ ] keeps the container, [[ ]] and $ take the column out. Several columns at once, by name or position:

R
people[, c("name", "city")]
people[, 1:2]
people[, -3]               # everything except the third column

Selecting rows by position

Put the row numbers before the comma:

R
people[2, ]
#>   name age city
#> 2  Ben  35 Rome

people[c(1, 3), ]          # rows 1 and 3
people[2:4, ]              # rows 2 to 4
people[-1, ]               # all but the first row

Combine both parts to pick cells: people[2, "age"] is 35, and people[1:2, c("name", "age")] is a 2 x 2 block.

Filtering rows with conditions

The workhorse is a logical vector in the row position. This is the same idea as x[x > 25] for vectors:

R
people[people$age > 30, ]
#>   name age city
#> 2  Ben  35 Rome
#> 3 Cleo  41 Oslo

people[people$city == "Oslo", c("name", "age")]
#>   name age
#> 1  Ana  28
#> 3 Cleo  41

How it works: people$age > 30 produces FALSE TRUE TRUE FALSE, one flag per row, and R keeps the rows flagged TRUE.

Combine conditions with & (and), | (or) and ! (not). Use the single versions because they work element by element. Wrap each comparison in parentheses when mixing them:

R
people[people$age > 25 & people$city == "Oslo", ]
people[people$age < 25 | people$city == "Rome", ]

Membership tests use %in%:

R
people[people$city %in% c("Oslo", "Lima"), ]
#>   name age city
#> 1  Ana  28 Oslo
#> 3 Cleo  41 Oslo
#> 4  Dev  22 Lima

Counting matches is easy because TRUE counts as 1: sum(people$age > 25) is 3. With the built-in mtcars data, nrow(mtcars[mtcars$cyl == 4, ]) is 11 cars with four cylinders.

Reading df[rows, columns]

Before the comma Which rows
After the comma Which columns
Left empty Means all

df[age > 30, ] keeps rows and all columns.

1 / 2

The NA trap

When a condition meets a missing value, the result is NA, and NA in the row position produces a row full of NA:

R
d <- data.frame(id = 1:3, age = c(28, NA, 41))

d[d$age > 30, ]
#>    id age
#> NA NA  NA
#> 3   3  41

The bogus NA row is rarely wanted. Two safe fixes:

R
d[which(d$age > 30), ]          # which() drops NA positions
#>   id age
#> 3  3  41

d[!is.na(d$age) & d$age > 30, ] # state the rule explicitly

(dplyr::filter(), in the next lessons, drops rows with NA conditions automatically.)

subset(): a friendlier filter

subset() lets you write column names without the df$ prefix:

R
subset(people, age > 25, select = c(name, city))
#>   name city
#> 1  Ana Oslo
#> 2  Ben Rome
#> 3 Cleo Oslo

It is convenient at the console. In reusable code, prefer the bracket form or dplyr, because subset() evaluates names in a special way that can behave oddly inside functions.

Missing values and duplicates

R
complete.cases(d)
#> [1]  TRUE FALSE  TRUE

na.omit(d)           # keep only complete rows
#>   id age
#> 1  1  28
#> 3  3  41

unique(people$city)
#> [1] "Oslo" "Rome" "Lima"

people[!duplicated(people$city), ]   # first row for each city

colSums(is.na(d)) counts missing values in each column, which is a good first check on any new data set.

Changing values with a condition

Assignment uses the same selection syntax:

R
people$age[people$name == "Dev"] <- 23         # fix one cell
people$city[people$city == "Oslo"] <- "Bergen" # recode a value
people[people$age < 25, "age"] <- NA           # set many cells

Read the first line as "in the age column, at the rows where name is Dev, store 23".

Row names after filtering

Filtered rows keep their original row names (notice the 2 and 3 above). To renumber them from 1:

R
result <- people[people$age > 30, ]
rownames(result) <- NULL

Selecting by pattern

grepl() returns a logical vector for text matching, so it plugs straight into a filter: people[grepl("^C", people$name), ] keeps names beginning with C. Text patterns are the topic of Regular Expressions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In df[rows, cols], what does leaving cols empty mean?

    An empty position means "everything" in that dimension.
  2. 2. What is the difference between df["age"] and df[["age"]]?

    Single brackets keep the data frame container; double brackets extract the vector.
  3. 3. Which keeps rows where age is over 30 and city is Oslo?

    The single & works element by element. && only handles single values.
  4. 4. Why can d[d$age > 30, ] return a row of NAs?

    Use which(), is.na() or dplyr::filter() to avoid it.

Summary

  • df[rows, columns]: before the comma rows, after it columns; empty means all.
  • df$col and df[["col"]] give a vector; df["col"] gives a data frame.
  • Filter rows with logical conditions combined by &, |, ! and %in%.
  • Beware NA in conditions: use which(), is.na(), complete.cases() or na.omit().
  • subset() is convenient interactively; assignment with the same syntax changes values.

Next: dplyr Basics