Bhaskar Mitra
2017-Feb-12 00:13 UTC
[R] Query - Merging and conditional replacement of values in a data frame
Hello Everyone, I have two data frames df1 and df2 as shown below. They are of different length. However, they have one common column - time. df1 <- time v1 v2 v3 1 2 3 4 2 5 6 4 3 1 3 4 4 1 3 4 5 2 3 4 6 2 3 4 df2 <- time v11 v12 v13 3 112 3 4 4 112 3 4 By matching the 'time' column in df1 and df2, I am trying to modify column 'v1' in df1 by replacing it with values in column 'v11' in df2. The modified df1 should look something like this: df1 <- time v1 v2 v3 1 2 3 4 2 5 6 4 3 112 3 4 4 112 3 4 5 2 3 4 6 2 3 4 I tried to use the 'merge' function to combine df1 and df2 followed by the conditional 'ifelse' statement. However, that doesn't seem to work. Can I replace the values in df1 by not merging the two data frames? Thanks for your help, Regards, Bhaskar [[alternative HTML version deleted]]
Bert Gunter
2017-Feb-12 02:43 UTC
[R] Query - Merging and conditional replacement of values in a data frame
Your "assignments" (<-) are not legitimate R code that can be cut and pasted. Learn to use dput() to provide examples that we can use. You fail to say whether the time column of df2 is a proper subset of df1 or may contain times not in df1. I shall assume the latter. You also did not say whether the time values occur in order in both data frames. I shall assume they do not. If I understand correctly,then, match and subscripting will do it, something like> df1 <-data.frame(time = 1:6, v1 = c(2,5,1,1,2,2)) > df2 <- data.frame(time = 4:3,v11 = c(112,113))> wm <- match(df1$time,df2$time) > df1[!is.na(wm),"v1"] <- df2[na.omit(wm),"v11"]> df1time v1 1 1 2 2 2 5 3 3 113 4 4 112 5 5 2 6 6 2 Cheers, Bert Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Sat, Feb 11, 2017 at 4:13 PM, Bhaskar Mitra <bhaskar.kolkata at gmail.com> wrote:> Hello Everyone, > > I have two data frames df1 and df2 as shown below. They > are of different length. However, they have one common column - time. > > df1 <- > time v1 v2 v3 > 1 2 3 4 > 2 5 6 4 > 3 1 3 4 > 4 1 3 4 > 5 2 3 4 > 6 2 3 4 > > > df2 <- > time v11 v12 v13 > 3 112 3 4 > 4 112 3 4 > > By matching the 'time' column in df1 and df2, I am trying to modify column > 'v1' in df1 by replacing it > with values in column 'v11' in df2. The modified df1 should look something > like this: > > df1 <- > time v1 v2 v3 > 1 2 3 4 > 2 5 6 4 > 3 112 3 4 > 4 112 3 4 > 5 2 3 4 > 6 2 3 4 > > I tried to use the 'merge' function to combine df1 and df2 followed by > the conditional 'ifelse' statement. However, that doesn't seem to work. > > Can I replace the values in df1 by not merging the two data frames? > > Thanks for your help, > > Regards, > Bhaskar > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
Jeff Newmiller
2017-Feb-12 05:49 UTC
[R] Query - Merging and conditional replacement of values in a data frame
Or use rownames and subscripting? df1 <- read.table( text"time v1 v2 v3 1 2 3 4 2 5 6 4 3 1 3 4 4 1 3 4 5 2 3 4 6 2 3 4 ",header=TRUE) df2 <- read.table( text"time v11 v12 v13 3 112 3 4 4 112 3 4 ",header=TRUE) df3 <- df1 rownames( df3 ) <- df3$time df3[ as.character( df2$time ), "v1" ] <- df2[ , "v11" ] df3 df3[ "7", c( "time", "v1" ) ] <- data.frame( time=7, v1=2 ) df3 df2b <- data.frame( time=c(7,8), v2=c(4,5), v3=c(6,7) ) df2b df3[ df2b$time, c( "time", "v2", "v3" ) ] <- df2b df3 On Sat, 11 Feb 2017, Bert Gunter wrote:> Your "assignments" (<-) are not legitimate R code that can be cut and > pasted. Learn to use dput() to provide examples that we can use. > > You fail to say whether the time column of df2 is a proper subset of > df1 or may contain times not in df1. I shall assume the latter. You > also did not say whether the time values occur in order in both data > frames. I shall assume they do not. > > If I understand correctly,then, match and subscripting will do it, > something like > > >> df1 <-data.frame(time = 1:6, v1 = c(2,5,1,1,2,2)) >> df2 <- data.frame(time = 4:3,v11 = c(112,113)) > >> wm <- match(df1$time,df2$time) >> df1[!is.na(wm),"v1"] <- df2[na.omit(wm),"v11"] > >> df1 > > time v1 > 1 1 2 > 2 2 5 > 3 3 113 > 4 4 112 > 5 5 2 > 6 6 2 > > Cheers, > Bert > > > > > > Bert Gunter > > "The trouble with having an open mind is that people keep coming along > and sticking things into it." > -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) > > > On Sat, Feb 11, 2017 at 4:13 PM, Bhaskar Mitra > <bhaskar.kolkata at gmail.com> wrote: >> Hello Everyone, >> >> I have two data frames df1 and df2 as shown below. They >> are of different length. However, they have one common column - time. >> >> df1 <- >> time v1 v2 v3 >> 1 2 3 4 >> 2 5 6 4 >> 3 1 3 4 >> 4 1 3 4 >> 5 2 3 4 >> 6 2 3 4 >> >> >> df2 <- >> time v11 v12 v13 >> 3 112 3 4 >> 4 112 3 4 >> >> By matching the 'time' column in df1 and df2, I am trying to modify column >> 'v1' in df1 by replacing it >> with values in column 'v11' in df2. The modified df1 should look something >> like this: >> >> df1 <- >> time v1 v2 v3 >> 1 2 3 4 >> 2 5 6 4 >> 3 112 3 4 >> 4 112 3 4 >> 5 2 3 4 >> 6 2 3 4 >> >> I tried to use the 'merge' function to combine df1 and df2 followed by >> the conditional 'ifelse' statement. However, that doesn't seem to work. >> >> Can I replace the values in df1 by not merging the two data frames? >> >> Thanks for your help, >> >> Regards, >> Bhaskar >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >--------------------------------------------------------------------------- Jeff Newmiller The ..... ..... Go Live... DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go... Live: OO#.. Dead: OO#.. Playing Research Engineer (Solar/Batteries O.O#. #.O#. with /Software/Embedded Controllers) .OO#. .OO#. rocks...1k
Jim Lemon
2017-Feb-12 09:35 UTC
[R] Query - Merging and conditional replacement of values in a data frame
Hi Bhaskar,
Maybe:
df1 <-read.table(text="time v1 v2 v3
1 2 3 4
2 5 6 4
3 1 3 4
4 1 3 4
5 2 3 4
6 2 3 4",
header=TRUE)
df2 <-read.table(text="time v11 v12 v13
3 112 3 4
4 112 3 4",
header=TRUE)
for(time1 in df1$time) {
time2<-which(df2$time==time1)
if(length(time2)) df1[df1$time==time1,]<-df2[time2,]
}
Jim
On Sun, Feb 12, 2017 at 11:13 AM, Bhaskar Mitra
<bhaskar.kolkata at gmail.com> wrote:> Hello Everyone,
>
> I have two data frames df1 and df2 as shown below. They
> are of different length. However, they have one common column - time.
>
> df1 <-
> time v1 v2 v3
> 1 2 3 4
> 2 5 6 4
> 3 1 3 4
> 4 1 3 4
> 5 2 3 4
> 6 2 3 4
>
>
> df2 <-
> time v11 v12 v13
> 3 112 3 4
> 4 112 3 4
>
> By matching the 'time' column in df1 and df2, I am trying to modify
column
> 'v1' in df1 by replacing it
> with values in column 'v11' in df2. The modified df1 should look
something
> like this:
>
> df1 <-
> time v1 v2 v3
> 1 2 3 4
> 2 5 6 4
> 3 112 3 4
> 4 112 3 4
> 5 2 3 4
> 6 2 3 4
>
> I tried to use the 'merge' function to combine df1 and df2 followed
by
> the conditional 'ifelse' statement. However, that doesn't seem
to work.
>
> Can I replace the values in df1 by not merging the two data frames?
>
> Thanks for your help,
>
> Regards,
> Bhaskar
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
Bhaskar Mitra
2017-Feb-12 19:02 UTC
[R] Query - Merging and conditional replacement of values in a data frame
Thanks for all your help. This is helpful. Best, Bhaskar On Sun, Feb 12, 2017 at 4:35 AM, Jim Lemon <drjimlemon at gmail.com> wrote:> Hi Bhaskar, > Maybe: > > df1 <-read.table(text="time v1 v2 v3 > 1 2 3 4 > 2 5 6 4 > 3 1 3 4 > 4 1 3 4 > 5 2 3 4 > 6 2 3 4", > header=TRUE) > > > df2 <-read.table(text="time v11 v12 v13 > 3 112 3 4 > 4 112 3 4", > header=TRUE) > > for(time1 in df1$time) { > time2<-which(df2$time==time1) > if(length(time2)) df1[df1$time==time1,]<-df2[time2,] > } > > Jim > > > On Sun, Feb 12, 2017 at 11:13 AM, Bhaskar Mitra > <bhaskar.kolkata at gmail.com> wrote: > > Hello Everyone, > > > > I have two data frames df1 and df2 as shown below. They > > are of different length. However, they have one common column - time. > > > > df1 <- > > time v1 v2 v3 > > 1 2 3 4 > > 2 5 6 4 > > 3 1 3 4 > > 4 1 3 4 > > 5 2 3 4 > > 6 2 3 4 > > > > > > df2 <- > > time v11 v12 v13 > > 3 112 3 4 > > 4 112 3 4 > > > > By matching the 'time' column in df1 and df2, I am trying to modify > column > > 'v1' in df1 by replacing it > > with values in column 'v11' in df2. The modified df1 should look > something > > like this: > > > > df1 <- > > time v1 v2 v3 > > 1 2 3 4 > > 2 5 6 4 > > 3 112 3 4 > > 4 112 3 4 > > 5 2 3 4 > > 6 2 3 4 > > > > I tried to use the 'merge' function to combine df1 and df2 followed by > > the conditional 'ifelse' statement. However, that doesn't seem to work. > > > > Can I replace the values in df1 by not merging the two data frames? > > > > Thanks for your help, > > > > Regards, > > Bhaskar > > > > [[alternative HTML version deleted]] > > > > ______________________________________________ > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide http://www.R-project.org/ > posting-guide.html > > and provide commented, minimal, self-contained, reproducible code. >[[alternative HTML version deleted]]
MacQueen, Don
2017-Feb-13 16:16 UTC
[R] Query - Merging and conditional replacement of values in a data frame
How about this? foo <- merge(df1, df2, all=TRUE) is.new <- !is.na(foo$v11) foo$v1[is.new] <- foo$v11[is.new] foo <- foo[, names(df1)]> footime v1 v2 v3 1 1 2 3 4 2 2 5 6 4 3 3 112 3 4 4 4 112 3 4 5 5 2 3 4 6 6 2 3 4 -- Don MacQueen Lawrence Livermore National Laboratory 7000 East Ave., L-627 Livermore, CA 94550 925-423-1062 On 2/11/17, 4:13 PM, "R-help on behalf of Bhaskar Mitra" <r-help-bounces at r-project.org on behalf of bhaskar.kolkata at gmail.com> wrote: Hello Everyone, I have two data frames df1 and df2 as shown below. They are of different length. However, they have one common column - time. df1 <- time v1 v2 v3 1 2 3 4 2 5 6 4 3 1 3 4 4 1 3 4 5 2 3 4 6 2 3 4 df2 <- time v11 v12 v13 3 112 3 4 4 112 3 4 By matching the 'time' column in df1 and df2, I am trying to modify column 'v1' in df1 by replacing it with values in column 'v11' in df2. The modified df1 should look something like this: df1 <- time v1 v2 v3 1 2 3 4 2 5 6 4 3 112 3 4 4 112 3 4 5 2 3 4 6 2 3 4 I tried to use the 'merge' function to combine df1 and df2 followed by the conditional 'ifelse' statement. However, that doesn't seem to work. Can I replace the values in df1 by not merging the two data frames? Thanks for your help, Regards, Bhaskar [[alternative HTML version deleted]] ______________________________________________ R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.