R: Get row values based on column name in R

Question

I have a dataframe like this:

ID Q4_1 Q4_2 Q4_3 Q4_4 Q4_5
1  4    3    1
2  1    3
3  3    1    2
4  5

Can I know how to rearrange the row values based on the column's prefix to have something like this?

ID Q4_1 Q4_2 Q4_3 Q4_4 Q4_5
1  1         3    4
2  1         3
3  1    2    3
4                      5

Answer 1

Here is a base R approach. Note that I've replaced the empty space in your input with NA.

The core of this solution is to use lapply to go through all rows in the dataframe, then use ifelse to match the row values with the header_suffix I created, which contains the suffix of colnames of the dataframe.

df <- read.table(header = T,
                 text = "ID Q4_1 Q4_2 Q4_3 Q4_4 Q4_5
1  4    3    1  NA  NA
2  1    3 NA  NA  NA
3  3    1    2  NA  NA
4  5  NA  NA  NA  NA")

header_suffix <- gsub("^.+_", "", grep("_", colnames(df), value = T))

header_suffix
#> [1] "1" "2" "3" "4" "5"

df2 <-
  cbind(df[, 1], 
        as.data.frame(
          do.call(
            rbind, 
            lapply(1:nrow(df), function(x) 
              ifelse(header_suffix %in% df[x, 2:ncol(df)], header_suffix, NA))
            )
          )
        )

colnames(df2) <- colnames(df)

df2
#>   ID Q4_1 Q4_2 Q4_3 Q4_4 Q4_5
#> 1  1    1 <NA>    3    4 <NA>
#> 2  2    1 <NA>    3 <NA> <NA>
#> 3  3    1    2    3 <NA> <NA>
#> 4  4 <NA> <NA> <NA> <NA>    5

^{Created on 2022-03-31 by the reprex package (v2.0.1)}