Left join on multiple columns

Question

I'm used to using dplyr with R where I would do something like

library(dplyr)
mtcars2=mtcars
mtcars3 = mtcars %>% left_join(mtcars2[,c("mpg","vs","hp")], by =c("mpg",'hp') )

# what this does is I do a left join with multiple columns and then bring over only *1* additional column.  This means that mtcars3 only has one additional field - a duplicated 'vs'

I can't figure out how to use pd.merge to do the same thing. I would want to join by two columns and then bring over only the 3rd column - not every column in the joined table except for the join-bys if that makes sense

import pandas as pd
mtcars = pd.read_csv('mtcars.csv')
mtcars2=mtcars

mtcars3  = pd.merge(mtcars, mtcars2['vs','hp','mpg'],how='left', on = ['mpg','hp'])

Answer 1

IIUC you can use subset by adding [] and omit mtcars2 - you can use mtcars again:

import pandas as pd
mtcars = pd.read_csv('mtcars.csv')
mtcars3  = pd.merge(mtcars, mtcars[['vs','hp','mpg']], how='left', on = ['mpg','hp'])

Sample:

import pandas as pd

mtcars = pd.DataFrame({'vs':[1,2,3],
                       'hp':[1,1,1],
                       'mpg':[7,7,9],
                       'aaa':[1,3,5]})

print (mtcars)
   aaa  hp  mpg  vs
0    1   1    7   1
1    3   1    7   2
2    5   1    9   3

mtcars3  = pd.merge(mtcars, mtcars[['vs','hp','mpg']], how='left', on = ['mpg','hp'])
print (mtcars3)
   aaa  hp  mpg  vs_x  vs_y
0    1   1    7     1     1
1    1   1    7     1     2
2    3   1    7     2     1
3    3   1    7     2     2
4    5   1    9     3     3

Answer 2

It's a late answer but if you are familiar with R/dplyr, here is a way to do it similarly in python:

>>> from datar.datasets import mtcars
>>> from datar.all import f, left_join, select
>>> mtcars >> left_join(mtcars >> select(f[f.mpg:f.hp:1]), by=[f.mpg, f.hp])
         mpg   cyl_x    disp_x      hp      drat        wt      qsec      vs      am    gear    carb   cyl_y    disp_y
   <float64> <int64> <float64> <int64> <float64> <float64> <float64> <int64> <int64> <int64> <int64> <int64> <float64>
0       21.0       6     160.0     110      3.90     2.620     16.46       0       1       4       4       6     160.0
1       21.0       6     160.0     110      3.90     2.620     16.46       0       1       4       4       6     160.0
2       21.0       6     160.0     110      3.90     2.875     17.02       0       1       4       4       6     160.0
3       21.0       6     160.0     110      3.90     2.875     17.02       0       1       4       4       6     160.0
... ...

I am the author of the datar package.