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

Question

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.

Please use `dput` to present your data. – Dominik Żabiński Sep 25 '22 at 08:37 — Dominik Żabiński, Sep 25 '22 at 08:37

jay.sf · Answer 1 · 2022-09-25T10:07:50.587

0

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))

edited Sep 25 '22 at 10:07

answered Sep 25 '22 at 09:28

jay.sf

60,139
8
53
110

Actually, I have the repetition of "Region, A,B,C" like 50 times in the whole dataset. Is there any other way I can do this? – Subhojit Chakraborty Sep 25 '22 at 10:01
@SubhojitChakraborty You don't seem willing to count the number of repetitions-I wouldn't be either :) Please see update for non-hardcoded version. – jay.sf Sep 25 '22 at 10:09

score 0 · Answer 2 · answered Sep 25 '22 at 12:36

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}

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

2 Answers2