Dividing across multiple columns in r using mutate_at call

Question

I have a dataframe (lets call it monthlyaverages) that looks something like this...

month_year       product_key_1      product_key_2        product_key_3        product_key_4
2014-08          NA                 NA                   NA                   50
2014-09          NA                 NA                   NA                   NA
2014-10          NA                 NA                   149                  NA
2014-11          NA                 40                   116.81               NA
2014-12          NA                 43                   117                  NA
2015-01          65                 NA                   117                  NA
2015-02          65                 NA                   300                  60
2015-03          65                 NA                   NA                   60
2015-04          NA                 NA                   NA                   70
2015-05          NA                 NA                   NA                   NA
2015-06          NA                 NA                   NA                   NA

But I have thousands of rows and a couple more months. What I want to do is create price relatives, but using the month before (not a base month of January). So, using product_key_3 as an example, I would have 116.81/149 as the price relative for 2014-09 and 117/116.81 as the price relative for 2014-10 and so on. Where there are NA's in the previous cell I would want, or there's only one price observed for that product throughout the months, I would want the price relative to be (using product_key_2) as an example, 40/40 for 2014-11.

My desired output would look something like this:

          month_year       pr_product_1      pr_product_2        pr_product_3   pr_product_4

            2014-08          NA                 NA                   NA                 1
            2014-09          NA                 NA                   NA                 NA
            2014-10          NA                 NA                   1                  NA
            2014-11          NA                 1                    0.7839             NA
            2014-12          NA                 1.075                1.0016             NA
            2015-01          1                  NA                   1                  NA
            2015-02          1                  NA                   2.5641             1
            2015-03          1                  NA                   NA                 1
            2015-04          NA                 NA                   NA                 1.16
            2015-05          NA                 NA                   NA                 NA
            2015-06          NA                 NA                   NA                 NA

I have managed to do what I have explained above by using: monthlyaveragestest <- monthlyaverages %>% mutate_at(.vars=vars(matches("product", ignore.case = FALSE)), .funs=funs(lag(lead(.)/.,)))

But now I want to do something similar but instead divide across columns instead of divide through the rows. I am aware there's probably a quick fix but i've tried many variations of this code and can't seem to get it to work and I can't find another question that is similar to what i'm trying to do.

Any help would be greatly appreciated. You can recreate my example dataset using:

date <- c(2014-08, 2014-09, 2014-10, 2014-11, 2014-12, 2015-01, 2015-02, 2015-03, 2015-04, 2015-05, 2015-06)
product_key_1 <- c(NA, NA, NA, NA, NA, 65, 65, 65, NA, NA, NA)                    
product_key_2 <- c(NA, NA, NA, 40, 43, NA, NA, NA, NA, NA, NA)
product_key_3 <- c(NA, NA, 149, 116.81, 117, 117, 300, NA, NA, NA, NA)
product_key_4 <- c(50, NA, NA, NA, NA, NA, 60, 60, 70, NA, NA)
monthlyaverages <- data.frame(date, product_key_1, product_key_2, product_key_3, product_key_4)

Please let me know if all of this makes sense and if I could make it any clearer. Thanks.

Answer 1

I think if you transform your data into long format, then use lag() to divide the columns, you should get close:

library(tidyverse)

monthlyaverages %>% 
    # turn it into long format
    gather(key, val, -month_year) %>%
    # insert a seperator to make it easier to split out the unique column name
    mutate(key = str_replace(key, "_(\\d+)", ";\\1") ) %>% 
    # split out the column name
    separate(key, c("key2", "type"), sep = ";") %>% 
    # sort by date, then by type
    group_by(month_year) %>%
    arrange(type) %>% 
    # divide the previous value by the current value, defaulting to 1 when val is NA
    # not sure exactly what you want--maybe you'll need to swap lag(val) and val
    mutate(  newval = lag(val)/coalesce(val,1)  ) %>% 
    ungroup() %>%
    # drop the unnecssary variables
    select(month_year, type, newval) %>% 
    # spread out the new variables
    spread(type, newval, sep = "div_")

Later, you could use left_join() to join this back to monthlyaverages by month_year.