Xtykutl

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

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

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))

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