how to update existing data frame in pandas?

Question

Given these two data frames:

>>> df1 = pd.DataFrame({'c1':['a','a','b','b'], 'c2':['x','y','x','y'], 'val':0})
>>> df1
  c1 c2  val
0  a  x    0
1  a  y    0
2  b  x    0
3  b  y    0

>>> df2 = pd.DataFrame({'c1':['a','a','b'], 'c2':['x','y','y'], 'val':[12,31,14]})
>>> df2
  c1 c2  val
0  a  x   12
1  a  y   31
2  b  y   14

Is there a function that takes the elements of val from df2 and puts them in the corresponding indexes of df1, resulting in:

>>> df1_updated 
  c1 c2  val
0  a  x   12
1  a  y   31
2  b  x    0
3  b  y   14

elyase · Accepted Answer · 2013-09-10 19:07:22Z

8

Yes, take a look at combine_first or update. For example:

>>> df1['val'] = df2['val'].combine_first(df1['val'])
>>> df1
Out[26]:
    c1  c2  val
0    a   x   12
1    a   y   31
2    b   x   14
3    b   y   0

EDIT: to combine according to c1 and c2 ignoring the current index:

>>> df1['val'] = df2.set_index(['c1','c2'])['val'].combine_first(df1.set_index(['c1','c2'])['val']).values
>> df1
Out[25]:
    c1  c2  val
0    a   x   12
1    a   y   31
2    b   x   0
3    b   y   14

edited Sep 10, 2013 at 19:07

answered Sep 10, 2013 at 18:41

elyase

41.2k12 gold badges121 silver badges123 bronze badges

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

5 Comments

HappyPy Over a year ago

That doesn't work, because it doesn't take into account the df1 indexes. The value 14 should be in row b y

HappyPy Over a year ago

Great, many thanks! And thanks also for taking the time to solve this :)

Yona Over a year ago

This doesn't work for me. When I try:

proxy.set_index(['symbol','symboltype'])['proxy'].combine_first(symbols.set_index(['symbol', 'symboltype'])['proxy']).value

I get: Exception: cannot handle a non-unique multi-index!

elyase Over a year ago

@Yona, might be better if you create a mcve and post a new question. Might be an issue with your particular dataset or you might have found a bug in pandas!

Andy L. Over a year ago

The docs says combine_first ‘choosing the calling Series’s values first. Result index will be the union of the two indexes’. In this case, df1[‘val’] is not NaN nor null, so it won’t pick df2[‘val’]. i tried this on latest pandas and after comdbine_first df1[‘val’] is still 0. How come you have the output as shown?

Collectives™ on Stack Overflow

how to update existing data frame in pandas?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related