CODEWITHSUNDEEP
rdataframedplyr
Joe Jalbert
Joe Jalbert

Reputation: 13

How can I use one column to determine where I get the value for another column?

I'm trying to use one column to determine which column to use as the value for another column It looks something like this:

     X   Y  Z   Target
1    a   b  c    X
2    d   e  f    Y
3    g   h  i    Z

And I want something that looks like this:

     X   Y  Z   Target  TargetValue
1    a   b  c    X           a
2    d   e  f    Y           e
3    g   h  i    Z           i

Where each TargetValue is the value determined by the column specified by Target. I've been using dplyr a bit to get this to work. If I knew how to make the output of paste the input for mutate that would be great,

mutate(TargetWordFixed = (paste("WordMove",TargetWord,".rt", sep="")))

but maybe there is another way to do the same thing.

Be gentle, I'm new to both stackoverflow and R...

Upvotes: 1

Views: 324

Answers (3)

Dan Houghton
Dan Houghton

Reputation: 121

library(tidyverse)

df <-setNames(data.frame(cbind(matrix(letters[1:9],3,3,byrow=T), c("X", "Y", "Z"))), c("X", "Y", "Z", "Target"))

df

df %>% 
  gather(key="ID", value="TargetValue", X:Z) %>%
  filter(ID==Target) %>%
  select(Target, TargetValue) %>%
  left_join(df, by="Target")

Upvotes: 0

talat
talat

Reputation: 70256

A vectorized approach would be to use matrix subsetting:

df %>% mutate(TargetValue = .[cbind(1:n(), match(Target, names(.)))])
#  X Y Z Target TargetValue
#1 a b c      X           a
#2 d e f      Y           e
#3 g h i      Z           i

Or just using base R (same approach):

transform(df, TargetValue = df[cbind(1:nrow(df), match(Target, names(df)))])

Explanation:

  • match(Target, names(.)) computes the column indices of the entries in Target (which column is called X etc)
  • The . in the dplyr version refers to the data you "pipe" into the mutate statement with %>% (i.e. it refers to df)
  • df[cbind(1:n(), match(Target, names(df))] creates a matrix to subset df to the correct values - the first column of the matrix is just the row numbers starting from 1 to the number of rows of df (therefore 1:nrow(df)) and the second column in the matrix is the index which column holds the Target value of interest (computed by match(Target, names(df))).

The matrix that is produced for subsetting the example data is:

cbind(1:nrow(df), match(df$Target, names(df)))
     [,1] [,2]
[1,]    1    1
[2,]    2    2
[3,]    3    3

Upvotes: 2

lukeA
lukeA

Reputation: 54237

You could try apply rowwise like this:

transform(df, TargetValue = apply(df, 1, function(x) x[x["Target"]]))
#   X Y Z Target TargetValue
# 1 a b c      X           a
# 2 d e f      Y           e
# 3 g h i      Z           i

Upvotes: 2

Related Questions