- concat — pandas level: just stick data together with no condition
- merge — pandas level: stick with a common series or index level
- join — DataFrame level: stick with ‘index’ only
Example 1: Basic join — merge two DataFrames on their index #
import pandas as pd
df1 = pd.DataFrame({
"Age": [25,30,22]
}, index=["Alice", "Bob", "Cat"])
df2 = pd.DataFrame({
"City": ["HK", "Tokyo", "Sydney", "Taiwan"]
}, index=["Alice", "Bob", "Cat", "David"])
df1.join(df2)output:
Age City Alice 25 HK Bob 30 Tokyo Cat 22 Sydney
Example 2: Join when the index doesn't fully match — missing side gets NaN #
import pandas as pd
df1 = pd.DataFrame({
"Age": [25,30,22, 40]
}, index=["Alice", "Bob", "Cat", "Peter"])
df2 = pd.DataFrame({
"City": ["HK", "Tokyo", "Sydney", "New York"]
}, index=["Alice", "Bob", "Cat", "David"])
df1.join(df2)output:
Age City Alice 25 HK Bob 30 Tokyo Cat 22 Sydney Peter 40 NaN
Example 3: Join when index doesn't match — Bob gets NaN #
#When index not match
import pandas as pd
df1 = pd.DataFrame({
"Age": [25,30]
}, index=["Alice", "Bob"])
df2 = pd.DataFrame({
"City": ["HK"]
}, index=["Alice"])
df1.join(df2)output:
Age City Alice 25 HK Bob 30 NaN
Example 4: Join multiple DataFrames at once — pass a list #
import pandas as pd
df1 = pd.DataFrame({
"Age": [25,30]
}, index=["Alice", "Bob"])
df2 = pd.DataFrame({
"City": ["HK", "Taiwan"]
}, index=["Alice", "Bob"])
df3 = pd.DataFrame({
"Score": [90, 50]
}, index=["Alice", "Bob"])
df1.join([df2, df3])output:
Age City Score Alice 25 HK 90 Bob 30 Taiwan 50
Example 5: Join with how="inner" — after lowercasing the index to match #
names = pd.DataFrame({"age":[30,40]}, index=["alice","ben"])
cities = pd.DataFrame({"city":["HK","Tokyo"]}, index=["Alice","Ben"])
names.index = names.index.str.lower()
cities.index = cities.index.str.lower()
print(names.join(cities, how="inner"))output:
age city alice 30 HK ben 40 Tokyo