4.3 Merging and reshaping data (tidyr)

Data manipulation is crucial in R programming. Merging and reshaping data with tidyr helps organize information efficiently. These techniques allow you to combine datasets, transform between wide and long formats, and handle complex structures like nested data frames.

By mastering these skills, you'll be able to wrangle data into the right shape for analysis. This topic builds on previous data manipulation concepts, enabling you to tackle more complex data challenges and prepare your data for visualization and modeling.

Tidy Data Concepts

Principles and Benefits

Top images from around the web for Principles and Benefits

• 

  dplyr: revolution of R syntax · Douglas C. Wu View original

  Is this image relevant?

• 

  Tidy data for efficiency, reproducibility, and collaboration View original

  Is this image relevant?

• 

  Tidy data for efficiency, reproducibility, and collaboration View original

  Is this image relevant?

• 

  dplyr: revolution of R syntax · Douglas C. Wu View original

  Is this image relevant?

• 

  Tidy data for efficiency, reproducibility, and collaboration View original

  Is this image relevant?

1 of 3

Top images from around the web for Principles and Benefits

• 

  dplyr: revolution of R syntax · Douglas C. Wu View original

  Is this image relevant?

• 

  Tidy data for efficiency, reproducibility, and collaboration View original

  Is this image relevant?

• 

  Tidy data for efficiency, reproducibility, and collaboration View original

  Is this image relevant?

• 

  dplyr: revolution of R syntax · Douglas C. Wu View original

  Is this image relevant?

• 

  Tidy data for efficiency, reproducibility, and collaboration View original

  Is this image relevant?

1 of 3

• Tidy data is a standard way of organizing data where each variable is a column, each observation is a row, and each type of observational unit is a table
• Tidy data principles enable efficient data manipulation, modeling, and visualization
  • Consistent structure facilitates applying functions and operations across datasets
  • Reduces errors and makes code more readable and maintainable
• Benefits of tidy data include:
  • Easier to filter, group, and summarize data using tools like

    dplyr

  • Enables use of ggplot2 for creating informative visualizations
  • Allows for more effective use of R's vectorized operations

Reshaping Data with tidyr

• Data reshaping involves transforming the structure of a dataset without changing its content, often to meet tidy data principles or to facilitate specific analyses
• Common reshaping operations include:
  • Gathering (wide to long): Converts multiple columns into key-value pairs, creating a longer dataset with fewer columns
  • Spreading (long to wide): Converts key-value pairs into multiple columns, creating a wider dataset with more columns
