Pandas: replace values in column

Question

I have df and I want to replace values there

df = pd.read_csv('visits_april.csv')

df1 = pd.read_csv('participants.csv')`

url, date
aliexpress.com/payment/payment-result.htm...  2016-04-30 22:57:41   
shoppingcart.aliexpress.com/order/confirm_orde...  2016-04-30 23:05:21   
shoppingcart.aliexpress.com/order/confirm_orde...  2016-04-30 23:05:33   
shoppingcart.aliexpress.com/order/confirm_orde...  2016-04-30 23:11:37   
aliexpress.com/payment/payment-result.htm...  2016-04-30 23:12:07   
shoppingcart.aliexpress.com/order/confirm_orde...  2016-04-30 23:15:31   
shoppingcart.aliexpress.com/order/confirm_orde...  2016-04-30 23:15:37   
aliexpress.com/payment/payment-result.htm...  2016-04-30 23:16:13   
            kupivip.ru/shop/checkout/confirmation  2016-04-30 23:18:15

if string from df contain string from df2 I want to replace it to string from df2

visits
aliexpress.com
ozon.ru
wildberries.ru
lamoda.ru
mvideo.ru
eldorado.ru
ulmart.ru
ebay.com
kupivip.ru

Desire output

url
aliexpress.com
aliexpress.com
aliexpress.com
aliexpress.com
aliexpress.com
aliexpress.com
aliexpress.com
aliexpress.com
kupivip.ru

I try df[df['url'].isin(df2['visits'])] but it return Empty DataFrame.

Could you please add the code for creating the panda dataframe, so we do not have to recreate it manually, to test possible solutions ? — HolyDanna
– HolyDanna, Commented Jul 25, 2016 at 10:03
@HolyDanna I read it from file file pd.read_csv('namefile.csv') — NineWasps
– NineWasps, Commented Jul 25, 2016 at 10:12

jezrael · Accepted Answer · 2016-07-25 10:19:08Z

2

You can extract joined list with | (regex or) from column visits by tolist:

L = df2.visits.tolist()
print (L)
['aliexpress.com', 'ozon.ru', 'wildberries.ru', 'lamoda.ru', 
'mvideo.ru', 'eldorado.ru', 'ulmart.ru', 'ebay.com', 'kupivip.ru']

print (df.url.str.extract('(' + '|'.join(L) + ')', expand=False))
0    aliexpress.com
1    aliexpress.com
2    aliexpress.com
3    aliexpress.com
4    aliexpress.com
5    aliexpress.com
6    aliexpress.com
7    aliexpress.com
8        kupivip.ru
Name: url, dtype: object

edited Jul 25, 2016 at 10:19

answered Jul 25, 2016 at 10:12

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

2 Comments

NineWasps Over a year ago

It replace it, but I have other columns in this df and they are dissapearing

jezrael Over a year ago

Sorry, then you need df['url'] = df.url.str.extract('(' + '|'.join(L) + ')', expand=False)

Collectives™ on Stack Overflow

Pandas: replace values in column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related