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

dplyr

#1

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.

Regards
John


#2

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 :


library(tidyverse)
library(lubridate)
#> 
#> 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.abb[month],
         # to get them ordered
         month = factor(month, levels = month.abb, 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).


#3

@cderv, Thanks, that worked perfectly.

Regards
John