Left join behavior in Pandas

Introduction

A left join helps you combine two tables by keeping all rows from the first table and matching rows from the second. It fills in missing matches with empty values.

You have a list of customers and want to add their orders, but keep all customers even if they have no orders.

You want to combine employee data with department info, but keep all employees listed.

You have a list of products and want to add supplier details, but keep all products even if some have no supplier.

You want to merge student records with test scores, but keep all students even if some missed the test.

Syntax

Pandas

pd.merge(left_df, right_df, how='left', on='key_column')

The 'how' parameter set to 'left' keeps all rows from the left dataframe.

Rows in the left dataframe without matches in the right get NaN in the new columns.

Examples

Merge df1 and df2 keeping all rows from df1, matching on 'id'.

Pandas

pd.merge(df1, df2, how='left', on='id')

Keep all customers and add their orders if any.

Pandas

pd.merge(customers, orders, how='left', on='customer_id')

Join employees with departments using different column names.

Pandas

pd.merge(employees, departments, how='left', left_on='dept_id', right_on='id')

Sample Program

This keeps all rows from 'left'. For ids 2 and 3, it adds scores from 'right'. For id 1, no match is found, so score is NaN.

Pandas

import pandas as pd

# Left dataframe
left = pd.DataFrame({'id': [1, 2, 3], 'name': ['Alice', 'Bob', 'Charlie']})

# Right dataframe
right = pd.DataFrame({'id': [2, 3, 4], 'score': [85, 90, 75]})

# Left join on 'id'
result = pd.merge(left, right, how='left', on='id')
print(result)

Important Notes

If no matching rows are found in the right dataframe, the result will have NaN for those columns.

You can join on multiple columns by passing a list to the 'on' parameter.

Left join is useful when you want to keep all data from the main table and add extra info if available.

Summary

Left join keeps all rows from the left table and adds matching rows from the right.

Unmatched rows from the right table result in NaN values in the output.

Use 'how="left"' in pd.merge to perform a left join.