How to merge rows within a dataset in R, based on a shared value

user_19678183 0
Viewed 46

I'm fairly new to R so apologies for perhaps the naivete of the question, but:
I would like to vertically merge multiple rows in a dataframe based on a duplicate value in one specific column.

My current dataframe looks something like:

Context Question ID Rating
1 Q1Item1 Public
1 Q2Item1 Quite a bit
1 Q3Item1 Somewhat
2 Q1Item2 Very
2 Q2Item2 Very
2 Q3Item2 Very

It does this all the way until Context# 1051.

I would like to end up with a df that looks something like:

Context Question ID1 Rating1 Question ID2 Rating2 Question ID3 Rating3
1 Q1Item1 Public Q2Item1 Quite a bit Q3Item1 Somewhat
2 Q1Item2 Very Q2Item2 Very Q3Item2 Very

This merge would result in multiple new columns and no duplicates in the "Context" row.
How should I go about completing this?

Thank you very much!!

tidyr dplyr duplicates r merge
1 Answers

This is a simple pivot_wider problem with some cleanup before and after, just run the steps individually if you are struggling to understand. Let me know if this works.

library(tidyverse)

read.table(text = "Context|Question ID | Rating
1 | Q1Item1 | Public
1 | Q2Item1 | Quite_a_bit
1 | Q3Item1 | Somewhat
2 | Q1Item2 | Very
2 | Q2Item2 | Very
2 | Q3Item2 | Very", sep = "|", header = TRUE) %>%
  mutate(across(everything(), str_trim),
         Context = as.integer(Context)) %>%
  as_tibble() %>%
  group_by(Context) %>%
  mutate(var = str_c("Question_ID", row_number())) %>%
  ungroup() %>%
  pivot_wider(names_from = var, values_from = c(Question.ID, Rating)) %>%
  rename_with(~str_remove(., "^Question.ID_|_Question_")) %>%
  relocate(Context, ends_with(as.character(seq(3))))
  
# # A tibble: 2 × 7
#   Context Question_ID1 RatingID1 Question_ID2 RatingID2   Question_ID3 RatingID3
#     <int> <chr>        <chr>     <chr>        <chr>       <chr>        <chr>    
# 1       1 Q1Item1      Public    Q2Item1      Quite_a_bit Q3Item1      Somewhat 
# 2       2 Q1Item2      Very      Q2Item2      Very        Q3Item2      Very
user_195126110
Related