python pandas np.where value from another column

Question

I am trying to apply different value to a df from another column using:

df['url']= 
     np.where(df['client'] == 'xyz',
     "/s?k={query}&s=relevanceblender&page=%s".format(query=df['keyword']), 
     "other")

however query is replaced by all values of df['keyword'], not only the row in question. thanks for your help.

Answer 1

Assuming this input:

df = pd.DataFrame({'client': ['abc', 'abc', 'xyz', 'xyz'],
                   'keyword': ['kw1', 'kw2', 'kw3', 'kw4']
                  })

You could use:

df['url'] = np.where(df['client'] == 'xyz',
                     df['keyword'].apply("/s?k={}&s=relevanceblender&page=%s".format),
                     'other')

Notice how {query} was changed to {}.

Or, if you cannot change the formatting string:

df['url'] = np.where(df['client'] == 'xyz',
                     df['keyword'].apply(lambda x: "/s?k={query}&s=relevanceblender&page=%s".format(query=x)),
                     'other')

Output:

  client keyword                                  url
0    abc     kw1                                other
1    abc     kw2                                other
2    xyz     kw3  /s?k=kw3&s=relevanceblender&page=%s
3    xyz     kw4  /s?k=kw4&s=relevanceblender&page=%s