How to combine data frames with differing nr of rows and sort equally

Question

dfa
              date league                  a                   b home draw away
1 2019-12-22 17:00     BL          Paderborn Eintracht Frankfurt 3.48 4.06 2.01
2 2019-12-22 14:30     BL Fortuna Düsseldorf        Unión Berlín 2.80 3.31 2.67

dfb
                    c                         d home1 draw1 away1
1      TSG Hoffenheim       Eintracht Frankfurt  2.37  3.60  2.75
2          Schalke 04  Borussia Mönchengladbach  2.40  3.50  2.75
3          RB Leipzig              Unión Berlín  1.25  6.00 10.00
4           Paderborn       Eintracht Frankfurt  3.40  4.00  2.00
5           Paderborn          Bayer Leverkusen  5.25  4.50  1.53
6               Mainz               SC Friburgo  2.30  3.30  3.10
7       Hertha Berlín          Bayern de Múnich 10.00  6.00  1.25
8  Fortuna Düsseldorf              Unión Berlín  2.75  3.25  2.62
9  Fortuna Düsseldorf             Werder Bremen  2.50  3.50  2.62
10            Colonia                Wolfsburgo  2.70  3.40  2.50
11          Augsburgo         Borussia Dortmund  5.00  4.33  1.57

I wish to combine dfa with dfb and get the below result.

data
              date                  a                   b home draw away                  c                   d home1 draw1 away1
1 2019-12-22 14:30 Fortuna Düsseldorf        Unión Berlín 2.80 3.31 2.67 Fortuna Düsseldorf        Unión Berlín  2.75  3.25  2.62
2 2019-12-22 17:00          Paderborn Eintracht Frankfurt 3.48 4.06 2.01          Paderborn Eintracht Frankfurt  3.40  4.00  2.00

I tried with this below but can't figure out how to arrange them with differing nr of rows. I'm looking for a solution that works no matter which df has more rows. Any input much appreciated.

myd <- merge(dfa, dfb, by = "row.names", all = TRUE)
myd
   Row.names             date league                  a                   b home draw away                  c                         d home1 draw1 away1
1          1 2019-12-22 17:00     BL          Paderborn Eintracht Frankfurt 3.48 4.06 2.01     TSG Hoffenheim       Eintracht Frankfurt  2.37  3.60  2.75
2         10             <NA>   <NA>               <NA>                <NA>   NA   NA   NA            Colonia                Wolfsburgo  2.70  3.40  2.50
3         11             <NA>   <NA>               <NA>                <NA>   NA   NA   NA          Augsburgo         Borussia Dortmund  5.00  4.33  1.57
4          2 2019-12-22 14:30     BL Fortuna Düsseldorf        Unión Berlín 2.80 3.31 2.67         Schalke 04  Borussia Mönchengladbach  2.40  3.50  2.75
5          3             <NA>   <NA>               <NA>                <NA>   NA   NA   NA         RB Leipzig              Unión Berlín  1.25  6.00 10.00
6          4             <NA>   <NA>               <NA>                <NA>   NA   NA   NA          Paderborn       Eintracht Frankfurt  3.40  4.00  2.00
7          5             <NA>   <NA>               <NA>                <NA>   NA   NA   NA          Paderborn          Bayer Leverkusen  5.25  4.50  1.53
8          6             <NA>   <NA>               <NA>                <NA>   NA   NA   NA              Mainz               SC Friburgo  2.30  3.30  3.10
9          7             <NA>   <NA>               <NA>                <NA>   NA   NA   NA      Hertha Berlín          Bayern de Múnich 10.00  6.00  1.25
10         8             <NA>   <NA>               <NA>                <NA>   NA   NA   NA Fortuna Düsseldorf              Unión Berlín  2.75  3.25  2.62
11         9             <NA>   <NA>               <NA>                <NA>   NA   NA   NA Fortuna Düsseldorf             Werder Bremen  2.50  3.50  2.62

