How to populate values inside a new column based values from other columns in a dataframe in Pandas

Question

I have dataframe df has shown below.

  df
DATA ELEMENT    PORT    LOCATION    P_RISK  S_RISK  W_RISK  Total_Risk
Event 1000     SGSIN                      0       0       0       0
Event 2000     MYKUL                      0       1       0       1
Event 3000                PARIS           0       0       1       1
Event 4000                LYON            1       0       0       1
Event 1000     USNYK                      0       0       0       0
Event 2000     INCOK                      0       0       1       1
Event 3000                MUMBAI          0       0       0       0
Event 4000                LAHORE          1       0       0       1
Event 2000     INCOK                      0       0       0       0
Event 3000                PARIS           0       0       0       0

Condition

*If Data Element is 1000 or 2000 and Total Risk is 1 then populate the new column comment with the With `Risk Name + PORT

*If Data Element is 3000 or 4000 and Total Risk is 1 then populate the new column comment with the With Risk Name + LOCATION

*If Total Risk is 0 then populate No Risk Indentified

Expected Output

df
    DATA ELEMENT    PORT    LOCATION    P_RISK  S_RISK  W_RISK  Total_Risk  Comment
    Event 1000     SGSIN                      0       0       0       0     No Risk Indentified
    Event 2000     MYKUL                      0       1       0       1     S_RISK  predicted at  MYKUL
    Event 3000                PARIS           0       0       1       1     W_RISK predicted at  PARIS
    Event 4000                LYON            1       0       0       1     P_RISK  predicted at  LYON
    Event 1000     USNYK                      0       0       0       0     No Risk Indentified
    Event 2000     INCOK                      0       0       1       1     W_RISK predicted at  INCOK
    Event 3000                MUMBAI          0       0       0       0     No Risk Indentified
    Event 4000                LAHORE          1       0       0       1     P_RISK  predicted at  LAHORE
    Event 2000     INCOK                      0       0       0       0     No Risk Indentified
    Event 3000                PARIS           0       0       0       0     No Risk Indentified

How can this be done?

BENY · Accepted Answer · 2019-11-21 02:36:08Z

3

I am use dot with np.where

s2=df.filter(like='_RISK')
s2=s2.dot(s2.columns)
df['new']=np.where(s2=='' ,'No Risk Indentified',s2 + ' predict at ' +df.PORT.mask(df.PORT=='',df.LOCATION))
df
Out[35]: 
    DATA  ELEMENT   PORT  ... W_RISK  Total_Risk                     new
0  Event     1000  SGSIN  ...      0           0     No Risk Indentified
1  Event     2000  MYKUL  ...      0           1   S_RISKpredict atMYKUL
2  Event     3000         ...      1           1   W_RISKpredict atPARIS
3  Event     4000         ...      0           1    P_RISKpredict atLYON
4  Event     1000  USNYK  ...      0           0     No Risk Indentified
5  Event     2000  INCOK  ...      1           1   W_RISKpredict atINCOK
6  Event     3000         ...      0           0     No Risk Indentified
7  Event     4000         ...      0           1  P_RISKpredict atLAHORE
8  Event     2000  INCOK  ...      0           0     No Risk Indentified
9  Event     3000         ...      0           0     No Risk Indentified
[10 rows x 9 columns]

Method 2

s2=df.filter(like='_RISK').ne(0).stack()                                                                             
s2=s2[s2].reset_index(level=1)                                                                                       
df['new']=(s2['level_1'] + ' predict at ' +df.PORT.mask(df.PORT=='',df.LOCATION)).fillna('No Risk Indentified')

edited Nov 21, 2019 at 2:36

answered Nov 21, 2019 at 1:52

BENY

324k22 gold badges176 silver badges250 bronze badges

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

13 Comments

Rahul rajan Over a year ago

@ Getting an Error can't multiply sequence by non-int of type 'float'

BENY Over a year ago

@Rahulrajan try s2=df.filter(like='_RISK').ne(0)

Rahul rajan Over a year ago

@WeNYoBen, I have around 2000 records and its taking lot time to execute. Its still running. Is there an alternate way

BENY Over a year ago

@Rahulrajan I am not sure ,why , in your data you have dummy variable , dot is convert dummy variable back

Rahul rajan Over a year ago

@WeNYoBen, Its still running .Is there any other way using like using normal if -else to achieve the result

|

Collectives™ on Stack Overflow

How to populate values inside a new column based values from other columns in a dataframe in Pandas

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related