Convert multivariable, multigroup data to ts object

Question

I have a tibble object that has multiple groups in a column, and with time-dependent variables in other columns. I am trying to convert this to a ts object.

Below is a snippet of the data that shows the grouping in the data I have, and would love to get some help on this.

DIVLEVEL4   Date    Sales   Cost
R   30.6.2018   279,0396819 33,48476183
R   31.3.2018   1656543,904 198785,2685
R   31.1.2018   3683,363612 442,0036334
R   31.1.2018   6434,16246  772,0994952
R   30.6.2018   30,70011316 3,684013579
A   31.3.2018   15101,69571 1812,203485
R   28.2.2018   2363,778089 283,6533707
R   31.5.2018   3863,311764 463,5974117
R   31.3.2018   87090,89559 10450,90747
R   31.5.2018   82564,35546 9907,722655
R   30.4.2018   71393,33929 8567,200715
R   30.4.2018   3543,333993 425,2000792
R   31.5.2018   5266,052788 631,9263346
R   31.1.2018   1761699,156 211403,8987
R   28.2.2018   126,0641971 15,12770365
R   28.2.2018   5080,899415 609,7079298
R   31.1.2018   3267,686071 392,1223285
R   30.6.2018   1808376,384 217005,1661
R   31.1.2018   78335,05219 9400,206263
R   28.2.2018   218880,0948 26265,61138
R   30.6.2018   4541,859592 545,023151
R   31.5.2018   62374,37761 7484,925313
R   30.4.2018   6927,553413 831,3064096
R   31.3.2018   5486,452873 658,3743448
R   28.2.2018   79677,19588 9561,263506
R   30.4.2018   199783,293  23973,99516
R   31.5.2018   2587,801823 310,5362188
A   28.2.2018   14519,7136  1742,365632
R   31.3.2018   115484,1636 13858,09963
R   31.3.2018   213,7964071 25,65556885
R   31.1.2018   4863,71885  583,646262
R   31.5.2018   26335,57824 3160,269389
R   30.4.2018   3143,861929 377,2634315
R   31.3.2018   79674,12633 9560,89516
R   31.5.2018   1542191,744 185063,0093
R   28.2.2018   1606610,664 192793,2797
R   31.5.2018   83,61105675 10,03332681
R   30.6.2018   220412,3576 26449,48291
R   31.1.2018   237432,0521 28491,84625
R   30.4.2018   86683,24446 10401,98934
R   28.2.2018   103523,9084 12422,86901
R   31.5.2018   217778,0876 26133,37051
R   30.6.2018   75493,35187 9059,202224
R   30.4.2018   23314,76204 2797,771445
R   30.6.2018   106981,3073 12837,75688
R   30.4.2018   4739,107287 568,6928744
R   30.6.2018   17640,08684 2116,810421
A   30.6.2018   12369,53103 1484,343724
R   30.4.2018   88070,29803 10568,43576
R   28.2.2018   85922,79601 10310,73552
R   28.2.2018   4116,664156 493,9996987
R   31.5.2018   5086,447528 610,3737034
R   30.6.2018   7534,892277 904,1870732
R   31.1.2018   67,49549179 8,099459015
R   31.1.2018   120281,8324 14433,81989
R   30.4.2018   1481159,672 177739,1606
R   30.4.2018   14,96676269 1,796011523
A   30.4.2018   11265,0625  1351,8075
R   31.1.2018   114562,3692 13747,4843
R   31.3.2018   5139,063183 616,687582
R   30.6.2018   4094,000594 491,2800713
R   31.1.2018   20397,0328  2447,643936
R   31.3.2018   219460,8671 26335,30405
A   31.1.2018   17581,37066 2109,764479
R   30.6.2018   89022,18097 10682,66172
A   31.5.2018   10138,00115 1216,560138
R   30.6.2018   2813,713577 337,6456292
R   31.5.2018   91834,59339 11020,15121
R   31.3.2018   14279,52208 1713,54265
R   31.3.2018   3586,275344 430,3530413
R   30.4.2018   71,77213351 8,612656021
R   28.2.2018   6241,624886 748,9949863
R   31.3.2018   2167,614897 260,1137876
R   28.2.2018   12270,68227 1472,481872

This data is saved as as.tibble() and then I try a simple as.ts() to see if this works but it does not. I know it isn't that easy, but my novice understanding of R is still limiting. Some guidance on how I can get around this would be apprecaited.

Thanks!

Hi adrrs. Please add a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610). Also, show what you have already tried. That way you can help others to help you! — dario, Feb 11 '20 at 12:05
I saved the data as `test` and then tried to do a `as.ts()` conversion on it, but that failed. I am pretty new to R! — adrrs, Feb 11 '20 at 12:10

G. Grothendieck · Accepted Answer · 2020-02-11T13:18:51.193

There is some question of whether a ts object is appropriate here in the first place but putting that aside note that you can't just use as.ts on what you have because it is not in the form of a time series. What you can do is to use read.zoo to transform and read it in at the same time.

Using the file created in the Note at the end read it into a zoo object z. ( Alternately if you already have it in a data frame DF use read.zoo(DF, ...) in place of read.zoo("test_file.dat", ...). ) Then either use that zoo object directly or convert it to ts using as.ts(z) now that it is in the appropriate form.

