How to create new column by comparing two columns in same dataframe using python?

Question

I have data frame shown below. df:

col_1 col_2 
EDU   facebook
EDU   google
EDU   google_usa
EDU   tabula
EDU   xyz
EDU   abc
IAR   facebook
IAR   google

If col_1 has 'EDU' and col_2 has 'facebook', 'google' new_col should have same string i.e facebook and google , if col_2 contains 'google_usa',tabula' new_col should contains 'gusa' and if col_2 has any other strings ne_col should have others in the same data frame. If col_1 has 'IAR'and col_2 has 'facebook' new_col should have facebook and for any other string in the col_2 it should contain 'other' in the same data frame.

Expected output:

col_1   col_2     new_col
EDU   facebook    facebook
EDU   google      google
EDU   google_usa  gusa
EDU   tabula      gusa
EDU   xyz         others
EDU   abc         others
IAR   facebook    facebook
IAR   google      others

I tried below code but not worked out.Please help me in this regard. thanks in advance.

if df['col_1'].str.contains('EDU').any():

        df['new_col'] = ['facebook' if 'facebook' in x else
                            'google' if 'google' == x else
                            'gcusa_tb' if 'taboola' in x else
                            'gcusa_tb' if 'google_cusa' in x else
                            'Others' for x in df['col_2']]

piRSquared · Accepted Answer · 2017-03-20 16:52:16Z

1

is_edu = df.col_1 == 'EDU'
g_or_f = df.col_2.isin(['google', 'facebook'])
g_or_t = df.col_2.isin(['google_usa', 'tabula'])
is_iar = df.col_1 == 'IAR'
is_fac = df.col_2 == 'facebook'

df.assign(
    new_col=np.where(
        is_edu,
        np.where(
            g_or_f, df.col_2,
            np.where(g_or_t, 'gusa', 'other')
        ),
        np.where(
            is_iar & is_fac, 'facebook', 'other'

        )
    )
)

  col_1       col_2   new_col
0   EDU    facebook  facebook
1   EDU      google    google
2   EDU  google_usa      gusa
3   EDU      tabula      gusa
4   EDU         xyz     other
5   EDU         abc     other
6   IAR    facebook  facebook
7   IAR      google     other

answered Mar 20, 2017 at 16:52

piRSquared

296k68 gold badges509 silver badges654 bronze badges

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

2 Comments

MattR Over a year ago

Just posting this for future reference for others stumbling across this post: This works for the example perfectly. But nested np.where always are hard for other people to follow. Output and efficiency are great, but readbility can be lacking.

piRSquared Over a year ago

@MattR also for posterity, this problem is all about nested if, then, else. If readability is a priority, you can wrap the np.where with a prettier function.

Marco Spinaci · Accepted Answer · 2017-03-20 16:53:47Z

I would use a few numpy commands:

df['new_col'] = 'others'
df.loc[np.logical_and(df.col_1=='EDU', np.in1d(df.col_2, ['facebook','google'])), 'new_col'] = df.loc[np.logical_and(df.col_1=='EDU', np.in1d(df.col_2, ['facebook','google'])), 'col_2']
df.loc[np.logical_and(df.col_1=='EDU', np.in1d(df.col_2, ['google_usa','tabula'])), 'new_col'] = 'gusa'

P.S. your request does not exactly coincide with the output you proposed, I hope I have interpreted the request correctly. My code would output:

    col_1   col_2   new_col
0   EDU facebook    facebook
1   EDU google      google
2   EDU google_usa  gusa
3   EDU tabula      gusa
4   EDU xyz         others
5   EDU abc         others
6   IAR facebook    others
7   IAR google      others

MattR · Accepted Answer · 2017-03-20 18:25:13Z

I believe that this is the easiest way of understanding how the code works so that you can apply it to more situations than just this example. It's fairly intuitive. You can add logic as you go.

1) First we create a function

2) Apply said function

def new_col(col):
    if col['col1'] == 'EDU' and col['col2'] == 'facebook':
        return 'facebook'
    if col['col1'] == 'EDU' and col['col2'] == 'google':
        return 'google'
    if col['col2'] == 'google_usa' or col['col2'] == 'tabula':
        return 'gusa'
    if col['col1'] == 'IAR' and col['col2'] == 'facebook':
        return 'facebook'
    return 'others'

df['new_col'] = df.apply(lambda col: new_col (col),axis=1)

output (my col1 and col2 are backwards. Don't mind this. it was just easier for me to read this way):

         col2 col1   new_col
0    facebook  EDU  facebook
1      google  EDU    google
2  google_usa  EDU      gusa
3      tabula  EDU      gusa
4         xyz  EDU    others
5         abc  EDU    others
6    facebook  IAR  facebook
7      google  IAR    others

Collectives™ on Stack Overflow

How to create new column by comparing two columns in same dataframe using python?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related