apply a function to a robust data frame with similar variables

Question

Perhaps someone could help me to elaborate better on the title of this post.

I am trying to apply the exact same formula to several sites (Australia, Mexico and France). Each site has the same variables (P, PET). To reproduce the example I have created the following data frame:

df <- data.frame(Australia.P = c(2015), Australia.PET = c(739),
                 Mexico.P = c(1284), Mexico.PET = c(808),
                 France.P = c(2010),France.PET = c(958))

for each site I would like to calculate the following formula: x = P * PET and it can be done as follows:

x_a <- df$Australia.P * df$Australia.PET # and so on for each Country
x_m <- df$Mexico.P * df$Mexico.PET
x_f <- df$France.P * df$France.PET

df_new <- data.frame(x_a, x_m, x_f)

and my questions is: How could I compute "x = P * PET" for all sites (without having to type each site variable) at once and end up with the same results in a data frame? This would help me in case I had a hundred sites (Countries)

I would appreciate any help. Thank you.

Answer 1

You could do a combination of pivot_longer, separate_wider_delim, reframe and pivot_wider to get this:

df %>%
  pivot_longer(everything()) %>%
  separate_wider_delim(name, names = c("name", "xx"), delim = ".") %>%
  reframe(new = value[xx == "P"] * value[xx == "PET"], .by = name) %>%
  pivot_wider(names_from = name, values_from = new)

Output

  Australia  Mexico  France
      <dbl>   <dbl>   <dbl>
1   1489085 1037472 1925580

Answer 2

I think this can be simply taken care of with one pivot:

library(tidyverse)

df |>
  pivot_longer(cols = everything(), 
               names_to = c("Country", ".value"), 
               names_sep = "\\.") |>
  mutate(x = P * PET)
#> # A tibble: 3 x 4
#>   Country       P   PET       x
#>   <chr>     <dbl> <dbl>   <dbl>
#> 1 Australia  2015   739 1489085
#> 2 Mexico     1284   808 1037472
#> 3 France     2010   958 1925580