How to join dataframes by ID column?

Question

I have 3 Dataframes like the ones below with IDs that may not necessarily occur in all

DF1:

  ID |    Name     |   Phone#   |  Country   | State   | Amount_month1
0210 |  John K.    | 8942829725 |   USA      |  PA     | 1300
0215 |  Peter      | 8711234566 |   USA      |  KS     | 50
2312 |  Steven     | 9012341221 |   USA      |  TX     | 1000
0005 |  Haris      | 9167456363 |   USA      |  NY     | 1200

DF2:

  ID |    Name     |   Phone#   |  Country   | State   | Amount_month2
0210 |  John K.    | 8942829725 |   USA      |  PA     | 200
2312 |  Steven     | 9012341221 |   USA      |  TX     | 350
2112 |  Jerry      | 9817273794 |   USA      |  CA     | 100

DF3:

  ID |    Name     |   Phone#   |  Country   | State   | Amount_month3
0210 |  John K.    | 8942829725 |   USA      |  PA     | 300
0005 |  Haris      | 9167456363 |   USA      |  NY     | 1250
1212 |  Jerry      | 9817273794 |   USA      |  CA     | 1200
1210 |  Drew       | 8012341234 |   USA      |  TX     | 1400

I would like to join these 3 dataframes by ID and add the varying column amounts as separate columns, the missing amount values can be either 0 or NA such as:

   ID |    Name  |   Phone#   |  Country |State| Amount_month1 | Amount_month2 | Amount_month3
0210  |  John K. | 8942829725 |   USA    | PA  |  1300         |  200          | 300
0215  |  Peter   | 8711234566 |   USA    | KS  |  50           |  0            | 0
2312  |  Steven  | 9012341221 |   USA    | TX  |  1000         |  350          | 0
0005  |  Haris   | 9167456363 |   USA    | NY  |  1200         |  0            | 1250 
1212  |  Jerry   | 9817273794 |   USA    |  CA |  0            | 100           | 1200      
1210  |  Drew    | 8012341234 |   USA    |  TX |  0            | 0             | 1400

Does this answer your question? [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — pietrodito, Apr 08 '21 at 17:20
What have you tried that didn't work? This seems like a simple set of left joins, which should be covered in many SO posts already — camille, Apr 08 '21 at 17:20

score 1 · Answer 1 · answered Apr 08 '21 at 17:14

1

It can be done in a single line using Reduce and merge

Reduce(function(x, y) merge(x, y, all=TRUE), list(DF1, DF2, DF3))

answered Apr 08 '21 at 17:14

Ashish Baid

513
4
9

Chris Ruehlemann · Answer 2 · 2021-04-08T17:19:41.533

You can use left_join from the package dplyr first joining the first two df`s, then joining that result with the third df:

library(dplyr)    
df_12 <- left_join(df1,df2, by = "ID")
df_123 <- left_join(df_12, df3, by = "ID")

Result:

df_123
  ID Amount_month1 Amount_month2 Amount_month3
1  1           100            NA            NA
2  2           200            50            NA
3  3           300           177           666
4  4           400            NA            77

Mock data:

df1 <- data.frame(
  ID = as.character(1:4),
  Amount_month1 = c(100,200,300,400)
)
df2 <- data.frame(
  ID = as.character(2:3),
  Amount_month2 = c(50,177)
)
df3 <- data.frame(
  ID = as.character(3:4),
  Amount_month3 = c(666,77)
)

How to join dataframes by ID column?

2 Answers2