Pandas DataFrame | asof method
Start your free 7-days trial now!
Pandas DataFrame.asof(~) method returns the last row that contains no NaN.
Parameters
1. wherelink | date or array-like of dates
The values of the index you want to check until. Typically, this would be a list of dates. Check the examples below for clarification.
2. subset | string or array-like of string | optional
The label of the columns to consider when checking for NaN. By default, all columns are considered.
Return Value
If where is a scalar, then a Series is returned. Otherwise, a DataFrame is returned.
The index of the source DataFrame must be sorted, otherwise an error is raised.
Examples
Basic usage
Consider the following DataFrame:
df = pd.DataFrame({"A":[3,4,pd.np.NaN,2], "B":[5,6,7,8]}, index=[10,20,30,40])df
A B10 3.0 520 4.0 630 NaN 740 2.0 8
Notice how the index of the DataFrame is sorted. This is a prerequisite for using this method.
To get the last row without NaN before index value 35 (inclusive):
df.asof(35)
A 4.0B 6.0Name: 35, dtype: float64
Here, we obtain the 2nd row (row with index 20). The 4th row, which has an index value of 40, was ignored since it exceeds the specified index value of 35.
Dates as index
Consider the following DataFrame with a DatetimeIndex:
my_index = pd.DatetimeIndex(["2020-12-24", "2020-12-25", "2020-12-26", "2020-12-27"])df = pd.DataFrame({"A":[3,4,pd.np.NaN,2], "B":[5,6,7,8]}, index=my_index)df
A B2020-12-24 3.0 52020-12-25 4.0 62020-12-26 NaN 72020-12-27 2.0 8
Here, the index values are dates, which again, are sorted.
To get the last rows without NaN before two specific dates (inclusive):
my_where = pd.DatetimeIndex(["2020-12-24", "2020-12-26"])df.asof(my_where)
A B2020-12-24 3.0 5.02020-12-26 4.0 6.0
Note the following:
the row values of
2020-12-25are returned for the second row. This is because the row values of2020-12-26contain aNaN, and so the next candidate is the row values of2020-12-25, which do not contain anyNaN.the new index values of the returned DataFrame are the ones you've specified in the parameter.
