How to drop column from the target data frame, but the column(s) are required for the join in merge

Question

I have two dataframe df1, df2

df1.columns

['id','a','b']

df2.columns

['id','ab','cd','ab_test','mn_test']

Expected out column is ['id','a','b','ab_test','mn_test']

How to get the all the columns from df1, and columns which contain test in the column name

pseudocode > pd.merge(df1,df2,how='id')

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Shubham Sharma, Jun 17 '20 at 15:39

yatu · Accepted Answer · 2020-06-17T15:53:11.873

You can merge and use filter one the second dataframe to keep the columns of interest:

df1.merge(df2.filter(regex=r'^id$|test'), on='id')

Or similarly through bitwise operations:

df1.merge(df2.loc[:,(df2.columns=='id')|df2.columns.str.contains('test')], on='id')

df1 = pd.DataFrame(columns=['id','a','b'])
df2 = pd.DataFrame(columns=['id','ab','cd','ab_test','mn_test'])

df1.merge(df2.filter(regex=r'^id$|test'), on='id').columns
# Index(['a', 'b', 'id', 'ab_test', 'mn_test'], dtype='object')

How to drop column from the target data frame, but the column(s) are required for the join in merge

1 Answers1