Forum,
with the datasets a and b below, I'm trying to establish a relationship
based on the common column "week" and insert the value from the column
weekAvg in b to the column weekAvg in a. The dataset a is several thousand
lines long. I've tried looking at 'match', writing functions,
'rbind.fill'
and various search terms in this forum to no avail. Thanks...
a<-read.table(textConnection("row ID date Reading WellID week weekAvg
1 Well_80-2 6/12/2000 2:00 -202.034 80-2 0 NA
2 Well_80-2 6/12/2000 3:00 -201.018 80-2 0 NA
3 Well_80-2 6/12/2000 4:00 -199.494 80-2 0 NA
4 Well_80-2 6/12/2000 5:00 -197.716 80-2 0 NA
5 Well_80-2 6/12/2000 6:00 -190.858 80-2 0 NA
6 Well_80-2 6/12/2000 7:00 -181.460 80-2 0 NA
7 Well_80-2 6/19/2000 10:00 -166.728 80-2 1 NA
8 Well_80-2 6/19/2000 11:00 -167.490 80-2 1 NA
9 Well_80-2 6/19/2000 12:00 -167.490 80-2 1 NA
10 Well_80-2 6/19/2000 13:00 -167.490 80-2 1 NA
11 Well_80-2 6/19/2000 14:00 -168.506 80-2 1 NA
12 Well_80-2 6/19/2000 15:00 -168.506 80-2 1 NA"),header=T)
closeAllConnections()
b<-read.table(textConnection("week weekAvg
0 -147.3726
1 -181.3429
2 -151.7208
3 -188.8653
4 -163.7465
5 -161.6873
6 -158.5168
7 -146.6136
8 -175.4351
9 -100.9450
10 -151.3655
11 -125.8975
12 -162.5993"),header=T)
closeAllConnections()
--
View this message in context:
http://r.789695.n4.nabble.com/insert-values-based-on-common-ID-tp2216735p2216735.html
Sent from the R help mailing list archive at Nabble.com.
On May 14, 2010, at 10:15 AM, emorway wrote:> > Forum, > > with the datasets a and b below, I'm trying to establish a relationship > based on the common column "week" and insert the value from the column > weekAvg in b to the column weekAvg in a. The dataset a is several thousand > lines long. I've tried looking at 'match', writing functions, 'rbind.fill' > and various search terms in this forum to no avail. Thanks... > > a<-read.table(textConnection("row ID date Reading WellID week weekAvg > 1 Well_80-2 6/12/2000 2:00 -202.034 80-2 0 NA > 2 Well_80-2 6/12/2000 3:00 -201.018 80-2 0 NA > 3 Well_80-2 6/12/2000 4:00 -199.494 80-2 0 NA > 4 Well_80-2 6/12/2000 5:00 -197.716 80-2 0 NA > 5 Well_80-2 6/12/2000 6:00 -190.858 80-2 0 NA > 6 Well_80-2 6/12/2000 7:00 -181.460 80-2 0 NA > 7 Well_80-2 6/19/2000 10:00 -166.728 80-2 1 NA > 8 Well_80-2 6/19/2000 11:00 -167.490 80-2 1 NA > 9 Well_80-2 6/19/2000 12:00 -167.490 80-2 1 NA > 10 Well_80-2 6/19/2000 13:00 -167.490 80-2 1 NA > 11 Well_80-2 6/19/2000 14:00 -168.506 80-2 1 NA > 12 Well_80-2 6/19/2000 15:00 -168.506 80-2 1 NA"),header=T) > closeAllConnections() > > b<-read.table(textConnection("week weekAvg > 0 -147.3726 > 1 -181.3429 > 2 -151.7208 > 3 -188.8653 > 4 -163.7465 > 5 -161.6873 > 6 -158.5168 > 7 -146.6136 > 8 -175.4351 > 9 -100.9450 > 10 -151.3655 > 11 -125.8975 > 12 -162.5993"),header=T) > closeAllConnections()See ?merge and ?subset merge() performs a SQL-like 'join' operation.> merge(subset(a, select = -weekAvg), b, by = "week", all.x = TRUE)week row ID date Reading WellID weekAvg 1 0 Well_80-2 6/12/2000 2:00 -202.034 80-2 -147.3726 2 0 Well_80-2 6/12/2000 3:00 -201.018 80-2 -147.3726 3 0 Well_80-2 6/12/2000 4:00 -199.494 80-2 -147.3726 4 0 Well_80-2 6/12/2000 5:00 -197.716 80-2 -147.3726 5 0 Well_80-2 6/12/2000 6:00 -190.858 80-2 -147.3726 6 0 Well_80-2 6/12/2000 7:00 -181.460 80-2 -147.3726 7 1 Well_80-2 6/19/2000 10:00 -166.728 80-2 -181.3429 8 1 Well_80-2 6/19/2000 11:00 -167.490 80-2 -181.3429 9 1 Well_80-2 6/19/2000 12:00 -167.490 80-2 -181.3429 10 1 Well_80-2 6/19/2000 13:00 -167.490 80-2 -181.3429 11 1 Well_80-2 6/19/2000 14:00 -168.506 80-2 -181.3429 12 1 Well_80-2 6/19/2000 15:00 -168.506 80-2 -181.3429 In the above, I am using subset() on 'a' to remove the pre-existing 'weekAvg' column, so that you only end up with one such column post merge. If you don't do this, you will have a 'weekAvg.x' and 'weekAvg.y' in the result. The two data frames are then merge()d on the common 'week' column. The 'all.x = TRUE', retains all rows in 'a' that match to the 'week' value in 'b'. Otherwise known as a 'left outer join'. HTH, Marc Schwartz
Hello, Good reproducible example. Is > merge(a[names(a) != "weekAvg"], b) what you want? emorway wrote:> Forum, > > with the datasets a and b below, I'm trying to establish a relationship > based on the common column "week" and insert the value from the column > weekAvg in b to the column weekAvg in a. The dataset a is several thousand > lines long. I've tried looking at 'match', writing functions, 'rbind.fill' > and various search terms in this forum to no avail. Thanks... > > a<-read.table(textConnection("row ID date Reading WellID week weekAvg > 1 Well_80-2 6/12/2000 2:00 -202.034 80-2 0 NA > 2 Well_80-2 6/12/2000 3:00 -201.018 80-2 0 NA > 3 Well_80-2 6/12/2000 4:00 -199.494 80-2 0 NA > 4 Well_80-2 6/12/2000 5:00 -197.716 80-2 0 NA > 5 Well_80-2 6/12/2000 6:00 -190.858 80-2 0 NA > 6 Well_80-2 6/12/2000 7:00 -181.460 80-2 0 NA > 7 Well_80-2 6/19/2000 10:00 -166.728 80-2 1 NA > 8 Well_80-2 6/19/2000 11:00 -167.490 80-2 1 NA > 9 Well_80-2 6/19/2000 12:00 -167.490 80-2 1 NA > 10 Well_80-2 6/19/2000 13:00 -167.490 80-2 1 NA > 11 Well_80-2 6/19/2000 14:00 -168.506 80-2 1 NA > 12 Well_80-2 6/19/2000 15:00 -168.506 80-2 1 NA"),header=T) > closeAllConnections() > > b<-read.table(textConnection("week weekAvg > 0 -147.3726 > 1 -181.3429 > 2 -151.7208 > 3 -188.8653 > 4 -163.7465 > 5 -161.6873 > 6 -158.5168 > 7 -146.6136 > 8 -175.4351 > 9 -100.9450 > 10 -151.3655 > 11 -125.8975 > 12 -162.5993"),header=T) > closeAllConnections() > >