Pandas - merging dataframes conditionally on multiple columns

Question

I have 2 dataframes and I want to take one of the columns from one and create a new column in the second based on values in multiple (other) columns

First dataframe (df1):

df1 = pd.DataFrame({'cond': np.repeat([1,2], 5),
                    'point': np.tile(np.arange(1,6), 2),
                    'value1': np.random.rand(10),
                    'unused1': np.random.rand(10)})

   cond  point   unused1    value1
0     1      1  0.923699  0.103046
1     1      2  0.046528  0.188408
2     1      3  0.677052  0.481349
3     1      4  0.464000  0.807454
4     1      5  0.180575  0.962032
5     2      1  0.941624  0.437961
6     2      2  0.489738  0.026166
7     2      3  0.739453  0.109630
8     2      4  0.338997  0.415101
9     2      5  0.310235  0.660748

and the second (df2):

df2 = pd.DataFrame({'cond': np.repeat([1,2], 10),
                    'point': np.tile(np.arange(1,6), 4),
                    'value2': np.random.rand(20)})

    cond  point    value2
0      1      1  0.990252
1      1      2  0.534813
2      1      3  0.407325
3      1      4  0.969288
4      1      5  0.085832
5      1      1  0.922026
6      1      2  0.567615
7      1      3  0.174402
8      1      4  0.469556
9      1      5  0.511182
10     2      1  0.219902
11     2      2  0.761498
12     2      3  0.406981
13     2      4  0.551322
14     2      5  0.727761
15     2      1  0.075048
16     2      2  0.159903
17     2      3  0.726013
18     2      4  0.848213
19     2      5  0.284404

df1['value1'] contains values for each combination of cond and point.

I want to create a new column (new_column) in df2 that contains values from df1['value1'], but the values should be the ones where cond and point are matching across the 2 dataframes.

So my desired output looks like this:

    cond  point    value2  new_column
0      1      1  0.990252    0.103046
1      1      2  0.534813    0.188408
2      1      3  0.407325    0.481349
3      1      4  0.969288    0.807454
4      1      5  0.085832    0.962032
5      1      1  0.922026    0.103046
6      1      2  0.567615    0.188408
7      1      3  0.174402    0.481349
8      1      4  0.469556    0.807454
9      1      5  0.511182    0.962032
10     2      1  0.219902    0.437961
11     2      2  0.761498    0.026166
12     2      3  0.406981    0.109630
13     2      4  0.551322    0.415101
14     2      5  0.727761    0.660748
15     2      1  0.075048    0.437961
16     2      2  0.159903    0.026166
17     2      3  0.726013    0.109630
18     2      4  0.848213    0.415101
19     2      5  0.284404    0.660748

In this example I could just use tile/repeat, but in reality df1['value1'] doesn't fit so neatly into the other dataframe. So I just need to do it based on matching the cond and point columns

I've tried merging them, but 1) the numbers dont seem to match and 2) I dont want to bring over any unused columns from df1:

df1.merge(df2, left_on=['cond', 'point'], right_on=['cond', 'point'])

Whats the correct way to add this new column without having to iterate through the 2 dataframes?

Answer 1

Option 1
For grace and speed with pure pandas, we can use lookup
This will produce the same output as all other options, seen below.

The concept is to represent the lookup data as a 2-D array and lookup values with the indices.

d1 = df1.set_index(['cond', 'point']).value1.unstack()
df2.assign(new_column=d1.lookup(df2.cond, df2.point))

Option 2
We can do the same thing with numpy to improve performance if the values are presented in the same way they are in df1. This is very fast!

a = df1.value1.values.reshape(2, -1)
df2.assign(new_column=a[df2.cond.values - 1, df2.point.values - 1])

Option 3
The canonical answer is to use merge with the left parameter
But we'll need to prep df1 a bit to nail the output

d1 = df1[['cond', 'point', 'value1']].rename(columns={'value1': 'new_column'})
df2.merge(d1, 'left')

Option 4
I thought this was fun. Build a mapping dictionary and a series to map on
Good for small data, not so good for large data. See timing below.

c1 = df1.cond.values.tolist()
p1 = df1.point.values.tolist()
v1 = df1.value1.values.tolist()
m = {(c, p): v for c, p, v in zip(c1, p1, v1)}

c2 = df2.cond.values.tolist()
p2 = df2.point.values.tolist()
i2 = df2.index.values.tolist()
s2 = pd.Series({i: (c, p) for i, c, p in zip(i2, c2, p2)})

df2.assign(new_column=s2.map(m))

---

OUTPUT

    cond  point    value2  new_column
0      1      1  0.990252    0.103046
1      1      2  0.534813    0.188408
2      1      3  0.407325    0.481349
3      1      4  0.969288    0.807454
4      1      5  0.085832    0.962032
5      1      1  0.922026    0.103046
6      1      2  0.567615    0.188408
7      1      3  0.174402    0.481349
8      1      4  0.469556    0.807454
9      1      5  0.511182    0.962032
10     2      1  0.219902    0.437961
11     2      2  0.761498    0.026166
12     2      3  0.406981    0.109630
13     2      4  0.551322    0.415101
14     2      5  0.727761    0.660748
15     2      1  0.075048    0.437961
16     2      2  0.159903    0.026166
17     2      3  0.726013    0.109630
18     2      4  0.848213    0.415101
19     2      5  0.284404    0.660748

---

Timing
small data

