1

I'm working alongside a SQL tutorial using queryparser and tidyquery in R. This has been going well until I was asked to do:

SELECT ... SUM(new_vaccinations) OVER (PARTITION BY location) as vaccinations_to_date

Tidyquery reported that it did not support OVER functions so I am trying to replicate the OVER (PARTITION BY...) function with dplyr.

This led me to with_order(order_by =... in dplyr. Now I'm struggling with getting the fun = to allow me to create a cumulative sum column.

library(tidyverse)
library(queryparser)
library(tidyquery)

mydf <- data.frame(date = as.Date(c("2021-06-01", '2021-06-02','2021-06-03','2021-06-04',
                                  '2021-6-01','2021-6-02','2021-6-03','2021-6-04',
                                  '2021-6-01','2021-6-02','2021-6-03','2021-6-04')),
                   location = c('United States','United States','United States','United States',
                                'Canada','Canada','Canada','Canada','Mexico','Mexico','Mexico','Mexico'),
                   new_vaccinations = c(100,98,32,50,99,34,97,53,35,101,97,56))

test <- mydf %>% 
  mutate (total_vax = with_order(order_by = location, fun = cumsum(new_vaccinations), x = desc(location)))

This gives me the error

could not find function "fun"

Am I looking down the wrong rabbit hole when it comes to how to recreating OVER(PARTITION BY...)? If so what is a better option? Or am I missing how to properly use with_order(order_by =...)?

If it is not clear, my goal is to create a new column that keeps a running total of vaccinations for each separate location.

James Wheeler
  • 13
  • 4
  • 1
    Maybe I am missing something, but I got sums of vaccinations by location just using test <- mydf %>% group_by(location) %>% summarise(new_vaccinations) – Kylie R. Nov 10 '21 at 20:48
  • How does your target data look like, @JamesWheeler? – Roman Nov 11 '21 at 00:07
  • To answer Roman and clarify for @Kylie R. this was what I ran to get the answer I wanted thanks to Simon's answer: `test1 <- mydf %>% select(date, location, new_vaccinations) %>% group_by(location) %>% arrange(location, date) %>% mutate(new = cumsum(new_vaccinations)) %>% arrange(location, date) %>% select(date, location, new_vaccinations, new) view(test1)` – James Wheeler Nov 12 '21 at 18:59

1 Answers1

1

The PARTITION BY aspect of SQL can often be done in dplyr using group_by.

And the ORDER BY aspect of SQL can often be done in dplyr using arrange.

Consider this R code:

library(dplyr)
data(mtcars)

mtcars %>%
  select(mpg, cyl) %>%
  group_by(cyl) %>%
  arrange(mpg) %>%
  mutate(new = cumsum(mpg)) %>%
  arrange(cyl, mpg) %>%
  select(cyl, mpg, new)

Is equivalent to this SQL:

SELECT cyl
    ,mpg
    ,SUM(mpg) OVER (PARTITON BY cyl ORDER BY mpg) AS new
FROM mtcars
Simon.S.A.
  • 6,240
  • 7
  • 22
  • 41
  • Thank you for this Simon. I responded in a comment on the original question with the final code that I used to solve the problem. If I run into any issues when moving to a larger dataset, would you mind me sending them your way? This was very helpful! – James Wheeler Nov 12 '21 at 19:01