R - Operation on many columns by another column and append to original table

Question

I have a csv with the total agricultural area for each state and the area of different crops and I want to calculate the % area of each crop out of the total agricultural area and add it to table. Below is a simplified example of the csv.

Ag_area	State	Pasture	Soy	Corn
200	WI	50	50	25
100	MN	25	30	10
150	IL	100	0	50

I'm looking to produce a new table like this:

Ag_area	State	Pasture	Soy	Corn	Pct_Pasture	Pct_Soy	Pct_Corn
200	WI	50	50	25	.25	.25	.125
100	MN	25	30	10	.25	.30	.1
150	IL	100	0	50	.33	0	.166

I know how to code this for each column individually: df$Pct_Pasture <- df$Pasture/df$Ag_area

My dataset contains lots of crops so but I'm hoping there us a way to do it all in one shot. All of the crop columns are next to one another so I can use their index [3:89] and then ideally I'd be able to append 'Pct_' to each crop for the new calculated column. I'm thinking I need to use mutate but I can't figure it out? Thanks to anyone who takes a look at this

Answer 1

Here is one option:

library(tidyverse)

df <- tibble::tribble(
  ~Ag_area, ~State, ~Pasture, ~Soy, ~Corn,
      200L,   "WI",      50L,  50L,   25L,
      100L,   "MN",      25L,  30L,   10L,
      150L,   "IL",     100L,   0L,   50L
  )

# Refer to the columns by name
df %>%
  mutate(across(c("Pasture", "Soy", "Corn"), ~ .x / Ag_area, .names = "Pct_{.col}"))
#> # A tibble: 3 × 8
#>   Ag_area State Pasture   Soy  Corn Pct_Pasture Pct_Soy Pct_Corn
#>     <int> <chr>   <int> <int> <int>       <dbl>   <dbl>    <dbl>
#> 1     200 WI         50    50    25       0.25     0.25    0.125
#> 2     100 MN         25    30    10       0.25     0.3     0.1  
#> 3     150 IL        100     0    50       0.667    0       0.333

# Refer to the columns by number
df %>%
  mutate(across(3:5, ~ .x / Ag_area, .names = "Pct_{.col}"))
#> # A tibble: 3 × 8
#>   Ag_area State Pasture   Soy  Corn Pct_Pasture Pct_Soy Pct_Corn
#>     <int> <chr>   <int> <int> <int>       <dbl>   <dbl>    <dbl>
#> 1     200 WI         50    50    25       0.25     0.25    0.125
#> 2     100 MN         25    30    10       0.25     0.3     0.1  
#> 3     150 IL        100     0    50       0.667    0       0.333

^{Created on 2022-03-03 by the reprex package (v2.0.1)}

For more details, see the docs for the across() function.