How to Count Teachers by Country Using pandas merge and join in Python

Introduction

Hello, I am a Python enthusiast. A fan asked a data‑analysis question about counting teachers from the United States, which serves as the basis for this tutorial.

Problem Statement

The goal is to determine how many teachers belong to each country, specifically how many are from the United States, using Python for data analysis.

Solution Overview

Method 1: pandas.merge()

Code example:

import pandas as pd

data1 = {"学校": ["哈佛", "MIT", "清华", "早稻田"], "国家": ["美国", "美国", "中国", "日本"]}

data2 = {"学校": ["哈佛", "MIT", "MIT", "清华", "清华", "早稻田"],
         "老师": ["John", "Mike", "Jason", "李明", "韩磊", "武田康福"]}

data1 = pd.DataFrame(data1)
data2 = pd.DataFrame(data2)

print(data1)
print(data2)

# Merge on the "学校" column and count occurrences of each country
print(data2.merge(data1, how='left').value_counts('国家'))

# If only the merged table is needed, remove the value_counts() call
print(data2.merge(data1, how='left'))

The merge operation joins the two tables on the school column and can count the number of teachers per country. When the requirement changed to list all records without counting, the value_counts() call is simply omitted.

Method 2: pandas.join()

The join() method joins on the index and can also satisfy the fan’s requirement. The original image shows code similar to:

data2.set_index('学校').join(data1.set_index('学校'))

Conclusion

The tutorial demonstrates practical use of pandas.merge() and pandas.join() to group and count data, fulfilling the fan’s request and deepening understanding of these essential pandas functions.