matching similar character strings

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