how to join two dataframe and align on a specific column like this?

Question

I want to do some work by joining two dataframe and aligning on a specific column like this:

dataframe left like:

dict1={'abstract': {0: 'A1', 1: 'A2', 2: 'A3', 3: 'B1', 4: 'B2', 5: 'B3', 6: 'B4', 7: 'C1', 8: 'C2'}, 
       'name': {0: 'A', 1: 'A', 2: 'A', 3: 'B', 4: 'B', 5: 'B', 6: 'B', 7: 'C', 8: 'C'}}

left=pd.DataFrame(dict1)

dataframe right like:

dict2={'abstract': {0: 'A1', 1: 'A2', 2: 'B1', 3: 'B2', 4: 'B3', 5: 'C1', 6: 'C2', 7: 'C3'}, 
       'name': {0: 'A', 1: 'A', 2: 'B', 3: 'B', 4: 'B', 5: 'C', 6: 'C', 7: 'C'}}

right=pd.DataFrame(dict2)

And I want to get a combined dataframe like this:

dict3={'name': {0: 'A', 1: 'A', 2: 'A', 3: 'B', 4: 'B', 5: 'B', 6: 'B', 7: 'C', 8: 'C', 9: 'C'}, 
       'abstract_right': {0: 'A1', 1: 'A2', 2: nan, 3: 'B1', 4: 'B2', 5: 'B3', 6: nan, 7: 'C1', 8: 'C2', 9: 'C3'}, 
       'abstract_left': {0: 'A1', 1: 'A2', 2: 'A3', 3: 'B1', 4: 'B2', 5: 'B3', 6: 'B4', 7: 'C1', 8: 'C2', 9: nan}}

combined=pd.DataFrame(dict3)

How to do it with Pandas?

Answer 1

You can merge with additional information where the value cam from and add the left and right columns later:

res = pd.merge(left, right, how='outer', indicator=True)
res['abstract_left'] = res.abstract[res._merge != 'right_only']
res['abstract_right'] = res.abstract[res._merge != 'left_only']
res.drop(['abstract', '_merge'], axis=1)

In Steps

Do an outer join:

res = pd.merge(left, right, how='outer', indicator=True)

This is the result:

Now, you add your two columns based on the values in _merged:

res['abstract_left'] = res.abstract[res._merge != 'right_only']
res['abstract_right'] = res.abstract[res._merge != 'left_only']

and remove the unwanted columns:

res.drop(['abstract', '_merge'], axis=1)

for the final result.

Answer 2

What you need is less than a join but more than a concatenation since they have to be matched by name. You can create an id column to help you merge and align the rows:

left['id'] = left.groupby('name').cumcount()
right['id'] = right.groupby('name').cumcount()
left.merge(right, on=['id', 'name'], how='outer', suffixes=['_left', '_right']).drop('id', axis=1)

Answer 3

You might be interested in the merge function

left_df.merge(right_df, how='outer', indicator=True)