Bert Gunter
2021-Apr-13 17:47 UTC
[R] Add a new row based on test set predicted values and time stamps
It may not be necessary to insert the rows in that order -- R can identify
and use the information from the rows in in most cases without it.
So to combine the results as you described (the code you sent got garbled a
bit btw -- you should proofread more carefully in future), all you would
need to do is:
## with train and test your train and test data frames of course
out <- na.omit(rbind(train, cbind(test[,c(1,3,4)], Value =
test[,"value"])))
## Note that the cbind() stuff is needed to create the correct "Value"
column for rbind(). See ?rbind for details
If you insist that you need the row ordering as you specified, then follow
this by:
out <- with(out, out[order(Group, id, as.POSIXct(Date,format =
"%D%")), ])
What this does is to first convert your text data column to POSIXct ("See
?DateTimeClasses for details) which gives them the desired calendar
ordering. The order() function (see ?order for details) then gives the
permutation ordering them from early to late within groups and id's, which
are then used as the row subscripts to reorder the rows in the data frame.
DO NOTE: For this to work reliably, your Date column must be consistent and
correct in its formatting!
Other note: It probably makes more sense to convert your Date column to a
POSIXct or POSIXlt dates from the beginning, as this will make things like
plotting in date order straightforward. There are also date-time packages
(in the "tidyverse" suite, I think, as well as others) that simplify
such
things. I am pretty ignorant about date-time stuff, so I can't really be
more specific. https://cran.r-project.org/web/views/TimeSeries.html will
have lots of info on this if you need it. As well as searching, of course.
HTH
Bert Gunter
"The trouble with having an open mind is that people keep coming along and
sticking things into it."
-- Opus (aka Berkeley Breathed in his "Bloom County" comic strip )
On Tue, Apr 13, 2021 at 3:26 AM Elahe chalabi via R-help <
r-help at r-project.org> wrote:
> Hi all,
>
> I have the prediction for my test set which are forecasted Value for
> "4/1/2020" for each match of "id" and
"Group". I would like to add a fourth
> row to each group by (Group,id) in my train set and the values for this row
> should come from test set :
>
> my train set:
>
> structure(list(Date = c("1/1/2020", "2/1/2020",
"3/1/2020",
> "1/1/2020",
> "2/1/2020", "3/1/2020", "1/1/2020",
"2/1/2020", "3/1/2020", ""
> ), Value = c(3.5, 2.7, 4, 2.5, 3.7, 0, 3, 0, 1, NA), Group =
c("A",
> "A", "A", "B", "B",
"B", "C", "C", "C", ""), id =
c(1L, 1L, 1L,
> 101L, 101L, 101L, 100L, 100L, 100L, NA)), class =
"data.frame",
> row.names = c(NA,
> -10L))
>
> test set:
>
> structure(list(Date = c("4/1/2020", "4/1/2020",
"4/1/2020", ""
> ), Value = c(3.5, 2.5, 3, NA), Group = c("A",
"B", "C", ""),
> id = c(1L, 101L, 100L, NA), value = c(0.2, 0.7, 0.9, NA)), class >
"data.frame", row.names = c(NA,
> -4L))structure(list(Date = c("4/1/2020",
"4/1/2020", "4/1/2020", ""
> ), Value = c(3.5, 2.5, 3, NA), Group = c("A", "B",
"C", ""),
> id = c(1L, 101L, 100L, NA)), class = "data.frame", row.names
= c(NA,
> -4L))
>
> desired output:
>
> structure(list(Date = c("1/1/2020", "2/1/2020",
"3/1/2020",
> "4/1/2020",
> "1/1/2020", "2/1/2020", "3/1/2020",
"4/1/2020", "1/1/2020",
> "2/1/2020",
> "3/1/2020", "4/1/2020"), Value = c(3.5, 2.7, 4,
0.2, 2.5, 3.7,
> 0, 0.7, 3, 0, 1, 0.9), Group = c("A", "A",
"A", "A", "B", "B",
> "B", "B", "C", "C",
"C", "C"), id = c(1L, 1L, 1L, 1L, 101L, 101L,
> 101L, 101L, 100L, 100L, 100L, 100L)), class = "data.frame",
row.names
> = c(NA,
> -12L))
>
> Data is dummy and I have milions of records in original data set.
>
> Thanks for any help.
> Elahe
>
>
> ______________________________________________
> 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.
>
[[alternative HTML version deleted]]
Bert Gunter
2021-Apr-13 17:55 UTC
[R] Add a new row based on test set predicted values and time stamps
(Revealing my ignorance): Simpler still than the as.POSIXct() idiom is just to use the as.Date version: out <- with(out, out [order(Group, id, as.Date(Date)),]) ## all else the same... Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Tue, Apr 13, 2021 at 10:47 AM Bert Gunter <bgunter.4567 at gmail.com> wrote:> It may not be necessary to insert the rows in that order -- R can identify > and use the information from the rows in in most cases without it. > So to combine the results as you described (the code you sent got garbled > a bit btw -- you should proofread more carefully in future), all you would > need to do is: > > ## with train and test your train and test data frames of course > out <- na.omit(rbind(train, cbind(test[,c(1,3,4)], Value > test[,"value"]))) > ## Note that the cbind() stuff is needed to create the correct "Value" > column for rbind(). See ?rbind for details > > If you insist that you need the row ordering as you specified, then follow > this by: > > out <- with(out, out[order(Group, id, as.POSIXct(Date,format = "%D%")), ]) > > What this does is to first convert your text data column to POSIXct ("See > ?DateTimeClasses for details) which gives them the desired calendar > ordering. The order() function (see ?order for details) then gives the > permutation ordering them from early to late within groups and id's, which > are then used as the row subscripts to reorder the rows in the data frame. > > DO NOTE: For this to work reliably, your Date column must be consistent > and correct in its formatting! > > Other note: It probably makes more sense to convert your Date column to a > POSIXct or POSIXlt dates from the beginning, as this will make things like > plotting in date order straightforward. There are also date-time packages > (in the "tidyverse" suite, I think, as well as others) that simplify such > things. I am pretty ignorant about date-time stuff, so I can't really be > more specific. https://cran.r-project.org/web/views/TimeSeries.html will > have lots of info on this if you need it. As well as searching, of course. > > HTH > > Bert Gunter > > "The trouble with having an open mind is that people keep coming along and > sticking things into it." > -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) > > > On Tue, Apr 13, 2021 at 3:26 AM Elahe chalabi via R-help < > r-help at r-project.org> wrote: > >> Hi all, >> >> I have the prediction for my test set which are forecasted Value for >> "4/1/2020" for each match of "id" and "Group". I would like to add a fourth >> row to each group by (Group,id) in my train set and the values for this row >> should come from test set : >> >> my train set: >> >> structure(list(Date = c("1/1/2020", "2/1/2020", "3/1/2020", >> "1/1/2020", >> "2/1/2020", "3/1/2020", "1/1/2020", "2/1/2020", "3/1/2020", "" >> ), Value = c(3.5, 2.7, 4, 2.5, 3.7, 0, 3, 0, 1, NA), Group = c("A", >> "A", "A", "B", "B", "B", "C", "C", "C", ""), id = c(1L, 1L, 1L, >> 101L, 101L, 101L, 100L, 100L, 100L, NA)), class = "data.frame", >> row.names = c(NA, >> -10L)) >> >> test set: >> >> structure(list(Date = c("4/1/2020", "4/1/2020", "4/1/2020", "" >> ), Value = c(3.5, 2.5, 3, NA), Group = c("A", "B", "C", ""), >> id = c(1L, 101L, 100L, NA), value = c(0.2, 0.7, 0.9, NA)), class >> "data.frame", row.names = c(NA, >> -4L))structure(list(Date = c("4/1/2020", "4/1/2020", "4/1/2020", "" >> ), Value = c(3.5, 2.5, 3, NA), Group = c("A", "B", "C", ""), >> id = c(1L, 101L, 100L, NA)), class = "data.frame", row.names = c(NA, >> -4L)) >> >> desired output: >> >> structure(list(Date = c("1/1/2020", "2/1/2020", "3/1/2020", >> "4/1/2020", >> "1/1/2020", "2/1/2020", "3/1/2020", "4/1/2020", "1/1/2020", >> "2/1/2020", >> "3/1/2020", "4/1/2020"), Value = c(3.5, 2.7, 4, 0.2, 2.5, 3.7, >> 0, 0.7, 3, 0, 1, 0.9), Group = c("A", "A", "A", "A", "B", "B", >> "B", "B", "C", "C", "C", "C"), id = c(1L, 1L, 1L, 1L, 101L, 101L, >> 101L, 101L, 100L, 100L, 100L, 100L)), class = "data.frame", row.names >> = c(NA, >> -12L)) >> >> Data is dummy and I have milions of records in original data set. >> >> Thanks for any help. >> Elahe >> >> >> ______________________________________________ >> 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. >> >[[alternative HTML version deleted]]