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
______________________________________________ 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.
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