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

user_2284060 0
Viewed 32

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

dplyr r
1 Answers

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.

user_129573400
Related