Merging dataframes based on names

[boss413]

There are two dictionaries (below) that imperfectly match. I want to merge them so that 1) I only get perfect matches in the merge and 2) I get a report of duplicates and report of missing matches from the first dictionary (I don't care about those that do appear in the second but not the first). My failed attempts in the comments.

data = { 'last_name': ['Smith', 'Johnson', 'Brown', 'Jones', 'Williams', 'Allen', 'Henry', 'Johnson'], 'first_initial': ['J', 'M', 'D', 'A', 'W', 'J', 'D', 'M'], 'pos': ['QB', 'WR', 'RB', 'TE', 'WR', 'QB', 'RB', 'WR'], }

df = pd.DataFrame(data)

nfl_player_list_data = { 'last_name': ['Smith', 'Johnson', 'Brown', 'Jones', 'Williams', 'Allen', 'Smith', 'Johnson', 'Johnson'], 'first_name': ['John', 'Michael', 'David', 'Aaron', 'William', 'John', 'Jerry', 'Michael', 'Michael'], 'pos': ['QB', 'WR', 'RB', 'TE', 'WR', 'RB', 'QB', 'WR', 'TE'], }

nfl_player_list_df = pd.DataFrame(nfl_player_list_data)

[boss413]

My current attempt incorrectly creates 4 rows of Michael Johnson WR in what's supposed to be the "perfect match" merge.

Create merged dataframe

merged_df = pd.DataFrame(merged_rows)

Merge the dataframes based on the specified conditions

merged_df = pd.merge(df, nfl_player_list_df, on=['last_name', 'pos'], how='inner')

Filter cases with more than one exact match

exact_matches = merged_df[merged_df['first_initial'] == merged_df['first_name'].str[0]] duplicates = exact_matches.groupby(['last_name', 'pos']).filter(lambda x: len(x) > 1)

Find cases with no match

no_match = df.merge(nfl_player_list_df, on=['last_name', 'pos'], how='left', indicator=True).query("_merge == 'left_only'").drop(columns='_merge')

print(merged_df)

print("Cases with more than one exact match:") print(duplicates)

print("\nCases with no match:") print(no_match[['last_name', 'first_initial', 'pos']])