pivot_longer (wide to long) in R Programming - Step-by-Step Execution

Concept Flow - pivot_longer (wide to long)

Start with wide data

↓

Select columns to pivot

↓

Gather columns into key-value pairs

↓

Create new 'name' column for keys

↓

Create new 'value' column for values

↓

Return long format data

↓

End

pivot_longer takes wide data and turns selected columns into two columns: one for the old column names (keys) and one for their values, making data longer.

Execution Sample

R Programming

library(tidyr)
data <- data.frame(
  id = 1:2,
  A = c(10, 20),
  B = c(30, 40)
)
pivot_longer(data, cols = c(A, B), names_to = "variable", values_to = "value")

This code converts columns A and B into a longer format with 'variable' and 'value' columns.

Execution Table

Step	Action	Input Data	Output Data	Notes
1	Start with wide data	id | A | B 1 |10|30 2 |20|40	Same as input	Initial wide data with columns id, A, B
2	Select columns to pivot	Columns A and B	Columns A and B selected	Choosing which columns to make longer
3	Gather columns into key-value pairs	A=10, B=30 (row 1)	variable='A', value=10 variable='B', value=30	Row 1 split into two rows
4	Gather columns into key-value pairs	A=20, B=40 (row 2)	variable='A', value=20 variable='B', value=40	Row 2 split into two rows
5	Add id column to each new row	id=1 or 2	id=1, variable='A', value=10 id=1, variable='B', value=30 id=2, variable='A', value=20 id=2, variable='B', value=40	id repeats for each new row
6	Return long format data	All rows combined	id | variable | value 1 | A | 10 1 | B | 30 2 | A | 20 2 | B | 40	Final long format data

💡 All selected columns pivoted; data transformed from wide to long format.

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
data	data.frame with columns id, A, B	id column unchanged; A and B split into key-value pairs	id repeated for each key-value pair	long format data with id, variable, value columns

Key Moments - 3 Insights

Why do the number of rows increase after pivot_longer?

Because each selected column in the wide data becomes its own row in the long data, as shown in execution_table steps 3 and 4 where one row splits into multiple rows.

What do the 'names_to' and 'values_to' arguments do?

'names_to' creates the new column holding the old column names (keys), and 'values_to' creates the new column holding the values, as seen in the output data in execution_table step 6.

Does the original id column get changed or removed?

No, the id column stays the same and repeats for each new row created, as shown in execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What happens to the row with id=1?

A It stays as one row with combined values.

B It is removed from the data.

C It splits into two rows with variable 'A' and 'B'.

D It duplicates the id column.

💡 Hint

Check the 'Output Data' column in step 3 of execution_table.

At which step does the id column get repeated for each new row?

A Step 4

B Step 5

C Step 2

D Step 6

💡 Hint

Look at the 'Notes' column describing id repetition in execution_table.

If you only pivot column A, how would the final number of rows change compared to pivoting A and B?

A It would be half the number of rows.

B It would be double the number of rows.

C It would be the same number of rows.

D It would be zero rows.

💡 Hint

Refer to variable_tracker and how each selected column creates new rows.

Concept Snapshot

pivot_longer(data, cols, names_to, values_to)
- Converts wide data to long format
- 'cols' selects columns to pivot
- 'names_to' names new key column
- 'values_to' names new value column
- Rows increase as columns become rows

Full Transcript

pivot_longer is a function in R that changes data from wide format to long format. It takes selected columns and turns them into two new columns: one for the old column names and one for their values. This makes the data longer by increasing the number of rows. The original columns not selected stay the same and repeat for each new row. This process helps when you want to analyze or plot data more easily. The execution steps show how each row splits into multiple rows with new key-value pairs, and the id column repeats accordingly.

Practice

1. What does the pivot_longer() function do in R's tidyverse?

easy

A. It filters rows based on a condition.

B. It creates new columns by splitting existing ones.

C. It transforms wide data into long data by stacking columns into rows.

D. It sorts data by a specific column.

Solution

1. Step 1: Understand the purpose of pivot_longer()

   The function is designed to reshape data from wide format (many columns) to long format (fewer columns, more rows).

2. Step 2: Identify the correct description

   Among the options, only It transforms wide data into long data by stacking columns into rows. correctly describes stacking columns into rows, which is what pivot_longer does.

3. Final Answer:

   It transforms wide data into long data by stacking columns into rows. -> Option C

4. Quick Check:

   pivot_longer = wide to long [OK]

Hint: pivot_longer stacks columns into rows to tidy data [OK]

Common Mistakes:

• Confusing pivot_longer with filtering or sorting functions
• Thinking it splits columns instead of stacking
• Assuming it creates new columns instead of reshaping

2. Which of the following is the correct syntax to pivot columns Jan and Feb into longer format with new columns named Month and Sales?

easy

A. pivot_longer(data, cols = c(Jan, Feb), names_to = "Month", values_to = "Sales")

B. pivot_longer(data, columns = c(Jan, Feb), names = "Month", values = "Sales")

C. pivot_longer(data, cols = c("Jan", "Feb"), names_to = Month, values_to = Sales)

