0

I recently got help with calculating net proportions for a table in R, but trying to make a summary of that hasn't worked and as I selected an answer I need to post a new question.

Here is my original data (I call qf):

genre  status  rb  wrb  inn
Fiction  FAILURE  621  66  1347
Fiction  FAILURE  400  46  928
Fiction  FAILURE  238  35  663
Poetry  FAILURE  513  105  1732
Poetry  FAILURE  165  47  393
Poetry  FAILURE  896  193  2350
Love-story  FAILURE  5690  501  8869
Love-story  FAILURE  1284  174  2793
Love-story  FAILURE  7279  715  13852
Love-story  SUCCESS  18150  1734  39635
Poetry  SUCCESS  1988  226  4712
Love-story  SUCCESS  20110  2222  43953
Love-story  SUCCESS  20762  2288  46706
Poetry  SUCCESS  1824  322  3984
Poetry  SUCCESS  1105  148  2751
Adventure  SUCCESS  4675  617  8462
Adventure  SUCCESS  7943  599  17247
Adventure  SUCCESS  7290  601  17774

Thanks to the answers I manage to get it to summarise by genre and success/failure like so(I like to track all transformations hence the multiple dataframes):

qf2 <- qf %>% group_by(genre,status) %>% summarise_all(sum)

qf3 <- ff2 %>%  as.data.frame()

qf4 <- qf3 %>% mutate(rowSum = rowSums(.[,names(qf3)[3:5]])) %>% 
group_by(genre) %>% 
summarise_at(vars(names(qf3)[3:5]),   
           funs(net = .[status == "SUCCESS"]/rowSum[status == "SUCCESS"] - 
                  .[status == "FAILURE"]/rowSum[status == "FAILURE"] )) %>%
as.data.frame()

However what I want to now do is get the overall proportions. But whatever I try it just won't work. I think I'm missing something obvious.

What I want to get is the output of:

Sum-FAILURE  0.329241738  0.036265536  0.634492726
Sum-SUCCESS  0.301794636  0.031519501  0.666685863
Net  -0.027447103  -0.004746035  0.032193137

The calculation I'm trying to create to get this is (for rb):

(Sum(success_rb)/(Sum(success_rb)+Sum(success_wrb)+Sum(Success_inn)) -  (Sum(failure_rb)/(Sum(failure_rb)+Sum(failure_wrb)+Sum(failure_inn))
Nisarg
  • 1,631
  • 6
  • 19
  • 31
JRUK
  • 67
  • 7

1 Answers1

2
qf %>% 
  select(-genre)%>%
  group_by(status) %>% 
  summarise_all(sum)%>%
  {.[-1]/rowSums(.[-1])}%>%
  rbind(.[2,]-.[1,])

          rb          wrb        inn
1   0.3292417  0.036265536 0.63449273
2   0.3017946  0.031519501 0.66668586
21 -0.0274471 -0.004746035 0.03219314

library(data.table)
setDT(qf)[,lapply(.SD,sum),status,.SDcols=3:5][,
             .SD/rowSums(.SD),.SDcols=-1][,rbind(.SD,.SD[2]-.SD[1])]
           rb          wrb        inn
1:  0.3292417  0.036265536 0.63449273
2:  0.3017946  0.031519501 0.66668586
3: -0.0274471 -0.004746035 0.03219314
Onyambu
  • 67,392
  • 3
  • 24
  • 53
  • That's great, thank you @Onyambu. Oddly enough the second solution using (data.table) gives me the negative opposite (so the net that are positive are negative and vice-versa), whereas the first solution works as expected when I plug it into my larger dataframe. – JRUK Jun 29 '18 at 19:57
  • 1
    @JRUK for the data.table solution, you could try `.sd[1]-.sd[2]` – Onyambu Jun 29 '18 at 20:35