Cumulative average for a specific value

user_675742 0
Viewed 1020

I want to calculate a cumulative average only if the value is >0. If I have a vector:

v <- c(1, 3, 0, 3, 2, 0)

The average would be 9/6 = 1.5, however I only want to take an average when the value is >0, so in this case it would be 9/4 = 2.25. But that average is over the entire set. I want to do this averaging as the data set builds and accumulates. So, initially it would be:

1+3/2, 1+3+0/2, 1+3+0+3/3, 1+3+0+3+2/4, 1+3+0+3+2+0/4

My data set is 9,000 rows and its growing. I can get cumsum to work and calculate the cumulative sum, but not the cumulative average for a "success".

cumulative-sum r
2 Answers

You can solve this by dividing the cumulative sum of v with the cumulative sum of the logical vector v > 0:

v1 <- cumsum(v)/cumsum(v>0)

which gives:

> v1
[1] 1.000000 2.000000 2.000000 2.333333 2.250000 2.250000

When you want to omit the first value:

v2 <- (cumsum(v)/cumsum(v>0))[-1]

which gives:

> v2
[1] 2.000000 2.000000 2.333333 2.250000 2.250000

The latter is equal to the desired outcome as specified in the question:

> ref <- c((1+3)/2, (1+3+0)/2, (1+3+0+3)/3, (1+3+0+3+2)/4, (1+3+0+3+2+0)/4)
> identical(v2, ref)
[1] TRUE

An implementation in a dataset:

# create an example dataset
df <- data.frame(rn = letters[seq_along(v)], v)

# calculate the 'succes-cummulative-mean'
library(dplyr)
df %>% 
  mutate(succes_cum_mean = cumsum(v)/cumsum(v>0))

which gives:

  rn v succes_cum_mean
1  a 1        1.000000
2  b 3        2.000000
3  c 0        2.000000
4  d 3        2.333333
5  e 2        2.250000
6  f 0        2.250000
user_22044100

dplyr package have a cummean function. If you want only for > 0, select value in v for v>0:

v <- c(1, 3, 0, 3, 2, 0)

dplyr::cummean(v[v>0])
#> [1] 1.000000 2.000000 2.333333 2.250000

if you want the results with repetition, you could play with index and a helper function from zoo.

# Create a vector container for the result (here with NA values)
v_res <- v[NA]
# Fill cumsum where you want to calculate it (here v>0)
v_res[v>0] <- dplyr::cummean(v[v>0])
# Fill the gap with previous value
zoo::na.locf(v_res)
#> [1] 1.000000 2.000000 2.000000 2.333333 2.250000 2.250000

it works with negative value in v too

v <- c(1, 3, 0, 3, -5, 2, 0, -6)
v_res <- v[NA]
v_res[v>0] <- dplyr::cummean(v[v>0])
zoo::na.locf(v_res)
#> [1] 1.000000 2.000000 2.000000 2.333333 2.333333 2.250000 2.250000 2.250000

You could use tidyverse too. This solution could be useful if your data is in a data.frame.

library(dplyr, warn.conflicts = F)
library(tidyr)

data <- data_frame(v = c(1, 3, 0, 3, 2, 0)) %>%
  tibble::rowid_to_column() 
res <- data %>%
  filter(v > 0) %>%
  mutate(cummean = cummean(v)) %>%
  right_join(data, by = c("rowid", "v")) %>%
  fill(cummean)
res
#> # A tibble: 6 x 3
#>   rowid     v  cummean
#>   <int> <dbl>    <dbl>
#> 1     1     1 1.000000
#> 2     2     3 2.000000
#> 3     3     0 2.000000
#> 4     4     3 2.333333
#> 5     5     2 2.250000
#> 6     6     0 2.250000
pull(res, cummean)[-1]
#> [1] 2.000000 2.000000 2.333333 2.250000 2.250000
user_34365350
Related