CODEWITHSUNDEEP
rdataframe
bill999
bill999

Reputation: 2527

data.frame matching

I have a simple R question. I have two data frames. The first contains all of my possible years. I assign NA to the second column. The second data frame has only a subset of the possible years, but an actual value for the second column. I want to combine the two data frames. More specifically, I want to match them by year and if the second has the correct year, to replace the NA in the first with the value of the second.

Here is example code. 

one <- as.data.frame(matrix(1880:1890, ncol=2, nrow=11))
one[,2] <- NA
two <- data.frame(ncol=2, nrow=3)
two[1,] <- c(1880, "a")
two[2,] <- c(1887, "b")
two[3,] <- c(1889, "c")

I want to get the first row, second column of one to have value "a," the eighth row, second column to be "b," and the tenth row, second column to be "c."

Feel free to make the above code more elegant.

One thing I tried as a preliminary step, but it gave a little weird result was:

one[,1]==two[,1] -> test

But test only contains values 1880 and 1887...

Upvotes: 1

Views: 430

Answers (3)

vitale232
vitale232

Reputation: 615

I like to use merge for these types of problems. It's pretty straightforward in my opinion. Check out the help article ?merge

three <- merge(one, two, by.x = 'V1', by.y = 'ncol', all = T)

Upvotes: 2

mrip
mrip

Reputation: 15163

one[match(two[,1],one[,1]),2]<-two[,2]

That should give you what you are looking for:

> one
     V1   V2
1  1880    a
2  1881 <NA>
3  1882 <NA>
4  1883 <NA>
5  1884 <NA>
6  1885 <NA>
7  1886 <NA>
8  1887    b
9  1888 <NA>
10 1889    c
11 1890 <NA>

Upvotes: 3

Tyler Rinker
Tyler Rinker

Reputation: 109844

Here's one approach (merge is another):

library(qdap)
one[, 2] <- lookup(one[, 1], two)
one

##      V1   V2
## 1  1880    a
## 2  1881 <NA>
## 3  1882 <NA>
## 4  1883 <NA>
## 5  1884 <NA>
## 6  1885 <NA>
## 7  1886 <NA>
## 8  1887    b
## 9  1888 <NA>
## 10 1889    c
## 11 1890 <NA>

Upvotes: 1

Related Questions