pivot_wider (long to wide) in R Programming - Mini Project: Build & Apply

Pivoting Data from Long to Wide Format with pivot_wider

📖 Scenario: Imagine you have survey data collected from different people about their favorite fruits. The data is in a long format, where each row shows one person's choice for one fruit type. You want to reshape this data so that each person has one row, and their favorite fruits are shown in separate columns.

🎯 Goal: You will learn how to use the pivot_wider function from the tidyr package in R to convert data from long format to wide format. This will help you organize data better for analysis or reporting.

📋 What You'll Learn

Create a data frame called survey_data with specific columns and values.

Create a variable called fruit_column to specify the column to spread.

Use pivot_wider to reshape the data from long to wide format.

Print the resulting wide data frame.

💡 Why This Matters

🌍 Real World

Pivoting data from long to wide format is common in data analysis when you want to compare categories side by side, like survey responses or sales data.

💼 Career

Data analysts and scientists often reshape data to prepare it for visualization, modeling, or reporting. Knowing pivot_wider helps you organize data efficiently.

1

Create the initial long format data frame

Create a data frame called survey_data with these exact columns and values:
person with values 'Alice', 'Alice', 'Bob', 'Bob', 'Charlie', 'Charlie';
fruit with values 'Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana';
rating with values 5, 3, 4, 2, 3, 4.

R Programming

# Create the data frame survey_data with columns person, fruit, and rating
# Your code here

Hint

Use data.frame() with vectors for each column named exactly as person, fruit, and rating.

2

Set the column to spread

Create a variable called fruit_column and set it to the string 'fruit' to specify the column that will become new columns in the wide format.

R Programming

survey_data <- data.frame(
  person = c('Alice', 'Alice', 'Bob', 'Bob', 'Charlie', 'Charlie'),
  fruit = c('Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana'),
  rating = c(5, 3, 4, 2, 3, 4)
)

# Create fruit_column variable with value 'fruit'
# Your code here

Hint

Just assign the string 'fruit' to the variable fruit_column.

3

Use pivot_wider to reshape the data

Use the pivot_wider function from the tidyr package to reshape survey_data into a wide format. Use id_cols = person, names_from = fruit_column, and values_from = rating. Save the result in a variable called wide_data. Make sure to load the tidyr package first.

R Programming

survey_data <- data.frame(
  person = c('Alice', 'Alice', 'Bob', 'Bob', 'Charlie', 'Charlie'),
  fruit = c('Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana'),
  rating = c(5, 3, 4, 2, 3, 4)
)

fruit_column <- 'fruit'

# Load tidyr package
# Use pivot_wider with id_cols = person, names_from = fruit_column, values_from = rating
# Save result in wide_data
# Your code here

Hint

Use library(tidyr) to load the package. Then call pivot_wider() with the correct arguments and assign to wide_data.

4

Print the wide format data

Print the variable wide_data to display the reshaped data frame.

R Programming

library(tidyr)

survey_data <- data.frame(
  person = c('Alice', 'Alice', 'Bob', 'Bob', 'Charlie', 'Charlie'),
  fruit = c('Apple', 'Banana', 'Apple', 'Banana', 'Apple', 'Banana'),
  rating = c(5, 3, 4, 2, 3, 4)
)

fruit_column <- 'fruit'

wide_data <- pivot_wider(survey_data, id_cols = person, names_from = fruit_column, values_from = rating)

# Print wide_data
# Your code here

Hint

Use print(wide_data) to show the reshaped data.

Practice

1. What does the pivot_wider function do in R's tidyverse?

easy

A. It filters rows based on a condition.

B. It transforms data from long format to wide format by spreading key-value pairs into columns.

C. It sorts data in ascending order.

D. It merges two data frames by a common column.

Solution

1. Step 1: Understand the purpose of pivot_wider

   The function is designed to reshape data by turning unique values from one column into multiple columns.

2. Step 2: Compare with other data operations

   Filtering, sorting, and merging are different operations; pivot_wider specifically changes data shape from long to wide.

3. Final Answer:

   It transforms data from long format to wide format by spreading key-value pairs into columns. -> Option B

4. Quick Check:

   pivot_wider = long to wide reshape [OK]

Hint: pivot_wider spreads rows into columns for easier comparison [OK]

Common Mistakes:

• Confusing pivot_wider with filtering or sorting functions
• Thinking pivot_wider merges data frames
• Assuming pivot_wider removes rows instead of reshaping

2. Which of the following is the correct syntax to use pivot_wider to spread the column key into multiple columns with values from value?

easy

A. pivot_wider(data, values_from = key, names_from = value)

