aggregate a column that contains numbers and strings

Question

I have a dataframe in my R script that looks something like this:

ID      B     C
1539   Blue   8
1539   Blue   4
1539   Red    9
1539   Red    13
1539   Yellow NCAA
3574   Green  RA
3574   Green  RA
3574   Green  RA
3574   Red    1 
3574   Red    1

How do I aggregate column C and transform the dataset such that it looks like this :

  ID     Blue    Yellow    Green   Red    
  1539   12      NCAA       -      22
  3574   -       -          3RA    2

I basically want to sum the numbers and count if the value is a factor in column C. Any help is much appreciated.

David Arenburg · Accepted Answer · 2016-04-11T21:47:04.303

A column in R can't contain different classes at the same time, so you don't really have numbers in column C- they are either characters or factors. We will probably need to make sure they are characters so we could apply type.convert on them. Also, factors have an integer storage mode- so it could get confusing. Afterwards, we could create an helper function per your requirements and convert the data to a wide format. Here's an example using data.table

library(data.table)
setDT(data1)[, C := as.character(C)] # Make sure it's a character column

# Define the function
f <- function(x) if(is.numeric(x <- type.convert(x))) {
                      as.character(sum(x)) 
                    } else paste0(length(x), x) 

# Rehsape
dcast(data1, ID ~ B, value.var = "C", f)
#      ID Blue Green Red Yellow
# 1: 1539   12     0  22  1NCAA
# 2: 3574    0   3RA   2      0

thats it I was doing a lot of things , subseting and filtering the strings , running aggregate on only numeric values etc etc..very complicated. This is very efficient. Thanks David. — Diggy Detroit, Apr 11 '16 at 21:38

score 0 · Answer 2 · edited May 23 '17 at 12:24

You do this with tidyr and dplyr using essentially two steps:

spread( tidyr) to create new columns with the names in B and values in C
group_by your ID column and then summarize_each of the remaining columns (dplyr) with a custom summary function to deal with either character or numeric columns

Here's the full solution, which requires a few more commands for bookkeeping.

1. spread. One gotcha with spread is that all rows have to have a unique id in some column(see here)

library(dplyr)
library(tidyr)

## spread with 'convert' will convert types automatically

spread_data <- dat %>%
  mutate(row= 1:nrow(dat)) %>% ## uniquely id rows
  spread(B, C, convert = TRUE) %>%
  select(-row)

## converting the data.frame to a tbl_df
## lets us easily see the cols are different types
tbl_df(spread_data)    
##Source: local data frame [10 x 5]
##
##      ID  Blue Green   Red Yellow
##   (int) (int) (chr) (int)  (chr)
##   1   1539     8    NA    NA     NA
##   2   1539     4    NA    NA     NA
##   3   1539    NA    NA     9     NA
##   4   1539    NA    NA    13     NA
##   5   1539    NA    NA    NA   NCAA
##   6   3574    NA    RA    NA     NA
##   7   3574    NA    RA    NA     NA
##   8   3574    NA    RA    NA     NA
##   9   3574    NA    NA     1     NA
##   10  3574    NA    NA     1     NA

2. group and summarize. first, we need to write a function that will handle either type of columns

summarizer <- function(x) {
  if (is.numeric(x)) {
    sum(x, na.rm = TRUE)
  } else {
    # assume x is a character
    if (all(is.na(x)))
      return("-")
    x[is.na(x)] <- ""
    x <- unique(x)
    paste0(x, collapse="")
  }
}

## summarize each applies summarizer to the columns that aren't being used
## for grouping (so, not ID in this case)
spread_data %>% group_by(ID) %>%
  summarize_each(funs(summarizer))
##Source: local data frame [2 x 5]
##
##     ID  Blue Green   Red Yellow
##  (int) (int) (chr) (int)  (chr)
##  1  1539    12     -    22   NCAA
##  2  3574     0    RA     2      -

aggregate a column that contains numbers and strings

2 Answers2