How can I calculate the sum of comma separated in the 2nd column

Question

sm_agg
   Group.1                   x
1     1001                   8
2     1002               16, 8
3     1003                   8
4     1004                  16
5     1005 5.33333333333333, 8
6     1006                   4
7     1007                   4
8     1008                   4
9     1009    5.33333333333333
10    1010 8, 5.33333333333333
11    1011                8, 4
12    1012    5.33333333333333
13    1013 5.33333333333333, 8
14    1014                   8
15    1015    5.33333333333333
16    1016    5.33333333333333

I want to get like this

sm_agg
   Group.1                   x
1     1001                   8
2     1002                   24
3     1003                   8
4     1004                  16
5     1005                  13.3
6     1006                   4
7     1007                   4
8     1008                   4
9     1009    5.33333333333333
10    1010                13.3
11    1011                  12
12    1012    5.33333333333333
13    1013                 13.3
14    1014                   8
15    1015    5.33333333333333
16    1016    5.33333333333333

Answer 1

Try this:

sm_agg$x <- sapply(strsplit(sm_agg$x, "[ ,]+"), function(i) sum(as.numeric(i)))
sm_agg
#    Group.1         x
# 1     1001  8.000000
# 2     1002 24.000000
# 3     1003  8.000000
# 4     1004 16.000000
# 5     1005 13.333333
# 6     1006  4.000000
# 7     1007  4.000000
# 8     1008  4.000000
# 9     1009  5.333333
# 10    1010 13.333333
# 11    1011 12.000000
# 12    1012  5.333333
# 13    1013 13.333333
# 14    1014  8.000000
# 15    1015  5.333333
# 16    1016  5.333333

Explanation:

1. For a single entry, we split it by one or more commas/spaces:

   strsplit(sm_agg$x[2], "[, ]+")
   # [[1]]
   # [1] "16" "8" 
   

2. With that, we want to convert to numbers and add, so

   as.numeric(strsplit(sm_agg$x[2], "[, ]+")[[1]])
   # [1] 16  8
   sum(as.numeric(strsplit(sm_agg$x[2], "[, ]+")[[1]]))
   # [1] 24
   

3. We want to do that for every element, so we instead feed the strsplit output into an sapply anon-function.

---

If your frame has factors instead of strings, then instead use

sapply(strsplit(as.character(sm_agg$x), "[ ,]+"), function(i) sum(as.numeric(i)))

---

Last Edit

I think your data is actually an embedded list. When data contains a list-column, it presents like that (which I find a little frustrating, but still ...).

I'll generate some fake data to demonstrate what I think you actually have:

sm2 <- data.frame(Group.1 = c("1001", "1002", "1003", "1005"))
sm2$x <- list(c(8L), c(16L,8L), c(8L), c(16/3, 8))
sm2
#   Group.1                  x
# 1    1001                  8
# 2    1002              16, 8
# 3    1003                  8
# 4    1005 5.333333, 8.000000

Okay. When we tried strsplit and even as.character, things break and are obviously not number-like:

as.character(sm2$x)
# [1] "8"                      "c(16, 8)"               "8"                     
# [4] "c(5.33333333333333, 8)"

When in fact, all we have to do is just sum them up, because they're already numbers.

sapply(sm2$x, sum)
# [1]  8.00000 24.00000  8.00000 13.33333

If by chance one of the nested things is actually a character:

sm2$y <- list(c("8"), c(16L,8L), c(8L), c(16/3, 8))
sm2
#   Group.1                  x                  y
# 1    1001                  8                  8
# 2    1002              16, 8              16, 8
# 3    1003                  8                  8
# 4    1005 5.333333, 8.000000 5.333333, 8.000000

which will cause our "simple" solution to fail.

sapply(sm2$y, sum)
# Error in FUN(X[[i]], ...) : invalid 'type' (character) of argument

Luckily, we can be a bit over-handed and force strings to numbers, and numbers to numbers:

sapply(sm2$y, function(i) sum(as.numeric(i)))
# [1]  8.00000 24.00000  8.00000 13.33333
sapply(sm2$x, function(i) sum(as.numeric(i)))
# [1]  8.00000 24.00000  8.00000 13.33333

Answer 2

We can use separate_rows to separate comma-separated enteries into different rows and then sum by group.

library(tidyverse)
df %>%
  separate_rows(x, sep = ",") %>%
  group_by(Group.1) %>%
  summarise(x = sum(as.numeric(x)))


#   Group.1  x
#     <dbl> <dbl>
# 1    1001  8   
# 2    1002 24   
# 3    1003  8   
# 4    1004 16   
# 5    1005 13.3 
# 6    1006  4   
# 7    1007  4   
# 8    1008  4   
# 9    1009  5.33
#10    1010 13.3 
#11    1011 12   
#12    1012  5.33
#13    1013 13.3 
#14    1014  8   
#15    1015  5.33
#16    1016  5.33

data

df <- structure(list(Group.1 = c(1001, 1002, 1003, 1004, 1005, 1006, 
 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016), 
    x = structure(c(5L, 7L, 5L, 6L, 10L, 2L, 1L, 1L, 9L, 11L, 
 4L, 8L, 10L, 3L, 8L, 8L), .Label = c("                 4", 
  "                4", "                8", "             8, 4", 
 "       8", "      16", "   16, 8", "  5.33333333333333", 
 " 5.33333333333333", " 5.33333333333333, 8", " 8, 5.33333333333333"
 ), class = "factor")), .Names = c("Group.1", "x"), class = 
 "data.frame", row.names = c(NA, 
 -16L))

Answer 3

using this method i get asnwer sapply(sm2$y, function(i) sum(as.numeric(i)))