Pandas: replace values of one data frame with values of another data frame based on index and column

Question

I want to merge/join/ two data frame replacing by index.

df1 = pd.DataFrame(index=range(5),columns=range(5))
df1 = df1.fillna(0)

df1
    0   1   2   3   4
0   0   0   0   0   0
1   0   0   0   0   0
2   0   0   0   0   0
3   0   0   0   0   0
4   0   0   0   0   0


d = {'2': [1,0,2,0,3], '4': [0,3,0,4,0]}
df2 = pd.DataFrame(data=d)
df2 = df2.iloc[[2,3],:]
df2
    2   4
2   2   0
3   0   4

This is what I have tried and it shows the below.

pd.concat([df1, df2], axis=1, join_axes=[df1.index])

    0   1   2   3   4   2   4
0   0   0   0   0   0   NaN NaN
1   0   0   0   0   0   NaN NaN
2   0   0   0   0   0   2.0 0.0
3   0   0   0   0   0   0.0 4.0
4   0   0   0   0   0   NaN NaN

I expect the merged data frame to be this.

    0   1   2   3   4   
0   0   0   0   0   0   
1   0   0   0   0   0   
2   0   0   2.0 0   0   
3   0   0   0   0   4.0 
4   0   0   0   0   0

BENY · Accepted Answer · 2019-05-14 15:02:49Z

4

Using update notice all method , you need make the index and columns dtype is same , that is why I first convert them to int , since when you create the df2 , the columns is str

df2.columns=df2.columns.astype(int)
df1.update(df2)
df1
Out[961]: 
   0  1    2  3    4
0  0  0  0.0  0  0.0
1  0  0  0.0  0  0.0
2  0  0  2.0  0  0.0
3  0  0  0.0  0  4.0
4  0  0  0.0  0  0.0

Or reindex_like

df2=df2.reindex_like(df1).fillna(0)
df2
Out[964]: 
     0    1    2    3    4
0  0.0  0.0  0.0  0.0  0.0
1  0.0  0.0  0.0  0.0  0.0
2  0.0  0.0  2.0  0.0  0.0
3  0.0  0.0  0.0  0.0  4.0
4  0.0  0.0  0.0  0.0  0.0

answered May 14, 2019 at 15:02

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

Pandas: replace values of one data frame with values of another data frame based on index and column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related