• The tidyr package in R provides a set of functions for tidying and reshaping data:

  • [pivot_longer](https://www.fiveableKeyTerm:pivot_longer)()

    : Gathers multiple columns into key-value pairs

  • [pivot_wider](https://www.fiveableKeyTerm:pivot_wider)()

    : Spreads key-value pairs across multiple columns

  • separate()

    : Splits a single column into multiple columns based on a delimiter

  • unite()

    : Combines multiple columns into a single column by concatenating their values
• Example of gathering data with

  pivot_longer()

  :

data_wide <- data.frame(
  name = c("John", "Alice"),
  age = c(30, 25),
  height = c(180, 165)
)

data_long <- data_wide %>%
  pivot_longer(cols = c(age, height), names_to = "variable", values_to = "value")

Merging and Joining Data

Combining Data Sets

• Merging and joining data sets involve combining two or more data frames based on common variables or keys
• Useful when data is stored in separate tables or when additional information needs to be incorporated into an existing dataset
• Common scenarios for merging and joining data:
  • Combining customer information with transaction data
  • Joining employee records with department information
  • Integrating data from multiple sources or databases
• Before merging or joining, ensure that the common variables have the same data type and format across the input data frames
  • Data cleaning and preprocessing may be necessary to standardize variable names, handle missing values, or convert data types

dplyr Functions for Merging and Joining

• The

  dplyr

  package in R provides functions for merging and joining data:

  • inner_join()

    : Returns only the rows that have matching values in both data frames

  • left_join()

    : Returns all rows from the left data frame and the matched rows from the right data frame

  • right_join()

    : Returns all rows from the right data frame and the matched rows from the left data frame

  • full_join()

    : Returns all rows from both data frames, filling in missing values with

    NA

    where necessary
• The

  by

  argument in the

  dplyr

  join functions is used to specify the common variables
  • Can merge or join based on a single common variable or multiple variables
  • If the variable names are the same in both data frames,

    by = "variable_name"

    can be used
  • If the variable names are different,

    by = c("left_variable" = "right_variable")

    can be used
• Example of joining data frames with

  left_join()

  :

customers <- data.frame(
  customer_id = c(1, 2, 3),
  name = c("John", "Alice", "Bob")
)

orders <- data.frame(
  order_id = c(101, 102, 103),
  customer_id = c(1, 2, 4),
  amount = c(100, 200, 150)
)

customer_orders <- left_join(customers, orders, by = "customer_id")

Reshaping Data: Wide vs Long

Wide and Long Data Formats

• Wide and long data formats are two common ways of structuring data
• Wide format:
  • Each variable is represented as a separate column
  • Observations are spread across multiple columns
  • Example: A dataset with columns for different time points (e.g.,

    time1

    ,

    time2

    ,

    time3

    )
• Long format:
  • Each observation is represented as a separate row with a key-value pair for each variable
  • Variables are stacked in a single column, with a corresponding value column
  • Example: A dataset with columns for

    variable

    (e.g.,

    time

    ) and

    value

    (e.g.,

    measurement

    )
• Choice between wide and long format depends on the analysis requirements and the structure of the data

Converting Between Wide and Long Formats

• The

  pivot_longer()

  function in tidyr is used to convert data from wide to long format
  • Gathers multiple columns into key-value pairs
  • Creates a new row for each unique combination of the non-gathered columns

  • names_to

    argument specifies the name of the new key column

  • values_to

    argument specifies the name of the new value column
• The

  pivot_wider()

  function in tidyr is used to convert data from long to wide format
  • Spreads key-value pairs across multiple columns
  • Creates a new column for each unique value of the key variable

  • [names_from](https://www.fiveableKeyTerm:names_from)

    argument specifies the column to use as the new column names

  • [values_from](https://www.fiveableKeyTerm:values_from)

    argument specifies the column to use as the values for the new columns
• The

  names_sep

  argument in

  pivot_longer()

  can be used to split column names into multiple variables
  • Useful when column names contain multiple components (e.g.,

    variable_year

    )
• Example of converting data from wide to long format with

  pivot_longer()

  :

data_wide <- data.frame(
  name = c("John", "Alice"),
  score_math = c(85, 92),
  score_science = c(78, 88)
)

data_long <- data_wide %>%
  pivot_longer(cols = starts_with("score"), names_to = "subject", values_to = "score")

Complex Data Structures

Nested Data Frames and List-Columns

• Complex data structures, such as nested data frames or list-columns, can arise when working with hierarchical or semi-structured data (e.g., JSON, XML)
• Nested data frames contain one or more columns that are themselves data frames
• List-columns are columns that contain lists, where each element of the list can be a vector, data frame, or another complex object
• Nested data frames and list-columns allow for storing and manipulating data with varying levels of granularity or multiple related observations per row
• Example of a nested data frame:

nested_data <- data.frame(
  group = c("A", "B"),
  data = list(
    data.frame(x = 1:3, y = c(10, 20, 30)),
    data.frame(x = 4:5, y = c(40, 50))
  )
)

Handling Nested Data with tidyr and purrr

• The

  unnest()

  function in tidyr is used to expand a nested data frame by converting each element of a list-column into a separate row
  • Flattens the hierarchical structure and allows for easier manipulation and analysis of the data
  • Can unnest multiple list-columns simultaneously
• The

  nest()

  function in tidyr is used to create a nested data frame by grouping rows based on one or more variables and collapsing the remaining columns into a list-column
  • Useful for organizing complex data or performing operations on subsets of the data
  • Can nest multiple columns into a single list-column
• The

  map()

  family of functions from the

  purrr

  package can be used to apply functions to each element of a list-column
  • Allows for flexible and efficient manipulation of nested data structures

  • map()

    applies a function to each element and returns a list

  • map_df()

    applies a function to each element and returns a data frame

  • map_int()

    ,

    map_dbl()

    ,

    map_chr()

    apply a function and return vectors of specific types
• Example of unnesting a nested data frame with

  unnest()

  :

unnested_data <- nested_data %>%
  unnest(cols = data)

• Handling complex data structures often involves a combination of data reshaping, unnesting, and mapping operations to extract, transform, and analyze the relevant information
• The tidyr and purrr packages in R provide a powerful toolset for working with nested data frames, list-columns, and other complex data structures