CODEWITHSUNDEEP
r
Matt
Matt

Reputation: 333

Insert missing data ranges in table with list of multiple parameters

I have the following code:

Date_from <- c("2013-01-01","2013-02-02","2013-05-10","2013-09-13","2013-12-02","2013-01-01","2013-02-02","2013-05-10","2013-09-13","2013-12-02","2013-01-01","2013-02-02","2013-05-10","2013-09-13","2013-12-02")
Date_to <- c("2013-02-01","2013-05-07","2013-08-12","2013-11-18","2013-12-31","2013-02-01","2013-05-07","2013-08-12","2013-11-18","2013-12-31","2013-02-01","2013-05-07","2013-08-12","2013-11-18","2013-12-31")
y <- data.frame(Date_from,Date_to)
y$concentration <- c("1.4","1.5","2.5","1.5","3.5","1.4","1.5","2.5","1.5","3.5","1.4","1.5","2.5","1.5","3.5")
y$Parameter<-c("A","A","A","A","A","B","B","B","B","B","C","C","C","C","C")
y$Date_from <- as.Date(y$Date_from)
y$Date_to <- as.Date(y$Date_to)
y$concentration <- as.numeric(y$concentration)

I will need to go through the data frame and detect missing date ranges for each of the parameters and then insert those missing date ranges including an NA for concentration and the respective parameter name.

UPDATE:

The following code added the missing date ranges but I couldn´t figure out how to add the paraemter name for each missing date range:

adding<-data.frame(Parameter=NA,concentration=NA,Date_from=y$Date_to[-nrow(y)]+1, Date_to=y$Date_from[-1]-1)
adding<-adding[ adding$Date_from<=adding$Date_to,]
res<-rbind(y,adding)
y <- res[order(res$Date_from),]

The result should look like this:

Date_from    Date_to concentration Parameter
2013-01-01 2013-02-01           1.4         A
2013-02-02 2013-05-07           1.5         A
2013-05-08 2013-05-09            NA         A
2013-05-10 2013-08-12           2.5         A
2018-08-13 2013-09-12            NA         A
2013-09-13 2013-11-18           1.5         A
2013-11-19 2013-12-01            NA         A
2013-12-02 2013-12-31           3.5         A
2013-01-01 2013-02-01           1.4         B
2013-02-02 2013-05-07           1.5         B  
2013-05-08 2013-05-09            NA         B
2013-05-10 2013-08-12           2.5         B
2018-08-13 2013-09-12            NA         B 
2013-09-13 2013-11-18           1.5         B
2013-11-19 2013-12-01            NA         B
2013-12-02 2013-12-31           3.5         B
2013-01-01 2013-02-01           1.4         C
2013-02-02 2013-05-07           1.5         C
2013-05-08 2013-05-09            NA         C
2013-05-10 2013-08-12           2.5         C
2018-08-13 2013-09-12            NA         C
2013-09-13 2013-11-18           1.5         C
2013-11-19 2013-12-01            NA         C
2013-12-02 2013-12-31           3.5         C

Upvotes: 0

Views: 31

Answers (1)

gabzo
gabzo

Reputation: 238

Is this what you need? 

Date_from <- c("2013-01-01","2013-02-02","2013-05-10","2013-09-13","2013-12-02","2013-01-01","2013-02-02","2013-05-10","2013-09-13","2013-12-02","2013-01-01","2013-02-02","2013-05-10","2013-09-13","2013-12-02")
Date_to <- c("2013-02-01","2013-05-07","2013-08-12","2013-11-18","2013-12-31","2013-02-01","2013-05-07","2013-08-12","2013-11-18","2013-12-31","2013-02-01","2013-05-07","2013-08-12","2013-11-18","2013-12-31")
y <- data.frame(Date_from,Date_to)
y$concentration <- c("1.4","1.5","2.5","1.5","3.5","1.4","1.5","2.5","1.5","3.5","1.4","1.5","2.5","1.5","3.5")
y$Parameter<-c("A","A","A","A","A","B","B","B","B","B","C","C","C","C","C")
y$Date_from <- as.Date(y$Date_from)
y$Date_to <- as.Date(y$Date_to)
y$concentration <- as.numeric(y$concentration)

adding<-data.frame(Parameter=NA, concentration=NA, Date_from=y$Date_to[-nrow(y)]+1, Date_to=y$Date_from[-1]-1)
adding<-adding[ adding$Date_from<=adding$Date_to,]
adding$Parameter <- rep(c("A","B","C"),3)
res<-rbind(y,adding)

y <- res[order(res$Date_from),]

Upvotes: 1

Related Questions