I have a dataframe like as shown below

1,Test Level,2021,1
2,Test Lvele,2022,1
2,dummy Inc,2022,1
2,dummy Pvt Inc,2022,1
3,dasho Ltd,2022,1
4,dasho PVT Ltd,2021,0
5,delphi Ltd,2021,1
6,delphi pvt ltd,2021,1

df = pd.read_clipboard(sep=',')

My objective is

a) To replace near duplicate strings using a common string.

For example - let's pick couple of strings from Name column. We have dummy Inc and dummy Pvt Inc. These both have to be replaced as dummy

I manually prepared a mapping df map_df like as below (but can't do this for big data)

  Test Level,Test
  Test Lvele,Test
  dummy Inc,dummy
  dummy Pvt Inc,dummy
  dasho Ltd,dasho
  dasho PVT Ltd,dasho
  delphi Ltd,delphi
  delphi pvt ltd,delphi

So, I tried the below

map_df = map_df.set_index(Name)
df['Name'] = df['Name'].map(map_df) # but this doesn't work and throws error

Is creating mapping table the only way or is there any NLP based approach?

I expect my output to be like as below

  • 1
    $\begingroup$ May worth checking out: dedupe $\endgroup$
    – lpounng
    May 12 at 2:06
  • $\begingroup$ @Ipounng - Is it open-source python package or commercial package? Can open-source package support only till 1000 rows? $\endgroup$
    – The Great
    May 12 at 2:16
  • $\begingroup$ It is MIT licensed. github.com/dedupeio/dedupe/blob/main/LICENSE $\endgroup$
    – lpounng
    May 12 at 2:17
  • $\begingroup$ The NLP technique is called record linkage, this answer might give you a few pointers. But you should be careful about the design, because it's likely that making it automatic will cause some errors (both false matches and false non-matches). An option is run the matching first, check manually that the results are correct, and then use a dictionary to replace in the data. Also the method can be more or less complex. In general it works better with long strings. $\endgroup$
    – Erwan
    May 12 at 16:26


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.