Using R how do I pivot the columns into rows to get the required structure in df3 from df2, while extracting new information from the previous dataframe:
df2<- structure(list(A=c("A_1_01", "A_1_01", "A_1_01"), B=c("A", "A", "A"), C=c("1", "1", "1"), D=c("inside", "eating", "sleeping"), "1"=c("1","1","0"), "2"=c("1","0","0"), "3"=c("0","0","1"), "4"=c("0","1","1"), "1_Location"=c("I","I", "I"), "2_Location"=c("I","I", "I"), "3_Location"=c("O","O", "O"), "4_Location"=c("O","O", "O")), class= "data.frame", row.names = c(NA,-3L))
df3<- structure(list(H=c("1","2","3","4","1","2","3","4","1","2","3","4"),
A=c("A_1_01", "A_1_01", "A_1_01","A_1_01", "A_1_01",
"A_1_01","A_1_01", "A_1_01", "A_1_01","A_1_01",
"A_1_01", "A_1_01"),
B=c("A", "A", "A","A", "A", "A","A", "A", "A","A", "A", "A"),
C=c("1", "1", "1","1", "1", "1","1", "1", "1","1", "1", "1"),
D=c("inside","inside","inside","inside",
"eating","eating","eating","eating",
"sleeping","sleeping","sleeping","sleeping"),
Value=c(1,1,0,0,1,0,0,1,0,0,1,1),
Location=c("I","I","O","O","I","I","O","O","I","I","O","O")),
class= "data.frame", row.names = c(NA,-12L))
Thank you
Try with pivot_longer after adding a suffix '_Value' to the digit only column names
library(stringr)
library(tidyr)
library(dplyr)
df2 %>%
rename_with(~ str_c(.x, "_Value"), matches("^\\d+$")) %>%
pivot_longer(cols = contains("_"), names_to = c("H", ".value"),
names_pattern = "(\\d+)_(.*)")
-output
# A tibble: 12 × 7
A B C D H Value Location
<chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 A_1_01 A 1 inside 1 1 I
2 A_1_01 A 1 inside 2 1 I
3 A_1_01 A 1 inside 3 0 O
4 A_1_01 A 1 inside 4 0 O
5 A_1_01 A 1 eating 1 1 I
6 A_1_01 A 1 eating 2 0 I
7 A_1_01 A 1 eating 3 0 O
8 A_1_01 A 1 eating 4 1 O
9 A_1_01 A 1 sleeping 1 0 I
10 A_1_01 A 1 sleeping 2 0 I
11 A_1_01 A 1 sleeping 3 1 O
12 A_1_01 A 1 sleeping 4 1 O