2

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.

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
user1462351
  • 133
  • 1
  • 1
  • 6

1 Answers1

2

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)
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • I tried out the code. But I get the error: "Error in as.POSIXlt.character(x, tz, ...) : character string is not in a standard unambiguous format". So removed the tz field and just used A <- read.zoo("File1.csv", header = TRUE, sep = ","). This resulted in an output. However, the output just concatenated the two datasets column-wise. In other words, the output had 7 columns: Time.A, A1, A2, Time.B, B1,B2,B3. – user1462351 Sep 07 '13 at 04:32
  • Looks like the position of the tz argument matters. When I placed it between header and sep, it worked. Thanks. – user1462351 Sep 07 '13 at 06:49
  • No. The position of tz does not matter. I have added a reproducible version that can be copied and pasted into R to illustrate this. – G. Grothendieck Sep 07 '13 at 14:21