-1

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 :-(

Progman
  • 16,827
  • 6
  • 33
  • 48
Peter
  • 118
  • 9

1 Answers1

1

You can try :

result <- rbind(Summary, cbind(YrFin = 'Perc', Summary[2, -1]/Summary[1, -1]))
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Thanks - that worked well :-) ... had to adjust the number of significant figures afterwards but still a quick and tidy solution. – Peter Nov 30 '20 at 00:09