How to return the name of a column if its value matches the value of another column?-CodePudding

I have the following data frame and I want to populate the column "TargetPos" with the name of the column whose value matches that of the column "Item" (so "Left" in this case) (in my actual data, I actually have four positions: TopRight, TopLeft, BottomRight, BottomLeft). What approach can one use?

Item	Right	Left	TargetPos
bed.png	flag.png	bed.png

I've been trying something along these lines:

df$TargetPos <- lapply(df$Item, function(x)
  if (x == df$Right) {
    names(df$Right)
  } else {
    Left
  })

CodePudding user response：

Since you want to have vector as output, you should use sapply instead of lapply.

The core of the solution is to use which to output the column index which has value(s) matching the Item column (remember the 1, since the Item column itself is not included when indexing), and output the colnames.

I have created some extra rows for demonstration purpose.

Input

df <- data.frame(Item = c("bed.png", "a", "b", "e", "532523"), 
                 Right = c("flag.png", "a", "c", "e", "aaa"), 
                 Left = c("bed.png", "b", "b", "e", "bbb"))

     Item    Right    Left
1 bed.png flag.png bed.png
2       a        a       b
3       b        c       b
4       e        e       e
5  532523      aaa     bbb

Code

df$TargetPos <- 
  sapply(1:nrow(df), 
         function(x) colnames(df)[which(df[x, -1] == df$Item[x])   1])

df
     Item    Right    Left   TargetPos
1 bed.png flag.png bed.png        Left
2       a        a       b       Right
3       b        c       b        Left
4       e        e       e Right, Left
5  532523      aaa     bbb

CodePudding user response：

Data from @benson23 (many thanks). Here is a tidyverse approach:

library(dplyr)
library(tidyr)

df %>% 
  mutate(across(c(Right, Left), ~case_when(. == Item ~ cur_column(),
                                           TRUE ~ NA_character_), .names = 'new_{col}')) %>% 
  unite(TargetPos, starts_with('new'), na.rm = TRUE, sep = ', ')

  Item    Right    Left   TargetPos
1 bed.png flag.png bed.png        Left
2       a        a       b       Right
3       b        c       b        Left
4       e        e       e Right, Left
5  532523      aaa     bbb

CodePudding user response：

In base R, we may also use apply

df$TargetPos <- apply(df$Item == df[-1], 1, \(x) toString(names(x)[x]))

-output

> df
     Item    Right    Left   TargetPos
1 bed.png flag.png bed.png        Left
2       a        a       b       Right
3       b        c       b        Left
4       e        e       e Right, Left
5  532523      aaa     bbb

data

df <- structure(list(Item = c("bed.png", "a", "b", "e", "532523"), 
    Right = c("flag.png", "a", "c", "e", "aaa"), Left = c("bed.png", 
    "b", "b", "e", "bbb")), class = "data.frame", row.names = c(NA, 
-5L))

CodePudding user response：

You could exploit the vectorization of == more. Basically it's df1$Item == df1[2:5] gives a boolean matrix then apply which over MARGIN=1 i.e. over the rows.

So if your data looks like this

df1
#          Item TopRight TopLeft BottomRight  BottomLeft
# 1     bed.png flag.png bed.png   stars.png stripes.png
# 2 stripes.png flag.png bed.png   stars.png stripes.png
# 3     foo.png  foo.png bar.png     baz.png     bam.png
# 4     baz.png  foo.png bar.png     baz.png     bam.png

you may do:

transform(df1, target=names(df1)[2:5][apply(Item == df1[2:5], 1, which)])
#          Item TopRight TopLeft BottomRight  BottomLeft      target
# 1     bed.png flag.png bed.png   stars.png stripes.png     TopLeft
# 2 stripes.png flag.png bed.png   stars.png stripes.png  BottomLeft
# 3     foo.png  foo.png bar.png     baz.png     bam.png    TopRight
# 4     baz.png  foo.png bar.png     baz.png     bam.png BottomRight

If it rather looks like that

df2
#          Item TopRight TopLeft BottomRight  BottomLeft
# 1     bed.png flag.png bed.png   stars.png stripes.png
# 2 stripes.png flag.png bed.png   stars.png stripes.png
# 3     foo.png  foo.png bar.png     baz.png     bam.png
# 4     baz.png  foo.png bar.png     baz.png     bam.png
# 5          -1  foo.png bar.png     baz.png     bam.png
# 6          -1       -1      -1          -1          -1

you could do:

within(df2, {
  target <- Vectorize(toString)(apply(Item == df2[2:5], 1, \(x) 
                                      names(which(x))))
  target <- replace(target, target == "", NA)
})
df2
#          Item TopRight TopLeft BottomRight  BottomLeft                                     target
# 1     bed.png flag.png bed.png   stars.png stripes.png                                    TopLeft
# 2 stripes.png flag.png bed.png   stars.png stripes.png                                 BottomLeft
# 3     foo.png  foo.png bar.png     baz.png     bam.png                                   TopRight
# 4     baz.png  foo.png bar.png     baz.png     bam.png                                BottomRight
# 5          -1  foo.png bar.png     baz.png     bam.png                                       <NA>
# 6          -1       -1      -1          -1          -1 TopRight, TopLeft, BottomRight, BottomLeft

Data:

df1 <- structure(list(Item = c("bed.png", "stripes.png", "foo.png", 
"baz.png"), TopRight = c("flag.png", "flag.png", "foo.png", "foo.png"
), TopLeft = c("bed.png", "bed.png", "bar.png", "bar.png"), BottomRight = c("stars.png", 
"stars.png", "baz.png", "baz.png"), BottomLeft = c("stripes.png", 
"stripes.png", "bam.png", "bam.png")), class = "data.frame", row.names = c(NA, 
-4L))

df2 <- structure(list(Item = c("bed.png", "stripes.png", "foo.png", 
"baz.png", "-1", "-1"), TopRight = c("flag.png", "flag.png", 
"foo.png", "foo.png", "foo.png", "-1"), TopLeft = c("bed.png", 
"bed.png", "bar.png", "bar.png", "bar.png", "-1"), BottomRight = c("stars.png", 
"stars.png", "baz.png", "baz.png", "baz.png", "-1"), BottomLeft = c("stripes.png", 
"stripes.png", "bam.png", "bam.png", "bam.png", "-1")), class = "data.frame", row.names = c(NA, 
-6L))