0

I have some addresses.csv in different international formats

Example Street 1
Teststraße 2
Teststr. 1-5
Baker Street 221b
221B Baker Street
19th Ave 3B
3B 2nd Ave
1-3 2nd Mount x Ave
105 Lock St # 219
Test Street, 1
BookAve, 54, Extra Text 123#

For example we in Germany write Teststraße 2 and in the USA 2 Test Street

Is there a way to seperate/extract all street names and street numbers? output-names.csv

Example Street
Teststraße
Teststr.
Baker Street
Baker Street
19th Ave
2nd Ave
2nd Mount Good Ave
Lock St # 219
Test Street
BookAve

output-numbers.csv

1
2
1-5
221b
221B
3B
3B
1-3
105
1
54

output-extra_text.csv











Extra Text 123#

I am using macOS 13.. the shell is zsh 5.8.1 or bash-3.2


my thoughts that i had: you could sort the addresses first like this:

x=The-adress-line;
if [ x = "begins with a letter"];
    then 
    if [ x = "begins with a letter + number + SPACE"];
        then
        echo 'something like "1A Street"';
        # NUMBER = '1A' / NAME = 'Street'
    else
        echo 'It begins with the STREET-NAME';
    fi;
elif [ x = "begins with a number"];
    then
    echo 'maybe STREET-NAME like "19th Ave 19B" or STREET-NUMBER like "19B Street"';
    # NUMBER = '19B' / NAME = '19th Ave' or 'Street'
    if [ x = "begins with a number + SPACE"];
        then
        echo 'It begins with the STREET-NUMBER like "1 Street"';
        # NUMBER = '1' / NAME = 'Street'
    elif [ x = "is (number)(text)(space)(text)(number(maybe-text))"];
        then
            echo 'For example 19th Street 19B -> The last number+text is the number (19B)'
            # NUMBER = '19B' / NAME = '19th Street'
    elif [ x = "is (number(maybe-text))(space)(number)(text)(space)(text)"];
        then
        echo 'For example 19B 19th Street -> The first number+text is the number (19B)'
            # NUMBER = '19B' / NAME = '19th Street'
    else
        echo 'INVALID';
else
    echo 'INVALID';
fi;
12
  • What about "42nd street"? I mean, pretty much anything, including numbers, can be street names.
    – terdon
    Commented Mar 2, 2023 at 16:51
  • Exactly.. "42nd street 3" (DE) or "3 42nd street" (US) means -> number="3" and name="42nd street"
    – R 9000
    Commented Mar 2, 2023 at 17:01
  • 3
    Which is why I don't think it is possible to automate this short of using an actual AI trained on real street names :/
    – terdon
    Commented Mar 2, 2023 at 17:08
  • I think it is possible.. for your example see "my thoughts" what I just added
    – R 9000
    Commented Mar 2, 2023 at 17:22
  • 2
    That pseudo-code is shell-like. You would not do something like this in shell as it'd be hard to get the syntax right and take forever to run. See why-is-using-a-shell-loop-to-process-text-considered-bad-practice. You should use awk or some other general-purpose text-processing tool instead.
    – Ed Morton
    Commented Mar 2, 2023 at 19:33

1 Answer 1

2

IMHO all you can do is a best-effort employing a series of regexps to match the addresses you know about, e.g. using GNU awk for the 3rd arg to match() and \s shorthand for [[:space:]] and 3 of the possible regexps defined:

$ cat tst.awk
BEGIN { OFS="\",\"" }
{
    name = number = type = ""
    gsub(/"/,"\"\"")
}
match($0,/^([^0-9]+)([0-9]+(-[0-9]+)?[[:alpha:]]?)$/,a) {
    # Example Street 1
    # Teststraße 2
    # Teststr. 1-5
    # Baker Street 221b
    # Test Street, 1
    type   = 1
    name   = a[1]
    number = a[2]
}
!type && match($0,/^([0-9]+[[:alpha:]])\s+([^0-9]+)$/,a) {
    # 221B Baker Street
    type   = 2
    name   = a[2]
    number = a[1]
}
!type && match($0,/^([0-9]+[[:alpha:]]{2}.*)\s+([0-9]+[[:alpha:]]?)$/,a) {
    # 19th Ave 3B
    type   = 3
    name   = a[1]
    number = a[2]
}
{
    gsub(/^\s+|\s+$/,"",name)
    gsub(/^\s+|\s+$/,"",number)
    if ( !doneHdr++ ) {
        print "\"" "type", "name", "number", "$0" "\""
    }
    print "\"" type, name, number, $0 "\""
}

$ awk -f tst.awk file
"type","name","number","$0"
"1","Example Street","1","Example Street 1"
"1","Teststraße","2","Teststraße 2"
"1","Teststr.","1-5","Teststr. 1-5"
"1","Baker Street","221b","Baker Street 221b"
"2","Baker Street","221B","221B Baker Street"
"3","19th Ave","3B","19th Ave 3B"
"","","","3B 2nd Ave"
"","","","1-3 2nd Mount x Ave"
"","","","105 Lock St # 219"
"1","Test Street,","1","Test Street, 1"
"","","","BookAve, 54, Extra Text 123#"

You'd add the other regexps to match the formats of address you know about in the appropriate order such that if an address might match 2 or more regexps you have the more restrictive regexp(s) first. You may actually want to modify the above to print a warning if an address matches 2 or more regexps as you may then want to tweak or re-order or consolidate them.

If you reach the print line with type still empty, that's the "invalid" case and then you could write/add a new regexp to match those if appropriate.

I do expect you'll come across cases where you simply can't write code to distinguish one address format from another but hopefully this best-effort approach will be adequate for your needs. If you have city/state/county you could always curl an address using google maps to see if it's real or not as a last-ditch effort for addresses you can't identify (but that'd take forever if you tried to do ONLY that for all your addresses).

Produce output however you like wherever you like once the address recognition algorithm is working, I'm just dumping CSV above for ease of developing/testing.

9
  • 1
    You're welcome. Given the above it's just a case of iterating writing additional regexps and/or refining existing regexps as you come across additional address formats. I do expect you'll come across cases where you simply can't write code to distinguish one address format from another but hopefully this best-effort approach will be adequate for your needs. If you have city/state/county you could always curl an address using google maps to see if it's real or not as a last-ditch effort for addresses you can't identify (but thatd take forever if you tried to do ONLY that for all your addresses)
    – Ed Morton
    Commented Mar 2, 2023 at 19:54
  • I think you’ll eventually hit an address that matches the regex but in interpreted wrong. The address 4593 NC 39 has the house number first but your 3rd regex would make that the street number.
    – doneal24
    Commented Mar 3, 2023 at 0:13
  • @doneal24 right, I said as much in my comment directly above yours - I do expect you'll come across cases where you simply can't write code to distinguish one address format from another. I copied that statement into my answer now so it's harder to miss.
    – Ed Morton
    Commented Mar 3, 2023 at 0:15
  • syntax error at source line 6 source file tst.awk context is >>> match($0,/^([^0-9]+)([0-9]+(-[0-9]+)?)[[:alpha:]]?$/, <<< awk: bailing out at source line 6 source file tst.awk So I can't know if this is working
    – R 9000
    Commented Mar 3, 2023 at 0:41
  • As I said in my answer, it requires GNU awk for the 3rd arg to match() and \s shorthand for [[:space:]]. You aren't using GNU awk, you should try really, really hard to get it as it has a ton of useful extensions. If you can't get it for some reason it's possible to do something similar with a POSIX awk but it requires a bit more code and a bit more complicated code (which is why GNU awk added that parameter to match()).
    – Ed Morton
    Commented Mar 3, 2023 at 0:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .