CODEWITHSUNDEEP
r
Rajarshi Bhadra
Rajarshi Bhadra

Reputation: 1944

Binding dataframes by multiple conditions in R

I have a data frame which looks like this:

> data
  Class Number
1     A      1
2     A      2
3     A      3
4     B      1
5     B      2
6     B      3
7     C      1
8     C      2
9     C      3

I have a reference data frame which is:

> reference
  Class Number Value
1     A      1   0.5
2     B      3   0.3

I want to join these data frames to create a single data frame:

> resultdata
  Class Number Value
1     A      1   0.5
2     A      2   0.0
3     A      3   0.0
4     B      1   0.0
5     B      2   0.0
6     B      3   0.3
7     C      1   0.0
8     C      2   0.0
9     C      3   0.0

How can I achieve this? Any help will be greatly appreciated

Upvotes: 0

Views: 58

Answers (3)

Steven Beaupré
Steven Beaupré

Reputation: 21641

You can do:

library(dplyr)
left_join(data, reference) %>% (function(x) { x[is.na(x)] <- 0; x })

Or (as per @akrun suggestion):

left_join(data, reference) %>% mutate(Value = replace(Value, is.na(Value), 0))

Which gives:

#  Class Number Value
#1     A      1   0.5
#2     A      2   0.0
#3     A      3   0.0
#4     B      1   0.0
#5     B      2   0.0
#6     B      3   0.3
#7     C      1   0.0
#8     C      2   0.0
#9     C      3   0.0

Upvotes: 2

akrun
akrun

Reputation: 887521

You can do

library(data.table)
setkey(setDT(reference), Class, Number)[data]

Or

setkey(setDT(data), Class, Number)[reference, 
              Value:= i.Value][is.na(Value), Value:=0]
#    Class Number Value
#1:     A      1   0.5
#2:     A      2   0.0
#3:     A      3   0.0
#4:     B      1   0.0
#5:     B      2   0.0
#6:     B      3   0.3
#7:     C      1   0.0
#8:     C      2   0.0
#9:     C      3   0.0

Upvotes: 2

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193637

The basic starting point for this would be merge. 

merge(data, reference, all = TRUE)
#   Class Number Value
# 1     A      1   0.5
# 2     A      2    NA
# 3     A      3    NA
# 4     B      1    NA
# 5     B      2    NA
# 6     B      3   0.3
# 7     C      1    NA
# 8     C      2    NA
# 9     C      3    NA

There are many questions which show how to replace NA with 0.

Upvotes: 2

Related Questions