Update a column with another table column where another column of the table is equal with each other pandas

Question

I have two pandas data frame as below,

Left_Frame 
       symbol    price timelab1 timelab2 timelab3     _merge
    0  145822  10.5018    19:00                    left_only
    1  145819  10.5055    19:00                    left_only
Right_Frame
        symbol  price timelab1 timelab2 timelab3      _merge
    16  145822  10.58      NaN      NaN      NaN  right_only
    17  145819  10.55      NaN      NaN      NaN  right_only

I want to update Left_Frame price with Right_Frame price where symbol are equal to the both data frames. Expected result is,

Left_Frame 
       symbol    price timelab1 timelab2 timelab3     _merge
    0  145822  10.58      19:00                    left_only
    1  145819  10.55      19:00                    left_only

How can I do this in Pandas?

Possible duplicate of [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Erfan, Apr 05 '19 at 11:56

iamklaus · Answer 1 · 2019-04-06T05:40:50.740

2

this would do the job

I want to update Left_Frame price with Right_Frame price where symbol are equal to the both data frames. Expected result is

left_frame['price'] = np.where(left_frame['symbol'].values == right_frame['symbol'].values, right_frame['price'],left_frame['price'])

Update

left_frame.loc[:,'price'] = np.where(left_frame['symbol'].values == right_frame['symbol'].values, right_frame['price'],left_frame['price'])

edited Apr 06 '19 at 05:40

answered Apr 05 '19 at 12:16

iamklaus

3,720
2
12
21

It works. But when I run, I get the this warning message: `SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy left_frame['price'] = np.where(left_frame['symbol'].values == right_frame['symbol'].values, right_frame['price'],left_frame['price'])` – Smith Dwayne Apr 06 '19 at 04:44
@SmithDwayne - solution is not general, working only in specific way - same number of rows in both DataFrames and testing only pairs - first value from one with first value in another. so I suggest test output careful. – jezrael Apr 06 '19 at 05:57

jezrael · Accepted Answer · 2019-04-05T11:55:12.787

1

Use Series.map by another Series created by DataFrame.set_index and for unmatched values replace by original by Series.fillna:

print (Left_Frame)
   symbol  price timelab1  timelab2  timelab3     _merge
0  145822     12    19:00       NaN       NaN  left_only
1  100000     13    19:00       NaN       NaN  left_only


print (Right_Frame)
    symbol  price  timelab1  timelab2  timelab3      _merge
16  145822     10       NaN       NaN       NaN  right_only
17  145819     18       NaN       NaN       NaN  right_only

s = Right_Frame.set_index('symbol')['price']
Left_Frame['price'] = Left_Frame['symbol'].map(s).fillna(Left_Frame['price'])

#slowier alternative
#Left_Frame['price'] = Left_Frame['symbol'].replace(s)

print (Left_Frame)
   symbol  price timelab1  timelab2  timelab3     _merge
0  145822   10.0    19:00       NaN       NaN  left_only
1  100000   13.0    19:00       NaN       NaN  left_only

edited Apr 05 '19 at 11:55

answered Apr 05 '19 at 11:47

jezrael

822,522
95
1,334
1,252

Your solution also works. But also get the same warning like, `SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy Left_Frame.loc['price'] = Left_Frame['symbol'].map(s).fillna(Left_Frame['price'])` – Smith Dwayne Apr 06 '19 at 05:59
1

@SmithDwayne - Yes, how is created `Left_Frame` ? I think if `Left_Frame = df[df['_merge'] == 'left_only']` need `Left_Frame = df[df['_merge'] == 'left_only'].copy()` – jezrael Apr 06 '19 at 06:00
Great. It works. What this copy function does here? – Smith Dwayne Apr 06 '19 at 06:01
1

@SmithDwayne - If you modify values in Left_Frame later you will find that the modifications do not propagate back to the original data (df), and that Pandas does warning. – jezrael Apr 06 '19 at 06:03
I can understand `s = Right_Frame.set_index('symbol')['price']` what it does. But can you please explain the line `Left_Frame['price'] = Left_Frame['symbol'].map(s).fillna(Left_Frame['price'])` – Smith Dwayne Apr 06 '19 at 06:04
1

@SmithDwayne - Sure, it mapping column `Left_Frame['symbol']` by `Series - s` , similar like dictionary. If not matched,get NaN, so this values are replaced by original column. Test only `Left_Frame['price1'] = Left_Frame['symbol'].map(s)` and `Left_Frame['price2'] = Left_Frame['symbol'].map(s).fillna(Left_Frame['price'])` – jezrael Apr 06 '19 at 06:07
1

This is great. I understand each and every piece of your work. Thank you for the explanation too. – Smith Dwayne Apr 06 '19 at 06:25
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/191351/discussion-between-smith-dwayne-and-jezrael). – Smith Dwayne Apr 06 '19 at 06:56

Update a column with another table column where another column of the table is equal with each other pandas

2 Answers2