CODEWITHSUNDEEP
pythonpandasreplace
matan
matan

Reputation: 461

How to use replace in pandas with two DataFrames?

I am trying to replace values on df with values from another df

First DataFrame:

   col1  col2  col3  col4  col5 type
0   NaN     4     7  14.0     0    B
1   3.0     2     6  11.0     5    B
2   4.0     4     0  22.0     7    C
3   5.0     6    11   8.0     3    A
4   9.0     0     3   6.0     8    B
5   2.0     1     6   NaN     2    A
6   6.0     5     7   9.0     9    B

Second DataFrame:

   col1  col2
0     1   111
1     2   222
2     3   333
3     4   444

My code is:

for i in df2["col1"]:
    new_value=df2.loc[df2["col1"]==i]["col2"]
    df=df.replace(i,new_value)
print(df)

But the values of DataFrame haven't changed.

This is what I expect to get in the end:

    col1  col2  col3  col4  col5 type
0    NaN   444     7  14.0     0    B
1  333.0   222     6  11.0     5    B
2  444.0  4414     0  22.0     7    C
3    5.0     6    11   8.0   333    A
4    9.0     0     3   6.0     8    B
5  222.0   111     6   NaN   222    A
6    6.0     5     7   9.0     9    B

Upvotes: 3

Views: 261

Answers (2)

I_Am_Yohan
I_Am_Yohan

Reputation: 186

If you replace:

new_value=df2.loc[df2["col1"]==i]["col2"]

with:

new_value=int(df2.loc[df2["col1"]==i]["col2"])

It should work.

Before new_value was being interpreted a data frame "slice" rather than numeric which is what was required for this type of replace operation.

Upvotes: 1

Quang Hoang
Quang Hoang

Reputation: 150745

I think you want:

df1.replace(df2.set_index('col1')['col2'])

Output:

    col1  col2  col3  col4  col5 type
0    NaN   444     7  14.0     0    B
1  333.0   222     6  11.0     5    B
2  444.0   444     0  22.0     7    C
3    5.0     6    11   8.0   333    A
4    9.0     0   333   6.0     8    B
5  222.0   111     6   NaN   222    A
6    6.0     5     7   9.0     9    B

Upvotes: 2

Related Questions