CODEWITHSUNDEEP
raggregate
user13267770
user13267770

Reputation:

grouping multiple columns and bringing values into character string

This is an example we can work with:

df <- tibble(y = c("a", "a", "a", "a", "a", "a"), z = c("b", "b", "b", "b", "b", "b"), a = c("aaa", "aaa", "aaa", "bbb", "bbb", "bbb"), 
             b = c(1,2,3,1,2,3), c = c(5,10,15,100,95,90))
df
# A tibble: 6 x 5
  y     z     a         b     c
  <chr> <chr> <chr> <dbl> <dbl>
1 a     b     aaa       1     5
2 a     b     aaa       2    10
3 a     b     aaa       3    15
4 a     b     bbb       1   100
5 a     b     bbb       2    95
6 a     b     bbb       3    90

I want to group the values in column y, z and a and combine column b and c to a single string. The final result should look exactly like this:

# A tibble: 2 x 4
  y      z       a        result
  <chr>  <chr>   <chr>    <chr>
1 a      b       aaa      {"1":5,"2":10,"3":15}
2 a      b       bbb      {"1":100,"2":95,"3":90}

Which i can almost achieve with:

b <- by(df[-1:-3], df$a, function(x) 
  sprintf("{%s}", toString(Reduce(paste0, c(x, "\"", "\":")[c(3, 1, 4, 2)]))))
data.frame(a=unique(df$a), result=do.call(rbind, as.list(b)), row.names=NULL)

    a                    result
1 aaa   {"1":5, "2":10, "3":15}
2 bbb {"1":100, "2":95, "3":90}

This only groups by column a, though and not by all three (y, z and a) columns. I got the hint that i can do fix it with the aggregate function but have a hard time appying it.

Upvotes: 0

Views: 131

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389315

Using dplyr you can make use sprintf/paste0 :

library(dplyr)

df %>%
  group_by(y, z, a) %>%
  summarise(result = paste0('{', toString(sprintf('"%d":"%d"', b, c)), '}')) %>%
  ungroup %>% data.frame()

#  y z   a                          result
#1 a b aaa   {"1":"5", "2":"10", "3":"15"}
#2 a b bbb {"1":"100", "2":"95", "3":"90"}

Using by this can be written as :

do.call(rbind, by(df, list(df$y, df$z, df$a), function(x) 
  cbind(unique(x[1:3]), 
        result = paste0('{', toString(sprintf('"%d":"%d"', x$b, x$c)), '}'))))

Upvotes: 0

Related Questions