CODEWITHSUNDEEP
rtime-series
Liu Bei
Liu Bei

Reputation: 615

How to convert a dataframe to year-and-month separated time series table in R

I am trying to convert a dataframe containing 3000+ observations collected every day into a time series table. But I get only a few first observations. I would like to get an average for each month in each year.

For example, on August, 2020, the average should be 750. Like this.

I would like to know if there is any way to get averages by month in each year? Could you please suggest me?

The data frame is like this enter image description here

But I want this time series table containing averages by month in each year

enter image description here

Sample data is here:

structure(list(Date = c("2015-08-07", "2015-08-08", "2015-08-09", 
"2015-08-10", "2015-08-11", "2015-08-12", "2015-08-13", "2015-08-14", 
"2015-08-15", "2015-08-16", "2015-08-17", "2015-08-18", "2015-08-19", 
"2015-08-20", "2015-08-21", "2015-08-22", "2015-08-23", "2015-08-24", 
"2015-08-25", "2015-08-26"), Open = c("2.831620", "2.793760", 
"0.706136", "0.713989", "0.708087", "1.058750", "1.222240", "1.810920", 
"1.802890", "1.684350", "1.581190", "1.215300", "1.166930", "1.251180", 
"1.477520", "1.396290", "1.375000", "1.345590", "1.228610", "1.132790"
), High = c("3.536610", "2.798810", "0.879810", "0.729854", "1.131410", 
"1.289940", "1.965070", "2.261880", "1.877240", "1.695240", "1.581190", 
"1.331160", "1.317990", "1.533300", "1.556420", "1.476410", "1.409700", 
"1.362780", "1.241820", "1.202480"), Low = c("2.521120", "0.714725", 
"0.629191", "0.636546", "0.663235", "0.883608", "1.171990", "1.754750", 
"1.570980", "1.089810", "1.185340", "1.087050", "1.166930", "1.248330", 
"1.352800", "1.352680", "1.297770", "1.231270", "1.128650", "1.061830"
), Close = c("2.772120", "0.753325", "0.701897", "0.708448", 
"1.067860", "1.217440", "1.827670", "1.827870", "1.688900", "1.566030", 
"1.203610", "1.087050", "1.258860", "1.464920", "1.395290", "1.379230", 
"1.352590", "1.231270", "1.140190", "1.159980"), Adj.Close = 
c("2.772120", "0.753325", "0.701897", "0.708448", "1.067860", "1.217440", "1.827670", 
"1.827870", "1.688900", "1.566030", "1.203610", "1.087050", "1.258860", 
"1.464920", "1.395290", "1.379230", "1.352590", "1.231270", "1.140190", 
"1.159980"), Volume = c("164329", "674188", "532170", "405283", 
"1463100", "2150620", "4068680", "4637030", "2554360", "3550790", 
"1942830", "1485680", "1486240", "2843760", "2020970", "948310", 
"1589300", "924920", "1307180", "1056750")), row.names = c(NA, 
20L), class = "data.frame")

Upvotes: 0

Views: 338

Answers (1)

Liu Bei
Liu Bei

Reputation: 615

all I also come up with my own solution. I aggregate the data with mean by year and month like this

ts_data$Month <- months(ts_data$Date)

#  Get years
ts_data$Year <- format(ts_data$Date, format = "%Y")

#  Aggregate Close on months and year and get mean
ts_data <- aggregate(. ~ Month + Year, ts_data, mean)

And I create a time series using this method

ts_df <- ts(ts_data[, "Close"], frequency = 12, start = c(2015, 8), end = c(2021, 10))

And this is the result. enter image description here

Upvotes: 1

Related Questions