change all values in a dataframe with other values from another dataframe-CodePudding

I just started with learning pandas. I have 2 dataframes. The first one is

   val  num
0    1    0
1    2    1
2    3    2
3    4    3
4    5    4

and the second one is

   0  1  2  3
0  1  2  3  4
1  5  3  2  2
2  2  5  3  2

I want to change my second dataframe so that the values present in the dataframe are compared with the column val in the first dataframe and every values that is the same needs then to be changed in the values that is present in de the num column from dataframe 1. Which means that in the end i need to get the following dataframe:

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

How do i do that in pandas?

CodePudding user response：

You can use DataFrame.replace() to do this:

df2.replace(df1.set_index('val')['num'])

Explanation:

The first step is to set the val column of the first DataFrame as the index. This will change how the matching is performed in the third step.
Convert the first DataFrame to a Series, by sub-setting to the index and the num column. It looks like this:
```
val
1    0
2    1
3    2
4    3
5    4
Name: num, dtype: int64
```
Next, use DataFrame.replace() to do the replacement in the second DataFrame. It looks up each value from the second DataFrame, finds a matching index in the Series, and replaces it with the value from the Series.

Full reproducible example:

import pandas as pd
import io

s = """   val  num
0    1    0
1    2    1
2    3    2
3    4    3
4    5    4"""
df1 = pd.read_csv(io.StringIO(s), delim_whitespace=True)
s = """   0  1  2  3
0  1  2  3  4
1  5  3  2  2
2  2  5  3  2"""
df2 = pd.read_csv(io.StringIO(s), delim_whitespace=True)

print(df2.replace(df1.set_index('val')['num']))

CodePudding user response：

Creat the mapping dict , then replace

mpd = dict(zip(df1.val,df1.num))
df2.replace(mpd, inplace=True)
   0  1  2  3
0  0  1  2  3
1  4  2  1  1
2  1  4  2  1