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

Multi tool use
Multi tool use












1















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









share|improve this question




















  • 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















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









share|improve this question




















  • 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








1








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









share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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














  • 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












1 Answer
1






active

oldest

votes


















0














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





share|improve this answer

























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    0














    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





    share|improve this answer






























      0














      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





      share|improve this answer




























        0












        0








        0







        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





        share|improve this answer















        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






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Dec 31 '18 at 12:59

























        answered Dec 31 '18 at 12:45









        akrunakrun

        407k13197272




        407k13197272
































            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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
            SHDpMF zeja fKtbPm IzIGQrHBk 8Mc

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas