Skip to content

Query - Merging and conditional replacement of values in a data frame

6 messages · Bert Gunter, Jeff Newmiller, Jim Lemon +2 more

Bhaskar Mitra
# 
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
Bert Gunter
# 
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
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:
Jeff Newmiller
# 
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:

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

        
---------------------------------------------------------------------------
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
    

    

      
      #
    

  


  

      
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:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Bhaskar Mitra
    

    

      
      #
    

  


  

      
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:

        

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

  
  


  


      

    

    

      
      

        


  

        

      MacQueen, Don
    

    

      
      #
    

  


  

      
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)]

            

      
      
      
    

        
time  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