I have to dataframes, df1 has columns A, B, C, D... and df2 has columns A, B, E, F...
The keys I want to merge with are in column A. B is also (most likely) the same in both dataframes. This is a big dataset so I do not have a good overview of everything yet.
When I do
pd.merge(df1, df2, on='A')
the results contains a column called B_x. Since the dataset is big and messy I haven't tried to investigate how B_x differs from B in df1 and B in df2.
So my question is just in general: What does Pandas mean when it has appended _x to a column name in the merged dataframe?
The suffixes are added for any clashes in column names that are not involved in the merge operation, see online docs.
So in your case if you think that they are same you could just do the merge on both columns:
pd.merge(df1, df2, on=['A', 'B'])
What this will do though is return only the values where A and B exist in both dataframes as the default merge type is an inner merge.
So what you could do is compare this merged df size with your first one and see if they are the same and if so you could do a merge on both columns or just drop/rename the _x/_y suffix B columns.
I would spend time though determining if these values are indeed the same and exist in both dataframes, in which case you may wish to perform an outer merge:
pd.merge(df1, df2, on=['A', 'B'], how='outer')
Then what you could do is then drop duplicate rows (and possibly any NaN rows) and that should give you a clean merged dataframe.
merged_df.drop_duplicates(cols=['A', 'B'],inplace=True)
See online docs for drop_duplicates