ENH: Add option `skipna: bool` to `Series.items()` / introduce `first_valid_value`

[randolf-scholz]

Feature Type

• Adding new functionality to pandas

• Changing existing functionality in pandas

• Removing existing functionality in pandas

Problem Description

I stumbled over the problem of finding the first non-null value in a Series: https://stackoverflow.com/questions/42137529/pandas-find-first-non-null-value-in-column

It seems that Series.first_valid_index is the intended function for this, however, it doesn't work well when there are duplicate indices. I have some real world data that looks like this:

import pandas as pd

values = [pd.NA, pd.NA, 0.6, 1.7, pd.NA, pd.NA]
index = pd.Index(["2020-12-09 09:48:38"] * len(values), name="time", dtype="datetime64[ns]")
s = pd.Series(values, dtype="Float32", name="value", index=index)

time
2020-12-09 09:48:38    <NA>
2020-12-09 09:48:38    <NA>
2020-12-09 09:48:38     0.6
2020-12-09 09:48:38     1.7
2020-12-09 09:48:38    <NA>
2020-12-09 09:48:38    <NA>
Name: value, dtype: Float32

Obviously first_valid_index is completely useless here, as s.loc[s.first_valid_index()] returns the Series as-is.

One can solve the problem and obtain the first non-null value by doing one of the following:

1. s.iloc[s.reset_index(drop=True).first_valid_index()]
2. s.loc[s.first_valid_index()].dropna().iloc[0]
3. s.dropna().loc[s.first_valid_index()].iloc[0]

However, all of these are quite verbose, or come with other disadvantages (e.g. computational overhead).

Feature Description

Two ideas:

1. Add an option skipna: bool = False to Series.items, such that the returned generator skips over null values. Then the first non-null value can be obtained via idx, val = next(iter(s.items(skipna=True)))
2. Add a function that gives the first non-null value (or None for empty Series / Series consisting only of nulls).

(2) can be implemented trivially using (1), however I think (1) could be quite useful in other contexts as well.

Alternative Solutions

Update documentation on Series.first_valid_index on how to robustly find the first non-null value.

Additional Context

No response

[rhshadrach]

Another alternative:

idx, val = next(e for e in s.items() if not pd.isna(e[1]))

I assume the expected behavior for an all NA Series is to raise a StopIteration error

[randolf-scholz]

@rhshadrach That works, but the performance can be quite bad due to the python for-loop. Consider the worst case when only the last element is not null:

import numpy as np
import pandas as pd

N = 10_000_000
x = np.full(N, float("nan"))
x[-1] = 0
s = pd.Series(x)

Then

%%timeit
idx = s.first_valid_index()
val = s.loc[idx]

Takes 7.62 ms ± 769 µs on my laptop (and will be faster in 2.1.0 still: #51549), but

%%timeit
idx, val = next(e for e in s.items() if not pd.isna(e[1]))

takes 3.41 s ± 185 ms!

[rhshadrach]

Indeed, good point. You suggested s.dropna().loc[s.first_valid_index()].iloc[0], but I think just s.dropna().iloc[0] also works?

[srkds]

but I think just s.dropna().iloc[0] also works?

yeah, it gives the same result.

Feature Description

Two ideas:

1. Add an option skipna: bool = False to Series.items, such that the returned generator skips over null values. Then the first non-null value can be obtained via idx, val = next(iter(s.items(skipna=True)))
2. Add a function that gives the first non-null value (or None for empty Series / Series consisting only of nulls).

(2) can be implemented trivially using (1), however I think (1) could be quite useful in other contexts as well.

I agree that 1st method makes sense if implemented.

here is the possible implementation

## s : series given above

def series_items(skipna=False):
  if skipna:
    return s.dropna()
  else:
    return s

series_items(skipna=True)

### output
time
2020-12-09 09:48:38    0.6
2020-12-09 09:48:38    1.7
Name: value, dtype: Float32

curious to know why we should implement while we can use s.dropna().iloc[0] to get desired result?

[randolf-scholz]

@srkds I think you meant to return s.items() and s.dropna().items() respectively. This would work but is sub-optimal as dropna costs O(N). Series.items is supposed to be a lazy iterator, and it would be expected that using Series.items(skipna=True) to find the first valid value has a cost dependant on the sparsity of the Series.

Any useful implementation likely needs to happen on the C-level.

[randolf-scholz]

The base implementation already seems to have this O(N) overhead problem:

pandas/pandas/core/generic.py

Lines 11837 to 11871 in 0f43794

	@final
	def _find_valid_index(self, *, how: str) -> Hashable | None:
	"""
	Retrieves the index of the first valid value.
	Parameters
	----------
	how : {'first', 'last'}
	Use this parameter to change between the first or last valid index.
	Returns
	-------
	idx_first_valid : type of index
	"""
	idxpos = find_valid_index(self._values, how=how, is_valid=~isna(self._values))
	if idxpos is None:
	return None
	return self.index[idxpos]
	@final
	@doc(position="first", klass=_shared_doc_kwargs["klass"])
	def first_valid_index(self) -> Hashable | None:
	"""
	Return index for {position} non-NA value or None, if no non-NA value is found.
	Returns
	-------
	type of index
	Notes
	-----
	If all elements are non-NA/null, returns None.
	Also returns None for empty {klass}.
	"""
	return self._find_valid_index(how="first")

[randolf-scholz]

I did a small experiment with numba:

import numba
import numpy as np

@numba.njit
def find_first_valid_index(x: np.ndarray):
    for idx, val in enumerate(x):
        if ~np.isnan(val):
            return idx

N = 1_000_000_000
sparsity = 1 - 1/N
x = np.full(N, np.nan)
# x[0] = 0  # 151±0.375ns, 583 ± 14.5ms
x[N//2] = 0  # 290±2.5 ms, 606±12.7ms
# x[-1] = 0  # 582±13.6 ms, 623±6.85ms
s = pd.Series(x)
assert ~np.isnan(x).all()

Then %timeit find_first_valid_index(x) and %timeit s.first_valid_index() yielded:

N=10⁹	numba.njit	Series.first_valid_index
i=0	152±0.4 ns	583±14.5 ms
i=N/2	290±2.5 ms	606±12.7 ms
i=N-1	582±13.6 ms	623±6.85 ms

Notice 1.000.000x performance difference when the first valid entry appears early. Although, ~500ms for a billion rows is likely good enough in most scenarios.

[srkds]

@srkds I think you meant to return s.items() and s.dropna().items() respectively. This would work but is sub-optimal as dropna costs O(N). Series.items is supposed to be a lazy iterator, and it would be expected that using Series.items(skipna=True) to find the first valid value has a cost dependant on the sparsity of the Series.

Any useful implementation likely needs to happen on the C-level.

@randolf-scholzI agree with you that the solution wasn't optimal.

[rhshadrach]

Any useful implementation likely needs to happen on the C-level.

Is there a proposed implementation for this? When a user wants access to the entire Series ignoring NAs, it seems to me we have a performant and idiomatic solution already. In the case where you only want the first value, it seems to me the peformence gain is not significant enough to warrant a new method/implementation.