Search code examples
rlapplyrbind

bind a percentage row to data frame

I am working with some code which summarises a large dataset (600000 rows) - so difficult to create a repex ... I am happy with the output of the following code -

 Summary <- dcast(NamedTestsLong %>%
                                  group_by(YrFin, Lab) %>%
                                  summarise(count = n(), .groups = "drop"),
                                  YrFin ~ Lab, value.var = "count")

Which gives me exactly what I want ..

Data summary

but now I wan to add a % variation to a new row at the bottom(2019/20 being the numerator and 2018/19 the denominator) ... I have done this in the past when the variance % is added as a new column with mutate but can't figure out how to append a row ... I have tried various things like ...

Summary <- dcast(NamedTestsLong %>%
                                  group_by(YrFin, Lab) %>%
                                  summarise(count = n(), .groups = "drop"),
                                  YrFin ~ Lab, value.var = "count") %>% 
                                  rbind(as.character(summarise_all(., funs(if(is.numeric(.)) round(diff(.)*100 -100, 2 else "Variance"))))

and various subsetting ideas like ...

 Summary <- dcast(NamedTestsLong %>%
                                  group_by(YrFin, Lab) %>%
                                  summarise(count = n(), .groups = "drop"),
                                  YrFin ~ Lab, value.var = "count") %>% 
                                  rbind(as.character(summarise_all(., funs(if(is.numeric(.)) round([2,]/[1,]*100 -100, 2 else "Variance"))))

but I'm now lost ...I think that lappy is probably the right direction but it's not something I have a good grasp of :-(

Solution

  • You can try :

    result <- rbind(Summary, cbind(YrFin = 'Perc', Summary[2, -1]/Summary[1, -1]))