CODEWITHSUNDEEP
rdataframetime-seriestransformation
Bartholomäus Dutkiewicz
Bartholomäus Dutkiewicz

Reputation: 67

R: Transforming a year-month data frame into a time series

My Situation: I want to analyze Lake Mead water elevation data with R. It is provided here as a year-month table. The data is imported into R as a data.frame object named "LM".

Structure:

 'data.frame':  86 obs. of  13 variables:
 $ Year: int  1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 ...
 $ JAN : num  NA 86 2 12 47 48 49 63 61 50 ...
 $ FEB : num  709 908 1026 1095 1157 ...
 $ MAR : num  702 907 1031 1100 1158 ...
 $ APR : num  752 922 1045 1109 1163 ...
 $ MAY : num  807 982 1079 1134 1176 ...
 $ JUN : num  909 1016 1097 1166 1183 ...
 $ JUL : num  928 1020 1103 1174 1181 ...
 $ AUG : num  926 1024 1100 1172 1177 ...
 $ SEP : num  921 1025 1098 1174 1179 ...
 $ OCT : num  915 1023 1097 1171 1176 ...
 $ NOV : num  908 1024 1096 1169 1173 ...
 $ DEC : num  908 1024 1096 1169 1170 ...

Head:

  Year     JAN     FEB     MAR     APR     MAY     JUN    JUL     AUG     SEP
1 1935     ---  708.70  701.70  752.40  806.60  909.10  928.4  925.90  920.80
2 1936  907.90  908.40  906.90  922.20  982.40 1015.50 1020.4 1024.40 1024.60
3 1937 1022.20 1026.20 1031.00 1044.60 1078.70 1096.60 1102.8 1099.60 1097.60

My Problem: How do I transform this data frame into a time series (class: "ts") object with the same structure as the well-known "AirPassangers" data set?

My R version: 3.6.3

Upvotes: 2

Views: 1831

Answers (3)

FilipW
FilipW

Reputation: 1515

You can also try the new package tsibble which has a very straight forward way to create tidy time series. Although this requires a long format. Read more about it here: https://tsibble.tidyverts.org/ 

library(rvest)
#> Loading required package: xml2
library(tidyverse)
library(tsibble)
#> 
#> Attaching package: 'tsibble'
#> The following object is masked from 'package:dplyr':
#> 
#>     id

dat <- html_table(read_html("https://www.usbr.gov/lc/region/g4000/hourly/mead-elv.html"), 
                                                                        fill=TRUE, header=TRUE)[[2]]

dat %>% 
    mutate(JAN = as.numeric(JAN)) %>% 
    pivot_longer(cols = JAN:DEC, names_to = "month", values_to = "elev") %>% 
    mutate(yearmon = paste0("01/",month,"/",Year) %>%
                                    as.Date(format = "%d/%b/%Y") %>%
                                    tsibble::yearmonth()) %>% 
    select(yearmon, elev) %>% 
    as_tsibble()
#> Warning: NAs introduced by coercion
#> Using `yearmon` as index variable.
#> # A tsibble: 912 x 2 [1M]
#>     yearmon  elev
#>       <mth> <dbl>
#>  1 1935 Jan   NA 
#>  2 1935 Feb  709.
#>  3 1935 Mar  702.
#>  4 1935 Apr  752.
#>  5 1935 May  807.
#>  6 1935 Jun  909.
#>  7 1935 Jul  928.
#>  8 1935 Aug  926.
#>  9 1935 Sep  921.
#> 10 1935 Oct  915.
#> # … with 902 more rows

Created on 2020-03-03 by the reprex package (v0.3.0)

Upvotes: 3

Shivam Sarin
Shivam Sarin

Reputation: 551

Create a new column, 

library(reshape2)
mdata <- melt(LM, id="Year",variable.name = "Month")
mdata$New_Date=paste0("01-",mdata$Month,"-",mdata$Year)
mdata$New_Date=as.Date(mdata$New_Date,"%d-%b-%Y")

You can now convert it into a ts object.

Upvotes: 3

jay.sf
jay.sf

Reputation: 72593

Try this approach.

dat <- ts(dat[-1], start=dat[1,1], end=dat[nrow(dat),1])

str(dat)
# Time-Series [1:76, 1:12] from 1935 to 2010: NA 908 1022 1095 1165 ...
# - attr(*, "dimnames")=List of 2
# ..$ : NULL
# ..$ : chr [1:12] "JAN" "FEB" "MAR" "APR" ...

Data:

library(rvest)
dat <- html_table(read_html("https://www.usbr.gov/lc/region/g4000/hourly/mead-elv.html"), 
                  fill=TRUE, header=TRUE)[[2]]

Upvotes: 4

Related Questions