How to replace&add the dataframe element by another dataframe in Python Pandas?

Question

Suppose I have two data frame 'df_a' & 'df_b' , both have the same index structure and columns, but some of the inside data elements are different:

>>> df_a
           sales cogs
STK_ID QT           
000876 1   100  100
       2   100  100
       3   100  100
       4   100  100
       5   100  100
       6   100  100
       7   100  100

>>> df_b
           sales cogs
STK_ID QT           
000876 5    50   50
       6    50   50
       7    50   50
       8    50   50
       9    50   50
       10   50   50

And now I want to replace the element of df_a by element of df_b which have the same (index, column) coordinate, and attach df_b's elements whose (index, column) coordinate beyond the scope of df_a . Just like add a patch 'df_b' to 'df_a' :

>>> df_c = patch(df_a,df_b)
           sales cogs
STK_ID QT           
000876 1   100  100
       2   100  100
       3   100  100
       4   100  100
       5    50   50
       6    50   50
       7    50   50
       8    50   50
       9    50   50
       10   50   50

How to write the 'patch(df_a,df_b)' function ?

This looks like a use case for the not yet implemented df_a.update(df_b, join='outer'), see help(df_a.update) — Wouter Overmeire
– Wouter Overmeire, Commented Sep 3, 2012 at 12:10

BrenBarn · Accepted Answer · 2012-08-31 15:16:22Z

2

Try this:

df_c = df_a.reindex(df_a.index | df_b.index)
df_c.ix[df_b.index] = df_b

answered Aug 31, 2012 at 15:16

BrenBarn

253k39 gold badges421 silver badges392 bronze badges

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

Comments

Garrett · Accepted Answer · 2012-09-03 19:49:30Z

2

To fill gaps in one dataframe with values (or even full rows) from another, take a look at the df.combine_first() built-in method.

In [34]: df_b.combine_first(df_a)
Out[34]: 
           sales  cogs
STK_ID QT             
000876 1     100   100
       2     100   100
       3     100   100
       4     100   100
       5      50    50
       6      50    50
       7      50    50
       8      50    50
       9      50    50
       10     50    50

edited Sep 3, 2012 at 19:49

answered Sep 3, 2012 at 19:33

Garrett

50.3k6 gold badges64 silver badges51 bronze badges

Comments

Def_Os · Accepted Answer · 2016-07-04 23:10:31Z

1

Similar to BrenBarn's answer, but with more flexibility:

# reindex both to union of indices
df_ar = df_a.reindex(df_a.index | df_b.index)
df_br = df_b.reindex(df_a.index | df_b.index)

# replacement criteria can be put in this lambda function
combiner = lambda: x, y: np.where(y < x, y, x)
df_c = df_ar.combine(df.br, combiner)

edited Jul 4, 2016 at 23:10

answered Aug 31, 2012 at 15:37

Def_Os

5,4775 gold badges37 silver badges64 bronze badges

2 Comments

Winand Over a year ago

I think on the 2nd line df_a.index already includes df_b.index

Def_Os Over a year ago

@Winand Correct. I fixed it.

score 0 · Accepted Answer · 2014-07-17 12:34:08Z

I was struggling with the same issue, the code in the previous answers didn't work in my dataframes. They have 2 index columns and the reindex operation results in NaN values in strange places (I'll post the dataframe contents if anyone is willing do debug it).

I found an alternate solution. I'm reviving this thread hoping this may be useful to others:

# concatenate df_a and df_b
df_c = concat([dfbd,dfplanilhas])

# clears the indexes (turns the index columns into regular dataframe columns)
df_c.reset_index(inplace='True')

# removes duplicates keeping the last occurence (hence updating df_a with values from df_b)
df_c.drop_duplicates(subset=['df_a','df_b'], take_last='True', inplace='True')

Not a very elegant solution, but seems to work.

I hope df.update gets a join='outer' option soon...

Collectives™ on Stack Overflow

How to replace&add the dataframe element by another dataframe in Python Pandas?

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related