Pandas: Merging DataFrames with Non-Unique Keys Causes Unexpected Duplicates
I've looked through the documentation and I'm still confused about I'm trying to merge two pandas DataFrames on a non-unique key, but I'm encountering unexpected duplicates in the resulting DataFrame. Here are the specifics: I have two DataFrames, `df1` and `df2`, which I want to merge on the `user_id` column. Both DataFrames contain multiple entries for the same `user_id`. Here's what my DataFrames look like: ```python import pandas as pd # DataFrame 1 data1 = { 'user_id': [1, 1, 2, 3], 'score': [10, 20, 30, 40] } df1 = pd.DataFrame(data1) # DataFrame 2 data2 = { 'user_id': [1, 2, 2, 4], 'level': ['A', 'B', 'C', 'D'] } df2 = pd.DataFrame(data2) ``` When I perform the merge using a left join: ```python result = pd.merge(df1, df2, on='user_id', how='left') ``` I expect to see the following output: ``` user_id score level 0 1 10 A 1 1 20 A 2 2 30 B 3 3 40 NaN ``` However, I am getting this unexpected output: ``` user_id score level 0 1 10 A 1 1 20 A 2 2 30 B 3 2 30 C 4 3 40 NaN ``` The row for `user_id` 2 appears twice, once for each corresponding entry in `df2`. I've checked my DataFrames and confirmed that they are set up correctly, and I expected the merge to align correctly based on the `user_id`. Is this behavior expected when merging on non-unique keys? How can I avoid these duplicate entries in the merged DataFrame while still retaining the correct associations? I've tried using `drop_duplicates()` after the merge, but it doesn't give the intended result as I need the data from both DataFrames to be preserved accurately. I'm using pandas version 1.5.1. Any help would be appreciated! For context: I'm using Python on Linux. Thanks in advance! Has anyone else encountered this?