convert csv file to time series format

0
votes

I am trying to convert a csv file to time series format when the file is imported into R studio. The csv data is in the following format:

      week1 week2 week3 week4 ...
2011    6     6     9     11
2012    11    16    18    14
2013    12    8     11    10
2014    17    16    10    7
2015    13    13    13    14
2016    9     13    16    16
2017    11    24    20    19
2018    5     14    18    13

and continues on for 21 weeks.

Ive tried using the following code to convert the data to a time series format:

library(zoo)
con <- read.csv(file = "TS_11.csv", header = T, sep = ",")
series <- as.ts(read.zoo(con, FUN = as.yearmon))

The results of the above code successfully converts the data to time series data but not in the format i would like it to be in.

series: Time-Series [1:8, 1:21] from 2011 to 2018: 6 11 12 1..

I want the data to be in the following format when converted to time series:

series: Time-Series [1:168] from 2011 to 2018: 6 11 12 1..

where 1:168 contains all the data contained in the csv file. This is the same format in which the AirPassengers time series data is in R studio. I want my data to be converted to the same time series format as the AirPassengers.

2
rtime-series

2 Answers

1
votes

If con is as in the question then transpose and unravel it with the appropriate start and frequency values. See Note for self-contained reproducible version.

ts(c(t(con)), start = start(con), frequency = ncol(con))

Note

Lines <- "year week1 week2 week3 week4
2011    6     6     9     11
2012    11    16    18    14
2013    12    8     11    10
2014    17    16    10    7
2015    13    13    13    14
2016    9     13    16    16
2017    11    24    20    19
2018    5     14    18    13"
library(zoo)
z <- read.zoo(text = Lines, header = TRUE, FUN = c)
ts(c(t(z)), start = start(z), frequency = ncol(z))
0
votes

Here is a non-zoo option:

yday <- seq(0,21)*7+1 # julian day
year <- 2011:2018 # year
g <- expand.grid(year=year, yday=yday)
g$date <- strptime(paste(g$year, g$yday, sep="-"), format = "%Y-%j", tz = "GMT")
G <- matrix(as.character(g$date), nrow = length(year), ncol = length(yday))
G <- as.data.frame(G)
L <- as.data.frame(lapply(G, as.Date))
colnames(L) <- paste0("week", seq(ncol(L)))

Result:

> L[,1:4]
       week1      week2      week3      week4
1 2011-01-01 2011-01-08 2011-01-15 2011-01-22
2 2012-01-01 2012-01-08 2012-01-15 2012-01-22
3 2013-01-01 2013-01-08 2013-01-15 2013-01-22
4 2014-01-01 2014-01-08 2014-01-15 2014-01-22
5 2015-01-01 2015-01-08 2015-01-15 2015-01-22
6 2016-01-01 2016-01-08 2016-01-15 2016-01-22
7 2017-01-01 2017-01-08 2017-01-15 2017-01-22
8 2018-01-01 2018-01-08 2018-01-15 2018-01-22