Inner join with encoding

user_10924836 0
Viewed 46

I have two table. My intention is to make inner join between them.

enter image description here enter image description here

library(dplyr)
        
Table1<-structure(list(ID = c(1, 2, 3, 4, 5), Sales = c(100, 200, 300, 
                                                                400, 500)), row.names = c(NA, -5L), class = c("tbl_df", "tbl", "data.frame"))
        
Table2<-structure(list(ID = c(1, 1, 2, 3, 3, 3, 4, 5, 5, 5), Store = c(1, 
                                                                               2, 3, 4, 5, 6, 4, 10, 15, 20)), row.names = c(NA, -10L), class = c("tbl_df", 
                                                                                                                                              "tbl", "data.frame"))

  INNER_JOIN_TABLE<-inner_join(Table1,Table2,by = c("ID"="ID"))

I already make inner join with this command below, but unfortunately this command can make only inner join, but I want to have something different like table below.

enter image description here

So can anybody help me how to solve this and to table like pic above?

dplyr r encoding
1 Answers

This will give you the required results:

library(reshape2)
dcast_Table2 <- dcast(Table2,formula = ID ~Store,fill = "")
dcast_Table2[-1][dcast_Table2[-1] != ""] <- 1

INNER_JOIN_TABLE<-inner_join(Table1,dcast_Table2,by = c("ID"="ID"))
INNER_JOIN_TABLE

Output:

> INNER_JOIN_TABLE
# A tibble: 5 x 11
     ID Sales `1`   `2`   `3`   `4`   `5`   `6`   `10`  `15`  `20` 
  <dbl> <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1     1   100 "1"   "1"   ""    ""    ""    ""    ""    ""    ""   
2     2   200 ""    ""    "1"   ""    ""    ""    ""    ""    ""   
3     3   300 ""    ""    ""    "1"   "1"   "1"   ""    ""    ""   
4     4   400 ""    ""    ""    "1"   ""    ""    ""    ""    ""   
5     5   500 ""    ""    ""    ""    ""    ""    "1"   "1"   "1"

Change Empty Values with 0:

library(reshape2)
dcast_Table2 <- dcast(Table2,formula = ID ~Store,fill = "")
dcast_Table2[-1][dcast_Table2[-1] != ""] <- 1
dcast_Table2[-1][dcast_Table2[-1] == ""] <- 0

INNER_JOIN_TABLE<-inner_join(Table1,dcast_Table2,by = c("ID"="ID"))
INNER_JOIN_TABLE

Output:

> INNER_JOIN_TABLE
# A tibble: 5 x 11
     ID Sales `1`   `2`   `3`   `4`   `5`   `6`   `10`  `15`  `20` 
  <dbl> <dbl> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1     1   100 1     1     0     0     0     0     0     0     0    
2     2   200 0     0     1     0     0     0     0     0     0    
3     3   300 0     0     0     1     1     1     0     0     0    
4     4   400 0     0     0     1     0     0     0     0     0    
5     5   500 0     0     0     0     0     0     1     1     1

To remove the Quotations, simply print the table as a DataFrame instead of a Tibble.

as.data.frame(INNER_JOIN_TABLE)

Output:

> as.data.frame(INNER_JOIN_TABLE)
  ID Sales 1 2 3 4 5 6 10 15 20
1  1   100 1 1 0 0 0 0  0  0  0
2  2   200 0 0 1 0 0 0  0  0  0
3  3   300 0 0 0 1 1 1  0  0  0
4  4   400 0 0 0 1 0 0  0  0  0
5  5   500 0 0 0 0 0 0  1  1  1
user_148934680
Related