Skip to content

Data frames and the pipe

Chapter 1 ran six programs. This chapter explains the parts that they are made of: values, vectors, data frames and the pipe. Then it shows four things that cause most silent errors in a study: missing values, dates, the order of groups and the messages in the console. Learn them once, and every later chapter is easier.

programs/02_1_vectors.R
# Chapter 2: a vector holds values of one type; a data frame is named vectors of the same length
library(dplyr)
ages <- c(63, 64, 71)
ages
#> [1] 63 64 71
length(ages)
#> [1] 3
ages * 12
#> [1] 756 768 852
ages >= 65
#> [1] FALSE FALSE TRUE
subjects <- tibble(
USUBJID = c("01-701-1015", "01-701-1023", "01-701-1028"),
AGE = ages,
SEX = c("F", "M", "M")
)
subjects
#> # A tibble: 3 x 3
#> USUBJID AGE SEX
#> <chr> <dbl> <chr>
#> 1 01-701-1015 63 F
#> 2 01-701-1023 64 M
#> 3 01-701-1028 71 M
subjects$AGE
#> [1] 63 64 71
nrow(subjects)
#> [1] 3
  • A value is one piece of data: the number 63 or the text "01-701-1015". Text is always in quotes.
  • A vector is a row of values of the same type, in order. c() combines values into a vector. The name means “combine”.
  • length() gives the number of values in a vector. ages has 3.
  • ages * 12 multiplies every value. You get 756, 768 and 852 without a loop. ages >= 65 asks the question for every value and gives three answers: FALSE FALSE TRUE.
  • A data frame is a set of vectors of the same length, side by side. Each vector is a column and has a name. tibble() makes one. Each position across the columns is a row: here, one subject.
  • subjects$AGE takes one column out of the data frame. The result is the vector again.
programs/02_2_verbs.R
# Chapter 2: filter(), select(), mutate() and arrange(), joined by the pipe
library(dplyr)
dm <- pharmaversesdtm::dm
nrow(dm)
#> [1] 306
dm_rand <- dm |>
filter(ARMCD != "Scrnfail") |>
select(USUBJID, SITEID, AGE, SEX, ARM)
nrow(dm_rand)
#> [1] 254
dm_rand |>
filter(SITEID == "701", SEX == "F") |>
mutate(AGE_MONTHS = AGE * 12) |>
arrange(desc(AGE)) |>
print(n = 5)
#> # A tibble: 18 x 6
#> USUBJID SITEID AGE SEX ARM AGE_MONTHS
#> <chr> <chr> <dbl> <chr> <chr> <dbl>
#> 1 01-701-1387 701 87 F Placebo 1044
#> 2 01-701-1047 701 85 F Placebo 1020
#> 3 01-701-1429 701 84 F Xanomeline Low Dose 1008
#> 4 01-701-1111 701 81 F Xanomeline Low Dose 972
#> 5 01-701-1133 701 81 F Xanomeline High Dose 972
#> # i 13 more rows

SDTM DM has 306 rows. Without the screen failures (ARMCD is "Scrnfail"), 254 remain. Then the program finds the women of site 701 and sorts them from the oldest down. There are 18. The oldest, 01-701-1387, is 87 years old: 1044 months.

Four functions of dplyr do most of the work. Each one takes a data frame and gives a new data frame back:

Verb What it does Example
filter() keeps the rows where a condition is true filter(SEX == "F")
select() keeps the columns that you name select(USUBJID, AGE)
mutate() makes a new column, or changes one mutate(AGE_MONTHS = AGE * 12)
arrange() sorts the rows arrange(desc(AGE))

The pipe |> gives the result of one step to the next step as its first input. dm |> filter(...) is the same as filter(dm, ...). With the pipe, you read the program from top to bottom, in the order that R does the steps. != means “is not equal to”. Two conditions in one filter(), with a comma between them, must both be true.

dm_rand <- ... keeps the result. A step without <- only prints its result. The data frame dm does not change: no verb changes its input.

