<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/using-a-column-as-a-column-index/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.win-vector.com/blog/2018/09/using-a-column-as-a-column-index/"> R – Win-Vector Blog</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>We <a rel="nofollow" href="https://community.rstudio.com/t/extracting-value-from-a-data-frame-where-column-name-to-extract-from-is-dynamically-determined-by-values-in-another-column/14585" target="_blank">recently saw</a> a great recurring <a rel="nofollow" href="https://www.r-project.org/" target="_blank">R</a> question: “how do you use one column to choose a different value for each row?” That is: how do you use a column as an index? Please read on for some idiomatic base R, data.table, and dplyr solutions.</p>
<p><span></span></p>
<p>
<p>Let’s say we have some example data:</p>
<pre>
df &lt;- data.frame(x = c(1, 2, 3, 4),
                 y = c(5, 6, 7, 8),
                 choice = c(&quot;x&quot;, &quot;y&quot;, &quot;x&quot;, &quot;z&quot;),
                 stringsAsFactors = FALSE)
</pre>
<p>Suppose our goal is to build a new column called “derived” that for each row chooses from the column named by the names in the column called “choice”. In our case the result would be “[1, 6, 3, NA]” (the last value being “NA” as “z” is not a column of df).</p>
<p>How does one do this?</p>
<p>In base-R we can use a matrix-index to specify the set of data cells we want (please see “help(<code>[</code>)” for details):</p>
<pre>
dtmp &lt;- df[, 
           intersect(df$choice, colnames(df)), 
           drop = FALSE]
dtmp[
  cbind(seq_len(nrow(df)),
        match(df$choice, colnames(dtmp)))]
]
# [1]  1  6  3 NA
</pre>
<p>data.table has some known notation and solutions for this (please see “help(.SD)” for details):</p>
<pre>
library(&quot;data.table&quot;)
dt &lt;- as.data.table(df)

dt[, derived := .SD[[choice]], by = choice][]

#    x y choice derived
# 1: 1 5      x       1
# 2: 2 6      y       6
# 3: 3 7      x       3
# 4: 4 8      z      NA
</pre>
<p>What we are doing is grouping by “choice”. In each group the choice column is a constant, and data.table supplies the name of the group as a scalar in the variable named choice. This lets us assign values from that column all at once (.SD, being data.table’s notation for the sub-data.table representing the group). Some discussion of the ideas can be found <a rel="nofollow" href="https://stackoverflow.com/questions/10176050/collating-data-from-a-different-column-per-row-of-a-data-table-depending-on-the" target="_blank">here</a>.</p>
<p>What is going on: is we are reducing the choice column to a series of scalars- so we can then use the scalar column selection operators (such as “[[]]”) to access columns.</p>
<p>There is also a dplyr solution:</p>
<pre>
df %>% 
  mutate(c2 = ifelse(choice %in% colnames(df),
                     choice,
                     colnames(df)[[1]])) %>%
  group_by(c2) %>%
  mutate(derived = .data[[ c2[[1]] ]]) %>%
  ungroup() %>%
  mutate(derived = ifelse(choice %in% colnames(df),
                          derived,
                          NA)) %>%
  select(-c2)
# # A tibble: 4 x 4
#       x     y choice derived
#   </pre>