CODEWITHSUNDEEP
rreshape
Jill Clover
Jill Clover

Reputation: 2328

How to reshape based on time?

I have a time series data:

  date value
12  2016-08-05   854
13  2016-07-29  1065
14  2016-07-22   878
15  2016-07-15  1145
16  2016-07-08   735
17  2016-07-01   730
18  2016-06-24   726
19  2016-06-17  1011
20  2016-06-10  1019

What I want is a matrix of monthly data:

Jan Feb March ... (more months)
123 222 555
234 333 555
456 444 666

How do I reshape the input data to the desired output? I believe reshape function may be helpful, but not sure how.

Upvotes: 1

Views: 36

Answers (2)

Sandipan Dey
Sandipan Dey

Reputation: 23101

The following should also work:

df <- read.table(text='date value
                 12  2016-08-05   854
                 13  2016-07-29  1065
                 14  2016-07-22   878
                 15  2016-07-15  1145
                 16  2016-07-08   735
                 17  2016-07-01   730
                 18  2016-06-24   726
                 19  2016-06-17  1011
                 20  2016-06-10  1019
                 21  2015-06-01  225', header=TRUE, stringsAsFactors=FALSE)

library(dplyr)
library(tidyr)
df$date <- as.Date(df$date)
df$month <- format(df$date, '%b')
df$year <- format(df$date, '%Y')
df <- df[c('year', 'month',  'value')]
df %>% group_by(month, year) %>% summarise(value=sum(value)) %>% spread(month, value)

#  year   Aug   Jul   Jun
#  (chr) (int) (int) (int)
#1  2015    NA    NA   225
#2  2016   854  4553  2756

Upvotes: 2

akrun
akrun

Reputation: 887118

We create the 'Month' column by converting the 'Date' to Date class, format it to extract the 'Month', convert it to factor with levels specified as month.abb and then dcast it to 'wide' format

library(data.table)
setDT(df1)[, Month := factor(format(as.Date(date), "%b"), levels = month.abb)]
dcast(df1, rowid(Month)~Month, value.var = "value", drop = FALSE, fill = 0)

Or we can do this tidyverse

library(dplyr)
library(tidyr)
 df1 %>% 
    group_by(Month = factor(month.abb[month(date)], levels = month.abb))  %>%
    mutate(ind = row_number()) %>% 
    spread(Month, value, drop = FALSE, fill=0)

Upvotes: 1

Related Questions