[R] matching similar character strings
Jeff Newmiller
jdnewmil at dcn.davis.CA.us
Sat Jul 6 17:25:51 CEST 2013
The intent of this list is to help you help yourself. If you spend the time to take Arun's ideas and run with therm, then you will do us and yourself a favor. We will benefit because you can then help others with similar problems, and you will benefit because you don't need to worry about failure to communicate out delays from us. Even if you cannot completely figure it out on your own yet, giving us your attempt every tone you post can demonstrate your participation and help clarify what your intent is. But asking us to just "do it" for you is not in the spirit of this list.
---------------------------------------------------------------------------
Jeff Newmiller The ..... ..... Go Live...
DCN:<jdnewmil at dcn.davis.ca.us> Basics: ##.#. ##.#. Live Go...
Live: OO#.. Dead: OO#.. Playing
Research Engineer (Solar/Batteries O.O#. #.O#. with
/Software/Embedded Controllers) .OO#. .OO#. rocks...1k
---------------------------------------------------------------------------
Sent from my phone. Please excuse my brevity.
A M Lavezzi <mario.lavezzi at unipa.it> wrote:
>Dear Arun,
>
>thank you so much! The code you suggest captures what we have in mind.
>However, what we are looking for is something a bit more general
>(sorry: I realised that maybe this was not so clear from the
>beginning).
>
>In particular:
>
>- in F1_ex the address in the "Indirizzo" field could be spelled more
>irregularly (ex: "Via De Amicis 18", "V. De Amicis 18", "Via E. De
>Amicis 18", etc.)
>
>- in F2 the classification of the portions of the street is based on
>odd and even numbers. For example, if we had number "15" in F1 it
>should be matched to row 3 and not to row 2 of F2 (I actually provided
>a wrong example with number 65: row 2 of F1_ex is currently matched to
>row 6 of F2_ex which contains even numbers. Moreover, there are no odd
>street numbers in this street higher than 37)
>
>Thank you very much once again
>
>Mario
>
>
>On Wed, Jul 3, 2013 at 6:47 AM, arun <smartpink111 at yahoo.com> wrote:
>> Dear Mario,
>> Not sure if this is what you wanted:
>> F1_ex<- read.table(text="
>> Nome.azienda;Indirizzo
>> 17;Alterego;Via Edmondo De Amicis, 18
>> 18;Alterego;Via Edmondo De Amicis, 65
>> ",sep=";",header=TRUE,stringsAsFactors=FALSE)
>>
>> F2_ex<- read.table(text="
>>
>CODICE;STRADA;AREADICIRCOLAZIONE;NUMBER1;BARRATO1;NUMBER2;BARRATO2;SECTION
>> 1;15620;VIA;DE AMICIS EDMONDO;1;;5;;1288
>> 2;15620;VIA;DE AMICIS EDMONDO;2;;34;;1261
>> 3;15620;VIA;DE AMICIS EDMONDO;7;;17;;1287
>> 4;15620;VIA;DE AMICIS EDMONDO;36;;62;;1264
>> 5;15620;VIA;DE AMICIS EDMONDO;37;;37;;1287
>> 6;15620;VIA;DE AMICIS EDMONDO;64;;84;;1262
>> ",sep=";",header=TRUE,stringsAsFactors=FALSE)
>> library(stringr)
>>
>vec1<-sapply(lapply(toupper(str_trim(gsub("[0-9,]","",F1_ex[,2]))),word,c(1,3,4,2)),paste,collapse="
>")
>> vec2<- as.numeric(gsub("\\D+","",F1_ex[,2]))
>> F1_ex[,1]<-F2_ex[sapply(vec2,function(x) which((x>F2_ex[,4] & x<
>F2_ex[,6]) & paste(F2_ex[,2],F2_ex[,3])%in%vec1)),"SECTION"]
>> F1_ex
>> # Nome.azienda Indirizzo
>> #17 1261 Via Edmondo De Amicis, 18
>> #18 1262 Via Edmondo De Amicis, 65
>> A.K.
>>
>>
>>
>>
>>
>> ----- Original Message -----
>> From: A M Lavezzi <mario.lavezzi at unipa.it>
>> To: r-help <r-help at r-project.org>
>> Cc:
>> Sent: Tuesday, July 2, 2013 10:22 AM
>> Subject: Re: [R] matching similar character strings
>>
>> Dear Arun,
>> please excuse me for this late reply, we had to stop working on this
>> temporaririly.
>>
>> Let me reproduce here two examples of rows from F1 and F2 (sorry, but
>> with dput() I am not able to produce a clear example)
>>
>>> F1_ex
>> Nome.azienda Indirizzo
>> 17 Alterego Via Edmondo De Amicis, 18
>>
>> On row 17 of F1 we have a firm named ("Nome.azienda") 'Alterego'
>whose
>> address ("indirizzo") is 'Via Edmondo de Amicis, 18'
>>
>> Below I reproduce the portion of F2 with information on the street
>> mentioned in F1_ex$Indirizzo.
>>
>>> F2_ex
>>
>> CODICE STRADA AREADICIRCOLAZIONE NUMBER1 BARRATO1
>> NUMBER2 BARRATO2 SECTION
>> 1 15620 VIA DE AMICIS EDMONDO
>1
>> 5 1288
>> 2 15620 VIA DE AMICIS EDMONDO
>2
>> 34 1261
>> 3 15620 VIA DE AMICIS EDMONDO
>7
>> 17 1287
>> 4 15620 VIA DE AMICIS EDMONDO
>36
>> 62 1264
>> 5 15620 VIA DE AMICIS EDMONDO
>37
>> 37 1287
>> 6 15620 VIA DE AMICIS EDMONDO
>64
>> 84 1262
>>
>>
>> Line 1 says that the portion of VIA DE AMICIS EDMONDO
>> ("STRADA"+"AREADICIRCOLAZIONE"), with street numbers between 1 and 5
>> belongs to SECTION 1288 (these are census sections). ("BARRATO1" and
>> "BARRATO2" refer to the letter in street numbers such as 12/A, 28/D,
>> etc. In the present example they are empty)
>>
>> Line 2 says that the portion of VIA DE AMICIS EDMONDO, with street
>> numbers between 2 and 34 belongs to SECTION 1261,
>>
>> etc.
>>
>> Our problem is to assign SECTION 1261 to 'Alterego', exploting the
>> information on its address. The problem is that the syntax of the
>> street address in F1 is different from the syntax in F2.
>>
>> Hope I have clarified the issue
>>
>> thanks a lot
>> Mario
>>
>>
>>
>>
>>
>>
>>
>>
>> On Fri, Jun 21, 2013 at 5:25 PM, arun <smartpink111 at yahoo.com> wrote:
>>> Dear Mario,
>>> I didn't find any difference between 1st and 2nd row of F2, except
>for the last three columns. Question is that why should F1 1st row
>should be merged to 2nd row of F2 instead of 1st row of F2. In your
>previous example, you mentioned about A1, A2, ... and B1, B2, etc.
>Here, it is not provided. As I mentioned before, it is better to
>provide the output of ?dput() from a subset of dataset.
>>> dput(head(F1,20))
>>>
>>> dput(head(F2,20))
>>>
>>> #so that there would be atleast some matching pairs within the
>example dataset. Also, please post it to r-help as I will be able to
>check only after a couple of hours
>>> Tx.
>>> Arun
>>>
>>>
>>>
>>> ----- Original Message -----
>>> From: Mario Lavezzi <mario.lavezzi at unipa.it>
>>> To: arun <smartpink111 at yahoo.com>
>>> Cc:
>>> Sent: Friday, June 21, 2013 11:08 AM
>>> Subject: Re: [R] matching similar character strings
>>>
>>> dear Arun
>>> thank you very much. Let me explain the problem:
>>>
>>> Imagine that a portion of the row in F1 is:
>>>
>>> ----------------------------
>>> F1
>>>
>>> 1) Street | J.F. Kennedy | 30
>>> ----------------------------
>>>
>>> it means that our unit of interest (a firm) has address: J.F.
>Kennedy Street, 30
>>>
>>>
>>> The F2 database contains the list of all the streets of the city,
>with additional variables characterizing that street (Census data). The
>database
>>> contains sometimes street divided in some parts, according to the
>street number. For example:
>>>
>>>
>>> Example of three rows of F2 concerning Kennedy street and Kennedy
>Road:
>>>
>>> F2
>>>
>>> 1) Street | Kennedy John Fitzgerald | 1 | 20 | A12
>>> 2) Street | Kennedy John Fitzgerald | 20 | 50 | A15
>>> 3) Road | Kennedy John | 1 | 50 | A23
>>>
>>>
>>> We'd like to have an algorithm able to understand that,
>notwithstanding the name is slightly different, element A15 should be
>added to row 1) of F1,
>>> producing an output such as:
>>>
>>> 1) Street | J.F. Kennedy | 30 | A15
>>>
>>>
>>> hope this clarifies the issue.
>>>
>>> thanks a lot! Mario
>>>
>>>
>>>
>>> Il 21/06/2013 15:29, arun ha scritto:
>>>> HI,
>>>> Could you dput() your example datasets and also your expected
>result? The Census section is not clear.
>>>> A.K.
>>>>
>>>>
>>>>
>>>>
>>>> ----- Original Message -----
>>>> From: A M Lavezzi <mario.lavezzi at unipa.it>
>>>> To: r-help <r-help at r-project.org>
>>>> Cc:
>>>> Sent: Friday, June 21, 2013 5:56 AM
>>>> Subject: [R] matching similar character strings
>>>>
>>>> Hello everybody
>>>>
>>>> I have this problem: I need to match an addresses database F1 with
>the
>>>> information contained in a toponymic database F2.
>>>>
>>>> The format of F1 is given by three columns and 800 rows, with the
>>>> columns being:
>>>>
>>>> A1. Street/Road/Avenue
>>>> A2. Name
>>>> A3. Number
>>>>
>>>> Consider for instance Avenue J. Kennedy , 3011. In F1 this is:
>>>>
>>>> A1. Avenue
>>>> A2. J. Kennedy
>>>> A3. 3011
>>>>
>>>> The format of F2 file is instead given by 20000 rows and five
>columns:
>>>>
>>>> B1. Street/Road/Avenue
>>>> B2. Name
>>>> B3. Starting Street Number
>>>> B4. Ending Street Number
>>>> B5. Census section
>>>>
>>>> So my problem is attributing the B5 Census section to every
>>>> observation of F1 if: A1=B1, A2=B2, and A3 is comprised between B3
>and
>>>> B4.
>>>>
>>>> The problem is that while the information in A2 is irregularly
>>>> recorded, B2 has a given format that is Family name (space) Given
>>>> name.
>>>>
>>>> So I could have that while in B2 the information is:
>>>>
>>>> Kennedy John
>>>>
>>>> In A2 it could be:
>>>>
>>>> John Kennedy
>>>> JF Kennedy
>>>> J. Kennedy
>>>>
>>>> and so on.
>>>>
>>>> Thanks,
>>>>
>>>> Mario
>>>>
>>>
>>> --
>>> PLEASE NOTICE NEW EMAIL ADDRESS AND HOME PAGE URL
>>>
>>> Andrea Mario Lavezzi
>>> Dipartimento di Studi su Politica, Diritto e Società
>>> Università di Palermo
>>> Piazza Bologni 8
>>> 90134 Palermo, Italy
>>> tel. ++39 091 23892208
>>> fax ++39 091 6111268
>>> skype: lavezzimario
>>> email: mario.lavezzi (at) unipa.it
>>> web: http://www.unipa.it/~mario.lavezzi
>>
>>
>>
>> --
>> Andrea Mario Lavezzi
>> Dipartimento di Scienze Giuridiche, della Società e dello Sport
>> Sezione Diritto e Società
>> Università di Palermo
>> Piazza Bologni 8
>> 90134 Palermo, Italy
>> tel. ++39 091 23892208
>> fax ++39 091 6111268
>> skype: lavezzimario
>> email: mario.lavezzi (at) unipa.it
>> web: http://www.unipa.it/~mario.lavezzi
>>
>> ______________________________________________
>> R-help at r-project.org mailing list
>> 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.
More information about the R-help
mailing list