CODEWITHSUNDEEP
r
Donald Harding
Donald Harding

Reputation: 3

Matching row entries with column names in R

I'm new to R and are trying to combine a couple of datasets into one. I have the following structure of my data:

opt <- data.frame( name=c("opt1", "opt2","opt3"), week=c(1,1,1,2,2,3), price=c(0))

price <- data.frame( week=c(1,2,3), opt1=c(3, 4,3.15), opt2=c(4.2, 3.5, 5), opt3=c(3,2,6))

I now want to extract the the numbers in "data.frame price" if the entries in row opt$name matches the column names in "data.frame price" and opt$week==price$week.

The next step is to add the selected number to the opt$price column. To create a new data.frame that looks like this:

optcomp <- data.frame( name=c("opt1", "opt2","opt3"), week=c(1,1,1,2,2,3), price=c(3.00,4.2,3,4.00,3.5,6))

I have tried to construct some loops but my skills in R is to limited.

Any help would be greatly appreciated!

Donald

Upvotes: 0

Views: 3659

Answers (1)

Matthew Lundberg
Matthew Lundberg

Reputation: 42629

Initial merge, to match the week column:

x <- merge(opt,price)

x
##   week name price opt1 opt2 opt3
## 1    1 opt1     0 3.00  4.2    3
## 2    1 opt2     0 3.00  4.2    3
## 3    1 opt3     0 3.00  4.2    3
## 4    2 opt1     0 4.00  3.5    2
## 5    2 opt2     0 4.00  3.5    2
## 6    3 opt3     0 3.15  5.0    6

The values that you want:

sapply(seq(nrow(x)), function(i) x[i,as.character(x$name[i])])
[1] 3.0 4.2 3.0 4.0 3.5 6.0

Specifying the row names of x as character allows matrix indexing by name (and returns character)

rownames(x) <- as.character(rownames(x))
x.ind <- matrix(c(rownames(x), as.character(x$name)),,2)
x[x.ind]
## [1] "3.00" "4.2"  "3"    "4.00" "3.5"  "6"

Upvotes: 1

Related Questions