How to Update Dataframe based on Results of Another Dataframe Using Pandas-CodePudding

I have two dataframes called channels and videos.

I need a way to update videos > website column instances based on information stored and matched within channels > Website column instance.

ID's to use are:

Dataframe channels: ID
Dataframe videos: Channel_id

Dataframe: channels

Channel       ID        Website   
=========     =====     =======
Company A     A0001     a.com
Company B     A0002     b.com
Company C     A0003     c.com
Company D     A0004     d.com

Dataframe: videos

Category    Channel_id    Channel_name   website
========    ==========    ============   =======
Comedy      A0003         AAA
Action      A0004         BBB
Horror      A0008         CCC
Comedy      A0001         DDD
Comedy      A0044         EEE
Comedy      A0002         FFF

Required Output

Category    Channel_id    Channel_name    website
Comedy      A0003         AAA             c.com
Action      A0004         BBB             d.com
Horror      A0008         CCC
Comedy      A0001         DDD             a.com
Comedy      A0044         EEE       
Comedy      A0002         FFF             b.com

Thank you in advance.

CodePudding user response：

can you try this?

result = pd.merge(videos, channels[['ID', 'Website']], how='left', left_on='Channel_id', right_on='ID').drop(columns='ID').fillna('')

Output

  Category Channel_id Channel_name Website
0   Comedy      A0003          AAA   c.com
1   Action      A0004          BBB   d.com
2   Horror      A0008          CCC        
3   Comedy      A0001          DDD   a.com
4   Comedy      A0044          EEE        
5   Comedy      A0002          FFF   b.com