CODEWITHSUNDEEP
rfunctionapply
W148SMH
W148SMH

Reputation: 172

Apply function to different groups in same column

I have some daily temperature data from 10 different sites in 3 different streams. Some sites have missing dates that need to be filled in with NA's How can I apply the code below to each site A-J so every site no longer has missing dates. Some sites are both missing the same dates. 

sorted_data = SiteA[order(SiteA$Date),]
data_length = length(sorted_data$Date)
time_min = sorted_data$Date[1]
time_max = sorted_data$Date[data_length]
all_dates = seq(time_min, time_max, by="day")
all_dates_frame = data.frame(list(Date=all_dates))
SiteA= merge(all_dates_frame, sorted_data, all=T)

Upvotes: 1

Views: 63

Answers (1)

Hack-R
Hack-R

Reputation: 23231

You could wrap that code into a function and apply it, but there are other problems with the code and it's also not a statistically valid imputation methodology (unless you know something I don't about this particular case).

This is what I recommend:

require(data.table) # to avoid trouble using rbind on dates and for speed
require(RRF)

# Simulate the data in question 
siteA <- data.table(Stream = c(1,1,1), Date = c(Sys.Date(), Sys.Date()+1, NA), site = 1)
siteB <- data.table(Stream = c(2,2,2), Date = c(Sys.Date()-1, Sys.Date()+1, NA), site = 2)
siteC <- data.table(Stream = c(3,3,3), Date = c(Sys.Date()-2, NA, Sys.Date()), site = 3)
siteD <- data.table(Stream = c(1,1,1), Date = c(NA, Sys.Date()+1, NA), site = 4)
siteE <- data.table(Stream = c(2,2,2), Date = c(Sys.Date(), NA, NA), site = 5)
siteF <- data.table(Stream = c(3,3,3), Date = c(Sys.Date(), Sys.Date()+1, NA), site = 6)
siteG <- data.table(Stream = c(1,1,1), Date = c(Sys.Date(), Sys.Date()-1, NA), site = 7)
siteH <- data.table(Stream = c(1,1,1), Date = c(Sys.Date(), Sys.Date()-3, NA), site = 8)
siteI <- data.table(Stream = c(1,1,1), Date = c(Sys.Date(), Sys.Date()+1, NA), site = 9)
siteJ <- data.table(Stream = c(1,1,1), Date = c(Sys.Date(), Sys.Date()+1, NA), site = 10)


# Combine data and impute date
DT <- rbind(siteA,siteB,siteC,siteD,siteE,siteF,siteG,siteH,siteI,siteJ)
DT <- DT[, Date:=as.factor(as.character(Date))]

DT <- na.roughfix(DT)
DT

    Stream       Date site
 1:      1 2016-06-14    1
 2:      1 2016-06-15    1
 3:      1 2016-06-14    1
 4:      2 2016-06-13    2
 5:      2 2016-06-15    2
 6:      2 2016-06-14    2
 7:      3 2016-06-12    3
 8:      3 2016-06-14    3
 9:      3 2016-06-14    3
10:      1 2016-06-14    4
11:      1 2016-06-15    4
12:      1 2016-06-14    4
13:      2 2016-06-14    5
14:      2 2016-06-14    5
15:      2 2016-06-14    5
16:      3 2016-06-14    6
17:      3 2016-06-15    6
18:      3 2016-06-14    6
19:      1 2016-06-14    7
20:      1 2016-06-13    7
21:      1 2016-06-14    7
22:      1 2016-06-14    8
23:      1 2016-06-11    8
24:      1 2016-06-14    8
25:      1 2016-06-14    9
26:      1 2016-06-15    9
27:      1 2016-06-14    9
28:      1 2016-06-14   10
29:      1 2016-06-15   10
30:      1 2016-06-14   10
    Stream       Date site

na.roughfix is, as the name implies a rough -- but widely used and statistically acceptable -- imputation method. If you want something even better (at the expense of taking a little longer to setup and run) try mice or even better amelia.

Upvotes: 1

Related Questions