Arrange data with ticker and year

[sirius1170]

Hello everybody,

I would like to ask for help.
I would like to change the format of this data as below format, but I am confused about using code for doing that. Thank you in advance for your support.

df<-data.frame(
      number = c(1L, 2L, 3L, 4L, 5L),
      ticker = as.factor(c("AAA", "AAM", "AAT", "ABS", "ABT")),
       X2008 = as.factor(c("  ", "  ", "  ", "  ", "  ")),
       X2009 = as.factor(c(NA, "0.36%", NA, NA, "19.22%")),
       X2010 = as.factor(c("3.99%", "1.44%", NA, NA, "15.60%")),
       X2011 = as.factor(c("10.42%", "1.50%", NA, NA, "9.20%")),
       X2012 = as.factor(c("21.61%", "2.65%", NA, NA, "7.21%")),
       X2013 = as.factor(c("21.09%", "2.55%", NA, NA, "6.78%")),
       X2014 = as.factor(c("20.59%", "0.97%", NA, NA, "7.47%")),
       X2015 = as.factor(c("19.13%", "0.49%", NA, NA, "7.53%")),
       X2016 = as.factor(c("17.49%", "0.57%", NA, NA, "4.14%")),
       X2017 = as.factor(c("11.93%", "0.82%", NA, NA, "4.74%")),
       X2018 = as.factor(c("9.88%", "0.98%", NA, NA, "2.77%"))
)

[nirgrahamuk]

Here is documentation for dplyr::pivot_longer()

Pivot data from wide to long — pivot_longer

[andresrcs]

library(tidyverse)

df<-data.frame(
    number = c(1L, 2L, 3L, 4L, 5L),
    ticker = as.factor(c("AAA", "AAM", "AAT", "ABS", "ABT")),
    X2008 = as.factor(c("  ", "  ", "  ", "  ", "  ")),
    X2009 = as.factor(c(NA, "0.36%", NA, NA, "19.22%")),
    X2010 = as.factor(c("3.99%", "1.44%", NA, NA, "15.60%")),
    X2011 = as.factor(c("10.42%", "1.50%", NA, NA, "9.20%")),
    X2012 = as.factor(c("21.61%", "2.65%", NA, NA, "7.21%")),
    X2013 = as.factor(c("21.09%", "2.55%", NA, NA, "6.78%")),
    X2014 = as.factor(c("20.59%", "0.97%", NA, NA, "7.47%")),
    X2015 = as.factor(c("19.13%", "0.49%", NA, NA, "7.53%")),
    X2016 = as.factor(c("17.49%", "0.57%", NA, NA, "4.14%")),
    X2017 = as.factor(c("11.93%", "0.82%", NA, NA, "4.74%")),
    X2018 = as.factor(c("9.88%", "0.98%", NA, NA, "2.77%"))
)

df %>% 
    pivot_longer(cols = starts_with("x"),
                 names_to = "year",
                 values_to = "percent",
                 names_pattern = "X(\\d{4})",
                 names_transform = list(year = as.integer)) %>%
    mutate(percent = parse_number(as.character(percent)))
#> # A tibble: 55 x 4
#>    number ticker  year percent
#>     <int> <fct>  <int>   <dbl>
#>  1      1 AAA     2008   NA   
#>  2      1 AAA     2009   NA   
#>  3      1 AAA     2010    3.99
#>  4      1 AAA     2011   10.4 
#>  5      1 AAA     2012   21.6 
#>  6      1 AAA     2013   21.1 
#>  7      1 AAA     2014   20.6 
#>  8      1 AAA     2015   19.1 
#>  9      1 AAA     2016   17.5 
#> 10      1 AAA     2017   11.9 
#> # … with 45 more rows

^{Created on 2020-10-13 by the reprex package (v0.3.0)}

[sirius1170]

Thank you for your support. But when I run it, there is an error like that.

[andresrcs]

Update your tidyr package

[sirius1170]

I installed package again. But it still doesn't work with the same error.

[andresrcs]

What is your tidyr version? Since it works for me but not for you, the only explanation I can think of is that you are using a different version. Anyways, you can also ommit that line of code and convert the year column to numeric later.

[sirius1170]

My version is tidyr_1.0.0. However, I omitted that line and it works well. Thank you so much for your help.
Best regard,