dataframe groupby nth with same behaviour as first and last

user_3136169 0
Viewed 104

In a dataframe, when performing groupby['col'].first() we get the first not nan value in each column (same for last). I am trying to get the second not nan value and I cannot find how. The only relevant function that I found is groupby['col'].nth(1), but it just gives me the second row with nans if exist. groupby['col'].nth(1, dropna='any') doesn't do the job since it skips rows with nans and doesn't check each column seperately. example:

df = pd.DataFrame({
    'A': [1, 1, 1, 1, 1],
    'B': [np.nan, 2, 3, 4, 5],
    'C': [np.nan, np.nan, 3, 4, 5]
}, columns=['A', 'B', 'C'])

first() behaviour: df.groupby('A').first().reset_index()

results with:

    A   B   C
0   1   2.0 3.0

on the other hand: df.groupby('A').nth(0, dropna='any').reset_index() gives:

    A   B   C
0   1   3.0 3.0

Is there a way to get the same behaviour of first/last in the nth function so I can apply it also for second or any nth item?

pandas-groupby pandas
1 Answers

You can use the generic aggregate method to filter each series with notna and then pick the index you want, for example:

df.groupby('A').aggregate(lambda x: x.array[pd.notna(x)][0])

Produces:

     B    C
A
1  2.0  3.0

Changing the index to 1 to get the second notna value gives:

     B    C
A
1  3.0  4.0

Of course that lambda is a bit naive because it will raise an IndexError if the array isn't long enough. A function like this should work:

def nth_notna(n):
    def inner(series):
        a = series.array[pd.notna(series)]
        if len(a) - 1 < n:
            return np.nan
        return a[n]
    
    return inner

Then df.groupby('A').aggregate(nth_notna(3)) will produce:

     B   C
A
1  5.0 NaN
user_14520020
Related