Reshape data with header rows

Question

I have data in this form, where a separate column is included in a separate row at the start of group of detail records

data <- data.frame(detail = c("", "Sue", "Fred", "", "Jayne", "Harold", "Jim"),
                   group = c("Group 1", "", "", "Group 2", "", "", ""))

I want to reshape it as follows. Can this be done with pivot_wider?

Detail	Group No
Sue	Group 1
Fred	Group 1
Jayne	Group 2
Harold	Group 2
Jim	Group 2

score 0 · Answer 1 · answered Apr 07 '22 at 12:54

here is a data.table approach

library(data.table)
# set to data.table fotmat
setDT(data)
# set "" as NA
data[data == ""] <- NA
# fill NA's 
data[, group := zoo::na.locf(group)]
# subset
data[!is.na(detail), ]
# detail   group
# 1:    Sue Group 1
# 2:   Fred Group 1
# 3:  Jayne Group 2
# 4: Harold Group 2
# 5:    Jim Group 2

Reshape data with header rows

1 Answers1