Automated Culling of Data from the Internet: Writing automation scripts

To automate your database requests, you need to create a script file that sends the appropriate requests, one at a time to the internet, and then prints the results or saves them to a file or files. The key to the whole approach of this tutorial is the use of the program "lynx" with the "-source" option. Lynx is essentially a minimal web browser that includes a single URL option. (One nice alternative is to use R instead of a scripting language and to use its url() function instead of Lynx. A complete example for getting census tracts from addresses is here.)

The first type of script used here is the "csh shell script". If you have some programming experience, but little or none with csh scripts, you can still probably follow and mimic what I do. You can get help with "man csh" on your unix system. One place to see the man page for csh is here . Additional scripting help may be found in links to umd . A good debugging hint is to add -v or -x or -xv to the end of the first line of your script to turn on command echoing, e.g. #!/bin/csh -xv.

For more complicated problems, I use perl scripts. Help on perl can be found at perl.com , and a good tutorial is Robert's . You can also get help with the unix command "perldoc". This is on-line at perldoc.perl.org.

If you can get it setup on your system, there is a great series of perl modules for WWW access and parsing including LWP, HTML::Parser, HTML::LinkExtor, and HTML::TokeParser available at www.cpan.org . With these, you make direct connections to the internet, rather than using "lynx". These are not set up on our system, so I don't discuss them here, but check out the book Data Munging with Perl .

Example 1: Chemicals

#!/bin/csh
# This script reads chemical names from the file in it's argument list,
# requests information on these chemicals from webbook.nist.gov using lynx,
# and saves the results to coresponding html files.

foreach chemical (`cat $1`)
  echo Looking up $chemical
  lynx -source "http://webbook.nist.gov/cgi/cbook.cgi?Name=$chemical&Units=SI" > $chemical.html
end

This approach, "csh/foreach", works well for many problems. Because of limitations of the capablilities of the csh shell, we'll need to switch to "perl" for more complex problems.

Of course, in most cases we need to further extract information from the results web pages, and we probably don't want to store all the pages before doing that. This cleanup step is Step 4 . But for now a simple example of a cleanup step may be found in the getcheminfo2 script. It uses the unix command "grep" to remove only the line containing the word "Registry" from the result web page, and it uses ">>" to append the results from each chemical to a single output file.

#!/bin/csh
# This script reads chemical names from the file in it's argument list,
# requests information on these chemicals from webbook.nist.gov using lynx,
# and saves the results to the second name it it's argument.
# E.g. use getcheminfo2 infile outfile

set tmp = tmpOKtoDelete
echo Chemical CAS\# > $2
foreach chemical (`cat $1`)
  echo Looking up $chemical
  lynx -source "http://webbook.nist.gov/cgi/cbook.cgi?Name=$chemical&Units=SI" > $tmp
  set CAS = `cat $tmp | grep Registry`
  echo $chemical $CAS >> $2
end
rm $tmp

Go on to step 4

Example 2: Baseball

Another nicety is to correctly handle a non-exisitent (or misspelled) player by looking for "No information on the player was found." or by noticing that our player is not on the "player list".

Here is getbaseball , a csh script that can get the appropriate baseball web pages:

#!/bin/csh
# Find baseball stats
# First argument is a player list file (First Last format), second is a year

if ( XX$1 == XX || XX$2 == XX ) then
  echo use getbaseball PlayerFile Year
  exit
endif

set mainURL = "http://www.mlb.com/NASApp/mlb/mlb/stats_historical/mlb_player_locator_results.jsp"
set altURL = "http://www.mlb.com/NASApp/mlb/mlb/stats_historical/mlb_individual_stats_player.jsp"
set tmp = tmpDeleteThis
set players = `cat $1`
set final = $#players
set index = 1

