Search code examples
rdataframereformat

Transform Date Dataframe

I have a dataframe that looks something like this:

County    2020-01-22  2020-01-23  2020-01-24
Autauga   0           1           0
Baldwin   0           2           4
Barbour   0           3           1

However, I would like to reformat it so that I get counts per day by county. Something like this:

County   year  month  day  value 
Autauga  2020  01     22   0
Baldwin  2020  01     22   0
Barbour  2020  01     22   0
Autauga  2020  01     23   1
Baldwin  2020  01     23   2
Barbour  2020  01     23   3
Autauga  2020  01     24   0
Baldwin  2020  01     24   4
Barbour  2020  01     24   1
Solution

  • We could use just pivot_longer and specify the names_sep

    library(tidyr)
pivot_longer(df1, cols = -County, 
     names_to = c("year", "month", "day"), names_sep = "-")

    -output

    # A tibble: 9 x 5
#  County  year  month day   value
#  <chr>   <chr> <chr> <chr> <int>
#1 Autauga 2020  01    22        0
#2 Autauga 2020  01    23        1
#3 Autauga 2020  01    24        0
#4 Baldwin 2020  01    22        0
#5 Baldwin 2020  01    23        2
#6 Baldwin 2020  01    24        4
#7 Barbour 2020  01    22        0
#8 Barbour 2020  01    23        3
#9 Barbour 2020  01    24        1

    data

    df1 <- structure(list(County = c("Autauga", "Baldwin", "Barbour"), 
       `2020-01-22` = c(0L, 
0L, 0L), `2020-01-23` = 1:3, `2020-01-24` = c(0L, 4L, 1L)), 
class = "data.frame", row.names = c(NA, 
-3L))