programs/02_3_columns.R
# Chapter 2: mutate() works on whole columns, so one step can use the mean of all rows
library(dplyr)
pharmaversesdtm::dm |>
filter(ARMCD != "Scrnfail") |>
select(USUBJID, AGE) |>
mutate(
MEAN_AGE = mean(AGE),
DIFF = AGE - MEAN_AGE
) |>
print(n = 3)
#> # A tibble: 254 x 4
#> USUBJID AGE MEAN_AGE DIFF
#> <chr> <dbl> <dbl> <dbl>
#> 1 01-701-1015 63 75.1 -12.1
#> 2 01-701-1023 64 75.1 -11.1
#> 3 01-701-1028 71 75.1 -4.09
#> # i 251 more rows

Inside mutate(), AGE is the whole column: 254 values. mean(AGE) is one value, 75.1, the mean age of all 254 subjects. R puts it on every row. Then DIFF = AGE - MEAN_AGE computes all 254 differences at once. Subject 01-701-1015 is 63, so its difference is -12.1.

This is the most important idea of the chapter. R does not read the data one row at a time. Each step gets whole columns and gives whole columns back. So one step can use a value that comes from all rows, such as a mean or a count.

A missing value in R is NA (“not available”). It is the same for numbers, text and dates. NA causes the most common silent error in R.

programs/02_4_missing.R
# Chapter 2: NA in a comparison gives NA, and filter() drops those rows without a message
library(dplyr)
sysbp <- pharmaversesdtm::vs |>
filter(VSTESTCD == "SYSBP") |>
select(USUBJID, VISIT, VSSTRESN)
NA < 140
#> [1] NA
nrow(sysbp)
#> [1] 8208
sysbp |> filter(VSSTRESN >= 140) |> nrow()
#> [1] 3121
sysbp |> filter(VSSTRESN < 140) |> nrow()
#> [1] 5084
sysbp |> filter(is.na(VSSTRESN))
#> # A tibble: 3 x 3
#> USUBJID VISIT VSSTRESN
#> <chr> <chr> <dbl>
#> 1 01-702-1082 SCREENING 2 NA
#> 2 01-703-1279 WEEK 2 NA
#> 3 01-713-1141 WEEK 6 NA
sysbp |>
mutate(BPCAT = case_when(
is.na(VSSTRESN) ~ "missing",
VSSTRESN >= 140 ~ "high",
.default = "normal"
)) |>
count(BPCAT)
#> # A tibble: 3 x 2
#> BPCAT n
#> <chr> <int>
#> 1 high 3121
#> 2 missing 3
#> 3 normal 5084
# Missing text: NA here, but "" after a trip through a SAS transport file
ae <- pharmaversesdtm::ae |>
filter(USUBJID == "01-701-1015") |>
select(AEDECOD, AEENDTC)
xpt <- tempfile(fileext = ".xpt")
haven::write_xpt(ae, xpt)
ae_sas <- haven::read_xpt(xpt)
ae$AEENDTC
#> [1] NA NA "2014-01-11"
#> attr(,"label")
#> [1] "End Date/Time of Adverse Event"
ae_sas$AEENDTC
#> [1] "" "" "2014-01-11"
#> attr(,"label")
#> [1] "End Date/Time of Adverse Event"
is.na(ae_sas$AEENDTC)
#> [1] FALSE FALSE FALSE

NA < 140 is not TRUE and not FALSE. It is NA: R does not know the value, so it does not know the answer. Then filter() keeps only the rows where the condition is TRUE. It drops the rows where the condition is NA, and it gives no message.

Look at the counts. There are 8208 systolic blood pressure records. 3121 are 140 or higher and 5084 are below 140. But 3121 + 5084 = 8205. The two filters lost 3 records. is.na() finds them: three records with no value. They belong neither to “high” nor to “normal”.

The fix is to name the missing values first. case_when() tests its conditions from top to bottom and uses the first one that is true. .default gives the value for all other rows. The result: high 3121, missing 3 and normal 5084. Now every record has a group.

Missing text. The last part of the program shows a second trap. In pharmaversesdtm, the end date of an adverse event that has not ended is NA. Data that comes from SAS is different. SAS has no missing text, only empty text "". After a round trip through a SAS transport file (.xpt), the same two values are "", and is.na() finds none of them. When your data comes from SAS files, test for both: is.na(x) | x == "". | means “or”.

The output also shows attr(,"label"). That is the label of the column. Chapter 3 explains labels.

Exercise 2-1. Keep only the subjects of DM whose ARM is "Placebo". How many rows do you get?

