R Language Reshaping Data With Gather And Spread Complete Guide

Step 1: Install and Load Required Packages

First, ensure that you have the tidyverse package installed, which contains tidyr and other useful packages like dplyr for data manipulation.

# Install tidyverse if you haven't already
install.packages("tidyverse")

# Load tidyverse for gather() and spread() functions
library(tidyverse)

Step 2: Create Sample Data

Let's create some sample data to work with. We will use tibble, a more user-friendly data structure than base data.frames.

# Create a tibble with sample data
sales_data <- tibble(
  product = c('apple', 'banana', 'cherry'),
  Q1    = c(100, 150, 90),
  Q2    = c(110, 160, 100),
  Q3    = c(120, 170, 110),
  Q4    = c(130, 180, 120)
)

# Print the sample data
print(sales_data)

You should see the following output:

# A tibble: 3 × 5
  product   Q1    Q2    Q3    Q4
  <chr>    <dbl> <dbl> <dbl> <dbl>
1 apple      100   110   120   130
2 banana     150   160   170   180
3 cherry      90   100   110   120

Step 3: Use the gather Function

The gather function is used to collapse columns into key-value pairs. This is often necessary to convert wide data (variables as columns) into long data (variables as rows).

In our example, we want to transform the sales data from wide format to long format where each quarter has its own row with key and value columns.

# Gather columns Q1-Q4 into key-value pairs
long_format_sales <- gather(sales_data, key = quarter, value = sales, Q1:Q4)

# Print the reshaped data
print(long_format_sales)

You should get:

# A tibble: 12 × 3
   product  quarter sales
   <chr>    <chr>   <dbl>
 1 apple    Q1        100
 2 banana   Q1        150
 3 cherry   Q1         90
 4 apple    Q2        110
 5 banana   Q2        160
 6 cherry   Q2        100
 7 apple    Q3        120
 8 banana   Q3        170
 9 cherry   Q3        110
10 apple    Q4        130
11 banana   Q4        180
12 cherry   Q4        120

Step 4: Use the spread Function

The spread function is used to spread key-value pairs into separate columns. This is the inverse operation of gather. We can use it to transform the long format data back into the original wide format.

# Spread the long format data back to wide format
wide_format_sales <- spread(long_format_sales, key = quarter, value = sales)

# Print the reshaped data
print(wide_format_sales)

You should get the same original data:

# A tibble: 3 × 5
  product   Q1    Q2    Q3    Q4
  <chr>    <dbl> <dbl> <dbl> <dbl>
1 apple      100   110   120   130
2 banana     150   160   170   180
3 cherry      90   100   110   120

Additional Example: Handling Missing Values

Let's add some complexity by introducing missing values in the dataset and see how gather and spread handle them.

Create a tibble with missing values:

# Create a tibble with missing values
sales_data_with_missing <- tibble(
  product = c('apple', 'banana', 'cherry'),
  Q1    = c(100, 150, NA),
  Q2    = c(110, NULL, 100),
  Q3    = c(NA, 170, 110),
  Q4    = c(130, 180, NA)
)

# Print the tibble with missing values
print(sales_data_with_missing)

You should see something like this:

# A tibble: 3 × 5
  product    Q1    Q2    Q3    Q4
  <chr>    <dbl> <lgl> <dbl> <dbl>
1 apple      100 NA       NA   130
2 banana     150 NA      170   180
3 cherry      NA NULL     110    NA

Gather the tibble with missing values:

# Gather columns Q1-Q4, including handling missing values
long_format_sales_missing <- gather(sales_data_with_missing, key = quarter, value = sales, Q1:Q4)

# Print the gathered data
print(long_format_sales_missing)

You should see:

# A tibble: 12 × 3
   product quarter sales
   <chr>   <chr>   <dbl>
 1 apple   Q1        100
 2 banana  Q1        150
 3 cherry  Q1         NA
 4 apple   Q2         NA
 5 banana  Q2         NA
 6 cherry  Q2        100
 7 apple   Q3         NA
 8 banana  Q3        170
 9 cherry  Q3        110
10 apple   Q4        130
11 banana  Q4        180
12 cherry  Q4         NA

Step 5: Handling Missing Values During Spread

Let's spread the data back to wide format with the missing values still included:

# Spread the long format data back to wide format, handling missing values
wide_format_sales_missing <- spread(long_format_sales_missing, key = quarter, value = sales)

# Print the spread data
print(wide_format_sales_missing)

You should get:

# A tibble: 3 × 5
  product   Q1    Q2    Q3    Q4
  <chr>    <dbl> <dbl> <dbl> <dbl>
1 apple      100    NA    NA   130
2 banana     150    NA   170   180
3 cherry      NA    100   110    NA

This shows that both gather and spread maintain missing values during the transformation process.

Recap

Here’s what each function does:

• gather – Transforms columns into two new columns: one for keys (column names) and one for values.
• spread – Transforms two columns (key-value pairs) back into multiple columns based on the key.

By using these functions, you can easily reshape your dataset to meet the requirements of analysis and visualization tasks.

Final Challenge