Search code examples
rmatchlookup

Multi criteria index match to column names in R

I am wanting to match one data frame to another so that the second column goes into the columns listed in the third column. Column three has values up to 20. Thanks!

Example df1

|.      |.  |. |
|-------|---|--|
|365    |58 |1 |
|12     |32 |1 |
|693    |79 |1 |
|365    |25 |2 |
|1      |37 |1 |
|693    |18 |2 |

Example df2

1
12
365
693

Desired output:

|    |1  |2. |
|----|---|---|
|1   |37 |   |
|12  |32 |   |
|365 |58 |25 |
|693 |18 |18 |

I have tried with case when but no success with column names.

Solution

  • You need to reshape/cast/pivot your df1 to a wide format first, and then it's a normal merge/join operation. E.g., in base R:

    merge(df2, reshape(df1, idvar="V1", timevar="V3", direction="wide"), by="V1")
#   V1 V2.1 V2.2
#1   1   37   NA
#2  12   32   NA
#3 365   58   25
#4 693   79   18

    Or the tidyverse:

    library(dplyr)
library(tidyr)
df1 %>% 
    pivot_wider(names_from=V3, values_from=V2) %>% 
    right_join(df2, by="V1")
## A tibble: 4 × 3
#     V1   `1`   `2`
#  <int> <int> <int>
#1   365    58    25
#2    12    32    NA
#3   693    79    18
#4     1    37    NA

    Using sample data from your post read in with names:

    df1 <- read.table(text="365 58  1
12  32  1
693 79  1
365 25  2
1   37  1
693 18  2", header=FALSE)

df2 <- read.table(text="1
12
365
693", header=FALSE)