Regular Expressions

Simple data cleaning

For the next few examples, I'll use data that I sloppily copied from this Wikipedia listing of countries by forest area.

Delete empty lines

I think everyone has had to do this at least once: you have a text file, and there's a bunch of annoying blank lines that shouldn't be there:

   1   Russia   7,762,602   2011   45.40%[2]
     2   Brazil   4,776,980[3]   2005   56.10%
     3   Canada   3,101,340[4]   2005   31.06%


   4   United States   3,030,890[5]   2007   30.84%
   5   China   1,821,000   2011   18.21%[6]


     6   European Union   1,600,000   2011   35.00%[7]
   7   Australia   1,470,832   2011   19.00%[8]



   8   Democratic Republic of the Congo   1,219,326   2011   52.00%[9]
     9   Argentina   945,336   2011   34.00%[10]
   10   Indonesia   884,950[11]   2011   46.46%
   11   Venezuela   747,881   2011   82.00%[12]

   12   India   778,424   2011   23.68%[13]   

Here's a quick regex to clean that up:

^\s*\n

^

The caret stands for the start of the line. It indicates that we are interested in a pattern from the very beginning of a given line. This is also referred to as an anchor.

\s*

The \s stands for any whitespace character. The asterisks * indicates that we are looking for 0 or more of these whitespaces. So the regex will work if there are no whitespaces or many whitespaces from the beginning of the line.

\n

This is a special character for a newline

The regex, in English, will match any line in which there is either nothing or just spaces/tabs from the beginning of the line to the line break (i.e. the end of the line). If you Replace it with nothing, all empty lines will be deleted:

   1   Russia   7,762,602   2011   45.40%[2]
     2   Brazil   4,776,980[3]   2005   56.10%
     3   Canada   3,101,340[4]   2005   31.06%
   4   United States   3,030,890[5]   2007   30.84%
   5   China   1,821,000   2011   18.21%[6]
     6   European Union   1,600,000   2011   35.00%[7]
   7   Australia   1,470,832   2011   19.00%[8]
   8   Democratic Republic of the Congo   1,219,326   2011   52.00%[9]
     9   Argentina   945,336   2011   34.00%[10]
   10   Indonesia   884,950[11]   2011   46.46%
   11   Venezuela   747,881   2011   82.00%[12]
   12   India   778,424   2011   23.68%[13]

Remove spaces from the beginning of the line

Let's make it so there isn't any leading whitespace at the start of each line. We only need the same components from the previous empty-line pattern:

^ +

^

Again, this is the beginning-of-the-line anchor.

 

The empty space is just a literal empty space. We could've also used \s

+

The plus +, known as the greedy operator, looks for one or more of the previous token, which in our example, is a whitespace.

This regex will look for one or more space characters from the beginning of the line. If you Replace that with nothing, then all leading whitespace will be deleted from the beginning of each line:

1   Russia   7,762,602   2011   45.40%[2]
2   Brazil   4,776,980[3]   2005   56.10%
3   Canada   3,101,340[4]   2005   31.06%
4   United States   3,030,890[5]   2007   30.84%
5   China   1,821,000   2011   18.21%[6]
6   European Union   1,600,000   2011   35.00%[7]
7   Australia   1,470,832   2011   19.00%[8]
8   Democratic Republic of the Congo   1,219,326   2011   52.00%[9]
9   Argentina   945,336   2011   34.00%[10]
10   Indonesia   884,950[11]   2011   46.46%
11   Venezuela   747,881   2011   82.00%[12]
12   India   778,424   2011   23.68%[13]

Remove bracketed numbers

When directly copying from Wikipedia, I also inadvertently copied the footnotes, which consists of numbers between brackets. These are only useful when looking at the actual webpage, so let's delete them. You can match them with this regex:

\[\d+\]

\[

Square brackets are a special character in regexes. But we don't want that special meaning. We just want a literal square bracket, so we escape it using a backslash \

\d+

The \d represents any numerical digit. Thus, when followed by the greedy operator +, the \d+ matches one or more numerical digits.

\]