Cuz, you put all = TRUE. (https://dplyr.tidyverse.org/reference/join.html) — Sang won kim, Dec 22 '19 at 03:02
I don't think you want to join by `row.names`, according to your output. What are your criteria for matching a row in `dfa` to one in `dfb`? — MichaelChirico, Dec 22 '19 at 03:10

Ronak Shah · Answer 1 · 2019-12-22T04:16:45.977

You can use merge like :

merge(dfa, dfb, by.x = c('a', 'b'), by.y = c('c', 'd'))

#                   a                   b             date league home draw away home1 draw1 away1
#1 Fortuna Düsseldorf        Unión Berlín 2019-12-22 14:30     BL 2.80 3.31 2.67  2.75  3.25  2.62
#2          Paderborn Eintracht Frankfurt 2019-12-22 17:00     BL 3.48 4.06 2.01  3.40  4.00  2.00

In dplyr, we can use inner_join

dplyr::inner_join(dfa, dfb, by = c('a' = 'c', 'b' = 'd'))

data

dfa <- structure(list(date = structure(2:1, .Label = c("2019-12-22 14:30", 
"2019-12-22 17:00"), class = "factor"), league = structure(c(1L, 
1L), .Label = "BL", class = "factor"), a = structure(2:1, 
.Label = c("Fortuna Düsseldorf", "Paderborn"), class = "factor"), 
b = structure(1:2, .Label = c("Eintracht Frankfurt", 
"Unión Berlín"), class = "factor"), home = c(3.48, 2.8), draw = c(4.06, 
3.31), away = c(2.01, 2.67)), class = "data.frame", row.names = c(NA, -2L))

dfb <- structure(list(c = structure(c(9L, 8L, 7L, 6L, 6L, 5L, 4L, 3L, 
3L, 2L, 1L), .Label = c("Augsburgo", "Colonia", "Fortuna Düsseldorf", 
"Hertha Berlín", "Mainz", "Paderborn", "RB Leipzig", "Schalke 04", 
"TSG Hoffenheim"), class = "factor"), d = structure(c(5L, 4L, 
7L, 5L, 1L, 6L, 2L, 7L, 8L, 9L, 3L), .Label = c("Bayer Leverkusen", 
"Bayern de Múnich", "Borussia Dortmund", "Borussia Mönchengladbach", 
"Eintracht Frankfurt", "SC Friburgo", "Unión Berlín", "Werder Bremen", 
"Wolfsburgo"), class = "factor"), home1 = c(2.37, 2.4, 1.25, 
3.4, 5.25, 2.3, 10, 2.75, 2.5, 2.7, 5), draw1 = c(3.6, 3.5, 6, 
4, 4.5, 3.3, 6, 3.25, 3.5, 3.4, 4.33), away1 = c(2.75, 2.75, 
10, 2, 1.53, 3.1, 1.25, 2.62, 2.62, 2.5, 1.57)), class = "data.frame", 
row.names = c(NA, -11L))

Thanks but when I try this I get: <0 rows> (or 0-length row.names) — mrsama, Dec 22 '19 at 03:41
In your example column c and d are not visible. I wish the result to be exactly like under "data". I'm sorry, I think I explain very poorly what I want. And even I try the dplyr suggestion I get: <0 rows> (or 0-length row.names) — mrsama, Dec 22 '19 at 04:04
@mrsama It gives me 2 rows as shown. Can you try on the updated data which I posted. How different is your data from the one you have shown ? Column `c` and `d` are same as `a` and `b` columns, hence only one set is shown. If you want duplicate columns you can just copy it. `inner_join(dfa, dfb, by = c('a' = 'c', 'b' = 'd')) %>% mutate(c = a, d = b)` — Ronak Shah, Dec 22 '19 at 04:22
Yes, when I copy and paste your code it works and I get the desired result. Do you mean I need to write the labels and all columns manually like this? — mrsama, Dec 22 '19 at 04:50
You want to merge only on two columns, right ? then the above code should work as it is. — Ronak Shah, Dec 22 '19 at 05:05

score 0 · Answer 2 · answered Dec 22 '19 at 05:04

0

Can we try this? This works for me.

data <- merge(dfa,dfb,by.x=c("a","b"),by.y=c("c","d"),all.x=T)

answered Dec 22 '19 at 05:04

Lovekush Goyal

36
3

How to combine data frames with differing nr of rows and sort equally

2 Answers2