Exercise 2-2. Diastolic blood pressure (VSTESTCD == "DIABP") has missing values too. How many? Split the records with VSSTRESN >= 90 and VSSTRESN < 90. Do the two parts add up?

For a small table, write the data in the program. These are the analysis windows of a BDS data set (chapter 10): one row per planned visit, with the first day, the target day and the last day of the window.

programs/02_5_files.R
# Chapter 2: data written in the program with tribble(), then saved as CSV and .rds and read back
library(dplyr)
library(readr)
windows <- tribble(
~AVISITN, ~AWLO, ~AWTARGET, ~AWHI,
2, 2, 15, 21,
4, 22, 29, 35,
6, 36, 43, 49,
8, 50, 57, 70,
12, 71, 85, 98,
16, 99, 113, 126,
20, 127, 141, 154,
24, 155, 169, 175,
26, 176, 183, NA
) |>
mutate(AVISIT = paste("Week", AVISITN))
windows
#> # A tibble: 9 x 5
#> AVISITN AWLO AWTARGET AWHI AVISIT
#> <dbl> <dbl> <dbl> <dbl> <chr>
#> 1 2 2 15 21 Week 2
#> 2 4 22 29 35 Week 4
#> 3 6 36 43 49 Week 6
#> 4 8 50 57 70 Week 8
#> 5 12 71 85 98 Week 12
#> 6 16 99 113 126 Week 16
#> 7 20 127 141 154 Week 20
#> 8 24 155 169 175 Week 24
#> 9 26 176 183 NA Week 26
csv_file <- file.path(tempdir(), "windows.csv")
write_csv(windows, csv_file)
read_lines(csv_file, n_max = 3)
#> [1] "AVISITN,AWLO,AWTARGET,AWHI,AVISIT" "2,2,15,21,Week 2"
#> [3] "4,22,29,35,Week 4"
windows_csv <- read_csv(csv_file)
#> Rows: 9 Columns: 5
#> -- Column specification --------------------------------------------------------
#> Delimiter: ","
#> chr (1): AVISIT
#> dbl (4): AVISITN, AWLO, AWTARGET, AWHI
#>
#> i Use `spec()` to retrieve the full column specification for this data.
#> i Specify the column types or set `show_col_types = FALSE` to quiet this message.
rds_file <- file.path(tempdir(), "windows.rds")
saveRDS(windows, rds_file)
identical(readRDS(rds_file), windows)
#> [1] TRUE
  • tribble() writes a data frame row by row. The first line names the columns, each with a ~. Then each line is one row. NA is a missing value: the last window has no last day.
  • write_csv() (package readr) writes a CSV file: values separated by commas, with a header line. read_lines() shows the first 3 lines of the file as text.
  • read_csv() reads the file back. It prints a message: 9 rows, 5 columns, 1 text column (chr) and 4 number columns (dbl). readr guessed the types from the data. Read the message. A number column that comes back as chr means that one value in it is not a number.
  • saveRDS() and readRDS() save and read one R object in R’s own format, an .rds file. The file keeps everything: types, labels and dates. identical() says that the data frame came back without a change.

tempdir() is a temporary folder that R deletes when the session ends. Use it for files that you do not need to keep. To keep a file, give a folder of your project, as Part II does with saveRDS(adsl, "data/adsl.rds").

SDTM stores dates as text in the ISO 8601 form: 2014-03-15, or with a time, 2014-03-15T09:30. A date can also be partial: only the year and the month, or only the year. To compute with a date, you first make it a real date.

programs/02_6_dates.R
# Chapter 2: from ISO 8601 text to a real date. A partial date cannot become a full date by itself
library(dplyr)
library(lubridate)
tibble(DTC = c("2014-03-15", "2014-03-15T09:30", "2014-03", "2014", NA)) |>
mutate(DT = as.Date(DTC, format = "%Y-%m-%d"))
#> # A tibble: 5 x 2
#> DTC DT
#> <chr> <date>
#> 1 2014-03-15 2014-03-15
#> 2 2014-03-15T09:30 2014-03-15
#> 3 2014-03 NA
#> 4 2014 NA
#> 5 <NA> NA
as.numeric(as.Date("2014-01-02"))
#> [1] 16072
as.Date("2014-01-11") - as.Date("2014-01-09")
#> Time difference of 2 days
ae <- pharmaversesdtm::ae |>
select(USUBJID, AESTDTC) |>
mutate(ASTDT = ymd(AESTDTC))
#> Warning:
#> There was 1 warning in `mutate()`.
#> i In argument: `ASTDT = ymd(AESTDTC)`.
#> Caused by warning:
#> ! 19 failed to parse.
ae |>
filter(nchar(AESTDTC) < 10, !is.na(ASTDT)) |>
print(n = 3)
#> # A tibble: 7 x 3
#> USUBJID AESTDTC ASTDT
#> <chr> <chr> <date>
#> 1 01-701-1148 2012-02 2020-12-02
#> 2 01-701-1192 2010-06 2020-10-06
#> 3 01-701-1192 2010-06 2020-10-06
#> # i 4 more rows
  • as.Date(x, format = "%Y-%m-%d") reads a year, a month and a day. It ignores the time after the date. A partial date and NA give NA, without a message.
  • R stores a date as the number of days since 1 January 1970. For 2014-01-02, the first dose of subject 01-701-1015, that number is 16072. So you can subtract two dates: from 9 to 11 January is 2 days.
  • ymd() from lubridate also reads dates. It guesses the layout of each value, and that is a trap with partial dates.

Look at the last part of the program. ymd() reads AESTDTC, the start dates of all adverse events. The warning says that 19 values failed to parse. That sounds like the whole story, but it is not. The filter then finds 7 partial dates that ymd() did not fail on: it guessed another layout. 2012-02 became 2 December 2020. These 7 wrong dates have no warning of their own.

The statistical analysis plan (SAP) decides how a partial date becomes a full date. To impute a value is to fill it by a rule. The direction depends on what the date is for:

  • Start dates (first dose, the onset of an adverse event) are imputed early: the first day of the month or of the year.
  • End dates (last dose, end of study) are imputed late: the last day of the month or of the year.
  • A flag records what was imputed: D when the day was imputed, M when the month and the day were imputed.
programs/02_7_impute.R
# Chapter 2: impute a partial date early (start dates) or late (end dates), with a flag
library(dplyr)
library(lubridate)
tibble(DTC = c("2014-03-15", "2014-03-15T09:30", "2014-03", "2014", NA)) |>
mutate(
DTC10 = substr(DTC, 1, 10),
DTF = case_when(
nchar(DTC10) == 7 ~ "D",
nchar(DTC10) == 4 ~ "M"
),
ASTDT = ymd(DTC10, truncated = 2),
AENDT = case_when(
DTF == "M" ~ make_date(year(ASTDT), 12, 31),
DTF == "D" ~ rollforward(ASTDT),
.default = ASTDT
)
) |>
select(DTC, DTF, ASTDT, AENDT)
#> # A tibble: 5 x 4
#> DTC DTF ASTDT AENDT
#> <chr> <chr> <date> <date>
#> 1 2014-03-15 <NA> 2014-03-15 2014-03-15
#> 2 2014-03-15T09:30 <NA> 2014-03-15 2014-03-15
#> 3 2014-03 D 2014-03-01 2014-03-31
#> 4 2014 M 2014-01-01 2014-12-31
#> 5 <NA> <NA> NA NA
  • substr(DTC, 1, 10) keeps the first 10 characters: the date without the time. nchar() counts the characters: 10 for a full date, 7 for a year and month, 4 for a year.
  • case_when() without .default gives NA to every other row. So a full date gets no flag.
  • ymd(x, truncated = 2) allows the last two parts (month and day) to be missing, and fills each with 1. That is the early imputation: 2014-03 becomes 2014-03-01 and 2014 becomes 2014-01-01.
  • rollforward() moves a date to the last day of its month: 2014-03-31. make_date(year, 12, 31) makes 31 December of the year.

Apply caps and floors only to imputed dates (when the flag is not NA). Never change a date that was collected.

Exercise 2-3. Add a cap to program 02_7: an imputed end date may not be later than 2014-03-31. Add a row "2014-06-30" to the data. Does the cap change it?

