Get indicator column when using df.join()

user_6623277 0
Viewed 125

I am currently working on a project involving huge Dataframes to be merged. The below code:

mergeddf = pd.merge(left=leftDataFrame,right=rightDataFrame,right_on = rightKey, left_on = leftKey, how='outer', suffixes = [leftName,rightName], indicator=True)

returns me a merged Dataframe with a column named "_merge" (due to the option indicator=True) which indicates if that row exists in "left_only", "right_only" or "both".

However, I found that merge takes a lot of time, specially when there are many columns as well as may rows (I am running this on chunks of 50K rows with 18 columns). An alternative I tried from Improve Pandas Merge performance is to set my "key" columns to join on as index and then use df.join(df2,how='outer') and it significantly ran faster!

But my problem is that join() does not return the "_merge" indicator column which I absolutely need. Is there any way to get that information on which row beloged to which dataframe (or both) when using join()?

pandas dataframe python-3.x python
1 Answers

@ayhan made the correct comment before me, but here's an elaboration:

leftDataFrame = leftDataFrame.set_index(leftKey)
rightDataFrame = rightDataFrame.set_index(rightKey)
mergeddf = pd.merge(
    left=leftDataFrame,
    right=rightDataFrame,
    left_index=True,
    right_index=True,
    how='outer',
    suffixes = [leftName, rightName],
    indicator=True)

As per documentation (https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html):

The related join() method, uses merge internally for the index-on-index (by ?>default) and column(s)-on-index join. If you are joining on index only, you may >wish to use DataFrame.join to save yourself some typing.

Digging deeper into the code, you can see that the code is a bit more involved than just a wrapper around merge, but the core functionality of join is captured with these lines:

                joined = merge(
                    joined, frame, how=how, left_index=True, right_index=True
                )
user_106935960
Related