Calculations across rows in a dataframe or tibble

[cj_risk]

I have a dataframe, or tibble, that I need to do some calculations across different observations & values. A simple example is in the df_old tibble below.

df_old <- tibble(
  fwd_date= as.Date(c("2019-12-01", "2019-12-01", "2020-01-01", "2020-01-01")),
  commodity=c("wti", "wcs", "wti", "wcs"),
  price=c(50.00,-12.00, 55.00, -18.00))

I simply need to add the wti and wcs value for each fwd_date so that it looks like the df_new tibble below. I tried a variety of manipulations (lead, lag, mutate etc.), but I'm stuck.

df_new <- tibble(
  fwd_date= as.Date(c("2019-12-01", "2020-01-01")),
  commodity=c("combined", "combined"),
  price=c(38, 37))

Any advice the r community can provide would be greatly appreciated.

Thanks in advance.

CJ

[andresrcs]

This produces the desired output

library(dplyr)

df_old <- tibble(
    fwd_date= as.Date(c("2019-12-01", "2019-12-01", "2020-01-01", "2020-01-01")),
    commodity=c("wti", "wcs", "wti", "wcs"),
    price=c(50.00,-12.00, 55.00, -18.00))

df_old %>%
    mutate(commodity = "combined") %>% 
    group_by(fwd_date, commodity) %>%
    summarise(price = sum(price))
#> # A tibble: 2 x 3
#> # Groups:   fwd_date [2]
#>   fwd_date   commodity price
#>   <date>     <chr>     <dbl>
#> 1 2019-12-01 combined     38
#> 2 2020-01-01 combined     37

^{Created on 2019-10-12 by the reprex package (v0.3.0.9000)}

[cj_risk]

Thanks. Very helpful.

If I wanted to perform other calculations rather than summing the grouped numbers, do you have any suggestions on how to to do this? For example, how could you multiply, or divide the prices.

[andresrcs]

Reshaping your data to a wide format would make those calculations simpler

library(tidyverse)

df_old <- tibble(
    fwd_date= as.Date(c("2019-12-01", "2019-12-01", "2020-01-01", "2020-01-01")),
    commodity=c("wti", "wcs", "wti", "wcs"),
    price=c(50.00,-12.00, 55.00, -18.00))

df_old %>% 
    spread(commodity, price) %>% 
    mutate(multiply = wcs * wti,
           divide = wcs / wti)
#> # A tibble: 2 x 5
#>   fwd_date     wcs   wti multiply divide
#>   <date>     <dbl> <dbl>    <dbl>  <dbl>
#> 1 2019-12-01   -12    50     -600 -0.24 
#> 2 2020-01-01   -18    55     -990 -0.327