Updating a python dataframe with new data from another dataframe

Question

I am trying to update an existing data frame (df1) with data that is received from a different data frame (df2). Data frame df2 may have new column, new rows or new data. Here is an example of what I am trying to accomplish.

df1

    var1   var2    var3
a      8      4      12
b     -1     -4      -3
c      9     12      11
d     12     15       7
e      1      3      12
f      2      4       6

df2 (note that this is almost like df1 except it does not have column var1, has a new column var4, has updated values for var3, var2 is the same and has a new row "month s3").

    var2    var3    var4
a      4      10      12 
b     -4       0       4
c     12      15       9
d     15      12       5
e      3      17       7
f      4      16       8
g      0       0       4

This should be the updated df

    var1   var2    var3    var4
a      8      4      10      12 
b     -1     -4       0       4
c      9     12      15       9
d     12     15      12       5
e      1      3      17       7
f      2      4      16       8
g      0      0       0       4

What's the best way to accomplish this task? (I am specifically stuck on adding any extra row that may be present in df2).

DYZ · Accepted Answer · 2018-09-14 05:04:40Z

1

Since both dataframes share the index, the easiest way to combine them is to perform an outer join:

df = df1.join(df2, how='outer', lsuffix='_l').fillna(0).astype(int)

lsuffix marks the duplicated columns in the left dataframe with the suffic '_l'. fillna fills the missing values in the left dataframe with zeros. Finally, remove the duplicated columns:

df = df.loc[:,~df.columns.str.endswith('_l')]

answered Sep 14, 2018 at 5:04

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

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

2 Comments

arqchicago Over a year ago

What if df2 does not contain a row that was in df1? For example, say that df2 is missing row e (3, 17, 7). The updated df should still have row e with values 1, 3, 12, 0 for var1, var2, var3 and var4 respectively.

DYZ Over a year ago

@arqchicago It will have that row. That's what outer join is for.

Collectives™ on Stack Overflow

Updating a python dataframe with new data from another dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related