This just matches the literal closing square bracket

Once again, putting that pattern as the thing to Find and entering nothing into the Replace field will result in all bracketed numbers being deleted from the text:

1   Russia   7,762,602   2011   45.40%
2   Brazil   4,776,980   2005   56.10%
3   Canada   3,101,340   2005   31.06%
4   United States   3,030,890   2007   30.84%
5   China   1,821,000   2011   18.21%
6   European Union   1,600,000   2011   35.00%
7   Australia   1,470,832   2011   19.00%
8   Democratic Republic of the Congo   1,219,326   2011   52.00%
9   Argentina   945,336   2011   34.00%
10   Indonesia   884,950   2011   46.46%
11   Venezuela   747,881   2011   82.00%
12   India   778,424   2011   23.68%

Now we're all cleaned up. If you paste the above result into Excel, Excel should recognize the tab characters and delimit accordingly:

^\d+\s+

[nothing]

Russia   7,762,602   2011   45.40%
Brazil   4,776,980   2005   56.10%
Canada   3,101,340   2005   31.06%
United States   3,030,890   2007   30.84%
China   1,821,000   2011   18.21%
European Union   1,600,000   2011   35.00%
Australia   1,470,832   2011   19.00%
Democratic Republic of the Congo   1,219,326   2011   52.00%
Argentina   945,336   2011   34.00%
Indonesia   884,950   2011   46.46%
Venezuela   747,881   2011   82.00%
India   778,424   2011   23.68%      

If you're an Excel expert, you might be shaking your head at this point because you can think of how you could've accomplished the same tasks in Excel. Maybe so, but those were just the very basics of regular expressions.

At one point, I think I could've done this cleaning in Excel. The advantage of regexes is that they are the same in every language and context (with a few variations in flavors) and will remain so. The Excel version that exists today, and its particular labyrinth of options, is quite different from the one I learned a few years ago.

Replacing misspelled words

The following exercises consists of how to use regex operators to match variations in spelling.

Copy and paste the following text into your editor:

Joe Smith takes care of they're property. The property of theirs includes their house, they're pool, and there dogs, of which Fido is their favorite.

There's a problem with the copy here. The writer used the wrong pronoun and meant "his" as opposed to "their". Even worse, "their" was misspelled as "they're" and its other homophones.

Open up your find-and-replace (Edit » Find/Replace or Ctrl/Cmd-F) and in the Find field, type in:

their|they're|there

Note that those are pipe-characters |, not lower-case L's. In the Replace field, enter:

his

And then hit the Replace All button. The text-block should look like this:

Joe Smith takes care of his property. The property of hiss includes his house, his pool, and his dogs, of which Fido is his favorite.

In one operation, we replaced all the variations of "their" with "his". The regular expression we used – their|they're|there – includes all those variations and the use of the pipe character |

However, our regex didn't quite finish the job: it replaced theirs with hiss.

The their in our regex only matches their – and thus, not the final s – in theirs. We can take care of that with a small alteration to our original regex, using the question mark ? operator .

Undo the previous changes to the text and run this regex:

theirs?|they're|there

It may seem that all this regex does is save us from doing find-and-replace three different times. We still had to type each variation in full. This is true. But we have yet to begin using the full power of regexes.

In the following expression, I take advantage of the fact that all three variations of their begin with the:

the(irs?|y're|re)

As you might guess, the parentheses change the order of operations (they also do something much more important than that, which we'll cover later). The alternation using the pipe character is solely among the latter sets of characters in the variations of "their", e.g. irs?, y're, and re (note that apostrophes are not special regex characters).

The Regexp class

Regex patterns look like strings, but they have their own notation. Instead of quotation marks, regular expressions are bounded by the forward-slash character /:

my_regex = /cat/
puts my_regex.class   #   Regexp

puts my_regex == "cat"   #   false
      

The gsub method for strings

Substituting characters in strings is one of the most common uses for regexes in code. To do this, strings have the sub method, which is short for substitution.

The sub method takes in two arguments:

1. either a String or a Regexp pattern
2. a String to substitute in for the first time that the pattern (in the first argument) matches the invoking string.

puts "My cat eats catfood".sub("cat", "dog")
# => My dog eats catfood
         

If you passed in /cat/, you'd get the same result as above, as the letters cat match their literal values:

puts "My cat eats catfood".sub(/cat/, "dog")
# => My dog eats catfood
         

In most scenarios, we'll want to replace all occurrences of a pattern, not just the first one. So we use the gsub method, which is short for global substitution:

puts "My cat eats catfood".gsub("cat", "dog")
# => My dog eats dogfood
         

Once again, passing in the regexp /cat/ would net you the same result.

Let's try a more complicated string:

str="My cat goes catatonic when I concatenate his food with Muscat grapes"
puts str.gsub("cat", "dog")
# => My dog goes dogatonic when I condogenate his food with Musdog grapes

      

Here, matching just the literal string "cat" does us no good. We have to use a regex if we want to only replace "cat" as a standalone word.

Instead of using just "cat" as the matching pattern, try this regex pattern for word-boundaries: \b

The use of \b means that we are looking for a character that is not an alphabetical letter or an underscore. So to match all instances of just "cat" by itself:

str="My cat gets catatonic when I attempt to concatenate his food with Muscat grapes"
puts str.gsub(/\bcat\b/, 'dog')
=> My dog gets catatonic when I attempt to concatenate his food with Muscat grapes
      

String.match

The String methods match and scan are used when you not only want to target a pattern, but do something (besides substitution) with the matched string(s).

Here are two Department of Defense contracts announced in 1994. Let's assume they are among a large set of such contracts that have been read into a Ruby array:

contracts_arr =    
   ["Hughes Missile Systems Company, Tucson, Arizona, is being awarded a $7,311,983 modification to a firm fixed price contract for the FY94 TOW missile production buy, total 368 TOW 2Bs. Work will be performed in Tucson, Arizona, and is expected to be completed by April 30, 1996. Of the total contract funds, $7,311,983 will expire at the end of the current fiscal year. This is a sole source contract initiated on January 14, 1991. The contracting activity is the U.S. Army Missile Command, Redstone Arsenal, Alabama (DAAH01-92-C-0260).",
   "Conventional Munitions Systems, Incorporated, Tampa, Florida, is being awarded a $6,952,821 modification to a firm fixed price contract for Dragon Safety Circuits Installation and retrofit of Dragon I Missiles with Dragon II Warheads. Work will be performed in Woodberry, Arkansas (90%), and Titusville, Florida (10%), and is expected to be completed by May 31, 1996. Contract funds will not expire at the end of the current fiscal year. This is a sole source contract initiated on May 2, 1994. The contracting activity is the U.S. Army Missile Command, Redstone Arsenal, Alabama (DAAH01-94-C-S076)."]

Suppose from such a list of entries, we are interested in quickly finding each contract's dollar amounts. Using enumeration and match:

contracts_arr.each do |contract|
   mtch = contract.match(/\$[\d,]+/)
   puts mtch
end

#=> $7,311,983
#=> $6,952,821

So far, we've been familiarized with every component of that regex. The \$ matches a literal dollar sign. The [\d,] matches any character that is either a numerical digit or a comma.

The plus sign + may be new, or at least worth reviewing. This is the greedy operator and it will match the pattern that precedes it one or more times. Therefore:

[\d,]+

...will match any of the following strings:

12,000
42
912,345,200
,,342134,,3,4,5   

The greedy operator can obviously get you in trouble by being, well, too greedy. In the above example, how does it know when to stop?

Answer: When it comes across a character that is neither a digit or a comma. So, since each dollar amount in our example is surrounded by spaces, we're safe (if sloppy) here.

contracts_arr.each do |contract|
   mtch = contract.match(/\w+ \d{1,2}, \d{4}/)
   puts mtch
end
#=> April 30, 1996
#=> May 31, 1996

The MatchData object

The object returned by match is a MatchData object, but for our purposes, it's very similar to a standard array. The first element contains the match When the regex includes capturing groups, the first element in the MatchData array-like object refers to the first capturing group, and so forth. If no matches are found in the string, then match returns nil.

contracts_arr.each do |contract|
   mtch = contract.match(/(\w+) (\d{1,2}), (\d{4})/)
   puts "Month: #{mtch[1]}"
   puts "Day: #{mtch[2]}"
   puts "Year: #{mtch[3]}"
   puts ""
end

#=> Month: April
#=> Day: 30
#=> Year: 1996

#=> Month: May
#=> Day: 31
#=> Year: 1996

The contracts in our sample have several dates – for when the contract was awarded and for when the work is expected to be completed. We saw that match only returns the first instance it finds. Let's rewrite it so that it captures a few of the words that precede the date so that we can know the context. And to show how MatchData is like an array, the following code also prints the textual content and date with a delimiter:

contracts_arr.each do |contract|
   mtch = contract.match(/\b(.{10,20}) (\w+ \d{1,2}, \d{4})/)
   puts "#{mtch[1]} \t\t #{mtch[2]}"
end

#=> to be completed by        April 30, 1996
#=> to be completed by        May 31, 1996

The all-being dot

Of special note here is the dot character in the regex pattern. To find the text that precedes each date, we can't simply use \d and \w, as they would fail to match punctuation and whitespaces. For all we know, the group of characters preceding each date could have exclamation marks.

The dot character is the regex character to rule them all. It matches anything, with the exception of start and end of the line markers, and new lines (unless used with a modifier).

The combination of the be-all dot with some kind of repetition operator, such as the greedy plus sign, won't rip apart the time-space continuum. But it can lead to unwanted results. This is why in the above example, I limit it with the {10,20} so that the dot only looks for the first 10 to 10 preceding characters, whatever they may be (it is only luck that in this example, they happen to be " to be completed by")

The greedy dot

What if we wanted everything before the date, all the way to the beginning of the sentence? Then it's useful to combine the dot and the +:

.+(\w+ \d{1,2}, \d{4})

If you're wondering, "But won't those two capture everything?" Well, no, not necessarily. In the above expression, the .+, being the greedy bastard it is, has the tendency to gobble up as many characters as it can. However, it still must honor the requirement that the pattern ends with: \w+ \d{1,2}, \d{4}

So let's look at the first contract again. I've highlighted the two date patterns:

Hughes Missile Systems Company, Tucson, Arizona, is being awarded a $7,311,983 modification to a firm fixed price contract for the FY94 TOW missile production buy, total 368 TOW 2Bs. Work will be performed in Tucson, Arizona, and is expected to be completed by April 30, 1996. Of the total contract funds, $7,311,983 will expire at the end of the current fiscal year. This is a sole source contract initiated on January 14, 1991. The contracting activity is the U.S. Army Missile Command, Redstone Arsenal, Alabama (DAAH01-92-C-0260).    

Remember how match captured only the first date match? If a greedy expression seeks to have everything it can, while still fulfilling the mininum requirements of the pattern, what is the most that it can grab here?

If you thought: "all the way up to January 14, 1991," you have the right idea. But you underestimated the greediness of the greedy dot. In fact, this is what the match will be:

y 14, 1991

Think about it for a second if the result confuses you. What is the bare minimum of characters that would satisfy the date pattern?

(\w+ \d{1,2}, \d{4})

The \w+ expression merely requires one or more alphanumeric characters, and then a single space. January would fit as a match.

However, so does the final y in January. That greedy dot bastard, of course, is going to prefer the latter situation. It will suck up all the letters of January up until the final y.

The lazy, greedy dot

Let's continue to personify our regular expressions. In the previous section, we dealt with a regex that consumed too much of the target string because of its greedy and indiscriminate nature.

The question mark operator adds the characteristic of laziness to a greedy pattern. We've already used the ? to designate that a pattern is optional to match, but it takes a different meaning when it comes after a +

first_contract = contracts_arr[0]
puts first_contract.match(/(.+)(\w+ \d{1,2}, \d{4})/)[2]
#=> y 14, 1991
puts first_contract.match(/(.+?)(\w+ \d{1,2}, \d{4})/)[1]
#=> April 30, 1996

So there are two differences in the resulting match from the lazy and greedy expression .+?: 1) it captured the whole date pattern, and 2) it captured the first date, April 30, 1996, rather than the latter.

