Merge by row number in R

Question

I am trying to merge two data frames, dat1 and dat2.

dat1 is an n x 65 data frame, whose rownames correspond to specific values in dat2. Here's a smaller, toy example:

 year <- c(1998, 2001, 2002, 2004, 2008)
 make <- c("Ford", "Dodge", "Toyota", "Tesla", "Jeep") 
 model <- c("Escape", "Ram", "Camry", "Model S", "Wrangler")
 price <- c(4750.00, 14567.00, 20000.00, 60123.00, 18469.00) 
 dat1 <- data.frame(year, make, model, price)

dat2 is an nx1 vector with a single column called rownumber. It contains row numbers from dat1 which are of interest. Here's an example:

dat2 <- as.data.frame(c(12, 45, 56, 123))
colnames(dat1)[1] <- "rownumber"

I attempt to merge both data frames as follows:

dat3 <- merge(dat1, dat2, by = "row.names", all.x = TRUE, all.y = FALSE)

The problem is that dat3 contains two columns, row.names and rownumber which do not match. For example, in row one of dat3, row.names = 1, but rownumber = 777.

Any thoughts on how to resolve this? Thanks in advance.

Sure, @RichardScriven: I would like `dat3` to be an n x 65 data frame where row one corresponds `dat1` row 12, row 2 corresponds to `dat1` row 45, row 3 corresponds to `dat1` row 56, etc. — Austin, Sep 22 '15 at 21:07
You reference `row.names` and `rownames`, but your `dat1` has neither a column with either of those names nor does it have `row.names` attribute defined. I would recommend explicitly adding a column to `dat1`. Also, with `merge` if the column names to match on are not the same you'll need to specify, e.g., `by.x = "rownames", by.y = "rownmber"`. — Gregor Thomas, Sep 22 '15 at 21:10
in your example your dat2 is an 4 x 1 df and dat1 is a 5 x 5 df; presumably you've missed one number out in dat2. my understanding of your problem is that you just wand a row permutation of dat1? if so, something like aashanand's answer below should do the job — stas g, Sep 22 '15 at 21:25

aashanand · Accepted Answer · 2015-09-22T21:27:29.603

Say you have a data.frame object called dat1 as defined below:

> year <- c(1998, 2001, 2002, 2004, 2008)
> make <- c("Ford", "Dodge", "Toyota", "Tesla", "Jeep") 
> model <- c("Escape", "Ram", "Camry", "Model S", "Wrangler")
> price <- c(4750.00, 14567.00, 20000.00, 60123.00, 18469.00) 
> dat1 <- data.frame(year, make, model, price)

Say you have a vector rownumbers that defines rows of interest from dat1.

> rownumbers <- c(2,5)

Your question does not state the desired results but assuming you want to subset rows of dat1 such that their row numbers are in the vector rownumbers, one simple way to do this is:

> dat1[rownumbers,] # Don't forget the comma
  year  make    model price
2 2001 Dodge      Ram 14567
5 2008  Jeep Wrangler 18469

Edit: you can assign your subset to a new variable dat3 if you'd like.

> dat3 <- dat1[rownumbers,]

score 2 · Answer 2 · answered Sep 22 '15 at 21:40

2

Thanks, all for the quick responses, and especially @aashanand for the elegant solution. Here's the quick way:

dat3 <- dat1[dat2$rownumbers, ]

answered Sep 22 '15 at 21:40

Austin

121
1
3
13

Merge by row number in R

2 Answers2

Linked