Adding Records to a Table in R

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Adding Records to a Table in R

PaulJr
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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Adding Records to a Table in R

Eric Berger
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 <[hidden email]> 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]]
>
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Adding Records to a Table in R

PaulJr
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 <[hidden email]>:

> 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 <[hidden email]>
> 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]]
>>
>> ______________________________________________
>> [hidden email] 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]]

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Adding Records to a Table in R

PaulJr
In reply to this post by Eric Berger
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 <[hidden email]>:

> 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 <[hidden email]>
> 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]]
>>
>> ______________________________________________
>> [hidden email] 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.
>
>
>
______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Adding Records to a Table in R

PIKAL Petr
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:[hidden email]] On Behalf Of Paul Bernal
> Sent: Wednesday, November 8, 2017 2:46 PM
> To: Eric Berger <[hidden email]>
> Cc: [hidden email]
> 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 <[hidden email]>:
>
> > 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 <[hidden email]>
> > 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]]
> >>
> >> ______________________________________________
> >> [hidden email] 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.
> >
> >
> >
> ______________________________________________
> [hidden email] 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.
______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Adding Records to a Table in R

PIKAL Petr
Sorry, I was too quick

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

Cheers
Petr

> 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:[hidden email]] On Behalf Of Paul
> > Bernal
> > Sent: Wednesday, November 8, 2017 2:46 PM
> > To: Eric Berger <[hidden email]>
> > Cc: [hidden email]
> > 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 <[hidden email]>:
> >
> > > 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 <[hidden email]>
> > > 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
> > >>

________________________________
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.
______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Adding Records to a Table in R

Eric Berger
In reply to this post by PIKAL Petr
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 <[hidden email]> 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:[hidden email]] On Behalf Of Paul
> Bernal
> > Sent: Wednesday, November 8, 2017 2:46 PM
> > To: Eric Berger <[hidden email]>
> > Cc: [hidden email]
> > 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 <[hidden email]>:
> >
> > > 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 <[hidden email]>
> > > 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]]
> > >>
> > >> ______________________________________________
> > >> [hidden email] 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.
> > >
> > >
> > >
> > ______________________________________________
> > [hidden email] 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.
> ______________________________________________
> [hidden email] 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]]

______________________________________________
[hidden email] 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.