multiphrenic multiphrenic - 3 months ago 18
R Question

How can I reference a list based on a variable within a data.frame?

I have a simple table with

emp_id
and
job_code
. I would like to return the correct
payout
based on the
job_code


I've managed this with nested ifelse's but what if I have more
job_code
's?

library(dplyr)
set.seed(1)

emp_id <- round(rnorm(100, 500000, 10000))
job_code <- sample(c('a', 'b', 'c'), 100, replace = TRUE)
result <- sample(c(1,2,3,4), 100, replace = TRUE)

df <- data.frame(emp_id = emp_id, job_code = job_code, result = result)

job_a <- c(0, 500, 1000, 5000)
job_b <- c(0, 200, 500, 750)
job_c <- c(0, 250, 750, 1000)

# Works but sucky
df %>% mutate(payout = ifelse(job_code == 'a', job_a[result],
ifelse(job_code == 'b', job_b[result],
job_c[result])))


and
dput
if you prefer:

structure(list(emp_id = c(493735, 501836, 491644, 515953, 503295,
491795, 504874, 507383, 505758, 496946, 515118, 503898, 493788,
477853, 511249, 499551, 499838, 509438, 508212, 505939, 509190,
507821, 500746, 480106, 506198, 499439, 498442, 485292, 495218,
504179, 513587, 498972, 503877, 499462, 486229, 495850, 496057,
499407, 511000, 507632, 498355, 497466, 506970, 505567, 493112,
492925, 503646, 507685, 498877, 508811, 503981, 493880, 503411,
488706, 514330, 519804, 496328, 489559, 505697, 498649, 524016,
499608, 506897, 500280, 492567, 501888, 481950, 514656, 501533,
521726, 504755, 492901, 506107, 490659, 487464, 502914, 495567,
500011, 500743, 494105, 494313, 498648, 511781, 484764, 505939,
503330, 510631, 496958, 503700, 502671, 494575, 512079, 511604,
507002, 515868, 505585, 487234, 494267, 487754, 495266), job_code = structure(c(1L,
1L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 3L, 3L, 1L, 3L, 3L, 3L, 1L, 2L,
3L, 3L, 2L, 1L, 1L, 1L, 2L, 3L, 2L, 1L, 1L, 2L, 3L, 2L, 1L, 2L,
2L, 2L, 3L, 3L, 2L, 2L, 2L, 1L, 2L, 3L, 1L, 2L, 1L, 2L, 1L, 2L,
3L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 2L, 2L, 3L, 2L, 1L, 1L, 3L, 3L,
1L, 1L, 3L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 2L, 3L, 1L,
2L, 3L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 3L, 1L, 2L, 3L, 1L,
1L, 1L, 3L), .Label = c("a", "b", "c"), class = "factor"), result = c(3,
1, 2, 2, 2, 4, 1, 4, 1, 2, 1, 1, 4, 3, 2, 2, 1, 2, 4, 3, 3, 2,
2, 4, 4, 4, 4, 4, 2, 4, 4, 2, 2, 4, 1, 2, 2, 1, 3, 4, 4, 1, 3,
2, 3, 2, 2, 1, 2, 3, 2, 1, 4, 2, 4, 2, 4, 1, 4, 2, 1, 2, 4, 2,
3, 4, 1, 3, 3, 2, 2, 3, 4, 1, 1, 2, 2, 4, 1, 2, 2, 3, 3, 4, 1,
1, 4, 4, 1, 4, 1, 1, 4, 3, 1, 2, 3, 2, 2, 1)), .Names = c("emp_id",
"job_code", "result"), row.names = c(NA, -100L), class = "data.frame")


What I'd like to do ideally is have the payouts within a data.frame but not sure how to reference it properly:

job_payouts <- data.frame(a = job_a, b = job_b, c = job_c)
# Won't work...
df %>% mutate(payout = job_payouts$job_code[result])

Answer

Using tools from tidyverse:

library(dplyr)
library(stringr)
library(tidyr)

# your data
set.seed(1)

emp_id <- round(rnorm(100, 500000, 10000))
job_code <- sample(c('a', 'b', 'c'), 100, replace = TRUE)
result <- sample(c(1,2,3,4), 100, replace = TRUE)

# construct a data frame
df <- 
  data.frame(emp_id = emp_id,
             job_code = job_code, 
             result = result,
             stringsAsFactors = FALSE)

# your jobs
job_a <- c(0, 500, 1000, 5000)
job_b <- c(0, 200, 500, 750)
job_c <- c(0, 250, 750, 1000)

# construct a data frame
my_job <- 
  data.frame(job_a, job_b, job_c) %>% 
  gather(job, value) %>% 
  group_by(job) %>% 
  mutate(result = 1:n(),
         job_code = str_replace(job, "job_", "")) %>% 
  ungroup %>% 
  select(-job)

# join df and my_job into my_results table
my_results <-
  left_join(df, my_job)

Results:

my_results %>% tbl_df

Source: local data frame [100 x 4]

   emp_id job_code result value
    (dbl)    (chr)  (dbl) (dbl)
1  493735        a      3  1000
2  501836        a      1     0
3  491644        b      2   200
4  515953        a      2   500
5  503295        a      2   500
6  491795        b      4   750
7  504874        b      1     0
8  507383        a      4  5000
9  505758        a      1     0
10 496946        c      2   250
..    ...      ...    ...   ...