insert a row after a group and fill with next row values

Multi tool use
I would like to insert a new row after each group (grp) and fill some of the new columns with the next row values and some with previous row values
I'm trying to use:
x<-rbind(setDT(DF), DF[,.SD[.N], grp][, color := shift(color,1L, type = "lag")][, Lat:= shift(Lat,1L, type = "lead")])[order(id)]
on a DF:
a <- c(1,2,3,4,5,6,7,8,9,10)
b <- c(10,20,30,40,50,60,70,80,90,100)
c <- c("a","a","b","b","b","a","a","b","c","c")
d <- c(11,23,67,89,90,100,101,123,200,290)
df <- data.frame(color=a, Lat=b, grp=c, id=d)
I'm probably using the shift() incorrect and I cant seem to make it work.
thanks
expected results to be:
color Lat grp id
1 1 10 a 11
2 2 20 a 23
new row with color from previous row, Lat from next row, grp from previous and if from next
3 3 30 b 67
4 4 40 b 89
5 5 50 b 90
new row as before
6 6 60 a 100
7 7 70 a 101
new row as before
8 8 80 b 123
new row as before
9 9 90 c 200
10 10 100 c 290
r list insert row shift
add a comment |
I would like to insert a new row after each group (grp) and fill some of the new columns with the next row values and some with previous row values
I'm trying to use:
x<-rbind(setDT(DF), DF[,.SD[.N], grp][, color := shift(color,1L, type = "lag")][, Lat:= shift(Lat,1L, type = "lead")])[order(id)]
on a DF:
a <- c(1,2,3,4,5,6,7,8,9,10)
b <- c(10,20,30,40,50,60,70,80,90,100)
c <- c("a","a","b","b","b","a","a","b","c","c")
d <- c(11,23,67,89,90,100,101,123,200,290)
df <- data.frame(color=a, Lat=b, grp=c, id=d)
I'm probably using the shift() incorrect and I cant seem to make it work.
thanks
expected results to be:
color Lat grp id
1 1 10 a 11
2 2 20 a 23
new row with color from previous row, Lat from next row, grp from previous and if from next
3 3 30 b 67
4 4 40 b 89
5 5 50 b 90
new row as before
6 6 60 a 100
7 7 70 a 101
new row as before
8 8 80 b 123
new row as before
9 9 90 c 200
10 10 100 c 290
r list insert row shift
1
Can you show the expected output. What is the issue in the current code
– akrun
Dec 31 '18 at 12:09
amended with the expect results.
– patfee
Dec 31 '18 at 12:30
that is perfect! exactly what I wanted. don't think I understand the code though
– patfee
Dec 31 '18 at 12:51
add a comment |
I would like to insert a new row after each group (grp) and fill some of the new columns with the next row values and some with previous row values
I'm trying to use:
x<-rbind(setDT(DF), DF[,.SD[.N], grp][, color := shift(color,1L, type = "lag")][, Lat:= shift(Lat,1L, type = "lead")])[order(id)]
on a DF:
a <- c(1,2,3,4,5,6,7,8,9,10)
b <- c(10,20,30,40,50,60,70,80,90,100)
c <- c("a","a","b","b","b","a","a","b","c","c")
d <- c(11,23,67,89,90,100,101,123,200,290)
df <- data.frame(color=a, Lat=b, grp=c, id=d)
I'm probably using the shift() incorrect and I cant seem to make it work.
thanks
expected results to be:
color Lat grp id
1 1 10 a 11
2 2 20 a 23
new row with color from previous row, Lat from next row, grp from previous and if from next
3 3 30 b 67
4 4 40 b 89
5 5 50 b 90
new row as before
6 6 60 a 100
7 7 70 a 101
new row as before
8 8 80 b 123
new row as before
9 9 90 c 200
10 10 100 c 290
r list insert row shift
I would like to insert a new row after each group (grp) and fill some of the new columns with the next row values and some with previous row values
I'm trying to use:
x<-rbind(setDT(DF), DF[,.SD[.N], grp][, color := shift(color,1L, type = "lag")][, Lat:= shift(Lat,1L, type = "lead")])[order(id)]
on a DF:
a <- c(1,2,3,4,5,6,7,8,9,10)
b <- c(10,20,30,40,50,60,70,80,90,100)
c <- c("a","a","b","b","b","a","a","b","c","c")
d <- c(11,23,67,89,90,100,101,123,200,290)
df <- data.frame(color=a, Lat=b, grp=c, id=d)
I'm probably using the shift() incorrect and I cant seem to make it work.
thanks
expected results to be:
color Lat grp id
1 1 10 a 11
2 2 20 a 23
new row with color from previous row, Lat from next row, grp from previous and if from next
3 3 30 b 67
4 4 40 b 89
5 5 50 b 90
new row as before
6 6 60 a 100
7 7 70 a 101
new row as before
8 8 80 b 123
new row as before
9 9 90 c 200
10 10 100 c 290
r list insert row shift
r list insert row shift
edited Dec 31 '18 at 12:30
patfee
asked Dec 31 '18 at 12:08
patfeepatfee
265
265
1
Can you show the expected output. What is the issue in the current code
– akrun
Dec 31 '18 at 12:09
amended with the expect results.
– patfee
Dec 31 '18 at 12:30
that is perfect! exactly what I wanted. don't think I understand the code though
– patfee
Dec 31 '18 at 12:51
add a comment |
1
Can you show the expected output. What is the issue in the current code
– akrun
Dec 31 '18 at 12:09
amended with the expect results.
– patfee
Dec 31 '18 at 12:30
that is perfect! exactly what I wanted. don't think I understand the code though
– patfee
Dec 31 '18 at 12:51
1
1
Can you show the expected output. What is the issue in the current code
– akrun
Dec 31 '18 at 12:09
Can you show the expected output. What is the issue in the current code
– akrun
Dec 31 '18 at 12:09
amended with the expect results.
– patfee
Dec 31 '18 at 12:30
amended with the expect results.
– patfee
Dec 31 '18 at 12:30
that is perfect! exactly what I wanted. don't think I understand the code though
– patfee
Dec 31 '18 at 12:51
that is perfect! exactly what I wanted. don't think I understand the code though
– patfee
Dec 31 '18 at 12:51
add a comment |
1 Answer
1
active
oldest
votes
We create two lead
columns i.e. next row values of 'Lat', and 'id' before we do the group by rleid
of 'grp'. Here, the rleid
is checking whether the adjacent elements of 'grp' are the same or not. If it is different, it assigns a new id to that element
library(data.table)
setDT(df)[, c("LatN", "idN") := shift(.SD, type = 'lead'), .SDcols = c('Lat', 'id')]
Get the last
observation of the selected columns by the rleid
of 'grp'
tmp <- df[, .(color = color[.N], Lat = LatN[.N], id = idN[.N], grp = grp[.N]),
.(grp1 = rleid(grp))][, grp1 := NULL]
rbind
with the original dataset columns, order
by the 'color' and remove any NA
rows
na.omit(rbind(df[,.(color, Lat, id, grp)], tmp)[order(color)])
# color Lat id grp
# 1: 1 10 11 a
# 2: 2 20 23 a
# 3: 2 30 67 a
# 4: 3 30 67 b
# 5: 4 40 89 b
# 6: 5 50 90 b
# 7: 5 60 100 b
# 8: 6 60 100 a
# 9: 7 70 101 a
#10: 7 80 123 a
#11: 8 80 123 b
#12: 8 90 200 b
#13: 9 90 200 c
#14: 10 100 290 c
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987342%2finsert-a-row-after-a-group-and-fill-with-next-row-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
We create two lead
columns i.e. next row values of 'Lat', and 'id' before we do the group by rleid
of 'grp'. Here, the rleid
is checking whether the adjacent elements of 'grp' are the same or not. If it is different, it assigns a new id to that element
library(data.table)
setDT(df)[, c("LatN", "idN") := shift(.SD, type = 'lead'), .SDcols = c('Lat', 'id')]
Get the last
observation of the selected columns by the rleid
of 'grp'
tmp <- df[, .(color = color[.N], Lat = LatN[.N], id = idN[.N], grp = grp[.N]),
.(grp1 = rleid(grp))][, grp1 := NULL]
rbind
with the original dataset columns, order
by the 'color' and remove any NA
rows
na.omit(rbind(df[,.(color, Lat, id, grp)], tmp)[order(color)])
# color Lat id grp
# 1: 1 10 11 a
# 2: 2 20 23 a
# 3: 2 30 67 a
# 4: 3 30 67 b
# 5: 4 40 89 b
# 6: 5 50 90 b
# 7: 5 60 100 b
# 8: 6 60 100 a
# 9: 7 70 101 a
#10: 7 80 123 a
#11: 8 80 123 b
#12: 8 90 200 b
#13: 9 90 200 c
#14: 10 100 290 c
add a comment |
We create two lead
columns i.e. next row values of 'Lat', and 'id' before we do the group by rleid
of 'grp'. Here, the rleid
is checking whether the adjacent elements of 'grp' are the same or not. If it is different, it assigns a new id to that element
library(data.table)
setDT(df)[, c("LatN", "idN") := shift(.SD, type = 'lead'), .SDcols = c('Lat', 'id')]
Get the last
observation of the selected columns by the rleid
of 'grp'
tmp <- df[, .(color = color[.N], Lat = LatN[.N], id = idN[.N], grp = grp[.N]),
.(grp1 = rleid(grp))][, grp1 := NULL]
rbind
with the original dataset columns, order
by the 'color' and remove any NA
rows
na.omit(rbind(df[,.(color, Lat, id, grp)], tmp)[order(color)])
# color Lat id grp
# 1: 1 10 11 a
# 2: 2 20 23 a
# 3: 2 30 67 a
# 4: 3 30 67 b
# 5: 4 40 89 b
# 6: 5 50 90 b
# 7: 5 60 100 b
# 8: 6 60 100 a
# 9: 7 70 101 a
#10: 7 80 123 a
#11: 8 80 123 b
#12: 8 90 200 b
#13: 9 90 200 c
#14: 10 100 290 c
add a comment |
We create two lead
columns i.e. next row values of 'Lat', and 'id' before we do the group by rleid
of 'grp'. Here, the rleid
is checking whether the adjacent elements of 'grp' are the same or not. If it is different, it assigns a new id to that element
library(data.table)
setDT(df)[, c("LatN", "idN") := shift(.SD, type = 'lead'), .SDcols = c('Lat', 'id')]
Get the last
observation of the selected columns by the rleid
of 'grp'
tmp <- df[, .(color = color[.N], Lat = LatN[.N], id = idN[.N], grp = grp[.N]),
.(grp1 = rleid(grp))][, grp1 := NULL]
rbind
with the original dataset columns, order
by the 'color' and remove any NA
rows
na.omit(rbind(df[,.(color, Lat, id, grp)], tmp)[order(color)])
# color Lat id grp
# 1: 1 10 11 a
# 2: 2 20 23 a
# 3: 2 30 67 a
# 4: 3 30 67 b
# 5: 4 40 89 b
# 6: 5 50 90 b
# 7: 5 60 100 b
# 8: 6 60 100 a
# 9: 7 70 101 a
#10: 7 80 123 a
#11: 8 80 123 b
#12: 8 90 200 b
#13: 9 90 200 c
#14: 10 100 290 c
We create two lead
columns i.e. next row values of 'Lat', and 'id' before we do the group by rleid
of 'grp'. Here, the rleid
is checking whether the adjacent elements of 'grp' are the same or not. If it is different, it assigns a new id to that element
library(data.table)
setDT(df)[, c("LatN", "idN") := shift(.SD, type = 'lead'), .SDcols = c('Lat', 'id')]
Get the last
observation of the selected columns by the rleid
of 'grp'
tmp <- df[, .(color = color[.N], Lat = LatN[.N], id = idN[.N], grp = grp[.N]),
.(grp1 = rleid(grp))][, grp1 := NULL]
rbind
with the original dataset columns, order
by the 'color' and remove any NA
rows
na.omit(rbind(df[,.(color, Lat, id, grp)], tmp)[order(color)])
# color Lat id grp
# 1: 1 10 11 a
# 2: 2 20 23 a
# 3: 2 30 67 a
# 4: 3 30 67 b
# 5: 4 40 89 b
# 6: 5 50 90 b
# 7: 5 60 100 b
# 8: 6 60 100 a
# 9: 7 70 101 a
#10: 7 80 123 a
#11: 8 80 123 b
#12: 8 90 200 b
#13: 9 90 200 c
#14: 10 100 290 c
edited Dec 31 '18 at 12:59
answered Dec 31 '18 at 12:45
akrunakrun
407k13197272
407k13197272
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53987342%2finsert-a-row-after-a-group-and-fill-with-next-row-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
y4e ylFE08sW4TkbJ,T,C F wso4f8BLsLn6,XZXW
1
Can you show the expected output. What is the issue in the current code
– akrun
Dec 31 '18 at 12:09
amended with the expect results.
– patfee
Dec 31 '18 at 12:30
that is perfect! exactly what I wanted. don't think I understand the code though
– patfee
Dec 31 '18 at 12:51