Matching up ICPSR IDs to Bioguide IDs using Charles Stewart's data, and the Sunlight Labs Congress API.

icpsr_to_bioguide.rb

# house-icpsr.csv and senate-icpsr.csv are made by converting the XLS files found here to CSV:
# http://web.mit.edu/17.251/www/data_page.html#2

# Specifically, these files that list information and IDs for members from the 103rd to 112th Congress:
# http://web.mit.edu/cstewart/www/data/house_members_103-112-1.xls
# http://web.mit.edu/cstewart/www/data/senators_103-112-1.xls

# This script looks through the two original CSVs, caches the ICPSR ID of every member from the 110th Congress onward, 
# then goes through every legislator in the Sunlight Labs Congress API and tries to match them up by a combination of
# last name, state, and party.

# This fails for something like a dozen members, some of which have issues with last names (accented letters, 
# or married women whose last names appear differently in our data than theirs), and some of which simply were never
# assigned ICPSR IDs (either elected in a recent special election, switched parties, or were in office 
# for too short a term to matter). 
# For the first two cases, you can fill in the ICPSR IDs by hand - for the latter, there is no match.

# The results_plus_hand_entry.csv file also attached to this Gist has had ICPSR IDs filled in by hand for the 
# cases where the legislator has an ICPSR ID, but the script couldn't match it up.

require 'rubygems'
require 'sunlight'
require 'fastercsv'

Sunlight::Base.api_key = '<YOUR API KEY>'

def leg_name(legislator)
  "#{legislator.title}. #{(legislator.nickname.to_s != "") ? legislator.nickname : legislator.firstname} #{legislator.lastname}"
end

def leg_slug(lastname, state, party)
  "#{lastname.downcase.tr(' ','').strip}-#{state}-#{party}"
end



puts "Opening house-icpsr.csv to find rows of 110th, 111th, and 112th Congress, caching in hash table"

# keys are lastname-state-party, where lastname is stripped of spaces, and casing
legs_cache = {}

["house-icpsr.csv", "senate-icpsr.csv"].each do |filename|
  puts "Caching legislators from #{filename}..."

  i = 0
  FasterCSV.foreach(filename) do |row|
    i += 1
    next if i < 3
    next unless row[0].to_i > 109

    slug = leg_slug row[3].split(',').first, row[5], row[7]
    # puts "[#{filename}][#{i}][#{row[0]}] #{slug}"
    legs_cache[slug] = row[2].strip # icpsr_id
  end
end



puts "Loading in members from Sunlight API.."
legislators = Sunlight::Legislator.all_where(:all_legislators => 1)


FasterCSV.open("icpsr_ids.csv", "w") do |csv|

  legislators.each do |legislator|
    bioguide_id = legislator.bioguide_id
    slug = leg_slug legislator.lastname, legislator.state, legislator.party
    
    if icpsr_id = legs_cache[slug]
      # puts "[#{bioguide_id}] Found, outputting"
      csv << [leg_name(legislator), bioguide_id, icpsr_id]
    else
      csv << [leg_name(legislator), bioguide_id, '']
      puts "\tCouldn't find #{leg_name(legislator)} in the original CSVs by #{slug}"
    end

  end

end

results_plus_hand_entry.csv