Note that we have taken the mean of duplicate indexes within group but you can use any appropriate function depending on what you want. Also since the data is all end-of-month dates we have used yearmon class for the index to ensure that the data is regularly spaced.

See the reference manual (help files) and vignettes (pdf documents) in the zoo package for more information.

library(zoo)

z <- read.zoo("test_file.dat", header = TRUE, split = 1, index = 2, 
  FUN = as.yearmon, format = "%d.%m.%Y", dec = ",", aggregate = mean)

giving:

> z
          Sales.A   Cost.A  Sales.R   Cost.R
Jan 2018 17581.37 2109.764 213729.4 25647.53
Feb 2018 14519.71 1742.366 193164.9 23179.79
Mar 2018 15101.70 1812.203 199011.5 23881.38
Apr 2018 11265.06 1351.807 164070.4 19688.45
May 2018 10138.00 1216.560 185451.5 22254.17
Jun 2018 12369.53 1484.344 194768.3 23372.20

Note

Lines <- "DIVLEVEL4   Date    Sales   Cost
R   30.6.2018   279,0396819 33,48476183
R   31.3.2018   1656543,904 198785,2685
R   31.1.2018   3683,363612 442,0036334
R   31.1.2018   6434,16246  772,0994952
R   30.6.2018   30,70011316 3,684013579
A   31.3.2018   15101,69571 1812,203485
R   28.2.2018   2363,778089 283,6533707
R   31.5.2018   3863,311764 463,5974117
R   31.3.2018   87090,89559 10450,90747
R   31.5.2018   82564,35546 9907,722655
R   30.4.2018   71393,33929 8567,200715
R   30.4.2018   3543,333993 425,2000792
R   31.5.2018   5266,052788 631,9263346
R   31.1.2018   1761699,156 211403,8987
R   28.2.2018   126,0641971 15,12770365
R   28.2.2018   5080,899415 609,7079298
R   31.1.2018   3267,686071 392,1223285
R   30.6.2018   1808376,384 217005,1661
R   31.1.2018   78335,05219 9400,206263
R   28.2.2018   218880,0948 26265,61138
R   30.6.2018   4541,859592 545,023151
R   31.5.2018   62374,37761 7484,925313
R   30.4.2018   6927,553413 831,3064096
R   31.3.2018   5486,452873 658,3743448
R   28.2.2018   79677,19588 9561,263506
R   30.4.2018   199783,293  23973,99516
R   31.5.2018   2587,801823 310,5362188
A   28.2.2018   14519,7136  1742,365632
R   31.3.2018   115484,1636 13858,09963
R   31.3.2018   213,7964071 25,65556885
R   31.1.2018   4863,71885  583,646262
R   31.5.2018   26335,57824 3160,269389
R   30.4.2018   3143,861929 377,2634315
R   31.3.2018   79674,12633 9560,89516
R   31.5.2018   1542191,744 185063,0093
R   28.2.2018   1606610,664 192793,2797
R   31.5.2018   83,61105675 10,03332681
R   30.6.2018   220412,3576 26449,48291
R   31.1.2018   237432,0521 28491,84625
R   30.4.2018   86683,24446 10401,98934
R   28.2.2018   103523,9084 12422,86901
R   31.5.2018   217778,0876 26133,37051
R   30.6.2018   75493,35187 9059,202224
R   30.4.2018   23314,76204 2797,771445
R   30.6.2018   106981,3073 12837,75688
R   30.4.2018   4739,107287 568,6928744
R   30.6.2018   17640,08684 2116,810421
A   30.6.2018   12369,53103 1484,343724
R   30.4.2018   88070,29803 10568,43576
R   28.2.2018   85922,79601 10310,73552
R   28.2.2018   4116,664156 493,9996987
R   31.5.2018   5086,447528 610,3737034
R   30.6.2018   7534,892277 904,1870732
R   31.1.2018   67,49549179 8,099459015
R   31.1.2018   120281,8324 14433,81989
R   30.4.2018   1481159,672 177739,1606
R   30.4.2018   14,96676269 1,796011523
A   30.4.2018   11265,0625  1351,8075
R   31.1.2018   114562,3692 13747,4843
R   31.3.2018   5139,063183 616,687582
R   30.6.2018   4094,000594 491,2800713
R   31.1.2018   20397,0328  2447,643936
R   31.3.2018   219460,8671 26335,30405
A   31.1.2018   17581,37066 2109,764479
R   30.6.2018   89022,18097 10682,66172
A   31.5.2018   10138,00115 1216,560138
R   30.6.2018   2813,713577 337,6456292
R   31.5.2018   91834,59339 11020,15121
R   31.3.2018   14279,52208 1713,54265
R   31.3.2018   3586,275344 430,3530413
R   30.4.2018   71,77213351 8,612656021
R   28.2.2018   6241,624886 748,9949863
R   31.3.2018   2167,614897 260,1137876
R   28.2.2018   12270,68227 1472,481872"
cat(Lines, file = "test_file.dat")

Just a clarification: if the file is already as a tibble, would the `read.zoo()` function remain of similar context? — adrrs, Feb 11 '20 at 13:14

Convert multivariable, multigroup data to ts object

1 Answers1

Note