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

Question

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

Rabinzel · Accepted Answer · 2022-11-03 21:01:11Z

1

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

answered Nov 3, 2022 at 21:01

Rabinzel

7,9533 gold badges12 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related