Adding Records to a Table in R

Hi

Instead of attachments copy directly result of dput(TransitDateFrame) and dput(dataset1) to your email. Or, if your data have more than about 20 rows you could copy only part of it.

dput(TransitDateFrame[,1:20])
dput(dataset1[,1:20])

Only with this approach we can evaluate your data in all aspects and provide correct answer.

Cheers
Petr

-----Original Message-----
From: R-help [mailto:r-help-bounces at r-project.org] On Behalf Of Paul Bernal
Sent: Wednesday, November 8, 2017 2:46 PM
To: Eric Berger <ericjberger at gmail.com>
Cc: r-help at r-project.org
Subject: Re: [R] Adding Records to a Table in R

Dear Eric,

Hope you are doing great. I also tried the following:

#First I created the complete date sequence

TransitDateFrame <- data.frame(TransitDate=seq(as.Date(dataset1[1,1]),
as.Date(dataset1[nrow(dataset1),1]), by = "month"))

#Then I did the merging

 dataset1NEW <- merge(TransitDateFrame, dataset1, by="TransitDate",
all.x=TRUE)

Now it has, as expected the total number of rows. The problem is, it filled
absolutely everything with NAs, and this shouldn?t be the case since there are
dates that actually have data.

why is this happening?

I am attaching the dataset1 table as a .csv document for your reference.
Basically what I want is to bring all the values in dataset1 and only add the
dates missing with value 0.

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.

______________________________________________
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/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.

________________________________
Tento e-mail a jak?koliv k n?mu p?ipojen? dokumenty jsou d?v?rn? a jsou ur?eny pouze jeho adres?t?m.
Jestli?e jste obdr?el(a) tento e-mail omylem, informujte laskav? neprodlen? jeho odes?latele. Obsah tohoto emailu i s p??lohami a jeho kopie vyma?te ze sv?ho syst?mu.
Nejste-li zam??len?m adres?tem tohoto emailu, nejste opr?vn?ni tento email jakkoliv u??vat, roz?i?ovat, kop?rovat ?i zve?ej?ovat.
Odes?latel e-mailu neodpov?d? za eventu?ln? ?kodu zp?sobenou modifikacemi ?i zpo?d?n?m p?enosu e-mailu.

V p??pad?, ?e je tento e-mail sou??st? obchodn?ho jedn?n?:
- vyhrazuje si odes?latel pr?vo ukon?it kdykoliv jedn?n? o uzav?en? smlouvy, a to z jak?hokoliv d?vodu i bez uveden? d?vodu.
- a obsahuje-li nab?dku, je adres?t opr?vn?n nab?dku bezodkladn? p?ijmout; Odes?latel tohoto e-mailu (nab?dky) vylu?uje p?ijet? nab?dky ze strany p??jemce s dodatkem ?i odchylkou.
- trv? odes?latel na tom, ?e p??slu?n? smlouva je uzav?ena teprve v?slovn?m dosa?en?m shody na v?ech jej?ch n?le?itostech.
- odes?latel tohoto emailu informuje, ?e nen? opr?vn?n uzav?rat za spole?nost ??dn? smlouvy s v?jimkou p??pad?, kdy k tomu byl p?semn? zmocn?n nebo p?semn? pov??en a takov? pov??en? nebo pln? moc byly adres?tovi tohoto emailu p??padn? osob?, kterou adres?t zastupuje, p?edlo?eny nebo jejich existence je adres?tovi ?i osob? j?m zastoupen? zn?m?.

This e-mail and any documents attached to it may be confidential and are intended only for its intended recipients.
If you received this e-mail by mistake, please immediately inform its sender. Delete the contents of this e-mail with all attachments and its copies from your system.
If you are not the intended recipient of this e-mail, you are not authorized to use, disseminate, copy or disclose this e-mail in any manner.
The sender of this e-mail shall not be liable for any possible damage caused by modifications of the e-mail or by delay with transfer of the email.

In case that this e-mail forms part of business dealings:
- the sender reserves the right to end negotiations about entering into a contract in any time, for any reason, and without stating any reasoning.
- if the e-mail contains an offer, the recipient is entitled to immediately accept such offer; The sender of this e-mail (offer) excludes any acceptance of the offer on the part of the recipient containing any amendment or variation.
- the sender insists on that the respective contract is concluded only upon an express mutual agreement on all its aspects.
- the sender of this e-mail informs that he/she is not authorized to enter into any contracts on behalf of the company except for cases in which he/she is expressly authorized to do so in writing, and such authorization or power of attorney is submitted to the recipient or the person represented by the recipient, or the existence of such authorization is known to the recipient of the person represented by the recipient.