CODEWITHSUNDEEP
rmergetime-serieszoo
user1462351
user1462351

Reputation: 133

merge two time series with different time granularities

I have two CsV files containing time series data. I want to merge the two into a single file. File1 has periodic data at 1-minute intervals. File2 has event-triggered data that is not periodic. The timestamps for data in File2 may or may not coincide with data in File1. I want to merge the two datasets to create a dataset whose timestamps are a union of Data1 and Data2. For timestamps that are not common to both, I want the missing entries for the corresponding dataset to be indicated as NA.

Here is a sample input for File1: 

     Time                 A1  A2       
  2013-08-05 00:00:00     2   1               
  2013-08-05 00:01:00     2   1                      
  2013-08-05 00:02:00     1   1

Here is a sample input for File2: 

     Time               B1     B2       B3
2013-08-01 12:10:21       5     1          1    
2013-08-05 00:02:00       5     1          1                
2013-08-05 12:13:44      14     1          2

The expected output is as follows:

  Time                 A1   A2      B1     B2       B3
2013-08-01 12:10:21    NA    NA     5     1          1    
2013-08-05 00:00:00    2     1      NA    NA         NA  
2013-08-05 00:01:00    2     1      NA    NA         NA        
2013-08-05 00:02:00    1      1     5     1          1 
2013-08-05 12:13:44    NA    NA     14     1          2

I used merge.zoo and also tried merge.xts as suggested by other relevant posts in this forum. But I am not getting the expected output. Here is the code I used. 

A <- read.zoo(read.csv("File1.csv", header=TRUE));
B <- read.zoo(read.csv("File2.csv", header=TRUE));
C <- merge.zoo(A,B);

I would appreciate any help you can provide. Thank you.

Upvotes: 2

Views: 1729

Answers (1)

G. Grothendieck
G. Grothendieck

Reputation: 270248

The lines that read in the files should be replaced as shown:

> A <- read.zoo("File1.csv", header = TRUE, tz = "", sep = ",")
> B <- read.zoo("File2.csv", header = TRUE, tz = "", sep = ",")
> merge(A, B)
                    A1 A2 B1 B2 B3
2013-08-01 12:10:21 NA NA  5  1  1
2013-08-05 00:00:00  2  1 NA NA NA
2013-08-05 00:01:00  2  1 NA NA NA
2013-08-05 00:02:00  1  1  5  1  1
2013-08-05 12:13:44 NA NA 14  1  2

Here it is in reproducible form:

Lines1 <- "   Time,                A1,  A2       
  2013-08-05 00:00:00,    2,  1               
  2013-08-05 00:01:00,    2,  1                      
  2013-08-05 00:02:00,    1,  1  
"

Lines2 <- " Time,              B1,    B2,      B3
2013-08-01 12:10:21,       5,    1,          1    
2013-08-05 00:02:00,      5,    1,          1                
2013-08-05 12:13:44,     14,    1,          2   
"

library(zoo)
A <- read.zoo(text = Lines1, header = TRUE, tz = "", sep = ",")
B <- read.zoo(text = Lines2, header = TRUE, tz = "", sep = ",")
merge(A, B)

Upvotes: 2

Related Questions