%%timeit 
a = df1.value1.values.reshape(2, -1)
df2.assign(new_column=a[df2.cond.values - 1, df2.point.values - 1])
1000 loops, best of 3: 304 µs per loop

%%timeit
d1 = df1.set_index(['cond', 'point']).value1.unstack()
df2.assign(new_column=d1.lookup(df2.cond, df2.point))
100 loops, best of 3: 1.8 ms per loop

%%timeit
c1 = df1.cond.values.tolist()
p1 = df1.point.values.tolist()
v1 = df1.value1.values.tolist()
m = {(c, p): v for c, p, v in zip(c1, p1, v1)}
​
c2 = df2.cond.values.tolist()
p2 = df2.point.values.tolist()
i2 = df2.index.values.tolist()
s2 = pd.Series({i: (c, p) for i, c, p in zip(i2, c2, p2)})
​
df2.assign(new_column=s2.map(m))
1000 loops, best of 3: 719 µs per loop

%%timeit
d1 = df1[['cond', 'point', 'value1']].rename(columns={'value1': 'new_column'})
df2.merge(d1, 'left')
100 loops, best of 3: 2.04 ms per loop

%%timeit
df = pd.merge(df2, df1.drop('unused1', axis=1), 'left')
df.rename(columns={'value1': 'new_column'})
100 loops, best of 3: 2.01 ms per loop

%%timeit
df = df2.join(df1.drop('unused1', axis=1).set_index(['cond', 'point']), on=['cond', 'point'])
df.rename(columns={'value1': 'new_column'})
100 loops, best of 3: 2.15 ms per loop

large data

df2 = pd.concat([df2] * 10000, ignore_index=True)

%%timeit 
a = df1.value1.values.reshape(2, -1)
df2.assign(new_column=a[df2.cond.values - 1, df2.point.values - 1])
1000 loops, best of 3: 1.93 ms per loop

%%timeit
d1 = df1.set_index(['cond', 'point']).value1.unstack()
df2.assign(new_column=d1.lookup(df2.cond, df2.point))
100 loops, best of 3: 5.58 ms per loop

%%timeit
c1 = df1.cond.values.tolist()
p1 = df1.point.values.tolist()
v1 = df1.value1.values.tolist()
m = {(c, p): v for c, p, v in zip(c1, p1, v1)}
​
c2 = df2.cond.values.tolist()
p2 = df2.point.values.tolist()
i2 = df2.index.values.tolist()
s2 = pd.Series({i: (c, p) for i, c, p in zip(i2, c2, p2)})
​
df2.assign(new_column=s2.map(m))
10 loops, best of 3: 135 ms per loop

%%timeit
d1 = df1[['cond', 'point', 'value1']].rename(columns={'value1': 'new_column'})
df2.merge(d1, 'left')
100 loops, best of 3: 13.4 ms per loop

%%timeit
df = pd.merge(df2, df1.drop('unused1', axis=1), 'left')
df.rename(columns={'value1': 'new_column'})
10 loops, best of 3: 19.8 ms per loop

%%timeit
df = df2.join(df1.drop('unused1', axis=1).set_index(['cond', 'point']), on=['cond', 'point'])
df.rename(columns={'value1': 'new_column'})
100 loops, best of 3: 18.2 ms per loop

Answer 2

You can use merge with left join and drop for remove unused1 column, last rename column:

Notice: Parameter on can be omit if in both DataFrames are only same columns for join. If more same column names, add on=['cond', 'point'].

df = pd.merge(df2, df1.drop('unused1', axis=1), 'left')
df = df.rename(columns={'value1': 'new_column'})
print (df)
    cond  point    value2  new_column
0      1      1  0.990252    0.103046
1      1      2  0.534813    0.188408
2      1      3  0.407325    0.481349
3      1      4  0.969288    0.807454
4      1      5  0.085832    0.962032
5      1      1  0.922026    0.103046
6      1      2  0.567615    0.188408
7      1      3  0.174402    0.481349
8      1      4  0.469556    0.807454
9      1      5  0.511182    0.962032
10     2      1  0.219902    0.437961
11     2      2  0.761498    0.026166
12     2      3  0.406981    0.109630
13     2      4  0.551322    0.415101
14     2      5  0.727761    0.660748
15     2      1  0.075048    0.437961
16     2      2  0.159903    0.026166
17     2      3  0.726013    0.109630
18     2      4  0.848213    0.415101
19     2      5  0.284404    0.660748

Another solution with join (default left join) with set_index + drop:

df = df2.join(df1.drop('unused1', axis=1).set_index(['cond', 'point']), on=['cond', 'point'])
df = df.rename(columns={'value1': 'new_column'})
print (df)
    cond  point    value2  new_column
0      1      1  0.990252    0.103046
1      1      2  0.534813    0.188408
2      1      3  0.407325    0.481349
3      1      4  0.969288    0.807454
4      1      5  0.085832    0.962032
5      1      1  0.922026    0.103046
6      1      2  0.567615    0.188408
7      1      3  0.174402    0.481349
8      1      4  0.469556    0.807454
9      1      5  0.511182    0.962032
10     2      1  0.219902    0.437961
11     2      2  0.761498    0.026166
12     2      3  0.406981    0.109630
13     2      4  0.551322    0.415101
14     2      5  0.727761    0.660748
15     2      1  0.075048    0.437961
16     2      2  0.159903    0.026166
17     2      3  0.726013    0.109630
18     2      4  0.848213    0.415101
19     2      5  0.284404    0.660748