programs/02_8_factors.R
# Chapter 2: a factor gives groups their order (the job of a SAS format)
library(dplyr)
dm_rand <- pharmaversesdtm::dm |>
filter(ARMCD != "Scrnfail") |>
mutate(AGEGR = case_when(
AGE < 65 ~ "<65",
AGE <= 80 ~ "65-80",
AGE > 80 ~ ">80"
))
dm_rand |> count(AGEGR)
#> # A tibble: 3 x 2
#> AGEGR n
#> <chr> <int>
#> 1 65-80 144
#> 2 <65 33
#> 3 >80 77
dm_rand |>
mutate(AGEGR = factor(AGEGR, levels = c("<65", "65-80", ">80"))) |>
count(AGEGR) |>
mutate(PCT = round(100 * n / sum(n), 1))
#> # A tibble: 3 x 3
#> AGEGR n PCT
#> <fct> <int> <dbl>
#> 1 <65 33 13
#> 2 65-80 144 56.7
#> 3 >80 77 30.3

The first count is right, but in a wrong order: 65-80, <65, >80. R sorts text by its characters, and the digit 6 comes before < and >. A table must show the age groups from the youngest up.

A factor is a column with a fixed list of allowed values, the levels, in an order that you choose. factor(AGEGR, levels = c("<65", "65-80", ">80")) makes one. Now count(), arrange() and tables use the order of the levels: 33 subjects (13%) are under 65, 144 (56.7%) are 65 to 80 and 77 (30.3%) are older than 80. The column type is now <fct>.

A factor keeps a level with no rows. So a table can show a row with 0 for a group that has no subjects.

Exercise 2-4. Count the adverse events of pharmaversesdtm::ae by AESEV, in the order MILD, MODERATE, SEVERE.

2.9 Read messages, warnings and errors like a QC programmer

Section titled “2.9 Read messages, warnings and errors like a QC programmer”

R tells you about a problem in three ways:

Kind What R does Example in this chapter
Message prints a note. The program goes on. read_csv() reports the column types (02_5)
Warning prints Warning. The program goes on, but a result may be wrong. 19 failed to parse. (02_6)
Error prints Error. The program stops. object 'WEIGHT' not found (below)
programs/02_9_error.R
# Chapter 2: a column that does not exist stops the program with an error
library(dplyr)
pharmaversesdtm::dm |>
filter(USUBJID == "01-701-1015") |>
mutate(BMI = WEIGHT / (HEIGHT / 100)^2)
#> Error in `mutate()`:
#> i In argument: `BMI = WEIGHT/(HEIGHT/100)^2`.
#> Caused by error:
#> ! object 'WEIGHT' not found

DM has no WEIGHT column, so mutate() stops. The error message has three parts: the function (mutate()), the argument that failed (BMI = ...) and the cause. Read them in that order.

An error is easy: you cannot miss it. A warning is the dangerous one. Program 02_6 ran to the end, and it made 7 wrong dates. A program for a study runs without unexplained warnings. When you see a warning, find the rows that caused it, as 02_6 does with filter(). Then fix the cause, or write down why the warning is expected.

  • A vector holds values of one type. A data frame is named vectors of the same length.
  • filter(), select(), mutate() and arrange() take a data frame and give a new one. The pipe |> joins them.
  • R works on whole columns. There is no loop over the rows and no PDV.
  • NA in a comparison gives NA, and filter() drops those rows. Test for NA first. Data from SAS files has "" for missing text.
  • tribble() writes data in the program. readr reads and writes CSV files. saveRDS() keeps an R object.
  • as.Date(x, format = "%Y-%m-%d") makes a date from complete ISO 8601 text. Do not let ymd() guess partial dates. Impute them by the SAP’s rule, with a flag.
  • A factor gives groups their order.
  • An error stops the program. A warning does not, and that is why you read every one.

Exercises 2-1 to 2-4 are in the sections above. One more:

Exercise 2-5. Convert AESTDTC of all adverse events with as.Date(x, format = "%Y-%m-%d"). How many dates stay missing? Then impute them early with ymd(x, truncated = 2) and set the flag. How many have the flag D, and how many M?

The answers are in Answers, Part I.

Found an error or have a question? Write to datastep.studio@gmail.com· Every program runs in DataStep Studio for R, the R extension for VS Code and Positron.

Data: the CDISC pilot study (cdiscpilot01), from the R packagespharmaversesdtm andpharmaverseadam (Apache License 2.0).

R is a project of the R Foundation. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. This book is not affiliated with or endorsed by SAS Institute Inc. or the R Foundation. CDISC, SDTM and ADaM are trademarks of the Clinical Data Interchange Standards Consortium.

© 2026 DataStep Labs. All rights reserved.