How to get column names that belong only to a right table that we brought in left join

user_3849781 0
Viewed 58

I am doing a following left join

left_join = left.merge(right, how="left", left_on=[attr1], right_on=[attr2])

How can I now get names of columns that belong only to a right table in left join? pandas sometimes renames columns if we bring same name column so I cannot just get the names of columns from right table. Also since we merge on one attribute one of the columns will not be present, thus I need to somehow extract them from left_join.

Thank you!

EDIT: My solution was simpler than I expected. I solved it as

names = left_join.columns.values

names[left.shape[1]:]
pandas python
3 Answers
# Get column names from `right` that were a part of the merge key.
m1 = left_join.columns.isin(right.columns)
# Get column names that were appended with suffix "_y".
m2 = left_join.columns.str.endswith('_y')

left_join.iloc[:, m1 | m2]

If all you want are columns that are exclusive to right, replace the last line of code above with

left_join.iloc[:, m2]
user_49090870

You can do with filter before merge then rename 

left_join = left[attr1].\
             merge(right, how="left", left_on=[attr1], right_on=[attr2]).\
              rename(columns=dict(zip(attr1,attr2)))
user_79645270

Use the suffixes argument

If there are any overlapping columns, you can control what gets appended to the column name with the suffixes argument.

left.merge(right, 'left', left_on=attr1, right_on=attr2, suffixes=['_', ''])

   A  B_     B    C    D    E    F
0  1   4  10.0    X    I  7.0  1.0
1  2   5  11.0    Y    J  8.0  2.0
2  3   6   NaN  NaN  NaN  NaN  NaN

Notice that the overlapping column name 'B' had the suffix of '_' appended for the column that came from the left dataframe and a suffix of '' (yes, the empty string) appended the to the column name from the right dataframe.

Now, I the column names from the right are the same names as the columns from the right

left.merge(right, 'left', left_on=attr1, right_on=attr2, suffixes=['_', ''])[[*right]]

      B    C    D    E    F
0  10.0    X    I  7.0  1.0
1  11.0    Y    J  8.0  2.0
2   NaN  NaN  NaN  NaN  NaN

Details of [[*right]]

right.columns.tolist()

['C', 'D', 'E', 'F']

Or as I put in the answer

[*right]

['C', 'D', 'E', 'F']

Setup

left = pd.DataFrame(dict(
    A=[1, 2, 3],
    B=[4, 5, 6],
))

right = pd.DataFrame(dict(
    B=[10, 11, 12],
    C=[*'XYZ'],
    D=[*'IJK'],
    E=[7, 8, 9],
    F=[1, 2, 4]
))

attr1 = 'A'
attr2 = 'F'
user_23366540
Related