Complicated issue with pasting 5 columns groups in R

Question

As an extension to the problem I posted in the following link,

Issue with pasting 5 columns groups in R

I have a table like below now.

Table:

V29  V30  V31  V32  V33  V34 V35 V36 V37 V38 
044  N    006  E    011  044 N   006 E   012 
045  N    007  E    028  NA  NA  NA  NA  NA

I want to obtain the following table, each row paste in 5 column groups. But, remove NA column when pasting.

Output:

    V29  V30  V31  V32  V33  V34 V35 V36 V37 V38   output
    044  N    006  E    011  044 N   006 E   012   044N006E011-044N006E012
    045  N    007  E    028  NA  NA  NA  NA  NA    045N007E028

Highly appreciate any help.

Thanks.

Use `dput` so that someone can use your data more easily. Check out https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — Ben G, Dec 07 '18 at 18:22
I think the easiest thing to do is combine all of them with the NA's and then extract them out after . . . but I'd have to see the data — Ben G, Dec 07 '18 at 18:32

Brigadeiro · Answer 1 · 2018-12-07T18:29:53.047

Assuming all of your columns are of character type, that there is always at least one value in the first 5 columns for each row, and assuming you have called your data.frame "df" the following should should do the trick:

df$output <- rep(NA, nrow(df))
for(i in 1:nrow(df)){
  if(!all(df[i, 6:10] == "")){
    df$output[i] <- paste0(paste0(df[i, 1:5], collapse = ""), "-",
                           paste0(df[i, 6:10], collapse = ""))
  } else {
    df$output[i] <- paste0(df[i, 1:5], collapse = "")
  }
}

score 0 · Answer 2 · answered Dec 07 '18 at 18:55

df = read.table(
  text = "
  V29  V30  V31  V32  V33  V34 V35 V36 V37 V38
  044    N  006    E  011  044   N 006   E 012
  045    N  007    E  028   NA  NA  NA  NA  NA",
  header = T
)

grp = c(rep(1, 5), rep(2, 5))
apply(df, 1, function(x)
{
  z=sapply(split(unlist(x), grp), function(y)
  {
    if (is.na(y[1]))
      ""
    else
      paste0(trimws(y), collapse = "")
  })

  if(z[2]=="")
    paste0(z, collapse = "")
  else
    paste0(z, collapse = "-")
})

Complicated issue with pasting 5 columns groups in R

2 Answers2