Cumulative sum for more values in one entry

Question

Let's say I have this dataframe (the "number" variable is also from character-type in the original dataframe):

df <- data.frame(
  id = c(1,2,2,1,2),
  number = c(30.6, "50.2/15.5", "45/58.4", 80, "57/6"))
df$number <- as.character(df$number)

Now I want to add another column with the cumulative sum for each ID and I did this with df %>% mutate(csum = ave(number, id, FUN=cumsum)), which works for the single numbers, but of course not for the numbers separated with "/". How can I solve this problem?

The final dataframe should be like this:

df2 <- data.frame(
  id = c(1,2,2,1,2),
  number = c(30.6, "50.2/15.5", "45/58.4", 80, "57/6"),
  csum = c(30.6, "50.2/15.5", "95.2/73.9", 110.6, "152.2/79.9"))
df2

TarJae · Accepted Answer · 2021-12-24T10:19:54.600

2

One way could be:

group with group_by
separate in column a and b
mutate across a and b and apply cumsum
unite from tidyr package using na.rm=TRUE argument

library(dplyr)
library(tidyr)

df %>% 
  group_by(id) %>% 
  separate(number, c("a", "b"), sep="/", remove = FALSE, convert = TRUE) %>% 
  mutate(across(c(a,b), ~cumsum(.))) %>% 
  unite(csum, c(a,b), sep = '/', na.rm = TRUE)

     id number    csum      
  <dbl> <chr>     <chr>     
1     1 30.6      30.6      
2     2 50.2/15.5 50.2/15.5 
3     2 45/58.4   95.2/73.9 
4     1 80        110.6     
5     2 57/6      152.2/79.9

edited Dec 24 '21 at 10:19

answered Dec 24 '21 at 09:30

TarJae

72,363
6
19
66

Thank you!! That's a great way to solve this issue. – Katharina Dec 24 '21 at 10:36

score 2 · Answer 2 · answered Dec 24 '21 at 10:38

You could use the extremely fast matrixStats::colCumsums.

res <- do.call(rbind, by(df, df$id, \(x) {
  cs <- matrixStats::colCumsums(do.call(rbind, strsplit(x$number, '/')) |> 
                                  type.convert(as.is=TRUE))
  r <- do.call(paste, c(as.list(as.data.frame(cs)), sep='/'))
  data.frame(id=x$id, number=x$number, csum=r)
}))

Note: R version 4.1.2 (2021-11-01).

Gives:

res
#     id    number       csum
# 1.1  1      30.6       30.6
# 1.2  1        80      110.6
# 2.1  2 50.2/15.5  50.2/15.5
# 2.2  2   45/58.4  95.2/73.9
# 2.3  2      57/6 152.2/79.9

Thank you! That's also a very good way to do it! – Katharina Dec 24 '21 at 11:59 — Katharina, Dec 24 '21 at 11:59

score 2 · Answer 3 · answered Dec 24 '21 at 17:05

We could use base R - read the 'number' column with read.table to split it to two columns, create a logical vector where there are no NAs, subset the 'd1' rows, loop over the columns, get the cumulative sum (cumsum) and paste, then assign it to a new column 'csum' in the original dataset

d1 <- read.table(text = df$number, sep = "/", fill = TRUE, header = FALSE)
i1 <- !rowSums(is.na(d1)) > 0
df$csum[i1] <-  do.call(paste, c(lapply(d1[i1,], cumsum), sep = "/"))

-output

> df
  id    number       csum
1  1      30.6       <NA>
2  2 50.2/15.5  50.2/15.5
3  2   45/58.4  95.2/73.9
4  1        80       <NA>
5  2      57/6 152.2/79.9

Cumulative sum for more values in one entry

3 Answers3