[R] Adding Records to a Table in R

[Paul Bernal]

Dear Eric, thank you for your kind reply,

Assume dataset1Frame is the table containing the missing dates,

and TransitDateFrame <- seq(as.Date(dataset1Frame[1,1]),
as.Date(dataset1Frame[nrow(dataset1Frame),1]), "months")

#dataset1Frame is basically reading some fields  from a SQL Server table,
the first one being the date

#here dataset1Frame[1,1] is the first date that appears in the table
#dataset1Frame[nrow(dataset1Frame),1] is the last date available in the
table

if(nrow(dataset1Frame)!=nrow(TransitDateFrame)){
    for(i in 1:nrow(dataset1Frame)){
        if(!TransitDateFrame[i,1] %in% dataset1Frame){
            dataset1Frame <- rbind(dataset1Frame,
TransitDateFrame[i,])}else{
                dataset1Frame}
    }
}

I used this code but didn´t work, maybe I am doing something wrong here?

Best regards,

Paul

2017-11-01 15:21 GMT-05:00 Eric Berger <ericjberger at gmail.com>:

> Hi Paul,
>
> #First I set up some sample data since I don't have a copy of your data
> dtOrig <- as.Date( c("1985-04-01","1985-07-01","1985-12-01","1986-04-01"))
> dfOrig <- data.frame( TransitDate=dtOrig, Transits=c(100,100,500,325),
> CargoTons=c(1000,1080,3785,4200) )
>
> #Generate the complete set of dates as a data frame
> dfDates<- data.frame( TransitDate=seq(from=as.Date("1985-04-01"),by="1
> month",length=13) )
>
> # do the merge adding the "missing" rows (where NA will appear)
> dfNew  <- merge(dfDates, dfOrig, by="TransitDate", all.x=TRUE )
>
> # replace the NA's by zero
> dfNew[is.na(dfNew)] <- 0
>
> HTH,
> Eric
>
>
> On Wed, Nov 1, 2017 at 9:45 PM, Paul Bernal <paulbernal07 at gmail.com>
> wrote:
>
>> Dear R friends,
>>
>> I am currently working with time series data, and I have a table(as data
>> frame) that has looks like this (TransitDate are in format = "%e-%B-%Y") :
>>
>> TransitDate       Transits      CargoTons
>> 1985-04-01        100            2500
>> 1985-05-01        135            4500
>> 1985-06-01        120            1750
>> 1985-07-01        100            3750
>> 1985-08-01        200            1250
>>
>> The problem is, that there are several periods that don´t exist in the
>> table, so it has the following behavior:
>>
>> TransitDate        Transits      CargoTons
>> 1985-04-01        100             1000
>> 1985-07-01        100             1080
>> 1985-12-01        500             3785
>> 1986-04-01        325             4200
>> .
>> .
>> 2017-09-01        400             2350 (*this is the last observation)
>>
>> You can see in the last table fragment that the series jumps from
>> 1985-04-01 to 1985-07-01, then it jumps from there to 1985-12-01 making
>> the
>> time series quite irregular (non-constant chronologically speaking).
>>
>> What I want to do is create a dummy table that has the sequence from the
>> first observation (1985-04-01) up to the last one (2017-09-01) and then
>> develop a code that checks if the dates contained in the dummy table exist
>> in the original table, if they don´t exist then add those dates and put
>> zeroes on the fields.
>>
>> How can I achieve this?
>>
>> Any help will be greatly appreciated,
>>
>> Best regards,
>>
>> Paul
>>
>>         [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide http://www.R-project.org/posti
>> ng-guide.html
>> and provide commented, minimal, self-contained, reproducible code.
>
>
>

	[[alternative HTML version deleted]]