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)