CODEWITHSUNDEEP
rdataframemean
Gustavo
Gustavo

Reputation: 85

Calculate the mean from a specific date

I have two dataframes.

I would like to make the average of sp variable for the previous 5 days defined by a specific date from a second dataframe.

For example, the mean from the day 1997.05.05 (that would be between the day 1997.05.05 and 1997.05.01) and the average between 1997.05.27 and 1997.05.31 calculate the days that have values (in this case 3).

Here are the variables:

sp < - c(7,9,9,4,2,5,2,9,NA,14,NA,NA,NA,NA,NA,14,25,NA,11,10,12,NA,9,NA,6,8,6,1,NA,7,NA)

Date <- c("1997-05-01","1997-05-02","1997-05-03","1997-05-04","1997-05-05",
              "1997-05-06","1997-05-07","1997-05-08","1997-05-09","1997-05-10",
              "1997-05-11","1997-05-12","1997-05-13","1997-05-14","1997-05-15",
              "1997-05-16","1997-05-17","1997-05-18","1997-05-19","1997-05-20",
              "1997-05-21","1997-05-22","1997-05-23","1997-05-24","1997-05-25",
              "1997-05-26","1997-05-27","1997-05-28","1997-05-29","1997-05-30",
              "1997-05-31")

data1 <- data.frame(sp, Date)

DateX <- c("1997-05-05","1997-05-15","1997-05-31")

data2 <- data.frame(DateX)

how to do that best? Help would be much appreciated.

Here is my expected result (in the second dataframe, data2):

 1. DateX  spMean 
 2. 1997-05-05 6.2
 3. 1997-05-15 NA
 4. 1997-05-31 4.6

Upvotes: 0

Views: 1051

Answers (1)

Nick Criswell
Nick Criswell

Reputation: 1743

I have made a few type changes to your initial code. Give the below a shot...I use lapply to run a quick function against the data1 object using the dates in your second object. 

sp <- c(7,9,9,4,2,5,2,9,NA,14,NA,NA,NA,NA,NA,14,25,NA,11,10,12,NA,9,NA,6,8,6,1,NA,7,NA)

Date <- as.Date(c("1997-05-01","1997-05-02","1997-05-03","1997-05-04","1997-05-05",
          "1997-05-06","1997-05-07","1997-05-08","1997-05-09","1997-05-10",
          "1997-05-11","1997-05-12","1997-05-13","1997-05-14","1997-05-15",
          "1997-05-16","1997-05-17","1997-05-18","1997-05-19","1997-05-20",
          "1997-05-21","1997-05-22","1997-05-23","1997-05-24","1997-05-25",
          "1997-05-26","1997-05-27","1997-05-28","1997-05-29","1997-05-30",
          "1997-05-31"))

data1 <- data.frame(sp, Date)

DateX <- as.Date(c("1997-05-05","1997-05-15","1997-05-31"))

data2 <- data.frame(DateX)

#Add column for mean, NA values return NA
data2$spMean_na <- lapply(DateX, 
               function(m) mean(data1$sp[data1$Date >= m - 5 & data1$Date <= m]))

#Add column for mean, remove NA values
data2$spMean_na_omit <- lapply(DateX, 
                          function(m) mean(data1$sp[data1$Date >= m - 5 & data1$Date <= m], 
                                           na.rm = TRUE))

> data2
       DateX spMean_na spMean_na_omit
1 1997-05-05       6.2            6.2
2 1997-05-15        NA             14
3 1997-05-31        NA            5.5

I think you might need to change your expected result. Row 29 has an NA for the sp value and is within 5 days of 1997-05-31. So it should return an NA per your requirements as I understand them.

Upvotes: 2

Related Questions