Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

r: replace NA with 0 when calculating cumulative values

Tags:

I have written a piece of code to calculate cumulative values of a variable of interest by decile. My data look like so:

 library(dplyr)
actual=c(1,1,1,0,0,1,1,0,0,1)
prob=c(0.8,0.8,0.2,0.1,0.6,0.7,0.8,0.9,0.7,0.9)
n=1:10
for_chart=data.frame(actual,prob,n)
for_chart=for_chart[with(for_chart, order(-prob)),]
for_chart$decile <- cut(n, breaks = quantile(n, probs = seq(0, 1, 0.1)), 
                        labels = 1:10, include.lowest = TRUE)

This is the code that builds the table and calculates cumulative values.

    out <- for_chart%>%
  group_by(decile)%>%
  summarise(sum=n())%>%
  mutate(cum=cumsum(sum))
out1 <-for_chart%>% 
  filter(actual==1)%>%
  group_by(decile)%>%
  summarise(sum_churn=n())%>%
  mutate(cum_churn=cumsum(sum_churn))
final_out <- left_join(out,out1,by='decile')

"out" gives the cumulative count of n. "out1" provides the cumulative value of the variable of interest, in this case "cum_churn". "final_out" is the final table. When the count of the variable for a specific decile is 0, the code puts an NA. Like so:

    final_out
    decile   sum   cum sum_churn cum_churn
       (fctr) (int) (int)     (int)     (int)
    1       1     1     1        NA        NA
    2       2     1     2         1         1
    3       3     1     3         1         2
    4       4     1     4         1         3
    5       5     1     5         1         4
    6       6     1     6         1         5
    7       7     1     7        NA        NA
    8       8     1     8        NA        NA
    9       9     1     9         1         6
    10     10     1    10        NA        NA

I would like my code to: 1. replace NAs with 0 and 2. include the 0 in the cumulative count

To be clear, the final output should be this:

  decile   sum   cum sum_churn cum_churn
   (fctr) (int) (int)     (int)     (int)
1       1     1     1         0         0
2       2     1     2         1         1
3       3     1     3         1         2
4       4     1     4         1         3
5       5     1     5         1         4
6       6     1     6         1         5
7       7     1     7         0         5
8       8     1     8         0         5
9       9     1     9         1         6
10     10     1    10         0         6
like image 323
La Machine Infernale Avatar asked Jul 29 '16 18:07

La Machine Infernale


1 Answers

We can try

 left_join(out,out1,by='decile') %>%
        mutate_each(funs(replace(., is.na(.), 0)), sum_churn:cum_churn)
like image 57
akrun Avatar answered Sep 28 '22 02:09

akrun