0

I am having problem on merging multiple dataframes in R, like:

table1:
NAME A1
a 1
b 2
c 3

table2:
NAME A2
a 10
b 20
d 30

table3:
NAME A3
a 100
b 200
e 400

I would like to merge these tables by "NAME", adding the new values and assign "0" to missing value, like:

merged table:
NAME A1 A2 A3
a 1 10 100
b 2 20 200
c 3 0 0
d 0 30 0
e 0 0 400

Any suggestion will be help!! Thanks a lot!!!

Lennon Lee
  • 194
  • 1
  • 14
  • I think this should be helpful - https://stackoverflow.com/questions/8091303/simultaneously-merge-multiple-data-frames-in-a-list You want to put each of your tables into a list and then keep `merge`ing them together. – thelatemail Nov 05 '18 at 05:39
  • thank you very much – Lennon Lee Nov 05 '18 at 11:44

3 Answers3

1

You can use reshape

library(reshape)
data <- merge_recurse(list(table1, table2, table3))

Missing values would be NAs. Replacing NAs with 0

data[is.na(data)] <- 0

enter image description here

Ashok KS
  • 659
  • 5
  • 21
1

if u wanna try without package and normal nested merge function it can be cone as :

Data :
    table1<-data.frame(NAME=c("a","b","c"), A1=c(1,2,3))
    table2<-data.frame(NAME=c("a","b","d"),A2=c(10,20,30))
    table3<-data.frame(NAME=c("a","b","e"),A3=c(100,200,400))


Code:
final_data<-merge( merge( table1, table2, by = "NAME", all = TRUE ), table3, 
       by = "NAME", all = TRUE )

OUTPUT:

 NAME A1 A2  A3
1    a  1 10 100
2    b  2 20 200
3    c  3 NA  NA
4    d NA 30  NA
5    e NA NA 400

not replace na with 0 using simple syntax

final_data[is.na(final_data)]<-"0"

OUTPUT:

  NAME A1 A2  A3
1    a  1 10 100
2    b  2 20 200
3    c  3  0   0
4    d  0 30   0
5    e  0  0 400

@Ashok KS has mentioned some code and it can also be written as

data<- reshape::merge_recurse(list(table1, table2,table3))

data[is.na(data)]<-0
sai saran
  • 737
  • 9
  • 32
1

Using tidyverse api

library(tidyverse)

df1 <- tribble(
    ~Name, ~A1,
    "a", 1,
    "b", 2,
    "c", 3
)

df2 <- tribble(
    ~Name, ~A2,
    "a", 10,
    "b", 20,
    "d", 30
)

df3 <- tribble(
    ~Name, ~A3,
    "a", 100,
    "b", 200,
    "e", 400
)


list(df1, df2, df3) %>% 
    reduce(full_join, by = "Name") %>% 
    replace(is.na(.), 0)
Vasim
  • 3,052
  • 3
  • 35
  • 56