Set the values of rows from one dataframe based on the rows of another dataframe-CodePudding

I made a mock-up example to illustrate my problem, naturally, what I am working with is something way more complex. Reading the example will make everything easier to understand but my goal is to use the row reference values of one dataframe to set the values of a new column of another dataframe. That, taking my example, I want to create a new column in df1 named z1, that column will be formed by considering the values of x1 taking the reference of y2 values of d2.

import numpy as np 
import pandas as pd

x1 = np.array([])
for i in np.arange(0, 15, 3):
    x1i = np.repeat(i, 3)
    x1 = np.append(x1, x1i)
    
y1 = np.linspace(0, 1, len(x1))

x2 = np.arange(0, 15, 3)
y2 = np.linspace(0, 1, len(x2))

df1 = pd.DataFrame([x1, y1]).T
df2 = pd.DataFrame([x2, y2]).T
df1.columns = ['x1', 'y1']
df2.columns = ['x2', 'y2']

So, we have that df1 is:

      x1        y1
0    0.0  0.000000
1    0.0  0.071429
2    0.0  0.142857
3    3.0  0.214286
4    3.0  0.285714
5    3.0  0.357143
6    6.0  0.428571
7    6.0  0.500000
8    6.0  0.571429
9    9.0  0.642857
10   9.0  0.714286
11   9.0  0.785714
12  12.0  0.857143
13  12.0  0.928571
14  12.0  1.000000

and df2 is:

     x2    y2
0   0.0  0.00
1   3.0  0.25
2   6.0  0.50
3   9.0  0.75
4  12.0  1.00

What I would like to achieve is:

      x1        y1        z1
0    0.0  0.000000      0.00
1    0.0  0.071429      0.00
2    0.0  0.142857      0.00
3    3.0  0.214286      0.25
4    3.0  0.285714      0.25
5    3.0  0.357143      0.25
6    6.0  0.428571      0.50
7    6.0  0.500000      0.50
8    6.0  0.571429      0.50
9    9.0  0.642857      0.75
10   9.0  0.714286      0.75
11   9.0  0.785714      0.75
12  12.0  0.857143      1.00
13  12.0  0.928571      1.00
14  12.0  1.000000      1.00

CodePudding user response：

You can use map for this.

df1['z'] = df1['x1'].map(df2.set_index('x2')['y2'])

      x1        y1     z
0    0.0  0.000000  0.00
1    0.0  0.071429  0.00
2    0.0  0.142857  0.00
3    3.0  0.214286  0.25
4    3.0  0.285714  0.25
5    3.0  0.357143  0.25
6    6.0  0.428571  0.50
7    6.0  0.500000  0.50
8    6.0  0.571429  0.50
9    9.0  0.642857  0.75
10   9.0  0.714286  0.75
11   9.0  0.785714  0.75
12  12.0  0.857143  1.00
13  12.0  0.928571  1.00
14  12.0  1.000000  1.00