CODEWITHSUNDEEP
rdplyr
Tom
Tom

Reputation: 199

Join dataframe which share column names and some character strings

I have what appears to be a simple join or merge of dataframes. Both dataframes share columns and some rows.

When using full_join the columns are matched but the shared rows are not being joined.

I have the following dummy code:

a<- c('x1', 'x2',   'x3',   'x4')
b<- c(12,   18, 11, 14)
c<- c(15,   23, 40, 43)
d<- c(0.2,  0.3,    NA, NA)

n1<-data.frame(a,b,c,d)

a<- c('x3', 'x4')
d<- c(1.1,  1.0)

n2<-data.frame(a,d)

n3<-full_join(n1,n2)

I got the following:

a   b   c   d
x1  12  15  0.2
x2  18  23  0.3
x3  11  40  NA
x4  14  43  NA
x3  NA  NA  1.1
x4  NA  NA  1

But am hoping for:

a   b   c   d
x1  12  15  0.2
x2  18  23  0.3
x3  11  40  1.1
x4  14  43  1

Any help will be appreciated.

Upvotes: 2

Views: 41

Answers (3)

akrun
akrun

Reputation: 887851

We can do this easily with data.table join without creating addtional columns and then modifying/removing

library(data.table)
setDT(n1)[n2, d := i.d, on = .(a)]
n1
#    a  b  c   d
#1: x1 12 15 0.2
#2: x2 18 23 0.3
#3: x3 11 40 1.1
#4: x4 14 43 1.0

Upvotes: 0

boski
boski

Reputation: 2467

A base R option would be to match row and column names.

mrow = match(n1$a,n2$a)
mcol = match(colnames(n1),colnames(n2))

# [-1] removes the x1,x2 .. column
n1[!is.na(mrow),which(!is.na(mcol))[-1]] = n2[na.omit(mrow),na.omit(mcol)[-1]]
> n1
   a  b  c   d
1 x1 12 15 0.2
2 x2 18 23 0.3
3 x3 11 40 1.1
4 x4 14 43 1.0

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 389235

Currently, the full_join is done using two columns "a" and "d" since they are the common columns between n1 and n2. You might need to join only by "a" and then use coalesce.

library(dplyr)

full_join(n1, n2, by = "a") %>%
   mutate(d = coalesce(d.x, d.y)) %>%
   select(-d.x, -d.y)

#   a  b  c   d
#1 x1 12 15 0.2
#2 x2 18 23 0.3
#3 x3 11 40 1.1
#4 x4 14 43 1.0

Upvotes: 0

Related Questions