Converting columns with date to rows in R

Question

Let's say we have a data.frame in R like this:

d = data.frame('2019q1' = 1, '2019q2' =2, '2019q3' = 3)

Which looks like this:

  X2019q1 X2019q2 X2019q3
1       1       2       3

How can I transform it to looks like this:

Year    Quarter    Value
2019    1          1
2019    2          2
2019    3          3

Answer 1

We can gather into 'long' format and extract the components with str_extract or parse_number

library(dplyr)
library(tidyr)
library(stringr)
gather(d) %>% 
   transmute(Year = readr::parse_number(key), 
             Quarter = as.numeric(str_extract(key, "(?<=q)\\d+$")), value)
#  Year Quarter value
#1 2019       1     1
#2 2019       2     2
#3 2019       3     3

Answer 2

A quick way with tidyr's newer pivot_longer function, which allows you to reshape data and split columns in one step. Taking a look at the column names:

names(d)
#> [1] "X2019q1" "X2019q2" "X2019q3"

You'll see that they start with X to make the names valid, and that the year and quarter are separated by "q". Use that as your delimiter in pivot_longer to split out the year and quarter, then remove the non-digit from year. Optionally, you could use dplyr::mutate to convert columns to numeric.

library(tidyr)

d %>%
  pivot_longer(everything(), names_to = c("Year", "Quarter"), 
               names_sep = "q", values_to = "Value") %>%
  dplyr::mutate(Year = stringr::str_remove(Year, "\\D"))
#> # A tibble: 3 x 3
#>   Year  Quarter Value
#>   <chr> <chr>   <dbl>
#> 1 2019  1           1
#> 2 2019  2           2
#> 3 2019  3           3