Merging two tables with additional condition-CodePudding

I am trying to combine two data frames.

First data frame you can see below

table1<-data.frame(type1=c("A","B","C","D"),
                   type2=c("AAA","BBB","CCC","DDD") ,
                   value=seq(1:4)
                    )
table1

And the second table you can see below

  table2<-data.frame(
                        type2=c("AAA","BBB","CCC","DDD","FFF","GGG"), 
                        A= seq(1:6),
                        B= seq(1:6),
                        C= seq(1:6),
                        D= seq(1:6),
                        F= seq(1:6),
                        G= seq(1:6)
                                )
    table2

Now I want to make join Table 1 with Table 2 but in a specific way. So I want to join column type 1 (e.g A, B,C, and D) from Table 1 with the same columns from Table 2 with one condition Type 2 columns to be the same in Table 1 and Table 2.

Below you can see the expected output

So can anybody help me how to do this?

CodePudding user response：

If you want to select columns in type2 based on the type1 in table1 try to pass type1 as an array of the strings:

library(tidyverse)
table1 %>% left_join(table2, by='type2') %>% select(type2,c(table1$type1))

Output:

  type2 A B C D
1   AAA 1 1 1 1
2   BBB 2 2 2 2
3   CCC 3 3 3 3
4   DDD 4 4 4 4

CodePudding user response：

Here is a base R approach:

table2[grepl(paste(paste(table1$type1, collapse = "|"), names(table2[1]), sep = "|"), colnames(table2))][c(1:nrow(table1)), ]

  type2 A B C D
1   AAA 1 1 1 1
2   BBB 2 2 2 2
3   CCC 3 3 3 3
4   DDD 4 4 4 4

CodePudding user response：

Another possible solution:

library(tidyverse)

inner_join(table1, table2, by = "type2") %>% 
  select(type2, intersect(str_split(.$type2, "") %>% unlist, names(table2)[-1]))

#>   type2 A B C D
#> 1   AAA 1 1 1 1
#> 2   BBB 2 2 2 2
#> 3   CCC 3 3 3 3
#> 4   DDD 4 4 4 4