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')

Answer 1

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')