0

I have data frame as shown below:

 str(Rainfall_Complete)
'data.frame':   8221 obs. of  18 variables:
 $ Date          : Date, format: "1985-04-29" "1985-04-30" "1985-05-01" ...
 $ Month         : Ord.factor w/ 12 levels "Jan"<"Feb"<"Mar"<..:
 $ Season        : Factor w/ 4 levels "Monsoon","PostMonsoon",..:.
 $ Year          : chr  "1985" "1985" "1985" "1985" ...
 $ Stn A         : num  0 8.8 0 15 26.2 0 2.5 0 0 0 ...
 $ Stn B         : num  0 0 26 11 13.8 20 0.26 0 0 0 ...
 $ Stn C         : num  0.1 0 0 0 13.5 27 16 5 0 0 …

I want to convert the above daily time series to monthly time series I want my data to look something like this

Year  Month StnA   StnB   StnC……..

1985   Jan   150    100   120

1985   Feb   120     98    58

….

2010   Jan   200    100    87

2010   Feb   140    145    120

I tried the following, however it works only for univariate series

library(dplyr)
Monthly_rainfall <- Rainfall_Complete %>% group_by(Year,Month)%>% summarise()

Any help would be appreciated

Vasker Sharma
  • 187
  • 1
  • 16
  • you need to convert year and month into proper date format 1st,later use your code you will be able to convert it – Hunaidkhan Nov 21 '18 at 10:23
  • If you want to use `summarise` on multiple columns try `summarise_at`. You'll be able to get the `sum` of multiple columns at the same time, based on your grouping. – AntoniosK Nov 21 '18 at 10:30
  • Maybe `padr::thicken()` could be of help. – meriops Nov 21 '18 at 11:16
  • Please take a look at how to make a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) to make it easier to help – camille Nov 21 '18 at 14:46

1 Answers1

0

Your attempt uses dplyr, but the question is tagged with xts and lubridate, thus here is a solution using those packages with a reprex.

library(lubridate)
library(xts)

## Create some basic data
ans6 <- xts(anscombe[, 1:6], order.by = as.Date("2018-01-28") + 1:nrow(anscombe))

## Summary by month
mon6 <- apply.monthly(ans6, FUN = mean)

## Re-format
df6 <- as.data.frame(mon6)
df6$year <- year(rownames(df6))
df6$month <- month(rownames(df6), label = TRUE)

df6
## x1       x2       x3    x4       y1       y2 year month
## 2018-01-31 10.33333 10.33333 10.33333 8.000 7.523333 8.673333 2018   Jan
## 2018-02-08  8.50000  8.50000  8.50000 9.375 7.492500 7.061250 2018   Feb
jmuhlenkamp
  • 2,102
  • 1
  • 14
  • 37