[SOLVED] R combine rows in data frame based on grouping variable when there are both numeric and character columns to be collapsed

R combine rows in data frame based on grouping variable when there are both numeric and character columns to be collapsed

I was provided with some interesting data that I need to aggregate/collapse/combine based on an ID field but different columns of the data frame contain both numeric and character vectors. The aggregate() function doesn't appear to work with character vectors. I did come up with a working loop solution but it isn't elegant. I was wondering if there are functions in any known packages that would do this quicker/easier. All the better if the solution is "R base" or in the data.table realm but I am interested in anything.

Here is an example set of the data:

    id winter wintercolor spring springcolor summer summercolor fall fallcolor
 1:  a      3        blue     NA        <NA>     NA        <NA>   NA      <NA>
 2:  a     NA        <NA>      4      purple     NA        <NA>   NA      <NA>
 3:  a     NA        <NA>     NA        <NA>      2       brown   NA      <NA>
 4:  a     NA        <NA>     NA        <NA>     NA        <NA>    5       red
 5:  b     NA        <NA>      4      yellow     NA        <NA>   NA      <NA>
 6:  b     NA        <NA>     NA        <NA>     NA        <NA>    2      blue
 7:  c      4         red     NA        <NA>     NA        <NA>   NA      <NA>
 8:  c     NA        <NA>     NA        <NA>      6      orange   NA      <NA>
 9:  c     NA        <NA>     NA        <NA>     NA        <NA>    3      blue
10:  d      5         red     NA        <NA>     NA        <NA>   NA      <NA>
11:  d     NA        <NA>     NA        <NA>      1        blue   NA      <NA>

Here is what I want to get to:

   id winter wintercolor spring springcolor summer summercolor fall fallcolor
1:  a      3        blue      4      purple      2       brown    5       red
2:  b     NA        <NA>      4      yellow     NA        <NA>    2      blue
3:  c      4         red     NA        <NA>      6      orange    3      blue
4:  d      5         red     NA        <NA>      1        blue   NA      <NA>

Here is working code (with the sample data set above) I developed to get the job done but hoping could be improved:

library(data.table)
id <- c('a','a','a','a','b','b','c','c','c','d','d')
winter <- c(3,NA,NA,NA,NA,NA,4,NA,NA,5,NA)
wintercolor <- c('blue',NA,NA,NA,NA,NA,'red',NA,NA,'red',NA)
spring <- c(NA,4,NA,NA,4,NA,NA,NA,NA,NA,NA)
springcolor <- c(NA,'purple',NA,NA,'yellow',NA,NA,NA,NA,NA,NA)
summer <- c(NA,NA,2,NA,NA,NA,NA,6,NA,NA,1)
summercolor <- c(NA,NA,'brown',NA,NA,NA,NA,'orange',NA,NA,'blue')
fall <- c(NA,NA,NA,5,NA,2,NA,NA,3,NA,NA)
fallcolor <- c(NA,NA,NA,'red',NA,'blue',NA,NA,'blue',NA,NA)

sampledat <- data.table(id,winter,wintercolor,spring,springcolor,summer,summercolor,fall,fallcolor)
setkey(sampledat,id)

colsets <- c('winter','spring','summer','fall')
nnn <- length(colsets)
holder <- vector('list',nnn)
for(i in 1:nnn){
#i=1
    loopcols <- c('id',names(sampledat)[grepl(colsets[i],names(sampledat))])
    loopdat <- sampledat[,loopcols, with=F]
    col2 <- as.name(loopcols[2])
    col3 <- as.name(loopcols[3])
    holder[[i]] <- loopdat[!is.na(eval(col2)) & !is.na(eval(col3))]
}

combodat <- Reduce(function(x, y) merge(x, y, by='id', all=T), holder)
combodat

Solution

One approach using dplyr:

df <- setDF(sampledat)

modified_max <- function(x){
  out <- suppressWarnings(max(x,na.rm=T) )
  out <- ifelse(is.infinite(out),NA_real_,out)
  out
}

df %>%
  group_by(id) %>%
  summarise_all(modified_max) 

  id    winter wintercolor spring springcolor summer summercolor  fall fallcolor
  <chr>  <dbl> <chr>        <dbl> <chr>        <dbl> <chr>       <dbl> <chr>    
1 a          3 blue             4 purple           2 brown           5 red      
2 b         NA NA               4 yellow          NA NA              2 blue     
3 c          4 red             NA NA               6 orange          3 blue     
4 d          5 red             NA NA               1 blue           NA NA