Gregg Powell
2022-Jun-15 15:43 UTC
[R] Is there a package that can do Fuzzy name matching to standardize names in a single column
Hello Ashim and kind regards for you taking the time to answer back.> library(fuzzyjoin) > ?stringdist_left_join-this will join two tables, but what I am trying to do is just standardize the similarly spelled duplicate names in just the first column of a single table. I don't think fuzzyjoin will help me in that regard. Thanks. Gregg Arizona, USA ------- Original Message ------- On Wednesday, June 15th, 2022 at 8:04 AM, Ashim Kapoor <ashimkapoor at gmail.com> wrote:>>> Dear Gregg, >> Check this out: >> library(fuzzyjoin) > ?stringdist_left_join >> Best Regards, > Ashim >> On Wed, Jun 15, 2022 at 8:28 PM Gregg Powell via R-help > r-help at r-project.org wrote: >> > Have data sets where there are names, in the first column, client names in the second, and Client start date in the third. > >> > There are thousands of these records with thousands of names/clients/client start dates. The name is entered each time the person begins with a new client such that each person has many entries in the name column. Often the names were not entered in a consistent way. With and without middle initial, middle name, or various abbreviations such as ",RN" at the end of the name. > >> > Is there a package that can do fuzzy name matching so that the names in name column get replaced with a "standardized" format - where some type of machine learning can pick the most common spelling of each repeat name and replace the different variations with the common spelling? > >> > I included an example below. First table includes the names with the various spellings. Second table depicts what I hope to achieve. > >> > Again - this is on a large scale - there are something like 10,000 records with names that need to be standardized. > >> > Name > >> > Client > >> > Client Start Date > >> > John Good > >> > Client 1 > >> > 1/1/2020 > >> > Joe Jackson > >> > Client 2 > >> > 6/1/2020 > >> > Bob A. Barker > >> > Client 3 > >> > 8/1/2020 > >> > John B. Good > >> > Client 4 > >> > 10/1/2020 > >> > Joe J. Jackson > >> > Client 5 > >> > 12/1/2020 > >> > Bob Allen Barker > >> > Client 6 > >> > 1/1/2021 > >> > John Good > >> > Client 7 > >> > 5/1/2021 > >> > Joe Jack Jackson > >> > Client 8 > >> > 8/1/2021 > >> > Bob Barker > >> > Client 9 > >> > 12/1/2021 > >> > Name > >> > Client > >> > Client Start Date > >> > John Good > >> > Client 1 > >> > 1/1/2020 > >> > Joe J. Jackson > >> > Client 2 > >> > 6/1/2020 > >> > Bob A. Barker > >> > Client 3 > >> > 8/1/2020 > >> > John Good > >> > Client 4 > >> > 10/1/2020 > >> > Joe J. Jackson > >> > Client 5 > >> > 12/1/2020 > >> > Bob A. Barker > >> > Client 6 > >> > 1/1/2021 > >> > John Good > >> > Client 7 > >> > 5/1/2021 > >> > Joe J. Jackson > >> > Client 8 > >> > 8/1/2021 > >> > Bob A. Barker > >> > Client 9 > >> > 12/1/2021 > >> > THANKS! > >> > Gregg Powell > >> > Arizona, USA______________________________________________ > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > > and provide commented, minimal, self-contained, reproducible code.-------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 509 bytes Desc: OpenPGP digital signature URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20220615/940a006c/attachment.sig>
Jeff Newmiller
2022-Jun-15 16:24 UTC
[R] Is there a package that can do Fuzzy name matching to standardize names in a single column
This is an intractable problem... you cannot know that "John Good" is the same person as "John B. Good"... and even when you augment their identity with information like which town they are in or what the first name of their spouse is you could be mislead by such information in multiple ways. Most of the time such lists are managed by creating an internal "unique id" for each person. But since there is no definitive way to do this, it is handled as an ongoing process, with multiple algorithms applied with or without human supervision and always with some risk of increasing the error rate by any specific algorithm than was originally present in the data. One common approach is using regular expressions or approximate match algorithms like base::agrep to filter possible like identities for people to check into further. Another approach could be a clustering algorithm as Bert suggested. It has been awhile since I had to do this kind of work... I suppose neural nets might also be applied to this problem these days. But an agrep pre-filtering human augmentation should not be discounted... 10k entries is not _that_ many. On June 15, 2022 8:43:14 AM PDT, Gregg Powell via R-help <r-help at r-project.org> wrote:> >Hello Ashim and kind regards for you taking the time to answer back. > > >> library(fuzzyjoin) >> ?stringdist_left_join > >-this will join two tables, but what I am trying to do is just standardize the similarly spelled duplicate names in just the first column of a single table. > >I don't think fuzzyjoin will help me in that regard. > >Thanks. >Gregg >Arizona, USA > >------- Original Message ------- >On Wednesday, June 15th, 2022 at 8:04 AM, Ashim Kapoor <ashimkapoor at gmail.com> wrote: > > >> > >> > >> Dear Gregg, >> > >> Check this out: >> > >> library(fuzzyjoin) >> ?stringdist_left_join >> > >> Best Regards, >> Ashim >> > >> On Wed, Jun 15, 2022 at 8:28 PM Gregg Powell via R-help >> r-help at r-project.org wrote: >> > >> > Have data sets where there are names, in the first column, client names in the second, and Client start date in the third. >> > > >> > There are thousands of these records with thousands of names/clients/client start dates. The name is entered each time the person begins with a new client such that each person has many entries in the name column. Often the names were not entered in a consistent way. With and without middle initial, middle name, or various abbreviations such as ",RN" at the end of the name. >> > > >> > Is there a package that can do fuzzy name matching so that the names in name column get replaced with a "standardized" format - where some type of machine learning can pick the most common spelling of each repeat name and replace the different variations with the common spelling? >> > > >> > I included an example below. First table includes the names with the various spellings. Second table depicts what I hope to achieve. >> > > >> > Again - this is on a large scale - there are something like 10,000 records with names that need to be standardized. >> > > >> > Name >> > > >> > Client >> > > >> > Client Start Date >> > > >> > John Good >> > > >> > Client 1 >> > > >> > 1/1/2020 >> > > >> > Joe Jackson >> > > >> > Client 2 >> > > >> > 6/1/2020 >> > > >> > Bob A. Barker >> > > >> > Client 3 >> > > >> > 8/1/2020 >> > > >> > John B. Good >> > > >> > Client 4 >> > > >> > 10/1/2020 >> > > >> > Joe J. Jackson >> > > >> > Client 5 >> > > >> > 12/1/2020 >> > > >> > Bob Allen Barker >> > > >> > Client 6 >> > > >> > 1/1/2021 >> > > >> > John Good >> > > >> > Client 7 >> > > >> > 5/1/2021 >> > > >> > Joe Jack Jackson >> > > >> > Client 8 >> > > >> > 8/1/2021 >> > > >> > Bob Barker >> > > >> > Client 9 >> > > >> > 12/1/2021 >> > > >> > Name >> > > >> > Client >> > > >> > Client Start Date >> > > >> > John Good >> > > >> > Client 1 >> > > >> > 1/1/2020 >> > > >> > Joe J. Jackson >> > > >> > Client 2 >> > > >> > 6/1/2020 >> > > >> > Bob A. Barker >> > > >> > Client 3 >> > > >> > 8/1/2020 >> > > >> > John Good >> > > >> > Client 4 >> > > >> > 10/1/2020 >> > > >> > Joe J. Jackson >> > > >> > Client 5 >> > > >> > 12/1/2020 >> > > >> > Bob A. Barker >> > > >> > Client 6 >> > > >> > 1/1/2021 >> > > >> > John Good >> > > >> > Client 7 >> > > >> > 5/1/2021 >> > > >> > Joe J. Jackson >> > > >> > Client 8 >> > > >> > 8/1/2021 >> > > >> > Bob A. Barker >> > > >> > Client 9 >> > > >> > 12/1/2021 >> > > >> > THANKS! >> > > >> > Gregg Powell >> > > >> > Arizona, USA______________________________________________ >> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >> > https://stat.ethz.ch/mailman/listinfo/r-help >> > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> > and provide commented, minimal, self-contained, reproducible code.-- Sent from my phone. Please excuse my brevity.
Chris Evans
2022-Jun-15 16:39 UTC
[R] Is there a package that can do Fuzzy name matching to standardize names in a single column
This isn't my expert area but I have at times encountered issues relating to
it and I think this isn't "just"
(as in "just standardize the similarly spelled duplicate names"). I
once thought about trying to work out how
many names I have in citations to my work. Over the years I have seen my name
as:
Chris Evans
Evans, Chris
Christopher Evans
Evans, Christopher
C.D.H.Evans
Evans, C.D.H.
and a great one that a bank once gave me: DR CHRISTOPHE D EVANS (honestly ...
why?)
Then there are all the misspellings as you say. Back in the days of snail mail
reprint requests I used to
get teased about getting a fair few addressed to "Christ Evans".
Then there are things that add permutations of my qualifications (OK, perhaps
not in your data but you have
the "Jr." and perhaps "III" or the like. I think these are
more common in the USA than the UK.)
I also suspect that having names of "non-English" origins in there may
complicate things too. I still get
Spanish naming conventions wrong and know that the default order of given name /
family name is reversed
in Japanese but that many Japanese know that much of the world won't know
this so reverse their name order
for things going outside Japan.
I think there's nothing trivial or "just" about doing this but I
suspect there are established, accepted,
and always fallible ways of doing it but I have a nasty suspicion that some are
proprietary and not at all
open source.
I think you may have to start with the issue of commas: are they being used
before terminal qualifiers
(", Jr.", ", Dr." ...) or are they reversing family name and
given name ("Evans, Chris")? I might start
by counting the numbers of commas in the entries and hoping it's always zero
or one. If it is, I would
then look at the parts after the commas and see if I could get a list of common
terminal qualifiers so I
would know they were not being treated as names (I know a man called Doctor
Ronnie Doctor, but I suspect
he is never typed in as "Ronnie Doctor, Doctor"!)
If you have reversed given/family names you might want to try generating all the
reversals and looking
for matches.
Then I might start to drill into full stops abbreviating names ("C.
Evans", "Evans, C.", "Evans, C.D.H.")
and what about "Evans, CDH"? Can you assume that text segments all in
upper case can be split, i.e.
always translate "CDH" into "C.D.H.". But then you have to
deal with "II", "III" and even "IV" I guess.
Good job you're not doing British or French monarchs: "Henry VIII"
and "Louise XVI" (I am not sure if
you can change your name to "Henry VIII" by deed poll in the UK. I do
know you can't change it to "Jesus
Christ".
One tangential thing that might help is if you have other demographics: you
might want to see if gender
(though it can change), age (will change), d.o.b. (shouldn't) might help you
disaggregate some matches.
Enough already! Challenging stuff.
Very best (all),
Chris
----- Original Message -----> From: "Gregg Powell via R-help" <r-help at r-project.org>
> To: "Ashim Kapoor" <ashimkapoor at gmail.com>
> Cc: "r-help" <R-help at r-project.org>, help at
r-project.org, "R-help-request at lists.R-project.org"
> <R-help-request at lists.r-project.org>
> Sent: Wednesday, 15 June, 2022 17:43:14
> Subject: Re: [R] Is there a package that can do Fuzzy name matching to
standardize names in a single column
> Hello Ashim and kind regards for you taking the time to answer back.
>
>
>> library(fuzzyjoin)
>> ?stringdist_left_join
>
> -this will join two tables, but what I am trying to do is just standardize
the
> similarly spelled duplicate names in just the first column of a single
table.
>
> I don't think fuzzyjoin will help me in that regard.
>
> Thanks.
> Gregg
> Arizona, USA
>
> ------- Original Message -------
> On Wednesday, June 15th, 2022 at 8:04 AM, Ashim Kapoor <ashimkapoor at
gmail.com>
> wrote:
>
>
>>
>
>>
>
>> Dear Gregg,
>>
>
>> Check this out:
>>
>
>> library(fuzzyjoin)
>> ?stringdist_left_join
>>
>
>> Best Regards,
>> Ashim
>>
>
>> On Wed, Jun 15, 2022 at 8:28 PM Gregg Powell via R-help
>> r-help at r-project.org wrote:
>>
>
>> > Have data sets where there are names, in the first column, client
names in the
>> > second, and Client start date in the third.
>> >
>
>> > There are thousands of these records with thousands of
names/clients/client
>> > start dates. The name is entered each time the person begins with
a new client
>> > such that each person has many entries in the name column. Often
the names were
>> > not entered in a consistent way. With and without middle initial,
middle name,
>> > or various abbreviations such as ",RN" at the end of the
name.
>> >
>
>> > Is there a package that can do fuzzy name matching so that the
names in name
>> > column get replaced with a "standardized" format - where
some type of machine
>> > learning can pick the most common spelling of each repeat name and
replace the
>> > different variations with the common spelling?
>> >
>
>> > I included an example below. First table includes the names with
the various
>> > spellings. Second table depicts what I hope to achieve.
>> >
>
>> > Again - this is on a large scale - there are something like 10,000
records with
>> > names that need to be standardized.
>> >
>
>> > Name
>> >
>
>> > Client
>> >
>
>> > Client Start Date
>> >
>
>> > John Good
>> >
>
>> > Client 1
>> >
>
>> > 1/1/2020
>> >
>
>> > Joe Jackson
>> >
>
>> > Client 2
>> >
>
>> > 6/1/2020
>> >
>
>> > Bob A. Barker
>> >
>
>> > Client 3
>> >
>
>> > 8/1/2020
>> >
>
>> > John B. Good
>> >
>
>> > Client 4
>> >
>
>> > 10/1/2020
>> >
>
>> > Joe J. Jackson
>> >
>
>> > Client 5
>> >
>
>> > 12/1/2020
>> >
>
>> > Bob Allen Barker
>> >
>
>> > Client 6
>> >
>
>> > 1/1/2021
>> >
>
>> > John Good
>> >
>
>> > Client 7
>> >
>
>> > 5/1/2021
>> >
>
>> > Joe Jack Jackson
>> >
>
>> > Client 8
>> >
>
>> > 8/1/2021
>> >
>
>> > Bob Barker
>> >
>
>> > Client 9
>> >
>
>> > 12/1/2021
>> >
>
>> > Name
>> >
>
>> > Client
>> >
>
>> > Client Start Date
>> >
>
>> > John Good
>> >
>
>> > Client 1
>> >
>
>> > 1/1/2020
>> >
>
>> > Joe J. Jackson
>> >
>
>> > Client 2
>> >
>
>> > 6/1/2020
>> >
>
>> > Bob A. Barker
>> >
>
>> > Client 3
>> >
>
>> > 8/1/2020
>> >
>
>> > John Good
>> >
>
>> > Client 4
>> >
>
>> > 10/1/2020
>> >
>
>> > Joe J. Jackson
>> >
>
>> > Client 5
>> >
>
>> > 12/1/2020
>> >
>
>> > Bob A. Barker
>> >
>
>> > Client 6
>> >
>
>> > 1/1/2021
>> >
>
>> > John Good
>> >
>
>> > Client 7
>> >
>
>> > 5/1/2021
>> >
>
>> > Joe J. Jackson
>> >
>
>> > Client 8
>> >
>
>> > 8/1/2021
>> >
>
>> > Bob A. Barker
>> >
>
>> > Client 9
>> >
>
>> > 12/1/2021
>> >
>
>> > THANKS!
>> >
>
>> > Gregg Powell
>> >
>
>> > Arizona, USA______________________________________________
>> > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more,
see
>> > https://stat.ethz.ch/mailman/listinfo/r-help
>> > PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
>> > and provide commented, minimal, self-contained, reproducible code.
> ______________________________________________
> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
--
Chris Evans (he/him) <chris at psyctc.org>
Visiting Professor, UDLA, Quito, Ecuador & Honorary Professor, University of
Roehampton, London, UK.
Work web site: https://www.psyctc.org/psyctc/
CORE site: https://www.coresystemtrust.org.uk/
Personal site: https://www.psyctc.org/pelerinage2016/
OMbook: https://ombook.psyctc.org/book/
Ashim Kapoor
2022-Jun-16 02:06 UTC
[R] Is there a package that can do Fuzzy name matching to standardize names in a single column
Dear Gregg, This is what I meant :-> df1Names 1 John Good 2 Joe Jackson 3 Bob A. Barker 4 John B. Good 5 Joe J. Jackson 6 Bob Allen Barker 7 John Good 8 Joe Jack Johnson 9 Bob Barker> stringdist_left_join(df1,df1,by="Names",max_dist = 3)Names.x Names.y 1 John Good John Good 2 John Good John B. Good 3 John Good John Good 4 Joe Jackson Joe Jackson 5 Joe Jackson Joe J. Jackson 6 Bob A. Barker Bob A. Barker 7 Bob A. Barker Bob Barker 8 John B. Good John Good 9 John B. Good John B. Good 10 John B. Good John Good 11 Joe J. Jackson Joe Jackson 12 Joe J. Jackson Joe J. Jackson 13 Bob Allen Barker Bob Allen Barker 14 John Good John Good 15 John Good John B. Good 16 John Good John Good 17 Joe Jack Johnson Joe Jack Johnson 18 Bob Barker Bob A. Barker 19 Bob Barker Bob Barker>You can join a table to itself while tinkering with the max_distance function.. Please notice the clusters that have formed. This has to be cleaned up. This is similar to the answer by Jan van der Laan. Best Regards, Ashim On Wed, Jun 15, 2022 at 9:13 PM Gregg Powell <g.a.powell at protonmail.com> wrote:> > > Hello Ashim and kind regards for you taking the time to answer back. > > > > library(fuzzyjoin) > > ?stringdist_left_join > > -this will join two tables, but what I am trying to do is just standardize the similarly spelled duplicate names in just the first column of a single table. > > I don't think fuzzyjoin will help me in that regard. > > Thanks. > Gregg > Arizona, USA > > ------- Original Message ------- > On Wednesday, June 15th, 2022 at 8:04 AM, Ashim Kapoor <ashimkapoor at gmail.com> wrote: > > > > > > > > > > Dear Gregg, > > > > > Check this out: > > > > > library(fuzzyjoin) > > ?stringdist_left_join > > > > > Best Regards, > > Ashim > > > > > On Wed, Jun 15, 2022 at 8:28 PM Gregg Powell via R-help > > r-help at r-project.org wrote: > > > > > > Have data sets where there are names, in the first column, client names in the second, and Client start date in the third. > > > > > > > There are thousands of these records with thousands of names/clients/client start dates. The name is entered each time the person begins with a new client such that each person has many entries in the name column. Often the names were not entered in a consistent way. With and without middle initial, middle name, or various abbreviations such as ",RN" at the end of the name. > > > > > > > Is there a package that can do fuzzy name matching so that the names in name column get replaced with a "standardized" format - where some type of machine learning can pick the most common spelling of each repeat name and replace the different variations with the common spelling? > > > > > > > I included an example below. First table includes the names with the various spellings. Second table depicts what I hope to achieve. > > > > > > > Again - this is on a large scale - there are something like 10,000 records with names that need to be standardized. > > > > > > > Name > > > > > > > Client > > > > > > > Client Start Date > > > > > > > John Good > > > > > > > Client 1 > > > > > > > 1/1/2020 > > > > > > > Joe Jackson > > > > > > > Client 2 > > > > > > > 6/1/2020 > > > > > > > Bob A. Barker > > > > > > > Client 3 > > > > > > > 8/1/2020 > > > > > > > John B. Good > > > > > > > Client 4 > > > > > > > 10/1/2020 > > > > > > > Joe J. Jackson > > > > > > > Client 5 > > > > > > > 12/1/2020 > > > > > > > Bob Allen Barker > > > > > > > Client 6 > > > > > > > 1/1/2021 > > > > > > > John Good > > > > > > > Client 7 > > > > > > > 5/1/2021 > > > > > > > Joe Jack Jackson > > > > > > > Client 8 > > > > > > > 8/1/2021 > > > > > > > Bob Barker > > > > > > > Client 9 > > > > > > > 12/1/2021 > > > > > > > Name > > > > > > > Client > > > > > > > Client Start Date > > > > > > > John Good > > > > > > > Client 1 > > > > > > > 1/1/2020 > > > > > > > Joe J. Jackson > > > > > > > Client 2 > > > > > > > 6/1/2020 > > > > > > > Bob A. Barker > > > > > > > Client 3 > > > > > > > 8/1/2020 > > > > > > > John Good > > > > > > > Client 4 > > > > > > > 10/1/2020 > > > > > > > Joe J. Jackson > > > > > > > Client 5 > > > > > > > 12/1/2020 > > > > > > > Bob A. Barker > > > > > > > Client 6 > > > > > > > 1/1/2021 > > > > > > > John Good > > > > > > > Client 7 > > > > > > > 5/1/2021 > > > > > > > Joe J. Jackson > > > > > > > Client 8 > > > > > > > 8/1/2021 > > > > > > > Bob A. Barker > > > > > > > Client 9 > > > > > > > 12/1/2021 > > > > > > > THANKS! > > > > > > > Gregg Powell > > > > > > > Arizona, USA______________________________________________ > > > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > > > https://stat.ethz.ch/mailman/listinfo/r-help > > > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > > > and provide commented, minimal, self-contained, reproducible code.