What do you expect from something "lazy"? The regex wants to match a string that contains: an unspecified number of any characters and ends with something matching \w+ \d{1,2}, \d{4}. The greedy version wants every last character it can grab while meeting the latter condition, so of course it will take the latter date.

The lazy version, however, wants to grab the smallest part of the string that satisfies the pattern. So it stop at the the first of the dates. And, being lazy, it sees that it has fulfilled the first part of the overall expression (one or more of any character) it leaves the full April to the date-capturing group.

The uber-regex-reference site, regular-expressions.info, has a more thorough explanation.

contracts_arr.each do |contract|
   mtch = contract.match(/(\$[\d,]+).+?(\w+ \d{1,2}, \d{4})/)
   puts "#{mtch[1]} \t\t #{mtch[2]}"
end        


#=> $7,311,983        April 30, 1996
#=> $6,952,821        May 31, 1996

String.scan

If you want to be able to access all instances of a regex match, use scan instead of match. It returns an array of all matches in the target string:

locations = 'Alabama, AL, Alaska, AK, Arizona, AZ, Arkansas, AR, California, CA, Colorado, CO, Connecticut, CT, Delaware, DE, Florida, FL, Georgia, GA, South Dakota, SD'

puts locations.scan(/[A-Z]{2}/).join(', ')
#=> AL, AK, AZ, AR, CA, CO, CT, DE, FL, GA, SD

