Long to wide: compacting rows-CodePudding

It is my first post. I'm a beginner with R.

I have a df like this:

date        value
2018-01-01  123
2018-02-01  12
2018-03-01  23
...
2019-01-01  3
2019-02-01  21
2019-03-01  2
...
2020-01-01  31
2020-02-01  23
2020-03-01  32
...

I want to transform it in:

year    ene feb mar     ...
2018    123 12  23  ...
2019    3   21  2   ...
2020    31  23  32  ...

I try

df <- mutate (df,year=year(as.Date(date)), month=month(as.Date(date), label=TRUE,abbr=TRUE))

I got:

date        value   month   year
2018-01-01  123     ene     2018
2018-02-01  12      feb     2018
2018-03-01  23      mar     2018
...
2019-01-01  3       ene     2019
2019-02-01  21      feb     2019
2019-03-01  2       mar     2019
...
2020-01-01  31      ene     2020
2020-02-01  23      feb     2020
2020-03-01  32      mar     2020
...

Then I do:

pivot_wider(df, names_from="month", values_from=value)

I got:

date        year    ene feb mar ...
2018-01-01  2018    123 NA  NA  ...
2018-02-01  2018    NA  12  NA  ...
2018-03-01  2018    NA  NA  23  ...
...
2019-01-01  2019    3   NA  NA  ...
2019-02-01  2019    NA  21  NA  ...
2019-03-01  2019    NA  NA  2   ...
...
2020-01-01  2020    31  NA  NA  ...
2020-02-01  2020    NA  23  NA  ...
2020-03-01  2020    NA  NA  32  ...

I need "compress" rows to up, grouping by "year", but i don't know how do it.

I'm close to solution, but I can't find it.

Thanks in advance!

CodePudding user response：

This should do it:

library(tidyverse)
library(lubridate)

df %>% 
  mutate(month = lubridate::month(as.Date(date), label=TRUE, abbr=TRUE),
         year = lubridate::year(as.Date(date))) %>%
  select(value, month, year) %>% 
  pivot_wider(id_cols = year, names_from = month, values_from = value)

Which returns:

# A tibble: 3 × 4
   year   Jan   Feb   Mar
  <dbl> <int> <int> <int>
1  2018   123    12    23
2  2019     3    21     2
3  2020    31    23    32