B. pivot_wider(data, spread = key, gather = value)

C. pivot_wider(data, key = names_from, value = values_from)

D. pivot_wider(data, names_from = key, values_from = value)

Solution

1. Step 1: Recall pivot_wider argument names

   The correct arguments are names_from for the column to spread and values_from for the values to fill.

2. Step 2: Match arguments to columns

   pivot_wider(data, names_from = key, values_from = value) correctly uses names_from = key and values_from = value, matching the expected syntax.

3. Final Answer:

   pivot_wider(data, names_from = key, values_from = value) -> Option D

4. Quick Check:

   names_from = key, values_from = value [OK]

Hint: Use names_from for columns, values_from for data [OK]

Common Mistakes:

• Swapping names_from and values_from arguments
• Using incorrect argument names like spread or gather
• Passing arguments in wrong order or with wrong names

3. Given this data frame in R:

library(tidyr)
data <- data.frame(
  id = c(1,1,2,2),
  variable = c('A', 'B', 'A', 'B'),
  value = c(10, 20, 30, 40)
)
result <- pivot_wider(data, names_from = variable, values_from = value)
print(result)

What will be the output?

medium

A. id A B
1 1 10 20
2 2 30 40

B. id variable value
1 1 A 10
2 1 B 20
3 2 A 30
4 2 B 40

C. id A
1 1 10
2 2 30

D. Error: object 'pivot_wider' not found

Solution

1. Step 1: Understand pivot_wider effect on data

   It converts the 'variable' column values ('A', 'B') into separate columns, filling them with corresponding 'value' entries.

2. Step 2: Check the resulting data frame structure

   Each 'id' has columns 'A' and 'B' with values 10, 20 for id=1 and 30, 40 for id=2.

3. Final Answer:

   id A B 1 1 10 20 2 2 30 40 -> Option A

4. Quick Check:

   pivot_wider spreads variable into columns with values [OK]

Hint: pivot_wider creates columns from variable, fills with value [OK]

Common Mistakes:

• Expecting original long format output
• Confusing column names after pivot
• Not loading tidyr library causing error

4. You run this code but get an error:

library(tidyr)
data <- data.frame(
  id = c(1,1,2,2),
  variable = c('A', 'B', 'A', 'B'),
  value = c(10, 20, 30, 40)
)
result <- pivot_wider(data, names_from = variable)
print(result)

What is the likely cause of the error?

medium

A. The library tidyr is not loaded.

B. The data frame has duplicate rows causing conflict.

C. Missing the values_from argument to specify which column fills new columns.

D. The variable column contains invalid characters.

Solution

1. Step 1: Check pivot_wider required arguments

   pivot_wider needs both names_from and values_from to know which columns to spread and fill.

2. Step 2: Identify missing argument

   The code only provides names_from = variable but misses values_from, causing an error.

3. Final Answer:

   Missing the values_from argument to specify which column fills new columns. -> Option C

4. Quick Check:

   values_from argument is required [OK]

Hint: Always include values_from with names_from in pivot_wider [OK]

Common Mistakes:

• Forgetting values_from argument
• Assuming names_from alone is enough
• Not reading error messages carefully

5. You have this long data frame with sales data:

sales <- data.frame(
  store = c('X', 'X', 'Y', 'Y'),
  product = c('apple', 'banana', 'apple', 'banana'),
  sales_amount = c(100, 150, 200, 250)
)

You want to create a wide table where each product is a column showing sales per store. Which code correctly does this and handles missing values by filling with 0?

hard

A. pivot_wider(sales, names_from = product, values_from = sales_amount, values_fill = 0)

B. pivot_wider(sales, names_from = sales_amount, values_from = product, values_fill = 0)

C. pivot_wider(sales, names_from = product, values_from = sales_amount)

D. pivot_wider(sales, names_from = store, values_from = sales_amount, values_fill = NA)

Solution

1. Step 1: Identify columns to spread and fill

   The products ('apple', 'banana') become new columns, so names_from = product. The sales amounts fill these columns, so values_from = sales_amount.

2. Step 2: Handle missing values

   Use values_fill = 0 to replace any missing sales with zero for completeness.

3. Final Answer:

   pivot_wider(sales, names_from = product, values_from = sales_amount, values_fill = 0) -> Option A

4. Quick Check:

   Use values_fill to replace missing with 0 [OK]

Hint: Add values_fill = 0 to fill missing cells with zero [OK]

Common Mistakes:

• Swapping names_from and values_from
• Not using values_fill causing NA in output
• Using wrong columns for names_from or values_from