If your regex uses a capturing group, then scan will return an array of arrays:

locations = 'Alabama, AL, Alaska, AK, Arizona, AZ, Arkansas, AR, California, CA, Colorado, CO, Connecticut, CT, Delaware, DE, Florida, FL, Georgia, GA, South Dakota, SD'

locations.scan(/(\w[A-Za-z ]+), ([A-Z]{2})/).map{|loc| {:state=>loc[1], :city=>loc[0]} }
#=> 
[{:state=>"AL", :city=>"Alabama"}, {:state=>"AK", :city=>"Alaska"}, {:state=>"AZ", :city=>"Arizona"}, {:state=>"AR", :city=>"Arkansas"}, {:state=>"CA", :city=>"California"}, {:state=>"CO", :city=>"Colorado"}, {:state=>"CT", :city=>"Connecticut"}, {:state=>"DE", :city=>"Delaware"}, {:state=>"FL", :city=>"Florida"}, {:state=>"GA", :city=>"Georgia"}, {:state=>"SD", :city=>"South Dakota"}] 

Exercise: Capture every proper noun in each entry

This exercise is open to interpretation and different answers. But to the best of your ability, write a script that iterates through each sample contract summary and prints out a list of the proper nouns that were in the summary.

We've already seen how to iterate and do matches by this point. The more interesting thinking involves what constitutes a proper noun, based on the sample text.

Solution

From the (tiny) sample text, I decided that the following characteristics would catch most of the proper nouns:

• The first character is a capitalized letter, preceded by 0 or more whitespaces and/or a non-alphanumeric character
• The next set of characters begins with an optional space and then consists of alphanumeric characters.
• A proper noun consists of one or more repetitions of the above pattern, for example: "New York" or "FY95 TOW"

The regex pattern I settled on was this:

(?:\s*\b([A-Z]+(?:\s*\w*)?)\b)+

It's not as complicated as it looks. But it doesn't work as well as I want it to. I will revisit this in a later update to this chapter.

