Select rows from a DataFrame based on a values in another dataframe and updating one of the column with values according to the second DataFrame

user_10206172 0
Viewed 3634

I have two Dataframes df and df1.

Main DataFrame is as follows:
DF:

    start   end price
0   A   Z   1
1   B   Y   2
2   C   X   3
3   A   Z   4
4   D   W   5

Second DataFrame:
DF1:

start   end price
    0   A   Z   100
    1   B   Y   200

I want the main dataframe df to update the values in 'price' columns based on the start and end in df1. it should update column value for all the rows having the same start and end as in df1. DF:

start   end price
0   A   Z   100
1   B   Y   200
2   C   X   3
3   A   Z   100
4   D   W   5

(all A-Z and B-Y in df should get updated). Is there anyway I can get this output ? In reality the datframes have more columns but I want to update only one column(eg.'Price').

pandas rows updating dataframe python
3 Answers

First, you can merge:

s = df1.merge(df2, left_on=['start', 'end'], right_on=['start', 'end'], how='left')

Then you can fillna and index your desired columns:

s.assign(price=s.price_y.fillna(s.price_x))[['start', 'end', 'price']]


  start end  price
0     A   Z  100.0
1     B   Y  200.0
2     C   X    3.0
3     A   Z  100.0
4     D   W    5.0
user_34832030

Using update 

df=df.set_index(['start','end'])
df.update(df1.set_index(['start','end']))
df.reset_index()
Out[99]: 
  start end  price
0     A   Z  100.0
1     B   Y  200.0
2     C   X    3.0
3     A   Z  100.0
4     D   W    5.0
user_79645270

merge

df.drop('price', 1).merge(df1, 'left').fillna(df)

  start end  price
0     A   Z  100.0
1     B   Y  200.0
2     C   X    3.0
3     A   Z  100.0
4     D   W    5.0
  1. I'm going to merge on ['start', 'end'] and that pesky price is going to get in my way. So, I drop it.
  2. I need to preserve df index because I have that repeat of 'A' and 'Z'. So, I use a 'left' merge
  3. Now my missing elements can be filled back in with df
user_23366540
Related