D. pivot_longer(data, cols = Jan, Feb, names_to = "Month", values_to = "Sales")

Solution

1. Step 1: Check argument names and syntax

   The correct argument names are cols, names_to, and values_to. Columns should be passed as symbols or strings inside c().

2. Step 2: Validate each option

   pivot_longer(data, cols = c(Jan, Feb), names_to = "Month", values_to = "Sales") uses correct argument names and syntax with symbols inside c(). pivot_longer(data, columns = c(Jan, Feb), names = "Month", values = "Sales") uses wrong argument names. pivot_longer(data, cols = c("Jan", "Feb"), names_to = Month, values_to = Sales) misses quotes around new column names. pivot_longer(data, cols = Jan, Feb, names_to = "Month", values_to = "Sales") incorrectly separates columns without c().

3. Final Answer:

   pivot_longer(data, cols = c(Jan, Feb), names_to = "Month", values_to = "Sales") -> Option A

4. Quick Check:

   Correct args: cols, names_to, values_to [OK]

Hint: Use cols = c(), names_to and values_to with quotes [OK]

Common Mistakes:

• Using wrong argument names like columns or names
• Forgetting quotes around new column names
• Not wrapping columns in c()

3. Given this data frame in R:

df <- data.frame(ID = 1:2, Jan = c(10, 20), Feb = c(15, 25))

What is the output of:

pivot_longer(df, cols = c(Jan, Feb), names_to = "Month", values_to = "Value")

medium

A. A data frame with columns ID, Month, and Value, where Jan and Feb values are stacked under Value.

B. A data frame with columns ID, Jan, Feb unchanged.

C. An error because cols argument is missing quotes.

D. A data frame with columns ID, Month, Value but values are all NA.

Solution

1. Step 1: Understand pivot_longer behavior on df

   The function stacks the Jan and Feb columns into two rows per ID, creating Month and Value columns.

2. Step 2: Predict the output structure

   The output will have 4 rows (2 IDs x 2 months) and columns: ID, Month (Jan or Feb), and Value (corresponding numbers).

3. Final Answer:

   A data frame with columns ID, Month, and Value, where Jan and Feb values are stacked under Value. -> Option A

4. Quick Check:

   pivot_longer stacks columns into rows [OK]

Hint: pivot_longer stacks selected columns into new key-value columns [OK]

Common Mistakes:

• Expecting original wide format unchanged
• Thinking it causes errors without quotes on cols
• Assuming values become NA

4. What is wrong with this code?

pivot_longer(df, cols = Jan, Feb, names_to = "Month", values_to = "Value")

medium

A. The function pivot_longer does not exist.

B. The cols argument should be a vector using c(), not separate arguments.

C. The values_to argument is missing.

D. The names_to argument should not be in quotes.

Solution

1. Step 1: Check the cols argument format

   The cols argument must be a vector of columns, written as cols = c(Jan, Feb). Passing them separately is invalid syntax.

2. Step 2: Verify other arguments

   names_to and values_to are correctly used with quotes. The function exists in the tidyverse package.

3. Final Answer:

   The cols argument should be a vector using c(), not separate arguments. -> Option B

4. Quick Check:

   cols = c(...) required [OK]

Hint: Wrap multiple columns in c() inside cols argument [OK]

Common Mistakes:

• Passing multiple columns without c()
• Removing quotes from names_to or values_to
• Assuming pivot_longer is not available

5. You have a data frame:

df <- data.frame(ID = 1:2, Q1_2019 = c(5, 6), Q2_2019 = c(7, 8), Q1_2020 = c(9, 10))

You want to pivot it longer so that the quarter and year are separate columns named Quarter and Year, and the values go into Score. Which code achieves this?

hard

A. pivot_longer(df, cols = c(Q1_2019, Q2_2019, Q1_2020), names_to = c("Quarter", "Year"), values_to = "Score")

B. pivot_longer(df, cols = c(Q1_2019, Q2_2019, Q1_2020), names_to = "Quarter_Year", values_to = "Score")

C. pivot_longer(df, cols = c(Q1_2019, Q2_2019, Q1_2020), names_to = c("Year", "Quarter"), names_sep = "_", values_to = "Score")

D. pivot_longer(df, cols = starts_with("Q"), names_to = c("Quarter", "Year"), names_sep = "_", values_to = "Score")

Solution

1. Step 1: Identify columns to pivot

   All columns starting with "Q" represent quarters and years, so starts_with("Q") selects them.

2. Step 2: Separate names into Quarter and Year

   Using names_to = c("Quarter", "Year") with names_sep = "_" splits the column names at underscore into two new columns.

3. Step 3: Assign values to Score

   The values_to = "Score" argument names the new value column.

4. Final Answer:

   pivot_longer(df, cols = starts_with("Q"), names_to = c("Quarter", "Year"), names_sep = "_", values_to = "Score") -> Option D

5. Quick Check:

   Use names_sep to split names_to vector [OK]

Hint: Use names_to vector and names_sep to split column names [OK]

Common Mistakes:

• Not using names_sep to split names into multiple columns
• Swapping order of Quarter and Year in names_to
• Not selecting all relevant columns with starts_with