Help me in reshaping data that is a daily time series into monthly one



Hi all,

I have a daily time series data for one year with two columns (Say, date and value), and I simply want to reshape my data into monthly (say, into 12 columns with column names as to January to December).

Note: I don’t want to calculate mean, sum etc., but simply reshape my data by months.

I am attaching a snapshot of the sample and reshaped data for better understanding.

Any kind of help from the community will be highly appreciated.



To reshape some data, you should look at tidyr and to manipulate datetime you should look at lubridate.
Both are in the tidyverse. Here is an example :

#> Attachement du package : 'lubridate'
#> The following object is masked from 'package:base':
#>     date

# data example with one date per month
tab <- data_frame(date = dmy("01-01-2015") + months(0:11), value = rnorm(seq_along(date)))

tab %>%
  # separate the datetime element
  mutate(month = month(date),
         # I do this to have english month because I am on a french computer
         # you could directly do month(date, label = T, abb = TRUE)
         month =[month],
         # to get them ordered
         month = factor(month, levels =, ordered = TRUE),
         day = day(date),
         year = year(date)) %>%
  # use tidyr verb to reshape the data
  select(-date) %>%
  spread(month, value)
#> # A tibble: 1 x 14
#>     day  year    Jan    Feb   Mar    Apr   May    Jun     Jul   Aug   Sep
#> * <int> <dbl>  <dbl>  <dbl> <dbl>  <dbl> <dbl>  <dbl>   <dbl> <dbl> <dbl>
#> 1     1  2015 -0.318 -0.989  1.69 -0.363  1.10 -0.104 -0.0217 -1.36  1.07
#> # ... with 3 more variables: Oct <dbl>, Nov <dbl>, Dec <dbl>

Created on 2018-01-13 by the reprex package (v0.1.1.9000).


@cderv, Thanks, that worked perfectly.