notebook.community

Edit and run



In [43]:

    
import sqlite3
import numpy as np
import pandas as pd
import time
pd.set_option('display.width', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.notebook_repr_html', True)

db = sqlite3.connect('L19DB_demo.sqlite')
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS candidates")
cursor.execute("DROP TABLE IF EXISTS contributors")
cursor.execute("PRAGMA foreign_keys=1")

cursor.execute('''CREATE TABLE candidates (
               id INTEGER PRIMARY KEY NOT NULL, 
               first_name TEXT, 
               last_name TEXT, 
               middle_init TEXT, 
               party TEXT NOT NULL)''')

db.commit() # Commit changes to the database

cursor.execute('''CREATE TABLE contributors (
          id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
          last_name TEXT, 
          first_name TEXT, 
          middle_name TEXT, 
          street_1 TEXT, 
          street_2 TEXT, 
          city TEXT, 
          state TEXT, 
          zip TEXT, 
          amount REAL, 
          date DATETIME, 
          candidate_id INTEGER NOT NULL, 
          FOREIGN KEY(candidate_id) REFERENCES candidates(id))''')

db.commit()

with open ("candidates.txt") as candidates:
    next(candidates) # jump over the header
    for line in candidates.readlines():
        cid, first_name, last_name, middle_name, party = line.strip().split('|')
        vals_to_insert = (int(cid), first_name, last_name, middle_name, party)
        cursor.execute('''INSERT INTO candidates 
                  (id, first_name, last_name, middle_init, party)
                  VALUES (?, ?, ?, ?, ?)''', vals_to_insert)

with open ("contributors.txt") as contributors:
    next(contributors)
    for line in contributors.readlines():
        cid, last_name, first_name, middle_name, street_1, street_2, \
            city, state, zip_code, amount, date, candidate_id = line.strip().split('|')
        vals_to_insert = (last_name, first_name, middle_name, street_1, street_2, 
                          city, state, int(zip_code), amount, date, candidate_id)
        cursor.execute('''INSERT INTO contributors (last_name, first_name, middle_name, 
                           street_1, street_2, city, state, zip, amount, date, candidate_id) 
                           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', vals_to_insert)

candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]

def viz_tables(cols, query):
    q = cursor.execute(query).fetchall()
    framelist = []
    for i, col_name in enumerate(cols):
        framelist.append((col_name, [col[i] for col in q]))
    return pd.DataFrame.from_items(framelist)



In [44]:

    
query='''SELECT contributors.last_name, contributors.first_name, candidates.last_name FROM contributors INNER JOIN candidates ON contributors.candidate_id = candidates.id'''
q=cursor.execute(query).fetchall()
print(q)









    



[('Agee', 'Steven', 'Huckabee'), ('Ahrens', 'Don', 'Huckabee'), ('Ahrens', 'Don', 'Huckabee'), ('Ahrens', 'Don', 'Huckabee'), ('Akin', 'Charles', 'Huckabee'), ('Akin', 'Mike', 'Huckabee'), ('Akin', 'Rebecca', 'Huckabee'), ('Aldridge', 'Brittni', 'Huckabee'), ('Allen', 'John D.', 'Huckabee'), ('Allen', 'John D.', 'Huckabee'), ('Allison', 'John W.', 'Huckabee'), ('Allison', 'Rebecca', 'Huckabee'), ('Allison', 'Rebecca', 'Huckabee'), ('Altes', 'R.D.', 'Huckabee'), ('Andres', 'Dale', 'Huckabee'), ('Anthony', 'John', 'Huckabee'), ('Arbogast', 'Robert', 'Huckabee'), ('Arbogast', 'Robert', 'Huckabee'), ('Ardle', 'William', 'Huckabee'), ('Atiq', 'Omar', 'Huckabee'), ('Atiq', 'Omar', 'Huckabee'), ('Baker', 'David', 'Huckabee'), ('Bancroft', 'David', 'Huckabee'), ('Banks', 'Charles', 'Huckabee'), ('Barbee', 'John', 'Huckabee'), ('Buckler', 'Steve', 'Obama'), ('Buckler', 'Steve', 'Obama'), ('Buckheit', 'Bruce', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buck', 'Thomas', 'Obama'), ('Buck', 'Jay', 'Obama'), ('Buck', 'Blaine', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buchman', 'Mark M', 'Obama'), ('Bucher', 'Ida', 'Obama'), ('Buchanek', 'Elizabeth', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Harrison', 'Ryan', 'Obama'), ('BYNUM', 'HERBERT', 'Giuliani'), ('BYINGTON', 'MARGARET', 'Giuliani'), ('BYERS', 'BOB', 'Giuliani'), ('BYERS', 'AUDREY', 'Giuliani'), ('BUSH', 'KRYSTIE', 'Giuliani'), ('BUSH', 'ERIC', 'Giuliani'), ('BURTON', 'SUSAN', 'Giuliani'), ('BURTON', 'STEVEN', 'Giuliani'), ('BURTON', 'GLENN', 'Giuliani'), ('BURKHARDT', 'CRAIG', 'Giuliani'), ('BURKHARDT', 'CRAIG', 'Giuliani'), ('BURKHARDT', 'BARBARA', 'Giuliani'), ('BURKE', 'SUZANNE', 'Giuliani'), ('BURKE', 'GAIL', 'Giuliani'), ('BURKE', 'DONALD', 'Giuliani'), ('BURGERT', 'RONALD', 'Giuliani'), ('BULL', 'BARTLE', 'Giuliani'), ('BULL', 'BARTLE', 'Giuliani'), ('BUKOWSKI', 'DANIEL', 'Giuliani'), ('BUISSON', 'MARGARET', 'Giuliani'), ('BUCKLEY', 'WALTER', 'Giuliani'), ('BUCKLEY', 'MARJORIE', 'Giuliani'), ('BRUNO', 'JOHN', 'Giuliani'), ('BRUNO', 'IRENE', 'Giuliani'), ('BROWN', 'TIMOTHY', 'Giuliani'), ('Schuff', 'Bryan', 'Paul'), ('Hobbs', 'James', 'Paul'), ('Ranganath', 'Anoop', 'Paul'), ('Nystrom', 'Michael', 'Paul'), ('Muse', 'Nina', 'Paul'), ('Waddell', 'James', 'Paul'), ('Brucks', 'William', 'Paul'), ('Kuehn', 'David', 'Paul'), ('Verster', 'Jeanette', 'Paul'), ('Uihlein', 'Richard', 'Paul'), ('Eskenberry', 'Robert', 'Paul'), ('Froehling', 'Alan', 'Paul'), ('Duryea', 'Marcia', 'Paul'), ('Perreault', 'Louise', 'Paul'), ('Rozenfeld', 'Timur', 'Paul'), ('Kazor', 'Christopher', 'Paul'), ('Lehner', 'Thomas', 'Paul'), ('Plummer', 'Joseph', 'Paul'), ('Raught', 'Philip', 'Paul'), ('Ferrara', 'Judith', 'Paul'), ('Johnson', 'Cathleen', 'Paul'), ('Sanford', 'Bradley', 'Paul'), ('Gaarder', 'Bruce', 'Paul'), ('Choe', 'Hyeokchan', 'Paul'), ('Jacobs', 'Richard', 'Paul'), ('Aaronson', 'Rebecca', 'Clinton'), ('Aarons', 'Elaine', 'Clinton'), ('Aarons', 'Elaine', 'Clinton'), ('Aarons', 'Elaine', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaronson', 'Rebecca', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Reid', 'Elizabeth', 'Clinton'), ('Reich', 'Thomas', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Shirley', 'Clinton'), ('Aaron', 'Sharron', 'Clinton'), ('Aaron', 'Patricia', 'Clinton'), ('Aaron', 'Patricia', 'Clinton'), ('Aaron', 'Jim', 'Clinton'), ('Aaron', 'Jim', 'Clinton'), ('Aaron', 'Carole', 'Clinton'), ('Aaron', 'Carole', 'Clinton'), ('Aaron', 'Carole', 'Clinton'), ('Aaron', 'Barbara', 'Clinton'), ('Aanonsen', 'Lin', 'Clinton'), ('Aanonsen', 'Lin', 'Clinton'), ('BOURNE', 'TRAVIS', 'Romney'), ('SECRIST', 'BRIAN', 'Romney'), ('TOLLESTRUP', 'TRAVIS', 'Romney'), ('ACCORD', 'DEAN', 'Romney'), ('ABTS', 'HENRY', 'Romney'), ('ABSHIER', 'LANNY', 'Romney'), ('ABSHIER', 'DIANA', 'Romney'), ('ABREU', 'KEVIN', 'Romney'), ('ABREU', 'KEVIN', 'Romney'), ('ABREU', 'KEVIN', 'Romney'), ('ABRAMOWITZ', 'NIRA', 'Romney'), ('ABRAMS', 'MICHAEL', 'Romney'), ('ABRAMOWITZ', 'KEN', 'Romney'), ('ABOUBAKARE', 'NASAR', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABEGG', 'PATRICIA', 'Romney'), ('ABDELLA', 'THOMAS', 'Romney'), ('ABBOTT', 'WELDON', 'Romney'), ('ABBOTT', 'WELDON', 'Romney'), ('ABBOTT', 'GERALD', 'Romney'), ('ABBOTT', 'GERALD', 'Romney'), ('ABEDIN', 'ZAINUL', 'McCain'), ('ABBOTT', 'SYBIL', 'McCain'), ('ABBOTT', 'SYBIL', 'McCain'), ('ABBOTT', 'RONALD', 'McCain'), ('ABBOTT', 'RONALD', 'McCain'), ('ABBOTT', 'ROBERT', 'McCain'), ('ABBOTT', 'MIKE', 'McCain'), ('ABBOT', 'DAVID', 'McCain'), ('ABBO', 'PAULINE', 'McCain'), ('ABATE', 'MARIA', 'McCain'), ('ABAIR', 'PETER', 'McCain'), ('ABACHERLI', 'SHIRLEY', 'McCain'), ('AARONS', 'CHARLES', 'McCain'), ('AARONS', 'CHARLES', 'McCain'), ('AARONS', 'CHARLES', 'McCain'), ('ABEL', 'JOHN', 'McCain'), ('ABEL', 'MARLING', 'McCain'), ('ABEL', 'RUDOLPH', 'McCain'), ('ABELE', 'RODNEY', 'McCain'), ('ABERCROMBIE', 'DENIS', 'McCain'), ('ABESHAUS', 'MERRILL', 'McCain'), ('ABRAHAM', 'GEORGE', 'McCain'), ('ABRAHAMSON', 'PETER', 'McCain'), ('ABRAHAM', 'SALEM', 'McCain'), ('ABRAHAM', 'SALEM', 'McCain')]



In [45]:

    
query='''SELECT contributors.last_name, contributors.first_name, candidates.last_name FROM contributors INNER JOIN candidates ON contributors.candidate_id = candidates.id WHERE candidates.last_name="Obama"'''
q=cursor.execute(query).fetchall()
print(q)









    



[('Buckler', 'Steve', 'Obama'), ('Buckler', 'Steve', 'Obama'), ('Buckheit', 'Bruce', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buckel', 'Linda', 'Obama'), ('Buck', 'Thomas', 'Obama'), ('Buck', 'Jay', 'Obama'), ('Buck', 'Blaine', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buck', 'Barbara', 'Obama'), ('Buchman', 'Mark M', 'Obama'), ('Bucher', 'Ida', 'Obama'), ('Buchanek', 'Elizabeth', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Buchanan', 'John', 'Obama'), ('Harrison', 'Ryan', 'Obama')]

nA	attr	idA
s1	23	0
s2	7	2
s3	15	2
s4	31	0

nB	attr	idB
t1	60	0
t2	14	7
t3	22	2

nA	A.attr	nB	B.attr
s1	23	t1	60
s2	7	t3	22
s3	15	t3	22
s4	31	t1	60

nA	A.attr	nB	B.attr
s1	23	t1	60
s4	31	t1	60
none	NaN	t2	14
s2	7	t3	22
s3	15	t3	22



In [35]:

    
query='''SELECT AVG(amount),count(last_name) from contributors GROUP BY candidate_id'''
q=cursor.execute(query).fetchall()
pd.DataFrame(q)



In [38]:

    
query = '''SELECT AVG(contributors.amount) AS average_contribution, 
COUNT(*) AS number_of_contributrs,
candidates.last_name AS candidate_last_name FROM contributors 
LEFT JOIN candidates ON candidates.id = contributors.candidate_id 
GROUP BY candidate_last_name'''
viz_tables(["average_contribution","number_of_contributrs","candidate_last_name"], query)









    Out[38]:







  
    
      
      average_contribution
      number_of_contributrs
      candidate_last_name
    
  
  
    
      0
      50.6000
      25
      Clinton
    
    
      1
      -1360.0000
      25
      Giuliani
    
    
      2
      772.0000
      25
      Huckabee
    
    
      3
      452.6000
      25
      McCain
    
    
      4
      472.4356
      25
      Obama
    
    
      5
      -519.7088
      25
      Paul
    
    
      6
      156.0000
      25
      Romney



In [39]:

    
q=cursor.execute(query).fetchall()
print(q)









    



[(50.6, 25, 'Clinton'), (-1360.0, 25, 'Giuliani'), (772.0, 25, 'Huckabee'), (452.6, 25, 'McCain'), (472.43559999999997, 25, 'Obama'), (-519.7088, 25, 'Paul'), (156.0, 25, 'Romney')]



In [40]:

    
dfcand = pd.read_csv("candidates.txt", sep="|")
dfcand









    Out[40]:







  
    
      
      id
      first_name
      last_name
      middle_name
      party
    
  
  
    
      0
      33
      Joseph
      Biden
      NaN
      D
    
    
      1
      36
      Samuel
      Brownback
      NaN
      R
    
    
      2
      34
      Hillary
      Clinton
      R.
      D
    
    
      3
      39
      Christopher
      Dodd
      J.
      D
    
    
      4
      26
      John
      Edwards
      NaN
      D
    
    
      5
      22
      Rudolph
      Giuliani
      NaN
      R
    
    
      6
      24
      Mike
      Gravel
      NaN
      D
    
    
      7
      16
      Mike
      Huckabee
      NaN
      R
    
    
      8
      30
      Duncan
      Hunter
      NaN
      R
    
    
      9
      31
      Dennis
      Kucinich
      NaN
      D
    
    
      10
      37
      John
      McCain
      NaN
      R
    
    
      11
      20
      Barack
      Obama
      NaN
      D
    
    
      12
      32
      Ron
      Paul
      NaN
      R
    
    
      13
      29
      Bill
      Richardson
      NaN
      D
    
    
      14
      35
      Mitt
      Romney
      NaN
      R
    
    
      15
      38
      Tom
      Tancredo
      NaN
      R
    
    
      16
      41
      Fred
      Thompson
      D.
      R



In [41]:

    
dfcontr = pd.read_csv("contributors.txt", sep="|")
dfcontr









    Out[41]:







  
    
      
      id
      last_name
      first_name
      middle_name
      street_1
      street_2
      city
      state
      zip
      amount
      date
      candidate_id
    
  
  
    
      0
      NaN
      Agee
      Steven
      NaN
      549 Laurel Branch Road
      NaN
      Floyd
      VA
      24091
      500.0
      2007-06-30
      16
    
    
      1
      NaN
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      250.0
      2007-05-16
      16
    
    
      2
      NaN
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      50.0
      2007-06-18
      16
    
    
      3
      NaN
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      100.0
      2007-06-21
      16
    
    
      4
      NaN
      Akin
      Charles
      NaN
      10187 Sugar Creek Road
      NaN
      Bentonville
      AR
      72712
      100.0
      2007-06-16
      16
    
    
      5
      NaN
      Akin
      Mike
      NaN
      181 Baywood Lane
      NaN
      Monticello
      AR
      71655
      1500.0
      2007-05-18
      16
    
    
      6
      NaN
      Akin
      Rebecca
      NaN
      181 Baywood Lane
      NaN
      Monticello
      AR
      71655
      500.0
      2007-05-18
      16
    
    
      7
      NaN
      Aldridge
      Brittni
      NaN
      808 Capitol Square Place, SW
      NaN
      Washington
      DC
      20024
      250.0
      2007-06-06
      16
    
    
      8
      NaN
      Allen
      John D.
      NaN
      1052 Cannon Mill Drive
      NaN
      North Augusta
      SC
      29860
      1000.0
      2007-06-11
      16
    
    
      9
      NaN
      Allen
      John D.
      NaN
      1052 Cannon Mill Drive
      NaN
      North Augusta
      SC
      29860
      1300.0
      2007-06-29
      16
    
    
      10
      NaN
      Allison
      John W.
      NaN
      P.O. Box 1089
      NaN
      Conway
      AR
      72033
      1000.0
      2007-05-18
      16
    
    
      11
      NaN
      Allison
      Rebecca
      NaN
      3206 Summit Court
      NaN
      Little Rock
      AR
      72227
      1000.0
      2007-04-25
      16
    
    
      12
      NaN
      Allison
      Rebecca
      NaN
      3206 Summit Court
      NaN
      Little Rock
      AR
      72227
      200.0
      2007-06-12
      16
    
    
      13
      NaN
      Altes
      R.D.
      NaN
      8600 Moody Road
      NaN
      Fort Smith
      AR
      72903
      2300.0
      2007-06-21
      16
    
    
      14
      NaN
      Andres
      Dale
      NaN
      1160 Glen Oaks Drive
      NaN
      West Des Moines
      IA
      50266
      250.0
      2007-06-06
      16
    
    
      15
      NaN
      Anthony
      John
      NaN
      211 Long Island Drive
      NaN
      Hot Springs
      AR
      71913
      2300.0
      2007-06-12
      16
    
    
      16
      NaN
      Arbogast
      Robert
      NaN
      12900 State Route 56 SE
      NaN
      Mount Sterling
      OH
      43143
      500.0
      2007-04-08
      16
    
    
      17
      NaN
      Arbogast
      Robert
      NaN
      12900 State Route 56 SE
      NaN
      Mount Sterling
      OH
      43143
      100.0
      2007-06-22
      16
    
    
      18
      NaN
      Ardle
      William
      NaN
      412 Dakota Avenue
      NaN
      Springfield
      OH
      45504
      50.0
      2007-06-28
      16
    
    
      19
      NaN
      Atiq
      Omar
      NaN
      7200 S Hazel Street
      NaN
      Pine Bluff
      AR
      71603
      1000.0
      2007-05-18
      16
    
    
      20
      NaN
      Atiq
      Omar
      NaN
      7200 S Hazel Street
      NaN
      Pine Bluff
      AR
      71603
      1000.0
      2007-06-27
      16
    
    
      21
      NaN
      Baker
      David
      NaN
      2550 Adamsbrooke Drive
      NaN
      Conway
      AR
      72034
      2300.0
      2007-04-11
      16
    
    
      22
      NaN
      Bancroft
      David
      NaN
      2934 Broderick Street
      NaN
      San Francisco
      CA
      94123
      250.0
      2007-04-24
      16
    
    
      23
      NaN
      Banks
      Charles
      NaN
      P.O. Box 251310
      NaN
      Little Rock
      AR
      72225
      1000.0
      2007-05-14
      16
    
    
      24
      NaN
      Barbee
      John
      NaN
      516 Kellyridge Drive
      NaN
      Apex
      NC
      27502
      500.0
      2007-05-23
      16
    
    
      25
      NaN
      Buckler
      Steve
      NaN
      24351 Armada Dr
      NaN
      Dana Point
      CA
      926291306
      50.0
      2007-07-30
      20
    
    
      26
      NaN
      Buckler
      Steve
      NaN
      24351 Armada Dr
      NaN
      Dana Point
      CA
      926291306
      25.0
      2007-08-16
      20
    
    
      27
      NaN
      Buckheit
      Bruce
      NaN
      8904 KAREN DR
      NaN
      FAIRFAX
      VA
      220312731
      100.0
      2007-09-19
      20
    
    
      28
      NaN
      Buckel
      Linda
      NaN
      PO Box 683130
      NaN
      Park City
      UT
      840683130
      2300.0
      2007-08-14
      20
    
    
      29
      NaN
      Buckel
      Linda
      NaN
      PO Box 683130
      NaN
      Park City
      UT
      840683130
      -2300.0
      2007-08-14
      20
    
    
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    
    
      145
      NaN
      ABDELLA
      THOMAS
      M.
      4231 MONUMENT WALL WAY #340
      NaN
      FAIRFAX
      VA
      220308440
      50.0
      2007-09-30
      35
    
    
      146
      NaN
      ABBOTT
      WELDON
      S.
      777 EAST SOUTH TEMPLE  4E
      NaN
      SALT LAKE CITY
      UT
      841021269
      100.0
      2007-09-29
      35
    
    
      147
      NaN
      ABBOTT
      WELDON
      S.
      777 EAST SOUTH TEMPLE  4E
      NaN
      SALT LAKE CITY
      UT
      841021269
      50.0
      2007-08-09
      35
    
    
      148
      NaN
      ABBOTT
      GERALD
      F.
      389 BENEFIT STREET
      NaN
      PROVIDENCE
      RI
      29032946
      100.0
      2007-09-15
      35
    
    
      149
      NaN
      ABBOTT
      GERALD
      F.
      389 BENEFIT STREET
      NaN
      PROVIDENCE
      RI
      29032946
      100.0
      2007-08-15
      35
    
    
      150
      NaN
      ABEDIN
      ZAINUL
      NaN
      715 N. CENTRAL AVENUE
      SUITE 212
      GLENDALE
      CA
      912031164
      500.0
      2008-01-21
      37
    
    
      151
      NaN
      ABBOTT
      SYBIL
      F.
      446 GAMES DRIVE
      NaN
      RENO
      NV
      895093326
      75.0
      2008-01-08
      37
    
    
      152
      NaN
      ABBOTT
      SYBIL
      F.
      446 GAMES DRIVE
      NaN
      RENO
      NV
      895093326
      50.0
      2008-01-08
      37
    
    
      153
      NaN
      ABBOTT
      RONALD
      LEANDER
      5453 HAWTHORNE STREET
      NaN
      MONTCLAIR
      CA
      917632551
      200.0
      2008-01-31
      37
    
    
      154
      NaN
      ABBOTT
      RONALD
      LEANDER
      5453 HAWTHORNE STREET
      NaN
      MONTCLAIR
      CA
      917632551
      100.0
      2008-01-08
      37
    
    
      155
      NaN
      ABBOTT
      ROBERT
      A.
      3061 LOREE ROAD
      NaN
      DECKERVILLE
      MI
      484279763
      500.0
      2008-01-21
      37
    
    
      156
      NaN
      ABBOTT
      MIKE
      E.
      4516 OSPREY LNDG
      NaN
      NICEVILLE
      FL
      325786810
      1000.0
      2008-01-15
      37
    
    
      157
      NaN
      ABBOT
      DAVID
      M.
      56 SALEM STREET
      NaN
      ANDOVER
      MA
      18102114
      200.0
      2008-01-21
      37
    
    
      158
      NaN
      ABBO
      PAULINE
      MORENCY
      10720 JACOB LANE
      NaN
      WHITE LAKE
      MI
      483862274
      35.0
      2008-01-07
      37
    
    
      159
      NaN
      ABATE
      MARIA
      ELENA
      1291 NIGHTINGALE AVENUE
      NaN
      MIAMI SPRINGS
      FL
      331663832
      2600.0
      2008-01-25
      37
    
    
      160
      NaN
      ABAIR
      PETER
      NaN
      40 EVANS STREET
      NaN
      WATERTOWN
      MA
      24722150
      25.0
      2008-01-09
      37
    
    
      161
      NaN
      ABACHERLI
      SHIRLEY
      M.
      29875 NEWPORT ROAD
      NaN
      MENIFEE
      CA
      925849524
      150.0
      2008-01-28
      37
    
    
      162
      NaN
      AARONS
      CHARLES
      NaN
      1730 SHORE DRIVE
      NaN
      ANCHORAGE
      AK
      995153207
      300.0
      2008-01-30
      37
    
    
      163
      NaN
      AARONS
      CHARLES
      NaN
      1730 SHORE DRIVE
      NaN
      ANCHORAGE
      AK
      995153207
      410.0
      2008-01-15
      37
    
    
      164
      NaN
      AARONS
      CHARLES
      NaN
      1730 SHORE DRIVE
      NaN
      ANCHORAGE
      AK
      995153207
      500.0
      2008-01-09
      37
    
    
      165
      NaN
      ABEL
      JOHN
      H.
      422 THOMAS STREET
      NaN
      BETHLEHEM
      PA
      180153316
      200.0
      2008-01-22
      37
    
    
      166
      NaN
      ABEL
      MARLING
      L.
      14 HANGING MOSS LANE
      NaN
      GREENVILLE
      SC
      296155069
      100.0
      2008-01-22
      37
    
    
      167
      NaN
      ABEL
      RUDOLPH
      NaN
      4532 OCEAN BLVD.
      # 108
      SARASOTA
      FL
      342421337
      100.0
      2008-01-08
      37
    
    
      168
      NaN
      ABELE
      RODNEY
      NaN
      3620 METAIRIE HEIGHTS AVENUE
      NaN
      METAIRIE
      LA
      700021823
      500.0
      2008-01-15
      37
    
    
      169
      NaN
      ABERCROMBIE
      DENIS
      NaN
      11811 WATER OAK CT
      NaN
      MAGNOLIA
      TX
      773546270
      500.0
      2008-01-30
      37
    
    
      170
      NaN
      ABESHAUS
      MERRILL
      M.
      1801 N. HEREFORD DRIVE
      NaN
      FLAGSTAFF
      AZ
      860011121
      120.0
      2008-01-16
      37
    
    
      171
      NaN
      ABRAHAM
      GEORGE
      NaN
      P.O. BOX 1504
      NaN
      LAKE CHARLES
      LA
      706021504
      800.0
      2008-01-17
      37
    
    
      172
      NaN
      ABRAHAMSON
      PETER
      J.
      1030 W. ROSCOE STREET
      NaN
      CHICAGO
      IL
      606572207
      50.0
      2008-01-25
      37
    
    
      173
      NaN
      ABRAHAM
      SALEM
      A.
      P.O. BOX 7
      NaN
      CANADIAN
      TX
      790140007
      1000.0
      2008-01-17
      37
    
    
      174
      NaN
      ABRAHAM
      SALEM
      A.
      P.O. BOX 7
      NaN
      CANADIAN
      TX
      790140007
      1300.0
      2008-01-30
      37
    
  

175 rows × 12 columns



In [42]:

    
del dfcontr['id']
dfcontr.head()









    Out[42]:







  
    
      
      last_name
      first_name
      middle_name
      street_1
      street_2
      city
      state
      zip
      amount
      date
      candidate_id
    
  
  
    
      0
      Agee
      Steven
      NaN
      549 Laurel Branch Road
      NaN
      Floyd
      VA
      24091
      500.0
      2007-06-30
      16
    
    
      1
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      250.0
      2007-05-16
      16
    
    
      2
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      50.0
      2007-06-18
      16
    
    
      3
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      100.0
      2007-06-21
      16
    
    
      4
      Akin
      Charles
      NaN
      10187 Sugar Creek Road
      NaN
      Bentonville
      AR
      72712
      100.0
      2007-06-16
      16



In [46]:

    
dbp = sqlite3.connect('L19_pandas_DB.sqlite')
csr = dbp.cursor()
csr.execute("DROP TABLE IF EXISTS candidates")
csr.execute("DROP TABLE IF EXISTS contributors")
csr.execute("PRAGMA foreign_keys=1")

csr.execute('''CREATE TABLE candidates (
               id INTEGER PRIMARY KEY NOT NULL, 
               first_name TEXT, 
               last_name TEXT, 
               middle_name TEXT, 
               party TEXT NOT NULL)''')

dbp.commit() # Commit changes to the database

csr.execute('''CREATE TABLE contributors (
          id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
          last_name TEXT, 
          first_name TEXT, 
          middle_name TEXT, 
          street_1 TEXT, 
          street_2 TEXT, 
          city TEXT, 
          state TEXT, 
          zip TEXT, 
          amount REAL, 
          date DATETIME, 
          candidate_id INTEGER NOT NULL, 
          FOREIGN KEY(candidate_id) REFERENCES candidates(id))''')

dbp.commit()



In [47]:

    
dfcand.query("first_name=='Mike' & party=='D'")









    Out[47]:







  
    
      
      id
      first_name
      last_name
      middle_name
      party
    
  
  
    
      6
      24
      Mike
      Gravel
      NaN
      D



In [48]:

    
dfcand[dfcand.middle_name.notnull()]









    Out[48]:







  
    
      
      id
      first_name
      last_name
      middle_name
      party
    
  
  
    
      2
      34
      Hillary
      Clinton
      R.
      D
    
    
      3
      39
      Christopher
      Dodd
      J.
      D
    
    
      16
      41
      Fred
      Thompson
      D.
      R



In [49]:

    
dfcand[(dfcand.first_name=="Mike") & (dfcand.party=="D")]









    Out[49]:







  
    
      
      id
      first_name
      last_name
      middle_name
      party
    
  
  
    
      6
      24
      Mike
      Gravel
      NaN
      D



In [50]:

    
dfcand[dfcand.first_name.isin(['Mike', 'Hillary'])]









    Out[50]:







  
    
      
      id
      first_name
      last_name
      middle_name
      party
    
  
  
    
      2
      34
      Hillary
      Clinton
      R.
      D
    
    
      6
      24
      Mike
      Gravel
      NaN
      D
    
    
      7
      16
      Mike
      Huckabee
      NaN
      R



In [52]:

    
dfcontr









    Out[52]:







  
    
      
      last_name
      first_name
      middle_name
      street_1
      street_2
      city
      state
      zip
      amount
      date
      candidate_id
    
  
  
    
      0
      Agee
      Steven
      NaN
      549 Laurel Branch Road
      NaN
      Floyd
      VA
      24091
      500.0
      2007-06-30
      16
    
    
      1
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      250.0
      2007-05-16
      16
    
    
      2
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      50.0
      2007-06-18
      16
    
    
      3
      Ahrens
      Don
      NaN
      4034 Rennellwood Way
      NaN
      Pleasanton
      CA
      94566
      100.0
      2007-06-21
      16
    
    
      4
      Akin
      Charles
      NaN
      10187 Sugar Creek Road
      NaN
      Bentonville
      AR
      72712
      100.0
      2007-06-16
      16
    
    
      5
      Akin
      Mike
      NaN
      181 Baywood Lane
      NaN
      Monticello
      AR
      71655
      1500.0
      2007-05-18
      16
    
    
      6
      Akin
      Rebecca
      NaN
      181 Baywood Lane
      NaN
      Monticello
      AR
      71655
      500.0
      2007-05-18
      16
    
    
      7
      Aldridge
      Brittni
      NaN
      808 Capitol Square Place, SW
      NaN
      Washington
      DC
      20024
      250.0
      2007-06-06
      16
    
    
      8
      Allen
      John D.
      NaN
      1052 Cannon Mill Drive
      NaN
      North Augusta
      SC
      29860
      1000.0
      2007-06-11
      16
    
    
      9
      Allen
      John D.
      NaN
      1052 Cannon Mill Drive
      NaN
      North Augusta
      SC
      29860
      1300.0
      2007-06-29
      16
    
    
      10
      Allison
      John W.
      NaN
      P.O. Box 1089
      NaN
      Conway
      AR
      72033
      1000.0
      2007-05-18
      16
    
    
      11
      Allison
      Rebecca
      NaN
      3206 Summit Court
      NaN
      Little Rock
      AR
      72227
      1000.0
      2007-04-25
      16
    
    
      12
      Allison
      Rebecca
      NaN
      3206 Summit Court
      NaN
      Little Rock
      AR
      72227
      200.0
      2007-06-12
      16
    
    
      13
      Altes
      R.D.
      NaN
      8600 Moody Road
      NaN
      Fort Smith
      AR
      72903
      2300.0
      2007-06-21
      16
    
    
      14
      Andres
      Dale
      NaN
      1160 Glen Oaks Drive
      NaN
      West Des Moines
      IA
      50266
      250.0
      2007-06-06
      16
    
    
      15
      Anthony
      John
      NaN
      211 Long Island Drive
      NaN
      Hot Springs
      AR
      71913
      2300.0
      2007-06-12
      16
    
    
      16
      Arbogast
      Robert
      NaN
      12900 State Route 56 SE
      NaN
      Mount Sterling
      OH
      43143
      500.0
      2007-04-08
      16
    
    
      17
      Arbogast
      Robert
      NaN
      12900 State Route 56 SE
      NaN
      Mount Sterling
      OH
      43143
      100.0
      2007-06-22
      16
    
    
      18
      Ardle
      William
      NaN
      412 Dakota Avenue
      NaN
      Springfield
      OH
      45504
      50.0
      2007-06-28
      16
    
    
      19
      Atiq
      Omar
      NaN
      7200 S Hazel Street
      NaN
      Pine Bluff
      AR
      71603
      1000.0
      2007-05-18
      16
    
    
      20
      Atiq
      Omar
      NaN
      7200 S Hazel Street
      NaN
      Pine Bluff
      AR
      71603
      1000.0
      2007-06-27
      16
    
    
      21
      Baker
      David
      NaN
      2550 Adamsbrooke Drive
      NaN
      Conway
      AR
      72034
      2300.0
      2007-04-11
      16
    
    
      22
      Bancroft
      David
      NaN
      2934 Broderick Street
      NaN
      San Francisco
      CA
      94123
      250.0
      2007-04-24
      16
    
    
      23
      Banks
      Charles
      NaN
      P.O. Box 251310
      NaN
      Little Rock
      AR
      72225
      1000.0
      2007-05-14
      16
    
    
      24
      Barbee
      John
      NaN
      516 Kellyridge Drive
      NaN
      Apex
      NC
      27502
      500.0
      2007-05-23
      16
    
    
      25
      Buckler
      Steve
      NaN
      24351 Armada Dr
      NaN
      Dana Point
      CA
      926291306
      50.0
      2007-07-30
      20
    
    
      26
      Buckler
      Steve
      NaN
      24351 Armada Dr
      NaN
      Dana Point
      CA
      926291306
      25.0
      2007-08-16
      20
    
    
      27
      Buckheit
      Bruce
      NaN
      8904 KAREN DR
      NaN
      FAIRFAX
      VA
      220312731
      100.0
      2007-09-19
      20
    
    
      28
      Buckel
      Linda
      NaN
      PO Box 683130
      NaN
      Park City
      UT
      840683130
      2300.0
      2007-08-14
      20
    
    
      29
      Buckel
      Linda
      NaN
      PO Box 683130
      NaN
      Park City
      UT
      840683130
      -2300.0
      2007-08-14
      20
    
    
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
      ...
    
    
      145
      ABDELLA
      THOMAS
      M.
      4231 MONUMENT WALL WAY #340
      NaN
      FAIRFAX
      VA
      220308440
      50.0
      2007-09-30
      35
    
    
      146
      ABBOTT
      WELDON
      S.
      777 EAST SOUTH TEMPLE  4E
      NaN
      SALT LAKE CITY
      UT
      841021269
      100.0
      2007-09-29
      35
    
    
      147
      ABBOTT
      WELDON
      S.
      777 EAST SOUTH TEMPLE  4E
      NaN
      SALT LAKE CITY
      UT
      841021269
      50.0
      2007-08-09
      35
    
    
      148
      ABBOTT
      GERALD
      F.
      389 BENEFIT STREET
      NaN
      PROVIDENCE
      RI
      29032946
      100.0
      2007-09-15
      35
    
    
      149
      ABBOTT
      GERALD
      F.
      389 BENEFIT STREET
      NaN
      PROVIDENCE
      RI
      29032946
      100.0
      2007-08-15
      35
    
    
      150
      ABEDIN
      ZAINUL
      NaN
      715 N. CENTRAL AVENUE
      SUITE 212
      GLENDALE
      CA
      912031164
      500.0
      2008-01-21
      37
    
    
      151
      ABBOTT
      SYBIL
      F.
      446 GAMES DRIVE
      NaN
      RENO
      NV
      895093326
      75.0
      2008-01-08
      37
    
    
      152
      ABBOTT
      SYBIL
      F.
      446 GAMES DRIVE
      NaN
      RENO
      NV
      895093326
      50.0
      2008-01-08
      37
    
    
      153
      ABBOTT
      RONALD
      LEANDER
      5453 HAWTHORNE STREET
      NaN
      MONTCLAIR
      CA
      917632551
      200.0
      2008-01-31
      37
    
    
      154
      ABBOTT
      RONALD
      LEANDER
      5453 HAWTHORNE STREET
      NaN
      MONTCLAIR
      CA
      917632551
      100.0
      2008-01-08
      37
    
    
      155
      ABBOTT
      ROBERT
      A.
      3061 LOREE ROAD
      NaN
      DECKERVILLE
      MI
      484279763
      500.0
      2008-01-21
      37
    
    
      156
      ABBOTT
      MIKE
      E.
      4516 OSPREY LNDG
      NaN
      NICEVILLE
      FL
      325786810
      1000.0
      2008-01-15
      37
    
    
      157
      ABBOT
      DAVID
      M.
      56 SALEM STREET
      NaN
      ANDOVER
      MA
      18102114
      200.0
      2008-01-21
      37
    
    
      158
      ABBO
      PAULINE
      MORENCY
      10720 JACOB LANE
      NaN
      WHITE LAKE
      MI
      483862274
      35.0
      2008-01-07
      37
    
    
      159
      ABATE
      MARIA
      ELENA
      1291 NIGHTINGALE AVENUE
      NaN
      MIAMI SPRINGS
      FL
      331663832
      2600.0
      2008-01-25
      37
    
    
      160
      ABAIR
      PETER
      NaN
      40 EVANS STREET
      NaN
      WATERTOWN
      MA
      24722150
      25.0
      2008-01-09
      37
    
    
      161
      ABACHERLI
      SHIRLEY
      M.
      29875 NEWPORT ROAD
      NaN
      MENIFEE
      CA
      925849524
      150.0
      2008-01-28
      37
    
    
      162
      AARONS
      CHARLES
      NaN
      1730 SHORE DRIVE
      NaN
      ANCHORAGE
      AK
      995153207
      300.0
      2008-01-30
      37
    
    
      163
      AARONS
      CHARLES
      NaN
      1730 SHORE DRIVE
      NaN
      ANCHORAGE
      AK
      995153207
      410.0
      2008-01-15
      37
    
    
      164
      AARONS
      CHARLES
      NaN
      1730 SHORE DRIVE
      NaN
      ANCHORAGE
      AK
      995153207
      500.0
      2008-01-09
      37
    
    
      165
      ABEL
      JOHN
      H.
      422 THOMAS STREET
      NaN
      BETHLEHEM
      PA
      180153316
      200.0
      2008-01-22
      37
    
    
      166
      ABEL
      MARLING
      L.
      14 HANGING MOSS LANE
      NaN
      GREENVILLE
      SC
      296155069
      100.0
      2008-01-22
      37
    
    
      167
      ABEL
      RUDOLPH
      NaN
      4532 OCEAN BLVD.
      # 108
      SARASOTA
      FL
      342421337
      100.0
      2008-01-08
      37
    
    
      168
      ABELE
      RODNEY
      NaN
      3620 METAIRIE HEIGHTS AVENUE
      NaN
      METAIRIE
      LA
      700021823
      500.0
      2008-01-15
      37
    
    
      169
      ABERCROMBIE
      DENIS
      NaN
      11811 WATER OAK CT
      NaN
      MAGNOLIA
      TX
      773546270
      500.0
      2008-01-30
      37
    
    
      170
      ABESHAUS
      MERRILL
      M.
      1801 N. HEREFORD DRIVE
      NaN
      FLAGSTAFF
      AZ
      860011121
      120.0
      2008-01-16
      37
    
    
      171
      ABRAHAM
      GEORGE
      NaN
      P.O. BOX 1504
      NaN
      LAKE CHARLES
      LA
      706021504
      800.0
      2008-01-17
      37
    
    
      172
      ABRAHAMSON
      PETER
      J.
      1030 W. ROSCOE STREET
      NaN
      CHICAGO
      IL
      606572207
      50.0
      2008-01-25
      37
    
    
      173
      ABRAHAM
      SALEM
      A.
      P.O. BOX 7
      NaN
      CANADIAN
      TX
      790140007
      1000.0
      2008-01-17
      37
    
    
      174
      ABRAHAM
      SALEM
      A.
      P.O. BOX 7
      NaN
      CANADIAN
      TX
      790140007
      1300.0
      2008-01-30
      37
    
  

175 rows × 11 columns



In [51]:

    
dfcontr.to_sql("contributors", dbp, if_exists="append", index=False)









    



---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
<ipython-input-51-5d6987a27146> in <module>()
----> 1 dfcontr.to_sql("contributors", dbp, if_exists="append", index=False)

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/core/generic.py in to_sql(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
   1343         sql.to_sql(self, name, con, flavor=flavor, schema=schema,
   1344                    if_exists=if_exists, index=index, index_label=index_label,
-> 1345                    chunksize=chunksize, dtype=dtype)
   1346 
   1347     def to_pickle(self, path, compression='infer'):

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)
    469     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,
    470                       index_label=index_label, schema=schema,
--> 471                       chunksize=chunksize, dtype=dtype)
    472 
    473 

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)
   1504                             dtype=dtype)
   1505         table.create()
-> 1506         table.insert(chunksize)
   1507 
   1508     def has_table(self, name, schema=None):

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in insert(self, chunksize)
    664 
    665                 chunk_iter = zip(*[arr[start_i:end_i] for arr in data_list])
--> 666                 self._execute_insert(conn, keys, chunk_iter)
    667 
    668     def _query_iterator(self, result, chunksize, columns, coerce_float=True,

/Users/zhaiyi/anaconda/lib/python3.6/site-packages/pandas/io/sql.py in _execute_insert(self, conn, keys, data_iter)
   1292     def _execute_insert(self, conn, keys, data_iter):
   1293         data_list = list(data_iter)
-> 1294         conn.executemany(self.insert_statement(), data_list)
   1295 
   1296     def _create_table_setup(self):

IntegrityError: FOREIGN KEY constraint failed



In [55]:

    
0.5*(1+2+0.9*0.315)









    Out[55]:





1.64175



In [ ]:

	0	1
0	772.0000	25
1	472.4356	25
2	-1360.0000	25
3	-519.7088	25
4	50.6000	25
5	156.0000	25
6	452.6000	25

	average_contribution	number_of_contributrs	candidate_last_name
0	50.6000	25	Clinton
1	-1360.0000	25	Giuliani
2	772.0000	25	Huckabee
3	452.6000	25	McCain
4	472.4356	25	Obama
5	-519.7088	25	Paul
6	156.0000	25	Romney

	id	first_name	last_name	middle_name	party
0	33	Joseph	Biden	NaN	D
1	36	Samuel	Brownback	NaN	R
2	34	Hillary	Clinton	R.	D
3	39	Christopher	Dodd	J.	D
4	26	John	Edwards	NaN	D
5	22	Rudolph	Giuliani	NaN	R
6	24	Mike	Gravel	NaN	D
7	16	Mike	Huckabee	NaN	R
8	30	Duncan	Hunter	NaN	R
9	31	Dennis	Kucinich	NaN	D
10	37	John	McCain	NaN	R
11	20	Barack	Obama	NaN	D
12	32	Ron	Paul	NaN	R
13	29	Bill	Richardson	NaN	D
14	35	Mitt	Romney	NaN	R
15	38	Tom	Tancredo	NaN	R
16	41	Fred	Thompson	D.	R

	id	last_name	first_name	middle_name	street_1	street_2	city	state	zip	amount	date	candidate_id
0	NaN	Agee	Steven	NaN	549 Laurel Branch Road	NaN	Floyd	VA	24091	500.0	2007-06-30	16
1	NaN	Ahrens	Don	NaN	4034 Rennellwood Way	NaN	Pleasanton	CA	94566	250.0	2007-05-16	16
2	NaN	Ahrens	Don	NaN	4034 Rennellwood Way	NaN	Pleasanton	CA	94566	50.0	2007-06-18	16
3	NaN	Ahrens	Don	NaN	4034 Rennellwood Way	NaN	Pleasanton	CA	94566	100.0	2007-06-21	16
4	NaN	Akin	Charles	NaN	10187 Sugar Creek Road	NaN	Bentonville	AR	72712	100.0	2007-06-16	16
5	NaN	Akin	Mike	NaN	181 Baywood Lane	NaN	Monticello	AR	71655	1500.0	2007-05-18	16
6	NaN	Akin	Rebecca	NaN	181 Baywood Lane	NaN	Monticello	AR	71655	500.0	2007-05-18	16
7	NaN	Aldridge	Brittni	NaN	808 Capitol Square Place, SW	NaN	Washington	DC	20024	250.0	2007-06-06	16
8	NaN	Allen	John D.	NaN	1052 Cannon Mill Drive	NaN	North Augusta	SC	29860	1000.0	2007-06-11	16
9	NaN	Allen	John D.	NaN	1052 Cannon Mill Drive	NaN	North Augusta	SC	29860	1300.0	2007-06-29	16
10	NaN	Allison	John W.	NaN	P.O. Box 1089	NaN	Conway	AR	72033	1000.0	2007-05-18	16
11	NaN	Allison	Rebecca	NaN	3206 Summit Court	NaN	Little Rock	AR	72227	1000.0	2007-04-25	16
12	NaN	Allison	Rebecca	NaN	3206 Summit Court	NaN	Little Rock	AR	72227	200.0	2007-06-12	16
13	NaN	Altes	R.D.	NaN	8600 Moody Road	NaN	Fort Smith	AR	72903	2300.0	2007-06-21	16
14	NaN	Andres	Dale	NaN	1160 Glen Oaks Drive	NaN	West Des Moines	IA	50266	250.0	2007-06-06	16
15	NaN	Anthony	John	NaN	211 Long Island Drive	NaN	Hot Springs	AR	71913	2300.0	2007-06-12	16
16	NaN	Arbogast	Robert	NaN	12900 State Route 56 SE	NaN	Mount Sterling	OH	43143	500.0	2007-04-08	16
17	NaN	Arbogast	Robert	NaN	12900 State Route 56 SE	NaN	Mount Sterling	OH	43143	100.0	2007-06-22	16
18	NaN	Ardle	William	NaN	412 Dakota Avenue	NaN	Springfield	OH	45504	50.0	2007-06-28	16
19	NaN	Atiq	Omar	NaN	7200 S Hazel Street	NaN	Pine Bluff	AR	71603	1000.0	2007-05-18	16
20	NaN	Atiq	Omar	NaN	7200 S Hazel Street	NaN	Pine Bluff	AR	71603	1000.0	2007-06-27	16
21	NaN	Baker	David	NaN	2550 Adamsbrooke Drive	NaN	Conway	AR	72034	2300.0	2007-04-11	16
22	NaN	Bancroft	David	NaN	2934 Broderick Street	NaN	San Francisco	CA	94123	250.0	2007-04-24	16
23	NaN	Banks	Charles	NaN	P.O. Box 251310	NaN	Little Rock	AR	72225	1000.0	2007-05-14	16
24	NaN	Barbee	John	NaN	516 Kellyridge Drive	NaN	Apex	NC	27502	500.0	2007-05-23	16
25	NaN	Buckler	Steve	NaN	24351 Armada Dr	NaN	Dana Point	CA	926291306	50.0	2007-07-30	20
26	NaN	Buckler	Steve	NaN	24351 Armada Dr	NaN	Dana Point	CA	926291306	25.0	2007-08-16	20
27	NaN	Buckheit	Bruce	NaN	8904 KAREN DR	NaN	FAIRFAX	VA	220312731	100.0	2007-09-19	20
28	NaN	Buckel	Linda	NaN	PO Box 683130	NaN	Park City	UT	840683130	2300.0	2007-08-14	20
29	NaN	Buckel	Linda	NaN	PO Box 683130	NaN	Park City	UT	840683130	-2300.0	2007-08-14	20
...	...	...	...	...	...	...	...	...	...	...	...	...
145	NaN	ABDELLA	THOMAS	M.	4231 MONUMENT WALL WAY #340	NaN	FAIRFAX	VA	220308440	50.0	2007-09-30	35
146	NaN	ABBOTT	WELDON	S.	777 EAST SOUTH TEMPLE 4E	NaN	SALT LAKE CITY	UT	841021269	100.0	2007-09-29	35
147	NaN	ABBOTT	WELDON	S.	777 EAST SOUTH TEMPLE 4E	NaN	SALT LAKE CITY	UT	841021269	50.0	2007-08-09	35
148	NaN	ABBOTT	GERALD	F.	389 BENEFIT STREET	NaN	PROVIDENCE	RI	29032946	100.0	2007-09-15	35
149	NaN	ABBOTT	GERALD	F.	389 BENEFIT STREET	NaN	PROVIDENCE	RI	29032946	100.0	2007-08-15	35
150	NaN	ABEDIN	ZAINUL	NaN	715 N. CENTRAL AVENUE	SUITE 212	GLENDALE	CA	912031164	500.0	2008-01-21	37
151	NaN	ABBOTT	SYBIL	F.	446 GAMES DRIVE	NaN	RENO	NV	895093326	75.0	2008-01-08	37
152	NaN	ABBOTT	SYBIL	F.	446 GAMES DRIVE	NaN	RENO	NV	895093326	50.0	2008-01-08	37
153	NaN	ABBOTT	RONALD	LEANDER	5453 HAWTHORNE STREET	NaN	MONTCLAIR	CA	917632551	200.0	2008-01-31	37
154	NaN	ABBOTT	RONALD	LEANDER	5453 HAWTHORNE STREET	NaN	MONTCLAIR	CA	917632551	100.0	2008-01-08	37
155	NaN	ABBOTT	ROBERT	A.	3061 LOREE ROAD	NaN	DECKERVILLE	MI	484279763	500.0	2008-01-21	37
156	NaN	ABBOTT	MIKE	E.	4516 OSPREY LNDG	NaN	NICEVILLE	FL	325786810	1000.0	2008-01-15	37
157	NaN	ABBOT	DAVID	M.	56 SALEM STREET	NaN	ANDOVER	MA	18102114	200.0	2008-01-21	37
158	NaN	ABBO	PAULINE	MORENCY	10720 JACOB LANE	NaN	WHITE LAKE	MI	483862274	35.0	2008-01-07	37
159	NaN	ABATE	MARIA	ELENA	1291 NIGHTINGALE AVENUE	NaN	MIAMI SPRINGS	FL	331663832	2600.0	2008-01-25	37
160	NaN	ABAIR	PETER	NaN	40 EVANS STREET	NaN	WATERTOWN	MA	24722150	25.0	2008-01-09	37
161	NaN	ABACHERLI	SHIRLEY	M.	29875 NEWPORT ROAD	NaN	MENIFEE	CA	925849524	150.0	2008-01-28	37
162	NaN	AARONS	CHARLES	NaN	1730 SHORE DRIVE	NaN	ANCHORAGE	AK	995153207	300.0	2008-01-30	37
163	NaN	AARONS	CHARLES	NaN	1730 SHORE DRIVE	NaN	ANCHORAGE	AK	995153207	410.0	2008-01-15	37
164	NaN	AARONS	CHARLES	NaN	1730 SHORE DRIVE	NaN	ANCHORAGE	AK	995153207	500.0	2008-01-09	37
165	NaN	ABEL	JOHN	H.	422 THOMAS STREET	NaN	BETHLEHEM	PA	180153316	200.0	2008-01-22	37
166	NaN	ABEL	MARLING	L.	14 HANGING MOSS LANE	NaN	GREENVILLE	SC	296155069	100.0	2008-01-22	37
167	NaN	ABEL	RUDOLPH	NaN	4532 OCEAN BLVD.	# 108	SARASOTA	FL	342421337	100.0	2008-01-08	37
168	NaN	ABELE	RODNEY	NaN	3620 METAIRIE HEIGHTS AVENUE	NaN	METAIRIE	LA	700021823	500.0	2008-01-15	37
169	NaN	ABERCROMBIE	DENIS	NaN	11811 WATER OAK CT	NaN	MAGNOLIA	TX	773546270	500.0	2008-01-30	37
170	NaN	ABESHAUS	MERRILL	M.	1801 N. HEREFORD DRIVE	NaN	FLAGSTAFF	AZ	860011121	120.0	2008-01-16	37
171	NaN	ABRAHAM	GEORGE	NaN	P.O. BOX 1504	NaN	LAKE CHARLES	LA	706021504	800.0	2008-01-17	37
172	NaN	ABRAHAMSON	PETER	J.	1030 W. ROSCOE STREET	NaN	CHICAGO	IL	606572207	50.0	2008-01-25	37
173	NaN	ABRAHAM	SALEM	A.	P.O. BOX 7	NaN	CANADIAN	TX	790140007	1000.0	2008-01-17	37
174	NaN	ABRAHAM	SALEM	A.	P.O. BOX 7	NaN	CANADIAN	TX	790140007	1300.0	2008-01-30	37