[R] Adding Records to a Table in R

Wed Nov 8 15:32:15 CET 2017

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.