while ( $index < $final )
  set first = $players[$index]
  @ index++
  set last = $players[$index]
  @ index++

  lynx -source "$mainURL?playerLocator=$last" > $tmp

  # Use grep to return any lines with "No information"
  if ( XX`cat $tmp | grep "No information"` != XX ) then
    echo No info on $first $last

  # Use grep to return any lines with "Hitting Stats"
  else if ( XX`cat $tmp | grep "Hitting Stats"` != XX ) then
    # A unique match was found if there is a "Hitting Stats" section.
    # Save under players name
    mv $tmp $first$last.html

  # Last possibility is multiple players with this last name.
  else
    # Need to find player ID number that matches first and last names.
    # Use grep to find the line with the playerID on the line with the full name.
    set ID = `grep "$first $last" $tmp | sed -e 's/^.*ID=//' -e 's/" cl.*$//'`
    if ( XX$ID == XX ) then
      echo No info on $first $last
    else
      # Slightly different URL is needed with ID rather than last name.
      # Save results under players name
      lynx -source "$altURL?playerID=$ID" > $first$last.html
    endif
  endif
end

There are a variety of fairly complex issues involved in getting this script to work. First, we can't use "foreach" because it trys to handle the first names separately from the last names. The solution is to load the "players.dat" file into the "players" variable using "set players = `cat $1`" and then to refer to the individual words in this variable using square brackets and an index. The special syntax "$#players" tells us how many words are in "players"; this is twice the number of players because we have listed first and last names for each. The while loop cycles through the players. Since we increment the index twice (with @ index++) per iteration, we loop once per player. The first "lynx" line constructs the full URL for the first attempt to find the player (by last name). Be sure to use the quotation marks as shown. The result, and entire web page, is stored in the file "tmpDeleteThis". (There are some better ways to name temporary files, but this isn't too bad.)

We use "grep" in the form of if ( XX`cat $tmp | grep "No information"` != XX ) to see if a particular string is in the html file . Here "!=" means "not equal", and this inequality will be true if and only if the "grep" result is non-blank, i.e. the file "tmpDeleteThis" does contain "No information". If so, we just let the user know that the player is not on file.

The "else if" clause uses "grep" to check for "Hitting Stats". If the string is found, we just use "mv" to rename the file tmpDeleteThis, which contains the whole results web page, to a file name made up of the player's first and last name followed by ".html".

The "else" clause handles neither "No information" nor "Hitting Stats". if our analysis of how the MLB site works is correct, this means that "tmpDeleteThis" contains a web page designed for selecting a player from a list of players. The "set ID =" command uses "sed" to extract the "playerID" from the html line containing the player's full name by substituting nothing for the text before and after the ID. If the ID is blank, the player was not on file, and we just report this conclusion. Otherwise we exectute a second "lynx" command in the alterate format to ask for the information on the player with the playerID we have found, and store this web page into an appropriately named file.

If you go on to Step 4 you will see what I think is an easier alternative to "sed" and "grep", and you will see two ways to get nice output rather than a bunch of html files.

Example 3: Zip codes

#!/usr/bin/perl -w
use strict;

my $address_file = "firms.dat";
my $URL = "http://www.usps.gov/cgi-bin/zip4/zip4inq2";

my $PS1 = "\"Firm=";
my $PS2 = "\&Urbanization=\&Delivery+Address=";
my $PS3 = "\&City=Pittsburgh\&State=PA\&Zip+Code=\"";

open (INPUT, "<$address_file") || (die "Can't open address file");
 
# for each line in the address list 
while (my $firm = <INPUT>) {
    chomp $firm;
    my $xfirm = $firm;
    $xfirm =~ s/\s/\+/g;
    my $address=<INPUT>;
    chomp $address;
    $address =~ s/\s/\+/g;
    my $poststring = $PS1 . $xfirm . $PS2 . $address . $PS3;
    my @html_source = `echo $poststring | lynx -dump -post_data \"$URL\"`;
    print $firm, "\n", grep(/PITTSBURGH/,@html_source);
}

close(INPUT);