How I can sort such data in R-CodePudding

Here is a sample of my data:

 dat<-read.table (text=" Id RA EA  R  B  M  value
    1 2 2 3 4 5 14
    2 2 3 1 2 3 9
    3 1 2 2 2 2 8
    ", header=TRUE)

I want to get the following table:

Id  RA  EA  R   B   M
1   2   2   3   4   5
1   2   14  NA  NA  NA
2   2   3   1   2   3
2   2   9   NA  NA  NA
3   1   2   2   2   2
3   1   8   NA  NA  NA

I want to get blank instead of NA. Here is my effort, but I don't get the NAs, i.e., get the blank:

dat2 <- dat %>%
  gather(Type, EA, M, value) %>%
  mutate_at(vars(-Id, -RA, -EA), funs(ifelse(Type %in% "value", NA, .))) %>%
  select(Id, RA, EA, R, B, M) %>%
  arrange(Id)

CodePudding user response：

No need to gather (or pivot), we can start with:

library(dplyr)
out <- bind_rows(
    select(dat, -value),
    select(dat, Id, RA, EA = value)
  ) %>%
  bind_rows() %>%
  arrange(Id, RA)
out
#   Id RA EA  R  B  M
# 1  1  2  2  3  4  5
# 2  1  2 14 NA NA NA
# 3  2  2  3  1  2  3
# 4  2  2  9 NA NA NA
# 5  3  1  2  2  2  2
# 6  3  1  8 NA NA NA

You say you want the NA values to be instead strings ... realize that you will not be able to do "math"-like things with those columns again. I'll infer that you want to do this for EA through M, so

out %>%
  mutate(across(EA:M, ~ if_else(is.na(.), "", as.character(.))))
#   Id RA EA R B M
# 1  1  2  2 3 4 5
# 2  1  2 14      
# 3  2  2  3 1 2 3
# 4  2  2  9      
# 5  3  1  2 2 2 2
# 6  3  1  8

or similarly (if you're comfortable with coalesceing data:

out %>%
  mutate(across(EA:M, ~ coalesce(as.character(.), "")))