Converting Monthly time series to daily time series

1
votes

I am using a monthly time series data which is infact a xts object. My aim is to covert the monthly data to daily data, such that each day in a Month has a value of the particular month.

For example: 

library("xts")
observation_dates <- as.Date(c("01.12.1993", "01.01.1994",
"01.02.1994", "01.03.1994", "01.04.1994", "01.05.1994", 
"01.06.1994", "01.07.1994", "01.08.1994", "01.09.1994", 
"01.10.1994", "01.11.1994", "01.12.1994"), format = "%d.%m.%Y")
air_data <- zoo(matrix(c(21, 21, 21, 30, 35.5, 36, 38.5, 
33, 37, 37, 30, 24, 21), ncol = 1), observation_dates)
colnames(air_data) = "air_temperature"

The series is as shown above. I want to have all the 31 days in December 1993 to have a value of 21 (Air temp) so that average of the month still remains 21. And similarly i want to proceed for the rest of the months as shown.

I have tried using to.period(x, period="days") but nothing changes.

please does anyone have any idea? Your help would be appreciated

2
rdatetime-series
please give an example and not an image of your table (even text is better and allow us to test your data). See stackoverflow.com/help/how-to-ask – denis

2 Answers

1
votes

Thank you so much for your response. However i was able to solve the problem. The approach i used is similar to as suggested by Ekatef. In my case i created empty xts object containing all the dates and converted all the variables in empty xts to numeric using lapply(). Then i merged the empty xts with monthly data series using: merge(x,y,fill=na.locf). here na.locf carries forward the last observation in the monthly series to all the days in the month and subsequently follows for the other month.

0
votes

The xts package isn't applicable to your problem as according to help of to.period:

It is not possible to convert a series from a lower periodicity to a higher periodicity - e.g. weekly to daily or daily to 5 minute bars, as that would require magic

It seems, approx() function may be the best solution if interpolation is desired

# emulation of the original monthly dates
observation_dates <- as.Date(c("01.12.1993", "01.01.1994", 
"01.02.1994", "01.03.1994", "01.04.1994", "01.05.1994", 
"01.06.1994", "01.07.1994", "01.08.1994", "01.09.1994", 
"01.10.1994", "01.11.1994", "01.12.1994"), format = "%d.%m.%Y")
t_air <- c(21, 23, 20, 30, 35.5, 36, 38.5, 33, 37, 37, 30, 24, 27)
# target dates
seq_date <- seq(from = as.Date("01.12.1993", format = "%d.%m.%Y"),
to = as.Date("31.12.1994", format = "%d.%m.%Y"), by = 1)
ans <- approx(observation_dates, y = t_air, xout = seq_date)

If only one value for each month should be used, I would solve your problems using two data frames. The first one obs_data to keep the observation data with a column of the dates in a convenient "year-month" format

ym_dates <- format(observation_dates, "%Y-%m")
t_air <- c(21, 23, 20, 30, 35.5, 36, 38.5, 33, 37, 37, 30, 24, 27)
obs_data <- data.frame(observation_dates, ym_dates ,t_air)

The second one res_df to keep the target dates seq_date of daily resolution. The column air_t is filled with NA first

res_df <- data.frame(seq_date, ym = format(seq_date, "%Y-%m"),
    stringsAsFactors = FALSE, air_t = NA)

Then fill the air_t column with data from the obs_data using correspondence of the years and months as a condition

dates_to_int <- unique(res_df$ym)
for (i in seq(along.with = dates_to_int))
{
    res_df[which(res_df$ym %in% dates_to_int[i]), "air_t"] <-
        obs_data[which(obs_data$ym_dates %in% dates_to_int[i]), "t_air"]
    }

Hope, it'll be helpful :)