how to convert a data frame to a time series (yearly)

Question

everyone. i have the problem to convert a data frame to the time series, but my time series is based on year. so basically i want to convert the table A to table B.table A , current layout what i want

the data is here: NameCorp<-c("AAC Holdings, Inc." ,"AAC Holdings, Inc." , "Accuride Corporation", "Accuride Corporation", "Accuride Corporation","Adeptus Health Inc." ,"Adeptus Health Inc." , "Akorn, Inc." ,"Akorn, Inc." ,"Akorn, Inc." ,"Aleris International, Inc.","Aleris International, Inc.","Aleris International, Inc.")

year<-c("2014","2015","2002","2005","2011","2018","2020","2012","2019","2020","2007","2009","2010")

value<-c("125","1115","300","500","2500","1100","47.5","150","250","900","2400","2025","500")

a<-data.frame(NameCorp=NameCorp,year=year,value=value)

i tried the read.zoo() and ts(), but it does not work.

Thank you !!!!

Answer 1

The problemd are:

• the data is character even though year and value are supposed to represent numeric values.

  str(a)
  ## 'data.frame':   13 obs. of  3 variables:
  ##  $ NameCorp: chr  "AAC Holdings, Inc." "AAC Holdings, Inc." 
  ##    "Accuride Corporation" "Accuride Corporation" ...
  ##  $ year    : chr  "2014" "2015" "2002" "2005" ...
  ##  $ value   : chr  "125" "1115" "300" "500" ...
  

• the question did not show the code that did not work so there could have been additional errors as well. We won't be able to address that without the code.

• the 0's shown in the image in the question should likely be NA's

We assume you want a zoo or ts object since that is what the functions you tried would have produced and the subject refers to time series.

The following code converts the character values in year and value to numeric and then to a zoo object z and finally converts to ts object tt. See ?read.zoo for more information. We also convert tt back to zoo giving zz. 'z' has years actually present in the data whereas tt and zz have all years between the smallest and largest years.

library(zoo)

z <- a |>
  type.convert(as.is = TRUE) |>  # convert to numeric
  read.zoo(FUN = c, index = "year", split = "NameCorp")
tt <- as.ts(z)
zz <- as.zoo(tt)

Here is a graph of z

library(ggplot2)
autoplot(na.approx(z), facet = NULL)

Answer 2

library(tidyverse) 

a %>% 
  type_convert() %>% 
  complete(NameCorp, year = full_seq(year, 1)) %>% 
  pivot_wider(names_from = NameCorp, values_from = value)

# A tibble: 19 × 6
    year `AAC Holdings, Inc.` `Accuride Corporation` `Adeptus Health Inc.` `Akorn, Inc.` `Aleris International, Inc.`
   <dbl>                <dbl>                  <dbl>                 <dbl>         <dbl>                        <dbl>
 1  2002                   NA                    300                  NA              NA                           NA
 2  2003                   NA                     NA                  NA              NA                           NA
 3  2004                   NA                     NA                  NA              NA                           NA
 4  2005                   NA                    500                  NA              NA                           NA
 5  2006                   NA                     NA                  NA              NA                           NA
 6  2007                   NA                     NA                  NA              NA                         2400
 7  2008                   NA                     NA                  NA              NA                           NA
 8  2009                   NA                     NA                  NA              NA                         2025
 9  2010                   NA                     NA                  NA              NA                          500
10  2011                   NA                   2500                  NA              NA                           NA
11  2012                   NA                     NA                  NA             150                           NA
12  2013                   NA                     NA                  NA              NA                           NA
13  2014                  125                     NA                  NA              NA                           NA
14  2015                 1115                     NA                  NA              NA                           NA
15  2016                   NA                     NA                  NA              NA                           NA
16  2017                   NA                     NA                  NA              NA                           NA
17  2018                   NA                     NA                1100              NA                           NA
18  2019                   NA                     NA                  NA             250                           NA
19  2020                   NA                     NA                  47.5           900                           NA