Pandas loc with prefix

user_3599541 0
Viewed 110

I have two data frames and I want for each line in one data frame to locate the matching line in the other data frame by a certain column (containing some id). I thought to go over the lines in the df1 and use the loc function to find the matching line in df2.

The problem is that some of the id's in df2 has some extra information except the id itself.

For example: df1 has the id: 1234, df2 has the id: 1234-KF

How can I locate this id for example with loc? Can loc somehow match only by prefixes?

pandas-loc pandas dataframe python
1 Answers

Extra information can be removed using e.g. regular expression (or substring):

import pandas as pd
import re

df1 = pd.DataFrame({
    'id': ['123', '124', '125'],
    'data': ['A', 'B', 'C']
})

df2 = pd.DataFrame({
    'id': ['123-AA', '124-AA', '125-AA'],
    'data': ['1', '2', '3']
})

df2.loc[df2.id.apply(lambda s : re.sub("[^0-9]", "", s)) == df1.id]
user_141686230
Related