CODEWITHSUNDEEP
python-2.7pandas
Erich
Erich

Reputation: 949

find previous max index in data frame with conditional statement

From a dataset like this, I need to find the maximum closest previous index for A. The data frame may be large, but it always must find the last previous index where the second column is equal to "A".

index  X
1     A
2     B
3     C
4     D
5     E
6     A
7     B
8     C
9     D
10    E

Here is the output that I am looking to achieve. The maximum closest previous index for "A"

index  X    Y
1     A    1
2     B    1
3     C    1
4     D    1
5     E    1
6     A    6
7     B    6
8     C    6
9     D    6
10    E    6

Something like this.

  for i in x: 
      max(if df[i][1] == 'A' and df[0] < df[i][0]])

Upvotes: 1

Views: 58

Answers (1)

Andy Hayden
Andy Hayden

Reputation: 375435

You can extract the index column where X is 'A' using where:

In [11]: df['index'].where(df['X'] == 'A')
Out[11]:
0     1
1   NaN
2   NaN
3   NaN
4   NaN
5     6
6   NaN
7   NaN
8   NaN
9   NaN
Name: index, dtype: float64

It looks like you just want to ffill this:

In [12]: df['index'].where(df['X'] == 'A').ffill()
Out[12]:
0    1
1    1
2    1
3    1
4    1
5    6
6    6
7    6
8    6
9    6
Name: index, dtype: float64

and setting this to column Y gets you the desired result:

In [13]: df['Y'] = df['index'].where(df['X'] == 'A').ffill()

Upvotes: 1

Related Questions