Skip to content

Combine two dataframe with different row number and interpolation between values

5 messages · javad bayat, Jim Lemon, PIKAL Petr +1 more

#
Dear Tim;
The dplyr did not work for me. My data frames have exactly similar columns
but different row numbers.
Mr Petr sent this code and the code worked but it copied the second
dataframe into the first one and did not replace the corresponding row.
Regarding filling the "NA" data, it does not matter for me to interpolate
between numbers or put the mean of numbers.
Sincerely
On Wed, Aug 31, 2022 at 5:17 PM Ebert,Timothy Aaron <tebert at ufl.edu> wrote:

            

  
    
#
Hi Javad,
You seem to have the data frame join worked out, so here is a function
to interpolate over sequences of NAs. I have found it quite useful.

# interpolate over sequences of NAs
# NA sequences at the beginning of a file are replaced with the first
nonNA value
# NA sequences at the end are filled with the last nonNA value
# sequences of all NAs are returned unaltered
interpNA<-function(x) {
 if(any(is.na(x))) {
  xrle<-rle(is.na(x))
  begin<-end<-1
  # if the sequence begins with NA
  # set the initial run of NAs to the 1st non-NA
  if(is.na(x[1])) x[1:xrle$lengths[1]]<-x[xrle$lengths[1]+1]
  for(i in 1:length(xrle$values)) {
   # if this is a run of NA, fill it
   if(xrle$values[i]) {
    end<-begin+xrle$lengths[i]+(i>1)
    if(is.na(x[end])) x[begin:(end-1)]<-x[begin]
    else x[begin:end]<-seq(x[begin],x[end],length.out=1+end-begin)
    begin<-end+xrle$lengths[i]-1
  }
   else {
    # set begin to the end of the non-NA values
    begin<-end+xrle$lengths[i]-1
   }
  }
 }
 return(x)
}

Jim
On Thu, Sep 1, 2022 at 3:29 PM javad bayat <j.bayat194 at gmail.com> wrote:
#
Hallo

This code do the merging without repeating the NA row.

df3 = merge(df1, df2, by=c("y", "d", "h"), all.y = TRUE)

Cheers
Petr
but
dataframe
between
http://www.R-project.org/posting-guide.html
#
Hi

You could also use approxfun to fill NA values

fff <- approxfun(1:dim(df3)[1], df3$ws.x)
df3$ws2 <- fff(1:dim(df3)[1])
head(df3)
     y d h     ws.x ws.y      ws2
1 2010 1 1       NA   NA       NA
2 2010 1 2       NA   NA       NA
3 2010 1 3 20.46314   NA 20.46314
4 2010 1 4       NA   NA 20.74001
5 2010 1 5       NA   NA 21.01689
6 2010 1 6 21.29376   NA 21.29376

And fill starting or trailing NA by na.locf function from zoo package or
change starting or trailing NA to some value before calling approxfun.

Cheers
Petr
columns
interpolate
one
of
average
at
recording ws
these
dataframe
=
with
=
rows in
third
Like
value
after
http://www.R-project.org/posting-guide.html
1 day later
#
You can also use match function to combine the two dataframes as shown below:

wh<- match(interaction(df2[c("y", "d", "h")]), interaction(df1[c("y", "d", "h")])) matched <- !is.na(wh) df2[matched, "ws"] <- df1[wh[matched],"ws"]

head(df2)
# y d h               ws
# 1 2010 1 1               NA
# 2 2010 1 2               NA
# 3 2010 1 3 21.7863481620828
# 4 2010 1 4               NA
# 5 2010 1 5               NA
# 6 2010 1 6 21.9653408303519

Nilesh

-----Original Message-----
From: R-help <r-help-bounces at r-project.org> On Behalf Of PIKAL Petr
Sent: Thursday, September 1, 2022 6:29 AM
To: Jim Lemon <drjimlemon at gmail.com>; javad bayat <j.bayat194 at gmail.com>; r-help mailing list <r-help at r-project.org>
Subject: Re: [R] Combine two dataframe with different row number and interpolation between values

Hi

You could also use approxfun to fill NA values

fff <- approxfun(1:dim(df3)[1], df3$ws.x)
df3$ws2 <- fff(1:dim(df3)[1])
head(df3)
     y d h     ws.x ws.y      ws2
1 2010 1 1       NA   NA       NA
2 2010 1 2       NA   NA       NA
3 2010 1 3 20.46314   NA 20.46314
4 2010 1 4       NA   NA 20.74001
5 2010 1 5       NA   NA 21.01689
6 2010 1 6 21.29376   NA 21.29376

And fill starting or trailing NA by na.locf function from zoo package or change starting or trailing NA to some value before calling approxfun.

Cheers
Petr
columns
interpolate
one
of
average
at
recording ws
these
dataframe
=
with
=
rows in
third
Like
value
after
https://eur03.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.r-project.org%2Fposting-guide.html&amp;data=05%7C01%7C%7C22d32a92ea5f4aa85d3208da8c04dc0f%7Cfcb2b37b5da0466b9b830014b67a7c78%7C0%7C0%7C637976250192968351%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C2000%7C%7C%7C&amp;sdata=zVgeOMKCYAFRaqqzUot0blLwXk%2FSgROm2qbmJu4hqFQ%3D&amp;reserved=0
________________________________

The information contained in this e-mail is for the excl...{{dropped:9}}