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!!!
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)
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
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
mergeing them together.