Updating dataframe on values from another dataframe

Question

I want to update data frame X on values from dataframe from Y.

X = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                  'B': ['B0', 'B1', 'B2'], 
                  'C': ['C0', 'C1', 'C2'], 
                  'D': ['D0', 'D1', 'D2']})

    A   B   C   D
0  A0  B0  C0  D0
1  A1  B1  C1  D1
2  A2  B2  C2  D2

Y = pd.DataFrame({'A': ['A0', 'A1'],
                  'B': ['B0', 'B1'], 
                  'C': ['C0xx', 'C1xx'], 
                  'D': ['D0xx', 'D1xx']})

    A   B     C     D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx

And the result to be:

    A   B   C   D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx
2  A2  B2  C2    D2

Of course my dataframe is match bigger.

What is output if Y = pd.DataFrame({'A': ['A0', 'A1'], 'B': ['B0', 'B1'], 'C': ['C0xx', 'C1xx'], 'D': ['D0xx', 'D1xx']}, index=[2,1]) ? — jezrael
– jezrael, Commented Jul 25, 2017 at 9:59

F. Moïni · Accepted Answer · 2017-07-25 11:43:57Z

3

1. Both DataFrames have the same index

This is the case you presented in the example given in your question. You might want to use the update method:

>>> X.update(Y)
>>> X

    A   B     C     D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx
2  A2  B2    C2    D2

It also works if lines are in a different order in X and Y:

>>> Y = pd.DataFrame({'A': ['A1', 'A0'], 
                      'B': ['B1', 'B0'], 
                      'C': ['C1xx', 'C0xx'], 
                      'D': ['D1xx', 'D0xx']}, 
                     index=[1,0])
>>> Y
    A   B     C     D
1  A1  B1  C1xx  D1xx
0  A0  B0  C0xx  D0xx

>>> X.update(Y)
>>> X
    A   B     C     D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx
2  A2  B2    C2    D2

2. Different indexes

If Y has a different index:

>>> Y = pd.DataFrame({'A': ['A0', 'A1'], 
                      'B': ['B0', 'B1'], 
                      'C': ['C0xx', 'C1xx'], 
                      'D': ['D0xx', 'D1xx']}, 
                     index=[2,1])
>>> Y

    A   B     C     D
2  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx

You can still use update if you can find another column usable as an index (identifying the lines so that they match the lines to be replaced). I take the example of the "A" column but a multiple index would work as well.

>>> X2, Y2 = X.set_index("A"), Y.set_index("A")
>>> X2.update(Y2)
>>> X2.reset_index(inplace=True)
>>> X2
    A   B     C     D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx
2  A2  B2    C2    D2

edited Jul 25, 2017 at 11:43

answered Jul 25, 2017 at 9:45

F. Moïni

714 bronze badges

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

3 Comments

Fabio Lamanna Over a year ago

Very neat answer!

jezrael Over a year ago

It works only because first rows in both dataframes have same index, what in real data is not possible. Check my edit.

F. Moïni Over a year ago

@jezrael Thank you for your remark, I added your example in my answer. If possible, using a different index works fine.

jezrael · Accepted Answer · 2017-07-25 10:05:56Z

1

I think you need combine_first with set_index if need add missing values by A, B columns in both df:

print (Y.set_index(['A','B']).combine_first(X.set_index(['A','B'])).reset_index())

    A   B     C     D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx
2  A2  B2    C2    D2

Unfortunately update works bad:

Y = pd.DataFrame({'A': ['A0', 'A1'],
                  'B': ['B0', 'B1'], 
                  'C': ['C0xx', 'C1xx'], 
                  'D': ['D0xx', 'D1xx']}, index=[2,1])
print (X)
    A   B   C   D
0  A0  B0  C0  D0
1  A1  B1  C1  D1
2  A2  B2  C2  D2

print (Y)
    A   B     C     D
2  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx

X.update(Y)
print (X)
    A   B     C     D
0  A0  B0    C0    D0
1  A1  B1  C1xx  D1xx
2  A0  B0  C0xx  D0xx

X.set_index(['A','B']).update(Y.set_index(['A','B']))
print (X)
    A   B   C   D
0  A0  B0  C0  D0
1  A1  B1  C1  D1
2  A2  B2  C2  D2

print (Y.set_index(['A','B']).combine_first(X.set_index(['A','B'])).reset_index())
    A   B     C     D
0  A0  B0  C0xx  D0xx
1  A1  B1  C1xx  D1xx
2  A2  B2    C2    D2

edited Jul 25, 2017 at 10:05

answered Jul 25, 2017 at 9:15

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

1 Comment

jezrael Over a year ago

Glad can help! Good luck and nice day!

Collectives™ on Stack Overflow

Updating dataframe on values from another dataframe

2 Answers 2

1. Both DataFrames have the same index

2. Different indexes

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1. Both DataFrames have the same index

2. Different indexes

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related