R: Get row values based on column name in R-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Here's a Tidyverse approach

df %>% pivot_longer(2:6, names_to = "Q", values_to = "no") %>% 
  mutate(new_Q = paste0("Q4_", no)) %>% 
  filter(new_Q != "Q4_NA") %>% 
  select(ID, new_Q, no ) %>% 
  pivot_wider(id_cols = ID, names_from = new_Q, values_from = no) %>% 
  select(ID, Q4_1, Q4_2, Q4_3, Q4_4, Q4_5 )

# A tibble: 4 x 6
     ID  Q4_1  Q4_2  Q4_3  Q4_4  Q4_5
  <int> <int> <int> <int> <int> <int>
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