Move last non-null value of each row - Pandas

user_13811734 0
Viewed 649

I'm working on a dataframe which is from a noSQL table, which implies the rows don't have the same length. I need to retrieve the last non-null value of each row, move it to a new column 'h' and remove it from its initial position.

My initial DataFrame is:

      a           b     c     d   e     f     g
0  1635  01/01/2018  Null  Null  95   120    80
1  7364  01/15/2018   178   182  99  Null  Null
2  8947  01/20/2018  Null   190  92  Null  Null
3  6473  01/24/2018    45   122  99    32  Null

And I'd like to get this result:

      a           b     c     d     e     f     g   h
0  1635  01/01/2018  Null  Null    95   120  Null  80
1  7364  01/15/2018   178   182  Null  Null  Null  99
2  8947  01/20/2018  Null   190  Null  Null  Null  92
3  6473  01/24/2018    45   122    99  Null  Null  32
pandas dataframe python
2 Answers

Use, DataFrame.ne along with DataFrame.cumsum and DataFrame.idxmax along axis=1 to get the columns containing the last non null value, finally use DataFrame.lookup to get the values, corresponding to the cols:

cols = df.ne('Null').cumsum(axis=1).idxmax(axis=1)
df['h'] = df.lookup(df.index, cols)

Result:

# print(df)
      a           b     c     d   e     f     g   h
0  1635  01/01/2018  Null  Null  95   120    80  80
1  7364  01/15/2018   178   182  99  Null  Null  99
2  8947  01/20/2018  Null   190  92  Null  Null  92
3  6473  01/24/2018    45   122  99    32  Null  32
user_128331660

As other solution you can use last_valid_index. However, you first have to convert all the Null values to np.NaN.

df[df=="Null"] = np.NaN

df["h"] = df.apply(lambda x: x[x.last_valid_index()], axis=1)
df

Output:

    a       b           c   d   e   f   g   h
0   1635    01/01/2018          95  120 80  80
1   7364    01/15/2018  178 182 99          99
2   8947    01/20/2018      190 92          92
3   6473    01/24/2018  45  122 99  32      32
user_133280100
Related