filter on multiple columns and operation row wise pandas dataframe

Question

if a1_status, b1_status, c1_status, d1_status equal "Active" and a1, b1, c1, d1 equals "Loan" then output column "Loan_active" contains count of "Active" row wise. input dataframe looks like this.

output dataframe :

use below code to create same dataframe mentioned above in image in pandas .

import pandas as pd
df = pd.DataFrame({'a1':['Loan','Loan','Loan'],
            'a1_status' : ['active','closed','active'] ,
            'b1' : ['Loan','Loan','Loan'],
            'b1_status' : ['active','active','active'] ,
            'c1' : ['Credit','Credit','Credit'],
            'c1_status' : ['closed','closed','closed'] ,
            'd1' : ['Loan','Loan','Loan'],
            'd1_status' : ['closed','closed','active'] ,
})
print(df)

What is the output columns name, d1_status or Loan_acitve which shows in your desire table ? And the output column in your desire table doesn't fit what you want in your question. Values in c1 only contain Credit, which doesn't meet the given condition for generating values for the new column. — Denny Chen
– Denny Chen, Commented Feb 25, 2022 at 2:34
if c1 column doesn't contain "Loan" value then i have to ignore that. and "Loan_acitve" is the output column name — sizo_abe
– sizo_abe, Commented Feb 25, 2022 at 2:37
did you attempt to solve it? kindly share your code for the attempt(s) — sammywemmy
– sammywemmy, Commented Feb 25, 2022 at 2:38

BENY · Accepted Answer · 2022-02-25 02:46:59Z

4

Let us do shift

df['new'] = (df.eq('active') & df.shift(axis=1).eq('Loan')).sum(axis=1)
Out[349]: 
0    2
1    1
2    3
dtype: int64

answered Feb 25, 2022 at 2:46

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

hteza Over a year ago

This is very elegant. Of course, it assumes that the "loan" column will be just left to "status" columns, but it can easily be worked around by sorting the columns first.

hteza · Accepted Answer · 2022-02-25 02:55:58Z

1

This solution is not as elegant as the other, but it explictly select the columns that includes ONLY loans, then check their status. But this also assumes that the status columns will be named loan_status.

loan = df.columns[df.isin(["Loan"]).all()]
df['loan_active'] = df[loan+"_status"].eq('active').sum(axis=1)

or if you prefer one liners;

df['loan_active'] = df[df.columns[df.isin(["Loan"]).all()]+"_status"].eq('active').sum(axis=1)

answered Feb 25, 2022 at 2:55

hteza

3211 silver badge8 bronze badges

Collectives™ on Stack Overflow

filter on multiple columns and operation row wise pandas dataframe

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related