如何使用Python合并或连接两个含字符串类型的Pandas DataFrame

Hey there! Let's walk through merging your two DataFrames that include string-type data. First, let's recap your existing code and data setup:

Your Current Code & Data #

import pandas as pd

df1 = pd.read_csv('test1.csv', encoding='utf8', index_col=['id_df1'], header=0)
df2 = pd.read_csv('test2.csv', encoding='utf8', index_col=['id_df2'], header=0)

print(df1)
print(df2)

# Verify string data types
print(type(df1['contact_person'][0]))
print(type(df2['parents_list'][0]))

Output of print(df1): #

student contact_person
id_df1                         
1           john            Amy
2           jeff           Cindy
3         steven            Bob
4           tina            Amy

Output of print(df2): #

student    parents_list
id_df2                         
1           tina     (Amy) (Bob)
2         steven     (Eric) (Bob)
3           john           (Amy)
4           jeff  (Frank) (Harry)

Type Check Result: #

Both contact_person and parents_list columns are confirmed to be string (str) types.

Solution: Merge Using the Common Column #

Looking at your data, the student column exists in both DataFrames and is the ideal key to merge them. Pandas' merge() function handles string values seamlessly for matching.

Example 1: Inner Join (Default Behavior) #

This keeps only rows where the student value exists in both DataFrames:

merged_df = pd.merge(df1, df2, on='student')
print(merged_df)

Merged Output: #

student contact_person    parents_list
0    john            Amy           (Amy)
1    jeff           Cindy  (Frank) (Harry)
2  steven            Bob     (Eric) (Bob)
3    tina            Amy     (Amy) (Bob)

Example 2: Other Merge Types #

• Left Join: Keep all rows from df1, even if there's no matching entry in df2:

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

• Full Outer Join: Keep all rows from both DataFrames, including unmatched entries:

  merged_df_full = pd.merge(df1, df2, on='student', how='outer')
  

Bonus: Case-Insensitive Matching #

If you need to match student names regardless of case, normalize the student column first:

# Convert all student names to lowercase
df1['student'] = df1['student'].str.lower()
df2['student'] = df2['student'].str.lower()

# Now merge with case-insensitive matching
merged_df_case_insensitive = pd.merge(df1, df2, on='student')

内容的提问来源于stack exchange，提问作者Steven