creating share by dividing against a fixed value within a group in R

user15032839 0
Viewed 22

I am trying to create a column called share which is calculated by dividing each value where noc_code is xxxx against noc_code=9999 WITHIN each industry group.

Here is a sample of my table

Industry noc_doe VALUE
mining 9999 100
mining 0001 15
mining 0005 33
mining 0941 50
agriculture 9999 100
agriculture 0701 25
agriculture 6785 4
agriculture 8456 90

my code is this, but it is not working

df %>%
 group_by(Industry)%>%
 mutate(Share = VALUE / VALUE[match("9999", noc_code)])-> df

I am hoping to get this result

Industry noc_doe VALUE Share
mining 9999 100 1
mining 0001 15 0.15
mining 0005 33 0.33
mining 0941 50 0.5
agriculture 9999 100 1
agriculture 0701 25 0.25
agriculture 6785 4 0.04
agriculture 8456 90 0.9
r
2 Answers

Here is a way.
Create a temp column div holding the divisor corresponding to the wanted noc_doe, by group of industry.

df<-"Industry   noc_doe     VALUE
mining  9999    100
mining  0001    15
mining  0005    33
mining  0941    50
agriculture     9999    100
agriculture     0701    25
agriculture     6785    4
agriculture     8456    90"
df <- read.table(textConnection(df), header = TRUE)

suppressPackageStartupMessages(library(dplyr))

df %>%
  group_by(Industry) %>%
  mutate(
    div = VALUE[noc_doe == 9999],
    share = VALUE/div
  ) %>%
  select(-div)
#> # A tibble: 8 × 4
#> # Groups:   Industry [2]
#>   Industry    noc_doe VALUE share
#>   <chr>         <int> <int> <dbl>
#> 1 mining         9999   100  1   
#> 2 mining            1    15  0.15
#> 3 mining            5    33  0.33
#> 4 mining          941    50  0.5 
#> 5 agriculture    9999   100  1   
#> 6 agriculture     701    25  0.25
#> 7 agriculture    6785     4  0.04
#> 8 agriculture    8456    90  0.9

Created on 2022-09-08 by the reprex package (v2.0.1)

Rui Barradas0

I would do it like this

library(tidyverse)

df <-
  structure(
    list(
      Industry = c(
        "mining",
        "mining",
        "mining",
        "mining",
        "agriculture",
        "agriculture",
        "agriculture",
        "agriculture"
      ),
      noc_doe = c(9999L, 1L, 5L, 941L, 9999L, 701L, 6785L, 8456L),
      VALUE = c(100L, 15L, 33L, 50L, 100L, 25L, 4L, 90L)
    ),
    class = "data.frame",
    row.names = c(NA,-8L)
  )

df %>%
  group_by(Industry) %>%
  mutate(
    Share  = VALUE/first(VALUE, order_by = desc(noc_doe == 9999L))
  )
#> # A tibble: 8 × 4
#> # Groups:   Industry [2]
#>   Industry    noc_doe VALUE Share
#>   <chr>         <int> <int> <dbl>
#> 1 mining         9999   100  1   
#> 2 mining            1    15  0.15
#> 3 mining            5    33  0.33
#> 4 mining          941    50  0.5 
#> 5 agriculture    9999   100  1   
#> 6 agriculture     701    25  0.25
#> 7 agriculture    6785     4  0.04
#> 8 agriculture    8456    90  0.9

Created on 2022-09-08 by the reprex package (v2.0.1)

Quixotic220
Related