CODEWITHSUNDEEP
rreplacematch
ToNoY
ToNoY

Reputation: 1378

Making a new column based on matching two column IDs within the same dataframe in R

I'm trying to match one column with another within the same data frame. It looks like:

vl<-read.csv("/Users/ToNoY/Documents/R/data.csv",
             stringsAsFactors=FALSE,header=TRUE)

>head(vl)

       old_ID  Sample_Date  DOC
1 001-2009-II 054-2009-II  1.44 
2 002-2009-II 030-2009-II  1.55  
3 005-2009-II 031-2009-II    NA  
4 006-2009-II 005-2009-II  1.89  
5 030-2009-II 004-2009-II  2.03  
6 054-2009-II 002-2009-II  1.43  
... ...

I want to make a NEW column where 'DOC' column values will be plugged in by matching 'old_ID' with 'Sample_Date'. In other words, the above column should look like:

       old_ID  Sample_Date  DOC  New
1 001-2009-II 054-2009-II  1.44   NA
2 002-2009-II 030-2009-II  1.55   NA
3 005-2009-II 031-2009-II    NA 1.89
4 006-2009-II 005-2009-II  1.89   NA
5 030-2009-II 004-2009-II  2.03 1.55
6 054-2009-II 002-2009-II  1.43 1.44
... ...

Upvotes: 0

Views: 81

Answers (2)

Tyler Rinker
Tyler Rinker

Reputation: 109874

## df <-  read.table(text="      old_ID  Sample_Date  DOC
## 1 001-2009-II 054-2009-II  1.44 
## 2 002-2009-II 030-2009-II  1.55  
## 3 005-2009-II 031-2009-II    NA  
## 4 006-2009-II 005-2009-II  1.89  
## 5 030-2009-II 004-2009-II  2.03  
## 6 054-2009-II 002-2009-II  1.43 ", header=TRUE)

library(qdap)
df[, "NEW"] <- df[, 1] %l% df[, -1]
df

##        old_ID Sample_Date  DOC  NEW
## 1 001-2009-II 054-2009-II 1.44   NA
## 2 002-2009-II 030-2009-II 1.55 1.43
## 3 005-2009-II 031-2009-II   NA 1.89
## 4 006-2009-II 005-2009-II 1.89   NA
## 5 030-2009-II 004-2009-II 2.03 1.55
## 6 054-2009-II 002-2009-II 1.43 1.44

This is rather fast (particularly on larger datasets) and requires little coding.

Upvotes: 1

josliber
josliber

Reputation: 44330

What you're describing is a database merge (specifically a left outer join). This can be accomplished with the merge function:

df = data.frame(old_ID=c("001-2009-II", "002-2009-II", "005-2009-II", "006-2009-II", "030-2009-II", "054-2009-II"),
                Sample_Date=c("054-2009-II", "030-2009-II", "031-2009-II", "005-2009-II", "004-2009-II", "002-2009-II"),
                DOC=c(1.44, 1.55, NA, 1.89, 2.03, 1.43))
merged = merge(df, df, by.x="old_ID", by.y="Sample_Date", all.x=TRUE)
new.df = data.frame(old_ID=merged$old_ID, Sample_Date=merged$Sample_Date,
                    DOC=merged$DOC.x, New=merged$DOC.y)
new.df
#        old_ID Sample_Date  DOC  New
# 1 001-2009-II 054-2009-II 1.44   NA
# 2 002-2009-II 030-2009-II 1.55 1.43
# 3 005-2009-II 031-2009-II   NA 1.89
# 4 006-2009-II 005-2009-II 1.89   NA
# 5 030-2009-II 004-2009-II 2.03 1.55
# 6 054-2009-II 002-2009-II 1.43 1.44

Upvotes: 1

Related Questions