How can I make this repetitive column data into proper data?-CodePudding

How do I change this table from wide to long format in R? I have tried a few different libraries but can't seem to get it.

  Region  A  B  C Region  A  B  C Region  A  B  C
1     SS 45 98 89     KK 87 NA 25     MM 88 NA 59
2     SS 22 NA 77     KK 45 78 88     MM NA 72 35

I have tried using reshape(df5, idvar=" " , timevar='' , direction="") but can't fix my data.

I want this data as-

  Region  A  B  C
1     SS 45 98 89
2     SS 22 NA 77
3     KK 87 NA 25
4     KK 45 78 88
5     MM 88 NA 59
6     MM NA 72 35

It would be great if I get the answer using reshape.

CodePudding user response：

This isn't actually a reshape problem. What you want is to rbind every n 1 columns, where n is the length of the repeating names.

n <- length(s <- grep('Region', names(dat)))

do.call(rbind, Map(\(x, y) dat[x   (n   1)*y], list(1:(s[2] - 1)), 0:(n - 1)))
#   Region  A  B  C
# 1     SS 45 98 89
# 2     SS 22 NA 77
# 3     KK 87 NA 25
# 4     KK 45 78 88
# 5     MM 88 NA 59
# 6     MM NA 72 35

Data:

dat <- structure(list(Region = c("SS", "SS"), A = c(45L, 22L), B = c(98L, 
NA), C = c(89L, 77L), Region = c("KK", "KK"), A = c(87L, 45L), 
    B = c(NA, 78L), C = c(25L, 88L), Region = c("MM", "MM"), 
    A = c(88L, NA), B = c(NA, 72L), C = c(59L, 35L)), class = "data.frame", row.names = c(NA, 
-2L))

CodePudding user response：

Another base R option like this:

df5 <- read.table(text = "  Region  A  B  C Region  A  B  C Region  A  B  C
1     SS 45 98 89     KK 87 NA 25     MM 88 NA 59
2     SS 22 NA 77     KK 45 78 88     MM NA 72 35", header = TRUE, check.names = FALSE)

as.data.frame(lapply(split(lapply(df5, as.character), names(df5)), unlist))
#>      A    B  C Region
#> 1   45   98 89     SS
#> 2   22 <NA> 77     SS
#> 3   87 <NA> 25     KK
#> 4   45   78 88     KK
#> 5   88 <NA> 59     MM
#> 6 <NA>   72 35     MM

^{Created on 2022-09-25 with reprex v2.0.2}