Get the second to last column per row that isn't null

user_6545542 0
Viewed 518

I'm trying to get the second to last non-null column per row, where the null could be in any column. Solutions such as this don't work due to where the null can be anywhere: Pandas select the second to last column which is also not nan

Not Ideal Solution: I was able to solve it with the following code, but there has to be a more concise way to write this. Any feedback would be appreciated.

data = [[1, 10, np.nan, np.nan], [2, 15, 13, np.nan], [9, 14, np.nan, np.nan]] 
df = pd.DataFrame(data, columns = ['a', 'b', 'c', 'd']) 

df['count_nulls'] = len(df.columns) - df.apply(lambda x: x.count(), axis=1)
df['count_nonnull'] = df.apply(lambda x: x.count(), axis=1)-1
df['new_index'] = np.where(df['count_nonnull']==1, 1, 
                             np.where(df['count_nonnull']==0,0, df['count_nonnull'] - 1))
df['value'] = df.values[np.arange(len(df)), df['new_index']-1]
df
pandas indexing python
3 Answers

You can check for notna and do a reverse cumsum on axis=1 , then get the first column that returns 2. and get its value using df.lookup:

u = df.notna().iloc[:,::-1].cumsum(axis=1)
df['value'] = df.lookup(df.index,u.eq(2).dot(u.columns+',').str.split(',').str[0])

print(df)

   a   b     c   d  value
0  1  10   NaN NaN      1
1  2  15  13.0 NaN     15
2  9  14   NaN NaN      9

Following comments since lookup is deprecated, one can use:

u = df.notna().iloc[:,::-1].cumsum(axis=1)
v = u.eq(2).dot(u.columns+',').str.split(',').str[0]
df['value'] = df.stack().loc[pd.MultiIndex.from_arrays((v.index,v))].to_numpy()

The other parts can be solved without resorting to apply, or the nested np.where

df.assign(
    count_nulls=df.isna().sum(1),
    count_non_null=df.notna().sum(1),
    new_index=lambda df: np.select(
        [df.count_non_null == 1, df.count_non_null == 0], 
         [1, 0], 
         df.count_non_null - 1))
user_98406370

You can use pandas.DataFrame.apply and pandas.Series.shift:

df.apply(lambda x: x.shift(x.isnull().sum())[-2], axis = 1)
#0     1.0
#1    15.0
#2     9.0

The idea is to shift the rows "number of NaNs" times, so the second to last that is not NaN will be always in the second to last position.

user_137432330
user_54317910
Related