For now, you can play around with this regex in a nifty interactive regex builder at gskinner.com. Click on this link to view my proposed pattern and what it would match in the sample text:

The interactive regex builder at gskinner.com

Collapse whitespace and convert line breaks into spaces

We learned early on that whitespace isn't particularly significant in Ruby. This is the same case for HTML, in which consecutive whitespace or linebreaks show up as just a single whitespace character.

However, when you scrape from webpages, you will be capturing all that whitespace. Sometimes you want to keep it. But if you don't want all those redundant characters, you can use a regex to collapse consecutive whitespace characters into one.

regex = /\s+/
puts "   hello   world
 the space   

is nice here!    ".gsub(regex, ' ')   
# =>  hello world the space is nice here!
   

Here's an explanation of those symbols:

\s

The backslash character with s is a pattern that captures all whitespace characters, including regular spaces, tab characters, and line breaks

+

The plus sign looks for a pattern that has one or more occurrences of the character preceding the +

So the script above looks for all occurrences within a string in which there is one or more whitespace characters and substitutes those with a single space. Thus, line break and tab characters are also reduced to a generic single space.

It's hard to see in the code example, but there is a single space surrounding the first and final non-whitespace characters. In many situations, you want to get rid of those. The string's strip method does that for us.

However, if you wanted to implement with a regex, you would use this pattern:

regex = /^\s+|\s+$/
puts "  hello world ".gsub(regex, '')
# => hello world
      
   

Currency to number

When pulling in money-related data (payrolls, campaign finance expenditures, price listings, etc.) from a document or webpage, the dollar values will typically be in human readable format, e.g. $3,023,405.12 instead of 3023405.12

