CA Surgeries: Parsing and Storing

Storing the Data with SQLite

If you haven't read the chapter on SQL and SQLite, review it now as I won't be explaining the basics here. You should be familiar with the basic syntax, including how to open a database and select rows.

Creating the table

In the previous code section, I defined the FIELD_NAMES constant which contains an array of the field names. It's used to print out the headers for the textfile and we can use it to generate the table schema:

TABLE_NAME = 'surgery_records'
q = "CREATE TABLE #{TABLE_NAME}(#{FIELD_NAMES.map{|f| "`#{f}`"}.join(', ')});"
puts q

That map method simply sets each field name to be enclosed in backticks (which is necessary if you have a field name like "A Hospital's Name" with non-alphanumeric characters)

CREATE TABLE surgery_records(`year`, `category`, `procedure`, `county`, `hospital`, `address`, `discharge_count`, `median_length_in_days`, `median_charge_per_stay`)

Specify column types

As with other variants of SQL, SQLite allows you to specify a datatype per column. It is helpful to specify that a column of data is supposed to be all numbers so that that column's data is treated as numbers when doing comparisons. Remember:

Turning FIELD_NAMES into a two-dimensional array allows us to modify the query string used to create the table in the following fashion:

FIELD_NAMES = [['year', 'NUMERIC'],['category', 'VARCHAR'], ['procedure', 'VARCHAR'],['county', 'VARCHAR'], ['hospital', 'VARCHAR'],  ['address', 'VARCHAR'], ['discharge_count', 'NUMERIC'], ['median_length_in_days', 'NUMERIC'], ['median_charge_per_stay', 'NUMERIC'] ]

q=  "CREATE TABLE #{TABLE_NAME}(#{FIELD_NAMES.map{|f| "`#{f[0]}` #{f[1]}"}.join(', ')});"

puts q
#=> CREATE TABLE surgery_records(`year` NUMERIC, `category` VARCHAR, `procedure` VARCHAR, `county` VARCHAR, `hospital` VARCHAR, `address` VARCHAR, `discharge_count` NUMERIC, `median_length_in_days` NUMERIC, `median_charge_per_stay` NUMERIC);
      

Create column indexes

For large datasets, you can greatly speed up your queries by indexing the columns that you intend to do "WHERE column_a = `dog`" conditions. For the Common Surgeries data, we will be aggregating numbers per procedure, county, and hospital, so it makes sense to index those.

Read more about indexing database tables here.

In the FIELD_NAMES array, we can add a third value per field that represents the We can add to. If that third value is present, we create an index for that field. If not, we don't create an index. The actual index name isn't important, nor will you need to remember it while doing queries. The SQLite engine will use the index automatically:

FIELD_NAMES = [['year', 'NUMERIC'],['category', 'VARCHAR'], ['procedure', 'VARCHAR', 'procedure_index' ],['county', 'VARCHAR', 'county_index'], ['hospital', 'VARCHAR', 'hospital_index'],  ['address', 'VARCHAR'], ['discharge_count', 'NUMERIC'], ['median_length_in_days', 'NUMERIC'], ['median_charge_per_stay', 'NUMERIC'] ]

FIELD_NAMES.each do |fn|
   puts "CREATE INDEX #{fn[2]} ON #{TABLE_NAME}(#{fn[0]})" unless fn[2].nil?
end         

#=> CREATE INDEX procedure_index ON surgery_records(procedure)
#=> CREATE INDEX county_index ON surgery_records(county)
#=> CREATE INDEX hospital_index ON surgery_records(hospital)
      

The INSERT statement

This is the INSERT statement for loading our data:

DB_INSERT_STATEMENT = "INSERT into #{TABLE_NAME} values (#{FIELD_NAMES.map{'?'}.join(',')})"
puts DB_INSERT_STATEMENT   #=>   "INSERT into surgery_records values (?,?,?,?,?,?,?,?,?)"         
      

Those question marks will be filled in with the values we specify once we actually execute the INSERT statement. How does SQLite know which value goes into which column? Since we don't specify anything, it simply inserts the values in sequential order, i.e. the value in the first ? will be inserted in the first column of the table.

As long as we specify the data in the same order of the table's column structure, we will be good to go. Luckily, we prepared in advance by creating the table columns with the FIELD_NAMES array. We use that array again when entering the actual data.

Providing the values for the INSERT statement is as simple as passing in an array of values to the SQLite instance's execute method:

DB.execute(DB_INSERT_STATEMENT, some_array_of_values)
   

require 'rubygems'
require 'nokogiri'
require 'sqlite3'

FIELD_NAMES = [['year', 'NUMERIC'],['category', 'VARCHAR'], ['procedure', 'VARCHAR', 'procedure_index' ],['county', 'VARCHAR', 'county_index'], ['hospital', 'VARCHAR', 'hospital_index'],  ['address', 'VARCHAR'], ['discharge_count', 'NUMERIC'], ['median_length_in_days', 'NUMERIC'], ['median_charge_per_stay', 'NUMERIC'] ]

# Set up scrape and text file
TABLE_DIV_ID = "#ctl00_ContentPlaceHolder1_gridHospitalList"      
OFILE = File.open('data-hold/ca-common-surgeries.txt', 'w')
OFILE.puts( FIELD_NAMES.map{|f| f[0]}.join("\t") )

# Set up database; delete existing sqlite file
DBNAME = "data-hold/ca-common-surgeries.sqlite"
File.delete(DBNAME) if File.exists?DBNAME
DB = SQLite3::Database.new( DBNAME )

TABLE_NAME = "surgery_records"
DB_INSERT_STATEMENT = "INSERT into #{TABLE_NAME} values
  (#{FIELD_NAMES.map{'?'}.join(',')})"

# Create table
DB.execute "CREATE TABLE #{TABLE_NAME}(#{FIELD_NAMES.map{|f| "`#{f[0]}` #{f[1]}"}.join(', ')});"
FIELD_NAMES.each do |fn| 
  DB.execute "CREATE INDEX #{fn[2]} ON #{TABLE_NAME}(#{fn[0]})" unless fn[2].nil?
end


Dir.glob("data-hold/pages/*.html").reject{|f| f =~ /All Counties/}.each do |fname|
   meta_info = File.basename(fname, '.html').split('--')
   page = Nokogiri::HTML(open(fname))
   
   page.css("#{TABLE_DIV_ID} tr")[1..-2].each do |tr|
      data_tds = tr.css('td').map{ |td|  
         td.text.gsub(/[$,](?=\d)/, '').gsub(/\302\240|\s/, ' ').strip
      }
    data_row = meta_info + data_tds
      OFILE.puts( data_row.join("\t"))  
      DB.execute(DB_INSERT_STATEMENT, data_row)

   end   
end

OFILE.close