JOIN in pandas

Posted by Kevin

Selecting data from multiple SQL tables through a JOIN.

Join two tables, return all columns in pandas

In SQL:

SELECT 
  t1.*, 
  t2.* 
FROM 
  table_1 AS t1 
  LEFT OUTER JOIN table_2 AS t2 ON t1.column_1 = t2.column_1;

In pandas:

table_1
    .merge(table_2, left_on='column_1', right_on='column_1', how='left')

Alternatively:

pd.merge(table_1, table_2, left_on='column_1', right_on='column_1', how='left')

The mechanics of joins in pandas are the same for left, right or inner or outer joins. Specify the kind of join in the how parameter.

Join two tables, return a selection of columns in pandas

In SQL:

SELECT 
  t1.column_1, 
  t1.column_2, 
  t2.column_3 
FROM 
  table_1 AS t1 
  LEFT JOIN table_2 AS t2 ON t1.column_1 = t2.column_1;

In pandas:

table_1[['column_1', 'column_2']]
    .merge(table_2[['column_1', 'column_3']], 
           left_on='column_1', 
           right_on='column_1', 
           how='left')

Alternatively:

pd.merge(table_1[['column_1', 'column_2']], 
         table_2[['column_1', 'column_3']], 
         left_on='column_1', 
         right_on='column_1', 
         how='left')

Alternatively, in case of join columns with equivalent names, specifying the on parameter:

pd.merge(table_1[['column_1', 'column_2']], 
         table_2[['column_1', 'column_3']], 
         on='column_1', 
         how='left')

Alternatively, in case of join columns with equivalent names, the on parameter can be ommitted:

pd.merge(table_1[['column_1', 'column_2']],
         table_2[['column_1', 'column_3']], 
         how='left')

References