Menu
Lately I often had to handle time series data from multiple .csv sources in the same analysis. Let's assume for simplicity that all series are regular quarterly series (no missing values in between). Typically the original .csv data contains a date column plus 1-3 variables. Unfortunately the series are not of equal length across .csv files.
I started to organize my dataset in R and ended up with a big mess containing lots of window()commands. Plus I had to concatenate NAs and original series before turning them into ts()objects because I found concatenating (multivariate) ts()objects so counter-intuitive.
Note that the reason why I added NAs is that I wanted to all series to be of the same length. Of course I could have trimmed the longer ones, but then I´d eventually loose observations when not using shorter series.
I thought about writing a function that reads .csv files and uses it's date column to create ts()objects and maybe with another function merge all the single series to create a multivariate series containing NAs when data is missing. I found myself switching data types all the time, reading through the ts and zoo manuals – i just could not believe it was that complex.
I really thought this problem is really common and thought about the preparations in excel.. I mean I really hate excel, but this time I wonder what more experienced useRs do? R or Excel?
EDIT: added some exemplary data (need to aggregate daily data) file1:
27.05.11;5965.95
26.05.11;5947.06
25.05.11;5942.82
24.05.11;5939.98
file2 (without date col, but i know start and frequency)
Germany;Switzerland;USA;OECDEurope
69,90974;61,8241;55,60966;64,96157
67,0394;62,18966;56,47361;64,15152
70,56651;63,6347;56,87237;65,43568
file3:
1984-04-01,33.3238396624473
1984-07-01,63.579833082501
1984-10-01,35.8375401560349
I admit exemplary data does help to illustrate the question, but it`s rather a best practice type of question adressing more experienced users than myself. How do you prepare your data for multivariate ts analysis ?
236
To create a time series plot in Excel, first select the time (DateTime in this case) Column and then the data series (streamflow in this case) column. Next, click on the Insert ribbon, and then select Scatter. From scatter plot options, select Scatter with Smooth Lines as shown below.
We often present time series data in a time series plot, which plots each observation against the time at which we measured (Moore et al., 2018). When performing time series analysis in R, we can store a time series as a time series object (i.e., a ts object). For example, we use the following R commands to store the data shown in Table 1.
Assuming every piece of data in a time series is equally useful to predict all future values, we use the average of the time series to represent the forecasts. This method works best when a time series does not contain significant T and S components, such as the time series shown in Figure 7.
The result from the R function agrees with the manual calculation. Different time series may have different units, for example, million dollars and gallon. When comparing forecasting methods applied to multiple time series with different units, we can use the mean absolute percentage error (MAPE) that does not associate a unit.
As a rule of thumb, we typically select 1, 2, 3, 4, or 5 data points for a non-seasonal time series. We use the length of an annual cycle as the subset’s length when analyzing seasonal time series, for example, selecting four data points for quarterly data and selecting 12 data points for monthly data (William & Sincich, 2012).
I do this in R all the time. You may find it easier to do in Excel but if your data change, you have to do the same process again. Using R makes it much easier to update and reproduce your results.
Dealing with monthly or quarterly frequencies are made significantly easier with zoo's yearmon and yearqtr index classes, respectively. Once you have your data in zoo objects with yearqtr indexes, all you have to do is merge all the objects.
Here's your sample data:
Lines1 <-
"27.05.11;5965.95
26.05.11;5947.06
25.05.11;5942.82
24.05.11;5939.98"
f1 <- read.csv2(con <- textConnection(Lines1), header=FALSE)
close(con)
Lines2 <-
"Germany;Switzerland;USA;OECDEurope
69,90974;61,8241;55,60966;64,96157
67,0394;62,18966;56,47361;64,15152
70,56651;63,6347;56,87237;65,43568"
f2 <- read.csv2(con <- textConnection(Lines2), header=TRUE)
close(con)
Lines3 <-
"1984-04-01,33.3238396624473
1984-07-01,63.579833082501
1984-10-01,35.8375401560349"
f3 <- read.csv(con <- textConnection(Lines3), header=FALSE)
close(con)
The example below assumes the starting date for the first file is 1984Q2 and the starting date for the second file is 1984Q4. You can see that merge.zoo takes care of aligning all the dates for you. After everything is aligned in your zoo object, you can use the as.ts method to create a mts object.
z1 <- zoo(f1[,-1], as.Date(f1[,1], "%d.%m.%y"))
z2 <- zoo(f2, as.yearqtr("1984Q4")+(seq_len(NROW(f1))-1)/4)
z3 <- zoo(f3[,-1], as.yearqtr(as.Date(f3[,1])))
library(xts)
# Use xts::apply.quarterly to aggregate series with higher periodicity.
# Here I just take the last obs but you could use another function (e.g. mean).
z1 <- apply.quarterly(z1, last)
index(z1) <- as.yearqtr(index(z1)) # convert the index to yearqtr
(Z <- merge(z1,z2,z3))
# z1 Germany Switzerland USA OECDEurope z3
# 1984 Q2 <NA> <NA> <NA> <NA> <NA> 33.32383
# 1984 Q3 <NA> <NA> <NA> <NA> <NA> 63.57983
# 1984 Q4 <NA> 69.90974 61.8241 55.60966 64.96157 35.83754
# 1985 Q1 <NA> 67.0394 62.18966 56.47361 64.15152 <NA>
# 1985 Q2 <NA> 70.56651 63.6347 56.87237 65.43568 <NA>
# 1985 Q3 <NA> 69.90974 61.8241 55.60966 64.96157 <NA>
# 2011 Q2 5965.95 <NA> <NA> <NA> <NA> <NA>
# Note that ts will create an object with a observation for every period,
# even if all the columns are missing.
TS <- as.ts(Z)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
