Update dataframe column with values from another dataframe by index

Question

I have two DataFrames.

One of them contains: item id, name, quantity and price. Another: item id, name and quantity.

The problem is to update names and quantity in first DataFrame taking information from the second DataFrame by item id. Also, first DataFrame has not all item id's, so I need to take into account only those rows from the second DataFrame, which are in the first one.

DataFrame 1

In [1]: df1
Out[1]: 
   id  name quantity price 
0  10  X    10       15  
1  11  Y    30       20  
2  12  Z    20       15  
3  13  X    15       10  
4  14  X    12       15

DataFrame 2

In [2]: df2
Out[2]: 
   id  name quantity
0  10  A    3       
1  12  B    3       
2  13  C    6

I've tried to use apply to iterate through rows and modify column value by condition like this:

def modify(row):
    row['name'] = df2[df2['id'] == row['id']]['name'].get_values()[0]
    row['quantity'] = df2[df2['id'] == row['id']]['quantity'].get_values()[0]

df1.apply(modify, axis=1)

But it doesn't have any results. DataFrame 1 is still the same

I am expecting something like this first:

In [1]: df1
Out[1]: 
   id  name quantity price 
0  10  A    3        15  
1  11  Y    30       20  
2  12  B    3        15  
3  13  C    6        10  
4  14  X    12       15

After that I want to drop the rows, which were not modified to get:

In [1]: df1
Out[1]: 
   id  name quantity price 
0  10  A    3        15  
1  12  B    3        15  
2  13  C    6        10

BENY · Accepted Answer · 2019-04-17 16:35:08Z

2

Using update

df1=df1.set_index('id')
df1.update(df2.set_index('id'))
df1=df1.reset_index()
Out[740]: 
   id name  quantity  price
0  10    A       3.0     15
1  11    Y      30.0     20
2  12    B       3.0     15
3  13    C       6.0     10
4  14    X      12.0     15

answered Apr 17, 2019 at 16:35

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

sailestim Over a year ago

Thanks a lot! Worked perfectly

iamklaus · Accepted Answer · 2019-04-17 16:26:30Z

0

new_df = df.merge(df2, on='id')
new.drop(['name_x','quantity_x'], inplace=True, axis=1)
new.columns = ['id','price','name','quantity']

Output

   id  price name  quantity
0  10     15    A         3
1  12     15    B         3
2  13     10    C         6

answered Apr 17, 2019 at 16:26

iamklaus

3,7682 gold badges14 silver badges21 bronze badges

Collectives™ on Stack Overflow

Update dataframe column with values from another dataframe by index

DataFrame 1

DataFrame 2

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

DataFrame 1

DataFrame 2

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related