Basic Data Wrangling

Author

Jeremy Selva

In this session, we will go through the basic of data wrangling using the Obstetrics and Periodontal Therapy study dataset from the R package medicaldata.

Learning Goals

  • Identify the overall data structure.
  • Using R to view the data by subsetting.
  • Introducing the R native pipe operator |>.
  • Using R package dplyr to perfrom simple data wrangling.

The Obstetrics and Periodontal Therapy study was a multi-center randomized controlled trial to determine if nonsurgical periodontal treatment intervention can reduce the risk of preterm birth (< 37 weeks) and low birth weight (< 2500 g) outcomes.

823 participants enrolled at 4 centers underwent stratified randomization, resulting in 413 women assigned to the treatment group and 410 to control. Treatment group consist of women treated before 21 weeks gestation (treatment) while the control consist of women treated after delivery (control). All participants were 13-16 weeks pregnant at time of randomization (baseline/visit 1).

Overall data structure

The data can be accessed from medicaldata::opt. However, we can see that using the print function alone has its limitations when the data has a large number of rows and columns.


We can start by identifying how many rows and columns medicaldata::opt has. This can be done using nrow() and ncol() respectively. The dim() function prints the numbers of rows and columns simultaneously.

 

Next, we can also try to identify the column names using colnames. Details of each column name can be found in here.


Subsetting the data

Sometimes we just want to take a peak on specific areas of the dataset instead of printing everything on the screen. Here are some additional functions that can be useful.

 

The head() function can be used to view the first n rows of a dataset, where users can choose n any value greater than 0. By default, n is 6.

The tail() function can be used to view the last n rows of a dataset, where users can choose n any value greater than 0. By default, n is 6.

The $ can be used to access one column in a data frame. It uses the syntax.

dataframe$column_name

Observe that the output is a vector.

You can select elements from a data frame with the help of square brackets [].

In this example, we pick the first column.

In this example, we pick the Age column.

Multiple columns can also be selected as well.

In this example, we pick the first, as well as the fifth to ninth column.

In this example, we pick the columns PID, Group and Age.

By using a comma, you can indicate what to select from the rows and the columns respectively.

In this example, we pick rows 3, 5 to 10 and columns 1, 4 to 9.

Column names can also be used

If columns are not specified, it means selects all columns.

The same applies for rows.

The |> operator

Earlier we have run the following code:

class(head(medicaldata::opt[1], n = 5))

This line of code involves applying two functions to medicaldata::opt[1].

In reality, a data analysis pipeline may involve a serial chain of multiple functions.

This may give rises to deeply nested parentheses (have to read from right to left) like

processed_data <- task_3(task_2(task_1(data)))

or numerous temporary variables (have to read in a z-shaped manner)

data_after_task_1 <- task_1(data)
data_after_task_2 <- task_2(data_after_task_1)
processed_data <- task_3(data_after_task_2)

which can be hard to read and debug, especially when it gives an error on a new dataset.

To increase code readability and inspired by the pipe operator | in Linux, R 4.1.0 introduced a native pipe operator |> such that the above code can be rewritten as a form that can be read from left to right

processed_data <- data |> task_1() |> task_2() |> task_3()

or top to bottom like a pipeline.

processed_data <- data |> 
  task_1() |> 
  task_2() |> 
  task_3()

Coming back to

class(head(medicaldata::opt[1], n = 5))

it can be rewritten as the follows:

Using dplyr

Data wrangling involves transforming the data into a format that is more appropriate and valuable for further downstream analysis. This process may involve creating new columns, choosing specific rows or columns that satisfy a specific condition.

The R package dplyr provides some functions that makes this process easier. Below are some of the commonly used functions.

 

dplyr::select is used to choose columns based on a condition.

If an external vector needs to used, you can use dplyr::all_of or dplyr::any_of

This is useful if we need to ensure that certain columns exists when the dataset has been updated to a new version.

The documentation indicates many helper function that you can use that can hep you pick very specific columns.

Below is an example using dplyr::starts_with and dplyr::ends_with.

dplyr::filter is used to choose rows based on a condition. Please note that dplyr::filter treats NA as FALSE and drops them.

To choose a column in the dataset using the pipe |> operator, we can use the syntax .data[[{column_name}]] to subset a column from a data frame using a character string or column_name as a variable is stored as a string like column_name <- "Hisp"

Below is an example to pick participants who are Hispanic or has a value of Yes in the Hisp column.

Here is an example with the use of comparison operators to keep participants between 20 and 60 who have a non-missing value in the BMI column.

dplyr::filter_out is used to drop rows based on a condition. Please note that dplyr::filter_out treats NA as FALSE but keeps them.

We can take a look at the difference using the column BMI which has missing values to see its usefulness.

Suppose we want to only remove participants with BMI above 25, if we use dplyr::filter like below.

We most likely forget to take those with missing BMI into consideration and remove them by mistake… until someone else tells you that the number remaining should be 406 using the R code

nrow(medicaldata::opt) - sum(medicaldata::opt$BMI > 25, na.rm = TRUE)

One workaround is add an additional condition to keep participants with missing BMI.


But with dplyr::filter_out, things are much more clearer.


dplyr::case_when allows one for a given vector to check for multiple conditions and assign different values for each case. Below is the common use syntax.

dplyr::case_when(
  condition_1 ~ result_1,    # Give result_1 if condition_1 is TRUE
  condition_2 ~ result_2,    # Give result_2 if condition_2 is TRUE & condition_1 is FALSE
  condition_3 ~ result_3,    # Give result_3 if condition_3 is TRUE & both condition_1 and condition_2 are FALSE
  .default = default_result  # Give default_result if none of the conditions above are TRUE
)

Let us look at the first ten BMI data as an example.


dplyr::mutate can be used to add new columns or update existing ones.

The below example shows new columns age_in_ten_years and bmi_group are created.