How Do I Get For Each Row A Value From The Next Row Which Matches A Criteria In Pandas?

Let's assume we have a table like the one below: A B 1 1.0 2 2.0 3 2.0 4 3.0 5 2.0 6 1.0 7 1.0 Now I want to get for each row the value from column A of the next following row for

Solution 1:

You can do that in just a few steps as follows:

import pandas as pd
import numpy as np

# initialize column 'C' with the value of column 'A'# for all rows with values for 'B' smaller than 2.0# use np.NaN if 'C' if 'B' > 2.0# because normal int columns do not support null values# we use the new type Int64 instead # (new in pandas version 0.25)df['C']= df['A'].astype('Int64').where(df['B']<=2.0, np.NaN)

# now just fill the gaps using the value of the next row# in which the field is filled and shift the columndf['C'].fillna(method='bfill', inplace=True)
df['C']=df['C'].shift(-1)

This results in:

>>> df
   AB    C
011.02122.03232.05343.05452.06561.07671.0  NaN

Solution 2:

You just need slicing df on B less than or equal 2 and reindex and bfill and shift

df['C'] = df.loc[df.B.le(2), 'A'].reindex(df.index).bfill().shift(-1)

Out[599]:
   A    B    C
0  1  1.0  2.0
1  2  2.0  3.0
2  3  2.0  5.0
3  4  3.0  5.0
4  5  2.0  6.0
5  6  1.0  7.0
6  7  1.0  NaN