As we know, mixing up numbers with non-numeric symbols typically requires putting them into the String object. And programming languages in general will not let you add strings like numbers (e.g. "$12.00" + "$0.64" # = $12.00$0.64).

Here's a regex that will convert U.S. dollar amounts to decimal numbers:

regex = /[^\d\-.]/
puts "$156,200.98".gsub(regex, '').to_f   # => 156200.98 

Here's an explanation of those symbols:

[]

Square brackets allow you to specify a set of characters to match. Read more at regular-expressions.info.

^

The caret, when used inside [], acts as a negator. It signifies that we want to match the set of characters NOT defined within the brackets

\d

The backslash character with d represents all numerical digits. It's shorthand for [0-9], which can be read as: the set of characters from 0 through 9

\-

As we saw in the previous item, the hyphen is used to denote a range of characters in a bracket set. So, [f-z] would match all lowercase letters from f through z. But since I just want to match a plain hyphen (to capture negative values), I use the backslash to signify that I'm not using the hyphen as a special regex range character./dt>

.

The dot character is very powerful in regexes. It represents all characters. So, a pattern of .. would match everything from ab to ?* to two tab characters.

But, inside the bracket set notation, a dot is just a dot. Which is exactly what we want since we want to match decimal numbers.

So when viewed all together, you should see that we are looking to match all types of characters except for digits, decimal points, and the minus sign. Hence, the use of the square brackets [] and negation character ^.

This may seem counterintuitive until you see what method I use next: gsub. I'm matching all the non-numerical characters with the intention of replacing them with nothing, e.g. the empty string ''

And finally, I invoke the resulting string's to_f method to turn it into a proper Float. What happens if you called to_f on the string without first cutting out the non-numerical characters? Try it out:

puts "$156,200.98".to_f   # => 0.0
puts "156,200.98".to_f # => 156.0
      

Essentially, the to_f method stops trying to convert characters to numbers upon the first non-numerical character. In the first line, it gives up at the $ sign, thus returning a value of 0.0.

Keep in mind that this pattern and method works well if all the currency strings are valid and follow the U.S. standard (i.e. empty spaces don't have significance). In many real-life situations, the challenge is to deal with input in which you have to detect invalid, human-entered values.

For example, a database may contain something like "$23,10". The regex I just demonstrated would convert it to 2310.0. But what if whoever made the typo meant the value to be "$23.10". Or "$23,100". When designing programs, you must consider the source of your information and plan for the mistakes he/she/it may have made, rather than just writing code that makes blithe assumptions.

Combining multi-line records

You asked an agency for some records. They give you a PDF. You convert it to text and what should be a nicely-ordered table like this:

Ends up converting to a mess like this:

Name
Address
Street
Payment
Smith, John
200
Broadway
$1,000
Jo, Sara
1
Main St.
$42
Eisenhower, Dwight David
90
Dover Ave.
$140,591,495.00

Assuming that there is just one line break per field, we can use a regex that translates line breaks into commas and also puts double-quotes around each field so that you have a proper comma-delimited file.

We only do this for every four line breaks, which will preserve the fifth line break that separates each record as it should be:

regex = /(.+)\n(.+)\n(.+)\n(.+)/
puts str.gsub(regex, '"\1","\2","\3","\4"')
 
#=> "Name","Address","Street","Payment"
#=>"Smith, John","200","Broadway","$1,000"
#=>"Jo, Sara","1","Main St.","$42"
#=>"Eisenhower, Dwight David","90","Dover Ave.","$140,591,495.00"

Here's an explanation of those symbols:

(.+)

The all-encompassing dot character, combined with the greedy plus operator, will capture everything until the end of the line.

\n

This is the special escape sequence that represents a line break. However, this (annoyingly) is not the case across all platforms; read this Stack Overflow discussion for more details.

\1

We used parentheses in the pattern to save what we've captured. In the replacement sequence, using backslash and a number will refer to those captured groups in the numerical order that they appear in the pattern. So, \1 will refer to the first set of parentheses.

Compare that simple regex pattern to using a Ruby loop, sans regular expressions, to do the replacement:

newstr = ""
arr = nil
str.split("\n").each_with_index do |line, idx|
   if idx%4==0 
      newstr << arr.map{|s| "\"#{s.chomp}\""}.join(',') + "\n" if arr
      arr = [line]
   else
      arr << line
   end
end
      

The more common situation is that the table conversion does not neatly result in one line break per field. Sometimes, particularly long fields will have an extra line break. So, instead of this:

Eisenhower, Dwight David
90
Dover Ave.
$140,591,495.00      
   

You end up with this mess:

Eisenhower, 
Dwight David
90
Dover Ave.
$140,591,495.
00      
      

So now you can't expect that every four lines constitutes a single record.

But, with a little more effort in your regex pattern, you can yet make order from this:

Note: I'm removing the headers line from str for reasons that I will explain later.

regex = /([A-Za-z, ]+)(\d+)([A-Za-z. ]+)(\$[\d,.]+)/
puts str.gsub(/\n/, '').gsub(regex, '"\1","\2","\3","\4"'+"\n")   
#=>   "Smith, John","200","Broadway","$1,000"
#=>   "Jo, Sara","1","Main St.","$42"
#=>   "Eisenhower, Dwight David","90","Dover Ave.","$140,591,495.00"
   

That regex looks like a doozy, but the general concept is that the four captured patterns are now more specific than .+

Here's a breakdown of each of the four captured groups:

[A-Za-z, ]+

Since every record begins with a name, our first pattern captures every alphabetical letter. It also captures commas and spaces, since the name format is "LASTNAME, FIRSTNAME".

Remember that inside the square brackets, hyphens denote a range of characters, in this case, A-Z and a-z. No other kind of delimiter is needed inside []; every character and range specified in it will be captured in the pattern.

Finally, the greedy + operator will grab everything matched within the [] until it hits the next pattern.

\d+

The next field is the address number, which we assume to be only digits. Remember that the previous pattern looked only for alphabet letters. Once it reaches a number, the regex finishes with that group and moves onto the second, digits-only pattern.

[A-Za-z. ]+

The next field is the street name, which we assume will only consist of alphabet letters, spaces, and dots (e.g. "Ave."). Remember that a dot within square brackets is just a literal dot, not the regex symbol for any-character.

\$[\d,.]+

The previous pattern continues until it hits a dollar sign, which denotes the start of the payments field. This final pattern captures all digits, commas, and a decimal point.

If you looked closely at the code, you'll notice that we called gsub twice consecutively. The first time was to remove all line breaks:

str.gsub(/\n/, '')         
      

Since line breaks are artificially thrown into the translated text, there's no reason to keep them. And since we have specific patterns for each field, the line breaks don't serve any kind of delimiting purpose (since it's possible that each field has been broken up into an unknown number of separate lines).

Now, there are a considerable number of caveats here. For example, the name field will likely be broader than what we specified, and we'll need to include hyphens (e.g. "Doe-Smith, Jane") and apostrophes (e.g. "O'Malley, Martin").

This pattern would be:

/([A-Za-z,'\- ]+)/

Note that the hyphen has to be escaped with a backslash for it to be a literal hyphen.

If you know that the name pattern will always be "LASTNAME, FIRSTNAME", then you should include the ", " as part of the pattern to make it even more discriminating. In fact, you may want to break it up into two captured groups, as it is always useful to have separate fields for first and last name:

The pattern for this would be:

/([A-Za-z'\- ]+), ([A-Za-z'\- ]+)/

Note that in the first captured group, that greedy + operator will gobble up everything until it hits that comma and a space. After that, it begins capturing for the second group.

For the above patterns to work, I removed the first headers row out of consideration because it consists of fields that were all alphabetical characters, which would gum up the regex matching.

The sample table I provided let us make easy assumptions. In real life, you can't assume that the street field will never have numbers, for example.

Luckily, there's way to make the patterns much more specific and complex to fit your needs (again, checkout regular-expressions.info). It can involve a bit of frustrating trial and error, but it sure beats sorting and cleaning thousands of fields by hand.

I wrote the following exercise but never quite finished it. It has cuss words and it reviews the basic syntax, so you still might find it useful.

Don't take my hall pass, you ass. - User 101, 7:40 AM
You are a total a$$-wipe - User 206, 7:45 AM
As I've said earlier, the document must be read assiduously in 
 order to assure quality - User 42 9:12 AM
You're an a55hole, kiss my ASS - User 101, 9:40 PM            
               

/ass/

/ass/i

\bass\b

[s5$]

s|5|\$

\ba[s5$][s5$]\b

/\ba[s5$]{2}\b/i

 
         Don't take my hall pass, you ***. - User 101, 7:40 AM
         You are a total ***-wipe - User 206, 7:45 AM
         As I've said earlier, the document must be read assiduously in 
         order to assure quality - User 42 9:12 AM
         You're an a55hole, kiss my *** - User 101, 9:40 PM
               

hole|wipe|face

(h[0o][il1]|w[i1l]p|fac)e

-?

\ba[s5$]{2}-?(\b|(h[0o][il1]|w[i1l]p|fac)e)

\ba[s5$]{2}-?(?:\b|(?:h[0o][il1]|w[i1l]p|fac)e)

Don't take my hall pass, you ***. - User 101, 7:40 AM
You are a total ***wipe - User 206, 7:45 AM
As I've said earlier, the document must be read assiduously in 
 order to assure quality - User 42 9:12 AM
You're an ***, kiss my *** - User 101, 9:40 PM

Get a cheat sheet

I could expand this chapter indefinitely with all the ways that regexes make life easier for data-scrounging. In fact, I'm considering making a Bastard's Book of Regexes. I hope this brief primer convinces you to at least try them out. Virtually every project this book undertakes will use regexes in some fashion.

Luckily, regexes don't require rigorous study. Print out this cheat sheet, bookmark regular-expressions.info, and keep them in mind anytime you ever have to clean messy data.

An aside: Regexes work great with my favorite data-cleaning tool, Google Refine. Here's a tutorial I wrote about using this supremely useful web-browser-based program.