Combine two matrix and mark common

Question

I have two matrix as like this

Vehicle1 Year type
Car1      20    A
Car2      21    A
Car8      20    A

Second one

Vehicle2 Year type
Car1      20    M
Car2      21    M
Car7      90    M

I just need to combine the matrix based on the first column(Vehicle) and need to mark common as A/M as like this

Vehicle  Year type
Car1      20  A/M
Car2      21  A/M
Car7      90   M
Car8      20   A

I used merge function for this but it only printing the common one

Please use `dput()` to provide reproducible examples – Sotos Jul 16 '20 at 09:50 — Sotos, Jul 16 '20 at 09:50

score 2 · Accepted Answer · answered Jul 16 '20 at 09:51

2

You can join the two dataframe and combine the type columns :

dplyr::full_join(df1, df2, by = c('Vehicle1' = 'Vehicle2', 'Year')) %>%
   tidyr::unite(type, type.x, type.y, sep = '/', na.rm = TRUE)

#  Vehicle1 Year type
#1     Car1   20  A/M
#2     Car2   21  A/M
#3     Car8   20    A
#4     Car7   90    M

data

df1 <- structure(list(Vehicle1 = c("Car1", "Car2", "Car8"), Year = c(20L, 
21L, 20L), type = c("A", "A", "A")), class = "data.frame", row.names = c(NA, -3L))

df2 <- structure(list(Vehicle2 = c("Car1", "Car2", "Car7"), Year = c(20L, 
21L, 90L), type = c("M", "M", "M")), class = "data.frame", row.names = c(NA, -3L))

answered Jul 16 '20 at 09:51

Ronak Shah

377,200
20
156
213

I got an error while trying this "no applicable method for 'full_join' applied to an object of class "c('matrix', 'character')"" – user90 Jul 16 '20 at 10:22
1

@user90 Those are matrix, please convert them to dataframe first. `df1 <-data.frame(df1)`, `df2 <-data.frame(df2)`. – Ronak Shah Jul 16 '20 at 10:23
You can use `Reduce` with `merge`. See this post https://stackoverflow.com/questions/8091303/simultaneously-merge-multiple-data-frames-in-a-list – Ronak Shah Oct 14 '20 at 12:43

score 1 · Answer 2 · answered Jul 16 '20 at 09:58

Another dplyr solution.

library(dplyr)

df2 %>%
  rename(Vehicle1 = Vehicle2) %>%
  bind_rows(df1, .) %>%
  group_by(Vehicle1, Year) %>%
  summarise(type = paste(type, collapse = "/")) %>%
  ungroup()

# # A tibble: 4 x 3
#   Vehicle1  Year type 
#   <chr>    <int> <chr>
# 1 Car1        20 A/M  
# 2 Car2        21 A/M  
# 3 Car7        90 M    
# 4 Car8        20 A

score 1 · Answer 3 · answered Jul 16 '20 at 10:19

You can also do this easily in base R.

rr <- merge(m1, m2, all=T, by.x="Vehicle1", by.y="Vehicle2")
rr <- setNames(na.omit(reshape(rr, idvar="Vehicle1", varying=list(c(2, 4), c(3, 5)), 
                      direction="long")), c("Vehicle1", "t", names(m1)[-1]))
dupes <- which(duplicated(rr$Vehicle1))
rr[rr$Vehicle1 %in% rr$Vehicle1[dupes], 4] <- "A/M"
res <- rr[-dupes, -2]
res
#        Vehicle1 Year type
# Car1.1     Car1   20  A/M
# Car2.1     Car2   21  A/M
# Car8.1     Car8   20    A
# Car7.2     Car7   90    M

Data:

m1 <- structure(list(Vehicle1 = c("Car1", "Car2", "Car8"), Year = c(20L, 
21L, 20L), type = c("A", "A", "A")), class = "data.frame", row.names = c(NA, 
-3L))

m2 <- structure(list(Vehicle2 = c("Car1", "Car2", "Car7"), Year = c(20L, 
21L, 90L), type = c("M", "M", "M")), class = "data.frame", row.names = c(NA, 
-3L))

score 1 · Answer 4 · answered Jul 16 '20 at 10:22

Here is a base R option using merge

tmp <- merge(df1, df2, by.x = c("Vehicle1", "Year"), by.y = c("Vehicle2", "Year"), all = TRUE)
dfout <- cbind(tmp[c("Vehicle1", "Year")],
    type = apply(
        tmp[grep("type", names(tmp))],
        1,
        function(...) ifelse(any(is.na(...)), na.omit(...), paste0(..., collapse = "/"))
    )
)

such that

> dfout
  Vehicle1 Year type
1     Car1   20  A/M
2     Car2   21  A/M
3     Car7   90    M
4     Car8   20    A

Data

> dput(df1)
structure(list(Vehicle1 = c("Car1", "Car2", "Car8"), Year = c(20L, 
21L, 20L), type = c("A", "A", "A")), class = "data.frame", row.names = c(NA,
-3L))
> dput(df2)
structure(list(Vehicle2 = c("Car1", "Car2", "Car7"), Year = c(20L, 
21L, 90L), type = c("M", "M", "M")), class = "data.frame", row.names = c(NA, 
-3L))

Combine two matrix and mark common

4 Answers4