R在合并时合并data.frames [英] R merge data.frames asof join

问题描述

我有一堆不规则时间间隔的data.frames.

I have a whole bunch of data.frames with irregular time spacing.

我想创建一个新的data.frame并将其他的加入其中，因为要加入的每个data.frame都要从新的data.frame中选取最新的值.

I would like to make a new data.frame and join the others to it, for each data.frame being joined picking the latest value out of the new data.frame.

例如，下面的listOfDataFrames包含一个data.frames列表，每个data.frames都有一个以秒为单位的时间列.我找到了总范围，将范围修改为60并通过seqn来获得增加的整分钟seqn.现在，我需要将data.frames列表合并到此新seqn的左侧.例如如果mypoints中的值是60，则连接到它的值应为最新值< = 60.

For example, listOfDataFrames below contains a list of data.frames each of which has a time column in seconds. I find the total range, mod the range by 60 and seqn it by to obtain an increasing seqn of full minutes. Now I need to merge the list of data.frames to the left of this new seqn. e.g. if the value in mypoints is 60, the value joined to it should be the latest value <= 60.

xrange <- range(lapply(listOfDataFrames,function(x) range(x$Time)))
mypoints <- 60*do.call(seq,as.list(xrange%/%60))

我相信有时也称为asof连接.

I believe this is sometimes called an asof join.

有一个简单的程序可以做到这一点吗?

Is there a simple procedure to do this?

谢谢

这是我目前使用的

xrange <- range(lapply(listOfDataFrames,function(x) range(x$Time)))
mypoints <- 60*seq(xrange[1]%/%60,1+xrange[2]%/%60)
result <- data.frame(Time=mypoints)
for(index in 1:length(listOfDataFrames))
{
  x<-listOfDataFrames[[index]]
  indices <- which(sort(c(mypoints,x$Time)) %in% mypoints) - 1:length(mypoints)
  indices[indices==0] <- NA
  newdf<-data.frame(new=x$Result[indices])
  colnames(newdf)<-paste("S",index,sep="")
  result <- cbind(result,newdf)
}

完整示例

AsOfJoin <- function (listOfDataFrames) {
  xrange <- range(lapply(listOfDataFrames,function(x) range(x$Time)))
  mypoints <- 60*seq(xrange[1]%/%60,1+xrange[2]%/%60)
  result <- data.frame(Time=mypoints)
  for(index in 1:length(listOfDataFrames))
  {
    x<-listOfDataFrames[[index]]
    indices <- which(sort(c(mypoints,x$Time)) %in% mypoints) - 1:length(mypoints)
    indices[indices==0] <- NA
    newdf<-data.frame(new=x$Result[indices])
    colnames(newdf)<-paste("S",index,sep="")
    result <- cbind(result,newdf)
  }
  result[is.na(result)]<-0
  result
}


a<-data.frame(Time=c(28947.5,28949.6,29000),Result=c(10,15,9))
b<-data.frame(Time=c(28947.8,28949.5),Result=c(14,19))
listOfDataFrames <- list(a,b)
result<-AsOfJoin(listOfDataFrames)

    > a
         Time Result
    1 28947.5     10
    2 28949.6     15
    3 29000.0      9
    > b
         Time Result
    1 28947.8     14
    2 28949.5     19
    > result
       Time S1 S2
    1 28920  0  0
    2 28980 15 19
    3 29040  9 19

推荐答案

请参见我的编辑以获取答案.显然是最好的方法.

See my edit for answer. Apparently the best way.

这篇关于R在合并时合并data.frames的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！