Pandas dataframe get value of last nonzero column

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
6
down vote

favorite












I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.



In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited. 



import pandas as pd

df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]], 
 columns=['site1', 'site2', 'site3'])
print(df)

 site1 site2 site3
0 5 8 1
1 8 0 0
2 1 17 0


In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.



I would like to create a new column, last_site, which shows the last site visited by the user in that session.



The result I want is this:



 site1 site2 site3 last_site
0 5 8 1 1
1 8 0 0 8
2 1 17 0 17


How can I do this in a concise way using pandas?






python pandas dataframe







share|improve this question

























    up vote
    6
    down vote

    favorite












    I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.



    In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited. 



    import pandas as pd

    df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]], 
     columns=['site1', 'site2', 'site3'])
    print(df)

     site1 site2 site3
    0 5 8 1
    1 8 0 0
    2 1 17 0


    In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.



    I would like to create a new column, last_site, which shows the last site visited by the user in that session.



    The result I want is this:



     site1 site2 site3 last_site
    0 5 8 1 1
    1 8 0 0 8
    2 1 17 0 17


    How can I do this in a concise way using pandas?






    python pandas dataframe







    share|improve this question























      up vote
      6
      down vote

      favorite









      up vote
      6
      down vote

      favorite











      I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.



      In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited. 



      import pandas as pd

      df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]], 
       columns=['site1', 'site2', 'site3'])
      print(df)

       site1 site2 site3
      0 5 8 1
      1 8 0 0
      2 1 17 0


      In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.



      I would like to create a new column, last_site, which shows the last site visited by the user in that session.



      The result I want is this:



       site1 site2 site3 last_site
      0 5 8 1 1
      1 8 0 0 8
      2 1 17 0 17


      How can I do this in a concise way using pandas?






      python pandas dataframe







      share|improve this question













      I have a pandas dataframe which contains 3 columns, each containing a site that a user has visited during a session.



      In some cases, a user may have not visited 3 sites in a single session. This is shown by a 0, denoting that no site has been visited. 



      import pandas as pd

      df = pd.DataFrame(data=[[5, 8, 1],[8,0,0],[1,17,0]], 
       columns=['site1', 'site2', 'site3'])
      print(df)

       site1 site2 site3
      0 5 8 1
      1 8 0 0
      2 1 17 0


      In the example above, user 0 has visited sites 5, 8 and 1. User 1 has visited site 8 only, and user 2 has visited sites 1 and 17.



      I would like to create a new column, last_site, which shows the last site visited by the user in that session.



      The result I want is this:



       site1 site2 site3 last_site
      0 5 8 1 1
      1 8 0 0 8
      2 1 17 0 17


      How can I do this in a concise way using pandas?






      python pandas dataframe




      python pandas dataframe






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked 50 mins ago









      kskyriacou

      2,21411533




      2,21411533






















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          8
          down vote



          accepted










          Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:



          df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17


          If performance is important is possible use numpy:



          a = df.values
          m = a != 0

          df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer


















          • 3




            This forward filling logic here is excellent across the rows :) +1
            – pygo
            41 mins ago










          • Yes forward filling across rows is out of the box thinking
            – Vishnudev
            37 mins ago











          • Great and Instant logic indeed :-) .
            – pygo
            37 mins ago


















          up vote
          2
          down vote













          Code:



          df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)


          Output:



           site1 site2 site3 last_site
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer




















          • Good one @Vishnudev +1 !
            – pygo
            37 mins ago










          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',
          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%2f53133183%2fpandas-dataframe-get-value-of-last-nonzero-column%23new-answer', 'question_page');

          );

          Post as a guest

















          2 Answers
          2






          active

          oldest

          votes








          2 Answers
          2






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes








          up vote
          8
          down vote



          accepted










          Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:



          df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17


          If performance is important is possible use numpy:



          a = df.values
          m = a != 0

          df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer


















          • 3




            This forward filling logic here is excellent across the rows :) +1
            – pygo
            41 mins ago










          • Yes forward filling across rows is out of the box thinking
            – Vishnudev
            37 mins ago











          • Great and Instant logic indeed :-) .
            – pygo
            37 mins ago















          up vote
          8
          down vote



          accepted










          Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:



          df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17


          If performance is important is possible use numpy:



          a = df.values
          m = a != 0

          df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer


















          • 3




            This forward filling logic here is excellent across the rows :) +1
            – pygo
            41 mins ago










          • Yes forward filling across rows is out of the box thinking
            – Vishnudev
            37 mins ago











          • Great and Instant logic indeed :-) .
            – pygo
            37 mins ago













          up vote
          8
          down vote



          accepted







          up vote
          8
          down vote



          accepted






          Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:



          df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17


          If performance is important is possible use numpy:



          a = df.values
          m = a != 0

          df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer














          Use forward filling of misisng values created by replacing 0 values and thenselect last column by iloc:



          df['last'] = df.replace(0, np.nan).ffill(axis=1).iloc[:, -1].astype(int)
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17


          If performance is important is possible use numpy:



          a = df.values
          m = a != 0

          df['last'] = a[np.arange(m.shape[0]), m.shape[1]-m[:,::-1].argmax(1)-1]
          print (df)
           site1 site2 site3 last
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17






          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 31 mins ago

























          answered 47 mins ago









          jezrael

          301k20229304




          301k20229304







          • 3




            This forward filling logic here is excellent across the rows :) +1
            – pygo
            41 mins ago










          • Yes forward filling across rows is out of the box thinking
            – Vishnudev
            37 mins ago











          • Great and Instant logic indeed :-) .
            – pygo
            37 mins ago













          • 3




            This forward filling logic here is excellent across the rows :) +1
            – pygo
            41 mins ago










          • Yes forward filling across rows is out of the box thinking
            – Vishnudev
            37 mins ago











          • Great and Instant logic indeed :-) .
            – pygo
            37 mins ago








          3




          3




          This forward filling logic here is excellent across the rows :) +1
          – pygo
          41 mins ago




          This forward filling logic here is excellent across the rows :) +1
          – pygo
          41 mins ago












          Yes forward filling across rows is out of the box thinking
          – Vishnudev
          37 mins ago





          Yes forward filling across rows is out of the box thinking
          – Vishnudev
          37 mins ago













          Great and Instant logic indeed :-) .
          – pygo
          37 mins ago





          Great and Instant logic indeed :-) .
          – pygo
          37 mins ago













          up vote
          2
          down vote













          Code:



          df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)


          Output:



           site1 site2 site3 last_site
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer




















          • Good one @Vishnudev +1 !
            – pygo
            37 mins ago














          up vote
          2
          down vote













          Code:



          df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)


          Output:



           site1 site2 site3 last_site
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer




















          • Good one @Vishnudev +1 !
            – pygo
            37 mins ago












          up vote
          2
          down vote










          up vote
          2
          down vote









          Code:



          df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)


          Output:



           site1 site2 site3 last_site
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17





          share|improve this answer












          Code:



          df['last_site'] = df.apply(lambda x: x.iloc[x.nonzero()].iloc[-1], axis=1)


          Output:



           site1 site2 site3 last_site
          0 5 8 1 1
          1 8 0 0 8
          2 1 17 0 17






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered 42 mins ago









          Vishnudev

          602315




          602315











          • Good one @Vishnudev +1 !
            – pygo
            37 mins ago
















          • Good one @Vishnudev +1 !
            – pygo
            37 mins ago















          Good one @Vishnudev +1 !
          – pygo
          37 mins ago




          Good one @Vishnudev +1 !
          – pygo
          37 mins ago

















           

          draft saved


          draft discarded















































           


          draft saved


          draft discarded














          StackExchange.ready(
          function ()
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53133183%2fpandas-dataframe-get-value-of-last-nonzero-column%23new-answer', 'question_page');

          );

          Post as a guest
































































          Comments

          Post a Comment

          Popular posts from this blog

          Long meetings (6-7 hours a day): Being “babysat” by supervisor

          Image
          Clash Royale CLAN TAG #URR8PPP .everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0; up vote 31 down vote favorite 4 I hold a PhD in mechanical engineering with a 7 year background in self-guided, self-paced research including 2 years as a graduate level course instructor and a subject matter expert for various engineering projects in a university setting in the United States. For the last 10-11 months, I am working in a research industry in France where my task is to debug C++ spaghetti code written over a period of several years by my current supervisor. It is a team of 2: just I and my supervisor. Diurnally, I am posed with a "bug of the day" to get out of this C++ code. My supervisor generally gives me about 15-30 minutes to solve it and then accosts me about whether or not the task is completed and then rinses and repeats this until the end of the day. Off-late, my supervisor has been holding 6-7 hour long "meetin
          Read more

          Is the Concept of Multiple Fantasy Races Scientifically Flawed? [closed]

          Image
          Clash Royale CLAN TAG #URR8PPP up vote 2 down vote favorite Many fantasy worlds have multiple humanoid species (elves, orcs, humans, etc) living in the same world, in addition to that, they often have the ability to interbreed. And I can't help but question their believability... fantasy-races reproduction interspecies-relations share | improve this question edited Aug 9 at 14:46 Michael Kjörling ♦ 21.1k 10 85 161 asked Aug 9 at 13:13 Chlodio 118 6 closed as unclear what you're asking by MichaelK, Gryphon, John, Vincent, Frostfyre Aug 9 at 15:25 Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question. 3 Stack Exchange exist to
          Read more

          Confectionery

          Image
          Prepared foods made with a lot of sugar or other cabohydrates "Sweetmeat" redirects here. For the racehorse, see Sweetmeat (horse). Not to be confused with Sweetbread. This Kransekake is a traditional Scandinavian baker's confection. Confectionery is the art of making confections , which are food items that are rich in sugar and carbohydrates. Exact definitions are difficult. [1] In general, though, confectionery is divided into two broad and somewhat overlapping categories, bakers' confections and sugar confections. [2] Bakers' confectionery, also called flour confections , includes principally sweet pastries, cakes, and similar baked goods. Sugar confectionery includes candies ( sweets in British English), candied nuts, chocolates, chewing gum, bubble gum, pastillage, and other confections that are made primarily of sugar. In some cases, chocolate confections (confections made of chocolate) are treated as a separate category, as are sugar-free versions of
          Read more