Hi Paul,
The following worked for me:
library(lubridate)
dataset1 <- read.csv("dataset1.csv",stringsAsFactors=FALSE)
dataset1$TransitDate <- mdy(dataset1$TransitDate)
TransitDateFrame <- data.frame(TransitDate=seq(as.Date("1985-10-01"),
as.Date("2017-10-01"), by = "month"))
dataset1NEW <- merge(TransitDateFrame, dataset1, by="TransitDate",
all.x=TRUE)
HTH,
Eric
On Wed, Nov 8, 2017 at 4:32 PM, PIKAL Petr <petr.pi...@precheza.cz> wrote:
> 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-boun...@r-project.org] On Behalf Of Paul
> Bernal
> > Sent: Wednesday, November 8, 2017 2:46 PM
> > To: Eric Berger <ericjber...@gmail.com>
> > Cc: r-help@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 <ericjber...@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 <paulberna...@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@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@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.
> ______________________________________________
> R-help@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.
>
[[alternative HTML version deleted]]
______________________________________________
R-help@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.
