CODEWITHSUNDEEP
rdataframe
struggleBus
struggleBus

Reputation: 365

Adding values from one data.frame to another based on 2 matching column values

I have two data frames with the same 3 columns: WeekNum, Year and Number.

A <- data.frame(WeekNum=c(1,2,3,4,5,1,2,3,4,5), 
                Year=c(2000,2000,2000,2000,2000,2001,2001,2001,2001,2001), 
                Number=c(0,0,0,0,0,0,0,0,0,0))

B <- data.frame(WeekNum=c(1,2,3,4,1,2,6), 
                Year=c(2000,2000,2000,2000,2001,2001,2001), 
                Number=c(0,1,0,1,2,5,6))

I want to create a new data frame with the same 3 columns using all WeekNum and Year combinations from A (and only those from B that are also present in A). When a WeekNum and Year combination is also present in B, I want to use the Number value from B. If the combination is not present in B, I want to leave the Number value as 0. Ultimately, I should have a data frame that looks like:

> C
   WeekNum Year Number
1        1 2000      0
2        2 2000      1
3        3 2000      0
4        4 2000      1
5        5 2000      0
6        1 2001      2
7        2 2001      5
8        3 2001      0
9        4 2001      0
10       5 2001      0

Upvotes: 2

Views: 3082

Answers (2)

thelatemail
thelatemail

Reputation: 93938

A variation on the answer from @Arun previously:

Bind the data together as required:

result <- rbind(
 A[!(paste(A$WeekNum, A$Year, sep=":") %in% paste(B$WeekNum, B$Year, sep=":")),],
 B[paste(B$WeekNum, B$Year, sep=":")   %in% paste(A$WeekNum, A$Year, sep=":"),]
)

Sorting to get the order right:

result <- result[order(result$Year,result$WeekNum),]

    WeekNum Year Number
51        1 2000      0
6         2 2000      1
7         3 2000      0
81        4 2000      1
5         5 2000      0
91        1 2001      2
101       2 2001      5
8         3 2001      0
9         4 2001      0
10        5 2001      0

Upvotes: 0

Arun
Arun

Reputation: 118889

A easy way is to create an id column for both data.frames A and B and then use match:

A$id <- paste(A$WeekNum, A$Year, sep=":")
B$id <- paste(B$WeekNum, B$Year, sep=":")

m1 <- match(A$id, B$id)
m2 <- which(!is.na(m1))
A$Number[m2] <- B$Number[m1[!is.na(m1)]]
subset(A, select=-c(id))

output: (Edit: I see you changed your input data.frame)

   WeekNum Year Number
1        1 2000      0
2        2 2000      1
3        3 2000      0
4        4 2000      1
5        5 2000      0
6        1 2001      2
7        2 2001      5
8        3 2001      0
9        4 2001      0
10       5 2001      0

Upvotes: 4

Related Questions