Get row index based on multiple column values in pandas

Question

Here is a sample df >> real one > 500k rows. I am trying to get the row index of every instance where column ‘Trigger’ is == 1 so I can get the value in column ‘Price’. See desired column.

df10 = pd.DataFrame({
  'Trigger': [0,0,1,1,1,0,0,1,0,1],
  'Price': [12,14,16,18,20,2,4,6,8,10],
  'Stock': ['AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'IBM','IBM','IBM','IBM','IBM'],
  'desired':[0,0,16,18,20,0,0,6,0,10]
 })

I was looking at answers online and you can use this code but it gives an array or all instances and I don’t know how to move the position in the array >> or if that is possible

df10['not_correct'] = np.where(df10['Trigger'] ==1 , df10.iloc[df10.index[df10['Trigger'] == 1][0],0],0)

So essentially, I want to find the index row number of (all) instances where column ‘Trigger’ == 1. It would be similar to a simple if statement in excel >> if (a[row#] == 1, b[row#],0)

Keep in mind this is example and I will NOT know where the 1 and 0 are in the actual df or how many 1’s there actually are in the ‘Trigger’ column >> it could be 0, 1 or 50.

David Erickson · Accepted Answer · 2020-07-30 02:08:56Z

1

To get the row number, use df.index in your np.where.

df10['row']=np.where(df10['Trigger']==1,df10.index,0)
df10
Out[7]: 
    Trigger  Price Stock  desired  row
0        0     12  AAPL        0    0
1        0     14  AAPL        0    0
2        1     16  AAPL       16    2
3        1     18  AAPL       18    3
4        1     20  AAPL       20    4
5        0      2   IBM        0    0
6        0      4   IBM        0    0
7        1      6   IBM        6    7
8        0      8   IBM        0    0
9        1     10   IBM       10    9

answered Jul 30, 2020 at 2:08

David Erickson

16.7k2 gold badges21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BENY · Accepted Answer · 2020-07-30 02:05:11Z

0

The np.where do not need filter the result

df10['New']=np.where(df10.Trigger==1,df10.Price,0)
df10
Out[180]: 
   Trigger  Price Stock  desired  New
0        0     12  AAPL        0    0
1        0     14  AAPL        0    0
2        1     16  AAPL       16   16
3        1     18  AAPL       18   18
4        1     20  AAPL       20   20
5        0      2   IBM        0    0
6        0      4   IBM        0    0
7        1      6   IBM        6    6
8        0      8   IBM        0    0
9        1     10   IBM       10   10

answered Jul 30, 2020 at 2:05

BENY

324k22 gold badges176 silver badges250 bronze badges

2 Comments

J Westwood Over a year ago

Ok - I see. Is there actually a way to get index row number when Trigger == 1 (without getting an array?)

BENY Over a year ago

@JWestwood df10.index[df10.Trigger==1]

Collectives™ on Stack Overflow

Get row index based on multiple column values in pandas

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related