I have two data frames where the data are connected via the "ID" label as follows:
test_frame1<- data.frame(
ID = c("A","B","C"),
ElementType1 = c(1,6,1),
ElementType2= c(4,4,5),
ElementType3 = c('',6,1),
Notes = c("Something random","","Something else random")
)
test_frame2<-data.frame(
ID = c("A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","C","C","C","C","C","C","C","C","C"),
Syllable = c(1,1,2,2,3,3,1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3),
ElementID = c(1,2,1,2,1,2,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3)
)
I want to add a new column to test_frame2 to represent the element type, as described by the middle columns in test_frame1. I want these element types to repeat for each different syllable as follows:
desired_frame<-data.frame(
ID = c("A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","C","C","C","C","C","C","C","C","C"),
Syllable = c(1,1,2,2,3,3,1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3),
ElementID = c(1,2,1,2,1,2,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3),
ElementType= c(1,4,1,4,1,4,6,4,6,6,4,6,6,4,6,1,5,1,1,5,1,1,5,1)
)
I first transposed test_frame1 so that the element types are in the same column, with ID being the column names. Then I tried to separate test_frame2 into a list grouped by ID and Syllable. At this point I hope to merge, cbind, or in some other way paste the values in the element type columns from test_frame1 to the corresponding list item for that ID into test_frame2. It seems like a 'for loop' might help with this, too. I am struggling, though, to find the right function to merge a list and multiple columns based on one part of the list elements title. Any ideas on a simply way to go about this?
You could pivot test_frame1 to long format, and then do a left_join from test_frame2 onto the result:
library(tidyverse)
desired_frame <- test_frame2 %>%
left_join(test_frame1 %>%
mutate(across(starts_with("Element"), as.numeric)) %>%
pivot_longer( starts_with("Element"), names_pattern = "(\\d+)",
names_to = "ElementID", values_to = "ElementType") %>%
mutate(ElementID = as.numeric(ElementID)), by = c("ID", "ElementID")) %>%
select(-Notes)
Resulting in
desired_frame
#> ID Syllable ElementID ElementType
#> 1 A 1 1 1
#> 2 A 1 2 4
#> 3 A 2 1 1
#> 4 A 2 2 4
#> 5 A 3 1 1
#> 6 A 3 2 4
#> 7 B 1 1 6
#> 8 B 1 2 4
#> 9 B 1 3 6
#> 10 B 2 1 6
#> 11 B 2 2 4
#> 12 B 2 3 6
#> 13 B 3 1 6
#> 14 B 3 2 4
#> 15 B 3 3 6
#> 16 C 1 1 1
#> 17 C 1 2 5
#> 18 C 1 3 1
#> 19 C 2 1 1
#> 20 C 2 2 5
#> 21 C 2 3 1
#> 22 C 3 1 1
#> 23 C 3 2 5
#> 24 C 3 3 1
Created on 2023-08-01 with reprex v2.0.2