Databases
StackOverflow2013
Postsdbo
POR: merge two irregular time series
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POR: merge two irregular time series
    primarykey
    data
    text
    <p>I have two multivariate time series x and y, both covering approximately the same range in time (one starts two years before the other, but they end on the same date). Both series have missing observations in the form of empty columns next to the date column, and also in the sense that one of the series has several dates that are not found in the other, and vice versa.</p> <p>I would like to create a data frame (or similar) with a column that lists all the dates found in x OR y, without duplicate dates. For each date (row), I would like to horizontally stack the observations from x next to the observations from y, with NA's filling the missing cells. Example:</p> <pre><code>&gt;x "1987-01-01" 7.1 NA 3 "1987-01-02" 5.2 5 2 "1987-01-06" 2.3 NA 9 &gt;y "1987-01-01" 55.3 66 45 "1987-01-03" 77.3 87 34 # result I would like "1987-01-01" 7.1 NA 3 55.3 66 45 "1987-01-02" 5.2 5 2 NA NA NA "1987-01-03" NA NA NA 77.3 87 34 "1987-01-06" 2.3 NA 9 NA NA NA </code></pre> <p>What I have tried: with the zoo package, I've tried the merge.zoo method, but this seems to just stack the two series next to each other, with the dates (as numbers, e.g. "1987-01-02" shown as 6210) from each series appearing in two separate columns.</p> <p>I've sat for hours getting almost nowhere, so all help is appreciated.</p> <p>EDIT: some code included below as per suggestion from Soumendra</p> <pre><code>atcoa &lt;- read.csv(file = "ATCOA_full_adj.csv", header = TRUE) atcob &lt;- read.csv(file = "ATCOB_full_adj.csv", header = TRUE) atcoa$date &lt;- as.Date(atcoa$date) atcob$date &lt;- as.Date(atcob$date) # only number of observations and the observations themselves differ &gt;str(atcoa) 'data.frame': 6151 obs. of 8 variables: $ date :Class 'Date' num [1:6151] 6210 6213 6215 6216 6217 ... $ max : num 4.31 4.33 4.38 4.18 4.13 4.05 4.08 4.05 4.08 4.1 ... $ min : num 4.28 4.31 4.28 4.13 4.05 3.95 3.97 3.95 4 4.02 ... $ close : num 4.31 4.33 4.31 4.15 4.1 3.97 4 3.97 4.08 4.02 ... $ avg : num NA NA NA NA NA NA NA NA NA NA ... $ tot.vol : int 877733 89724 889437 1927113 3050611 846525 1782774 1497998 2504466 5636999 ... $ turnover : num 3762300 388900 3835900 8015900 12468100 ... $ transactions: int 12 9 24 17 31 26 34 35 37 33 ... &gt;atcoa[1:1, ] date a.max a.min a.close a.avg a.tot.vol a.turnover a.transactions 1 1987-01-02 4.31 4.28 4.31 NA 877733 3762300 12 # using timeSeries package ts.atcoa &lt;- timeSeries::as.timeSeries(atcoa, format = "%Y-%m-%d") ts.atcob &lt;- timeSeries::as.timeSeries(atcob, format = "%Y-%m-%d") &gt;str(ts.atcoa) Time Series: Name: object Data Matrix: Dimension: 6151 7 Column Names: a.max a.min a.close a.avg a.tot.vol a.turnover a.transactions Row Names: 1970-01-01 01:43:30 ... 1970-01-01 04:12:35 Positions: Start: 1970-01-01 01:43:30 End: 1970-01-01 04:12:35 With: Format: %Y-%m-%d %H:%M:%S FinCenter: GMT Units: a.max a.min a.close a.avg a.tot.vol a.turnover a.transactions Title: Time Series Object Documentation: Wed Aug 17 13:00:50 2011 &gt;ts.atcoa[1:1, ] GMT a.max a.min a.close a.avg a.tot.vol a.turnover a.transactions 1970-01-01 01:43:30 4.31 4.28 4.31 NA 877733 3762300 12 # The following will create an object of class "data frame" and mode "list", which contains observations for the days mutual for the two series &gt;ts.atco &lt;- timeSeries::merge(atcoa, atcob) # produces same result as base::merge, apparently &gt;ts.atco[1:1, ] date a.max a.min a.close a.avg a.tot.vol a.turnover a.transactions b.max b.min b.close b.avg b.tot.vol b.turnover b.transactions 1 1989-08-25 7.92 7.77 7.79 NA 269172 2119400 19 7.69 7.56 7.64 NA 81176693 593858000 12 </code></pre> <p>EDIT: problem solved by (using zoo package)</p> <pre><code>atcoa &lt;- read.zoo(read.csv(file = "ATCOA_full_adj.csv", header = TRUE)) atcob &lt;- read.zoo(read.csv(file = "ATCOB_full_adj.csv", header = TRUE)) names(atcoa) &lt;- c("a.max", "a.min", "a.close", "a.avg", "a.tot.vol", "a.turnover", "a.transactions") names(atcob) &lt;- c("b.max", "b.min", "b.close", "b.avg", "b.tot.vol", "b.turnover", "b.transactions") atco &lt;- merge.zoo(atcoa, atcob) </code></pre> <p>Thank you all for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBenjamin Allévius
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POR: merge two irregular time series
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POR: merge two irregular time series
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POR: merge two irregular time series
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou didn't show what you actually tried but `merge.zoo` certainly does not just stack zoo objects. There is a help page on `merge.zoo` which includes documentation and many examples: `?merge.zoo` . Also there are 5 vignettes (pdf documents) that come with zoo that give additional documentation and examples. See my post below for more info.
      singulars
      1. POR: merge two irregular time series
      1. USG. Grothendieck
    2. COSuggest you do it like this: `nms <- c("", "max", "min", "close", "avg", "tot.vol", "turnover", "transactions"); atcoa <- read.zoo("ATCOA_full_adj.dat", header = TRUE, col.names = paste("a", nms, sep = "."))` . Also since this does not appear to be a csv file, i.e. the fields are not separated by commas, it would be better not to use csv as the extension when naming the file.
      singulars
      1. POR: merge two irregular time series
      1. USG. Grothendieck
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload