In [1]:
import sqlite3
import pandas as pd
pd.set_option('display.width', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.notebook_repr_html', True)
db = sqlite3.connect('L18DB_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)
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)
candidate_cols = [col[1] for col in cursor.execute("PRAGMA table_info(candidates)")]
query = '''SELECT * FROM candidates'''
viz_tables(candidate_cols, query)
query = '''SELECT * FROM candidates WHERE middle_init <> ""'''
viz_tables(candidate_cols, query)
print("{} candidates have a middle initial.".format(viz_tables(candidate_cols, query).shape[0]))
query = '''SELECT * FROM candidates ORDER BY id DESC'''
viz_tables(candidate_cols, query)
3 candidates have a middle initial.
Out[1]:
id
first_name
last_name
middle_init
party
0
41
Fred
Thompson
D.
R
1
39
Christopher
Dodd
J.
D
2
38
Tom
Tancredo
R
3
37
John
McCain
R
4
36
Samuel
Brownback
R
5
35
Mitt
Romney
R
6
34
Hillary
Clinton
R.
D
7
33
Joseph
Biden
D
8
32
Ron
Paul
R
9
31
Dennis
Kucinich
D
10
30
Duncan
Hunter
R
11
29
Bill
Richardson
D
12
26
John
Edwards
D
13
24
Mike
Gravel
D
14
22
Rudolph
Giuliani
R
15
20
Barack
Obama
D
16
16
Mike
Huckabee
R
In [2]:
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]
query = '''SELECT * FROM contributors WHERE state == "PA" '''
viz_tables(contributor_cols, query)
Out[2]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
71
BUCKLEY
WALTER
W.
1635 COUNTRY ROAD
BETHLEHEM
PA
180155718
-100.0
2008-03-05
22
1
72
BUCKLEY
MARJORIE
B.
1635 COUNTRY ROAD
BETHLEHEM
PA
180155718
-100.0
2008-03-05
22
2
94
Raught
Philip
M
4714 Plum Way
Pittsburgh
PA
15201
-1046.0
2008-04-21
32
3
95
Ferrara
Judith
D
1508 Waterford Road
Yardley
PA
19067
-1100.0
2008-04-21
32
4
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.0
2008-01-22
37
In [3]:
query = '''SELECT * FROM contributors WHERE amount > 1000 '''
viz_tables(contributor_cols, query)
Out[3]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
6
Akin
Mike
181 Baywood Lane
Monticello
AR
71655
1500.0
2007-05-18
16
1
10
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1300.0
2007-06-29
16
2
14
Altes
R.D.
8600 Moody Road
Fort Smith
AR
72903
2300.0
2007-06-21
16
3
16
Anthony
John
211 Long Island Drive
Hot Springs
AR
71913
2300.0
2007-06-12
16
4
22
Baker
David
2550 Adamsbrooke Drive
Conway
AR
72034
2300.0
2007-04-11
16
5
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
6
31
Buckel
Linda
PO Box 683130
Park City
UT
840683130
4600.0
2007-08-14
20
7
34
Buck
Blaine
M
45 Eaton Ave
Camden
ME
48431752
2300.0
2007-09-30
20
8
46
Buchanan
John
2025 NW 29th Rd
Boca Raton
FL
334316303
1300.0
2007-08-09
20
9
136
ABRAMOWITZ
NIRA
411 HARBOR ROAD
SOUTHPORT
CT
68901376
2300.0
2007-09-14
35
10
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.0
2008-01-25
37
11
175
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1300.0
2008-01-30
37
In [4]:
query = '''SELECT * FROM contributors WHERE state == "UT" and amount > 1000 '''
viz_tables(contributor_cols, query)
Out[4]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
1
31
Buckel
Linda
PO Box 683130
Park City
UT
840683130
4600.0
2007-08-14
20
In [5]:
query = '''SELECT * FROM contributors WHERE state == '' '''
viz_tables(contributor_cols, query)
Out[5]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
126
BOURNE
TRAVIS
LAGE KAART 77
BRASSCHATT
2930
-500.0
2008-11-20
35
In [6]:
query = '''SELECT * FROM contributors WHERE state IN ("WA","PA") '''
viz_tables(contributor_cols, query)
Out[6]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
63
BURKE
SUZANNE
M.
3401 EVANSTON
SEATTLE
WA
981038677
-700.0
2008-03-05
22
1
71
BUCKLEY
WALTER
W.
1635 COUNTRY ROAD
BETHLEHEM
PA
180155718
-100.0
2008-03-05
22
2
72
BUCKLEY
MARJORIE
B.
1635 COUNTRY ROAD
BETHLEHEM
PA
180155718
-100.0
2008-03-05
22
3
94
Raught
Philip
M
4714 Plum Way
Pittsburgh
PA
15201
-1046.0
2008-04-21
32
4
95
Ferrara
Judith
D
1508 Waterford Road
Yardley
PA
19067
-1100.0
2008-04-21
32
5
101
Aaronson
Rebecca
2000 Village Green Dr Apt 12
Mill Creek
WA
980125787
100.0
2008-02-08
34
6
107
Aaronson
Rebecca
2000 Village Green Dr Apt 12
Mill Creek
WA
980125787
100.0
2008-02-14
34
7
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.0
2008-01-22
37
In [7]:
query = '''SELECT * FROM contributors WHERE amount BETWEEN 100.00 and 200.00 '''
viz_tables(contributor_cols, query)
Out[7]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
4
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
100.0
2007-06-21
16
1
5
Akin
Charles
10187 Sugar Creek Road
Bentonville
AR
72712
100.0
2007-06-16
16
2
13
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
200.0
2007-06-12
16
3
18
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
100.0
2007-06-22
16
4
28
Buckheit
Bruce
8904 KAREN DR
FAIRFAX
VA
220312731
100.0
2007-09-19
20
5
32
Buck
Thomas
4206 Terrace Street
Kansas City
MO
64111
100.0
2007-09-25
20
6
33
Buck
Jay
K.
1855 Old Willow Rd Unit 322
Northfield
IL
600932918
200.0
2007-09-12
20
7
38
Bucher
Ida
M
1400 Warnall Ave
Los Angeles
CA
900245333
100.0
2007-07-10
20
8
47
Buchanan
John
2025 NW 29th Rd
Boca Raton
FL
334316303
200.0
2007-08-14
20
9
101
Aaronson
Rebecca
2000 Village Green Dr Apt 12
Mill Creek
WA
980125787
100.0
2008-02-08
34
10
104
Aarons
Elaine
481 Buck Island Rd Apt 17A
APT 17A
West Yarmouth
MA
26733300
100.0
2008-02-08
34
11
106
Aaron
Shirley
101 Cherry Ave
Havana
FL
323331311
100.0
2008-02-29
34
12
107
Aaronson
Rebecca
2000 Village Green Dr Apt 12
Mill Creek
WA
980125787
100.0
2008-02-14
34
13
108
Aaron
Shirley
101 Cherry Ave
Havana
FL
323331311
100.0
2008-02-24
34
14
109
Aaron
Shirley
101 Cherry Ave
Havana
FL
323331311
100.0
2008-02-22
34
15
110
Aaron
Shirley
101 Cherry Ave
Havana
FL
323331311
100.0
2008-02-17
34
16
113
Aaron
Shirley
101 Cherry Ave
Havana
FL
323331311
100.0
2008-02-08
34
17
114
Aaron
Shirley
101 Cherry Ave
Havana
FL
323331311
100.0
2008-02-03
34
18
116
Aaron
Patricia
418 NW 35th St
Oklahoma City
OK
731188602
200.0
2008-02-26
34
19
117
Aaron
Patricia
418 NW 35th St
Oklahoma City
OK
731188602
100.0
2008-02-12
34
20
119
Aaron
Jim
2178 Fairway Cir
Canton
MI
481885097
200.0
2008-02-29
34
21
122
Aaron
Carole
PO Box 1806
Ogunquit
ME
39071806
100.0
2008-02-03
34
22
125
Aanonsen
Lin
897 Raymond Ave
Saint Paul
MN
551141508
100.0
2008-02-08
34
23
130
ABTS
HENRY
P. O. BOX 7299
INCLINE VILLAGE
NV
894527299
100.0
2007-07-13
35
24
134
ABREU
KEVIN
M.
1305 GARDEN GLEN LANE
PEARLAND
TX
775816547
150.0
2007-08-09
35
25
147
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
100.0
2007-09-29
35
26
149
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-09-15
35
27
150
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-08-15
35
28
154
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
200.0
2008-01-31
37
29
155
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
100.0
2008-01-08
37
30
158
ABBOT
DAVID
M.
56 SALEM STREET
ANDOVER
MA
18102114
200.0
2008-01-21
37
31
162
ABACHERLI
SHIRLEY
M.
29875 NEWPORT ROAD
MENIFEE
CA
925849524
150.0
2008-01-28
37
32
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.0
2008-01-22
37
33
167
ABEL
MARLING
L.
14 HANGING MOSS LANE
GREENVILLE
SC
296155069
100.0
2008-01-22
37
34
168
ABEL
RUDOLPH
4532 OCEAN BLVD.
# 108
SARASOTA
FL
342421337
100.0
2008-01-08
37
35
171
ABESHAUS
MERRILL
M.
1801 N. HEREFORD DRIVE
FLAGSTAFF
AZ
860011121
120.0
2008-01-16
37
In [8]:
query='''SELECT * from contributors ORDER BY last_name DESC'''
viz_tables(contributor_cols, query)
Out[8]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
81
Waddell
James
L.
1823 Spel Lane SW
Rochester
MN
55902
-28.00
2008-04-21
32
1
84
Verster
Jeanette
M.
7220 SW 61st St
Miami
FL
331431807
-1000.00
2008-04-21
32
2
85
Uihlein
Richard
1396 N Waukegan Rd
Lake Forest
IL
600451147
-2300.00
2008-04-21
32
3
128
TOLLESTRUP
TRAVIS
W.
16331 WINECREEK RD.
SAN DIEGO
CA
92127
-1000.00
2008-05-15
35
4
76
Schuff
Bryan
1700 W Sweden Rd
Brockport
NY
14420
-25.00
2008-08-22
32
5
97
Sanford
Bradley
940 Post St #43
San Francisco
CA
94109
-24.53
2008-04-21
32
6
127
SECRIST
BRIAN
L.
3 MULE DEER TRAIL
LITTLETON
CO
801275722
-1000.00
2008-04-07
35
7
90
Rozenfeld
Timur
57 Herbert Road
Robbinsville
NJ
8691
-777.95
2008-04-21
32
8
111
Reid
Elizabeth
73 W Patent Rd
OPHIR FARM NORTH
Bedford Hills
NY
105072222
-350.00
2008-08-28
34
9
112
Reich
Thomas
499 Park Ave
New York
NY
100221240
-2300.00
2008-08-28
34
10
94
Raught
Philip
M
4714 Plum Way
Pittsburgh
PA
15201
-1046.00
2008-04-21
32
11
78
Ranganath
Anoop
2507 Willard Drive
Charlottesville
VA
22903
-100.00
2008-04-21
32
12
93
Plummer
Joseph
587 Blake Hill Rd
New Hampton
NH
32564424
-24.60
2008-04-21
32
13
89
Perreault
Louise
503 Brockridge Hunt Drive
Hampton
VA
23666
-34.08
2008-04-21
32
14
79
Nystrom
Michael
A
93A Fairmont Street
Arlington
MA
2474
-503.00
2008-04-21
32
15
80
Muse
Nina
Jo
2915 Toro Canyon Rd
Austin
TX
78746
-50.00
2008-04-21
32
16
92
Lehner
Thomas
S.
2701 Star Lane
Wadsworth
OH
44281
-200.00
2008-04-21
32
17
83
Kuehn
David
14502 West 93rd Street
Lenexa
KS
66215
-330.00
2008-04-21
32
18
91
Kazor
Christopher
M
707 Spindletree ave
Naperville
IL
60565
-2592.00
2008-04-21
32
19
96
Johnson
Cathleen
E.
1003 Justin Ln Apt 2016
Austin
TX
787572648
-14.76
2008-04-21
32
20
100
Jacobs
Richard
G.
14337 Tawya Rd
Apple Valley
CA
923075545
-1000.00
2008-04-21
32
21
77
Hobbs
James
229 Cherry Lane
White House
TN
37188
-25.00
2008-08-19
32
22
50
Harrison
Ryan
2247 3rd St
La Verne
CA
917504918
25.00
2007-07-26
20
23
98
Gaarder
Bruce
PO Box 4085
Mountain Home AFB
ID
83648
-261.00
2008-04-21
32
24
87
Froehling
Alan
L.
302 Broadway St
Mount Vernon
IL
628645116
-844.80
2008-04-21
32
25
95
Ferrara
Judith
D
1508 Waterford Road
Yardley
PA
19067
-1100.00
2008-04-21
32
26
86
Eskenberry
Robert
P
10960 Gray Cir
Westminster
CO
80020
-223.00
2008-04-21
32
27
88
Duryea
Marcia
A.
123 Bayview Ave
Amityville
NY
11701
-299.50
2008-04-21
32
28
99
Choe
Hyeokchan
207 Bridle Way
Fort Lee
NJ
70246302
-39.50
2008-04-21
32
29
26
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
50.00
2007-07-30
20
...
...
...
...
...
...
...
...
...
...
...
...
...
145
169
ABELE
RODNEY
3620 METAIRIE HEIGHTS AVENUE
METAIRIE
LA
700021823
500.00
2008-01-15
37
146
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.00
2008-01-22
37
147
167
ABEL
MARLING
L.
14 HANGING MOSS LANE
GREENVILLE
SC
296155069
100.00
2008-01-22
37
148
168
ABEL
RUDOLPH
4532 OCEAN BLVD.
# 108
SARASOTA
FL
342421337
100.00
2008-01-08
37
149
140
ABEGG
PATRICIA
T.
1862 E. 5150 S.
SALT LAKE CITY
UT
841176911
75.00
2007-09-25
35
150
141
ABEGG
PATRICIA
T.
1862 E. 5150 S.
SALT LAKE CITY
UT
841176911
25.00
2007-09-17
35
151
142
ABEGG
PATRICIA
T.
1862 E. 5150 S.
SALT LAKE CITY
UT
841176911
75.00
2007-08-31
35
152
143
ABEGG
PATRICIA
T.
1862 E. 5150 S.
SALT LAKE CITY
UT
841176911
75.00
2007-08-14
35
153
144
ABEGG
PATRICIA
T.
1862 E. 5150 S.
SALT LAKE CITY
UT
841176911
25.00
2007-08-06
35
154
145
ABEGG
PATRICIA
T.
1862 E. 5150 S.
SALT LAKE CITY
UT
841176911
25.00
2007-07-10
35
155
151
ABEDIN
ZAINUL
715 N. CENTRAL AVENUE
SUITE 212
GLENDALE
CA
912031164
500.00
2008-01-21
37
156
146
ABDELLA
THOMAS
M.
4231 MONUMENT WALL WAY #340
FAIRFAX
VA
220308440
50.00
2007-09-30
35
157
147
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
100.00
2007-09-29
35
158
148
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
50.00
2007-08-09
35
159
149
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.00
2007-09-15
35
160
150
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.00
2007-08-15
35
161
152
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
75.00
2008-01-08
37
162
153
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
50.00
2008-01-08
37
163
154
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
200.00
2008-01-31
37
164
155
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
100.00
2008-01-08
37
165
156
ABBOTT
ROBERT
A.
3061 LOREE ROAD
DECKERVILLE
MI
484279763
500.00
2008-01-21
37
166
157
ABBOTT
MIKE
E.
4516 OSPREY LNDG
NICEVILLE
FL
325786810
1000.00
2008-01-15
37
167
158
ABBOT
DAVID
M.
56 SALEM STREET
ANDOVER
MA
18102114
200.00
2008-01-21
37
168
159
ABBO
PAULINE
MORENCY
10720 JACOB LANE
WHITE LAKE
MI
483862274
35.00
2008-01-07
37
169
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.00
2008-01-25
37
170
161
ABAIR
PETER
40 EVANS STREET
WATERTOWN
MA
24722150
25.00
2008-01-09
37
171
162
ABACHERLI
SHIRLEY
M.
29875 NEWPORT ROAD
MENIFEE
CA
925849524
150.00
2008-01-28
37
172
163
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
300.00
2008-01-30
37
173
164
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
410.00
2008-01-15
37
174
165
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
500.00
2008-01-09
37
175 rows × 12 columns
In [9]:
query='''SELECT * from contributors WHERE amount BETWEEN 1000 and 5000 ORDER BY amount DESC '''
viz_tables(contributor_cols, query)
Out[9]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
31
Buckel
Linda
PO Box 683130
Park City
UT
840683130
4600.0
2007-08-14
20
1
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.0
2008-01-25
37
2
14
Altes
R.D.
8600 Moody Road
Fort Smith
AR
72903
2300.0
2007-06-21
16
3
16
Anthony
John
211 Long Island Drive
Hot Springs
AR
71913
2300.0
2007-06-12
16
4
22
Baker
David
2550 Adamsbrooke Drive
Conway
AR
72034
2300.0
2007-04-11
16
5
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
6
34
Buck
Blaine
M
45 Eaton Ave
Camden
ME
48431752
2300.0
2007-09-30
20
7
136
ABRAMOWITZ
NIRA
411 HARBOR ROAD
SOUTHPORT
CT
68901376
2300.0
2007-09-14
35
8
6
Akin
Mike
181 Baywood Lane
Monticello
AR
71655
1500.0
2007-05-18
16
9
10
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1300.0
2007-06-29
16
10
46
Buchanan
John
2025 NW 29th Rd
Boca Raton
FL
334316303
1300.0
2007-08-09
20
11
175
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1300.0
2008-01-30
37
12
9
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1000.0
2007-06-11
16
13
11
Allison
John W.
P.O. Box 1089
Conway
AR
72033
1000.0
2007-05-18
16
14
12
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
1000.0
2007-04-25
16
15
20
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-05-18
16
16
21
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-06-27
16
17
24
Banks
Charles
P.O. Box 251310
Little Rock
AR
72225
1000.0
2007-05-14
16
18
45
Buchanan
John
2025 NW 29th Rd
Boca Raton
FL
334316303
1000.0
2007-08-28
20
19
123
Aaron
Barbara
2298 Pacific Ave # 6
San Francisco
CA
941151435
1000.0
2008-02-11
34
20
139
ABOUBAKARE
NASAR
1400 SAN MIGUEL DRIVE
CORONA DEL MAR
CA
926251300
1000.0
2007-07-09
35
21
157
ABBOTT
MIKE
E.
4516 OSPREY LNDG
NICEVILLE
FL
325786810
1000.0
2008-01-15
37
22
174
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1000.0
2008-01-17
37
In [10]:
query='''SELECT * from contributors WHERE amount BETWEEN 1000 and 5000 ORDER BY candidate_id, amount DESC '''
viz_tables(contributor_cols, query)
Out[10]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
0
14
Altes
R.D.
8600 Moody Road
Fort Smith
AR
72903
2300.0
2007-06-21
16
1
16
Anthony
John
211 Long Island Drive
Hot Springs
AR
71913
2300.0
2007-06-12
16
2
22
Baker
David
2550 Adamsbrooke Drive
Conway
AR
72034
2300.0
2007-04-11
16
3
6
Akin
Mike
181 Baywood Lane
Monticello
AR
71655
1500.0
2007-05-18
16
4
10
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1300.0
2007-06-29
16
5
9
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1000.0
2007-06-11
16
6
11
Allison
John W.
P.O. Box 1089
Conway
AR
72033
1000.0
2007-05-18
16
7
12
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
1000.0
2007-04-25
16
8
20
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-05-18
16
9
21
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-06-27
16
10
24
Banks
Charles
P.O. Box 251310
Little Rock
AR
72225
1000.0
2007-05-14
16
11
31
Buckel
Linda
PO Box 683130
Park City
UT
840683130
4600.0
2007-08-14
20
12
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
13
34
Buck
Blaine
M
45 Eaton Ave
Camden
ME
48431752
2300.0
2007-09-30
20
14
46
Buchanan
John
2025 NW 29th Rd
Boca Raton
FL
334316303
1300.0
2007-08-09
20
15
45
Buchanan
John
2025 NW 29th Rd
Boca Raton
FL
334316303
1000.0
2007-08-28
20
16
123
Aaron
Barbara
2298 Pacific Ave # 6
San Francisco
CA
941151435
1000.0
2008-02-11
34
17
136
ABRAMOWITZ
NIRA
411 HARBOR ROAD
SOUTHPORT
CT
68901376
2300.0
2007-09-14
35
18
139
ABOUBAKARE
NASAR
1400 SAN MIGUEL DRIVE
CORONA DEL MAR
CA
926251300
1000.0
2007-07-09
35
19
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.0
2008-01-25
37
20
175
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1300.0
2008-01-30
37
21
157
ABBOTT
MIKE
E.
4516 OSPREY LNDG
NICEVILLE
FL
325786810
1000.0
2008-01-15
37
22
174
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1000.0
2008-01-17
37
In [11]:
query='''SELECT DISTINCT first_name, last_name from contributors'''
viz_tables(["first_name","last_name"], query)
Out[11]:
first_name
last_name
0
Steven
Agee
1
Don
Ahrens
2
Charles
Akin
3
Mike
Akin
4
Rebecca
Akin
5
Brittni
Aldridge
6
John D.
Allen
7
John W.
Allison
8
Rebecca
Allison
9
R.D.
Altes
10
Dale
Andres
11
John
Anthony
12
Robert
Arbogast
13
William
Ardle
14
Omar
Atiq
15
David
Baker
16
David
Bancroft
17
Charles
Banks
18
John
Barbee
19
Steve
Buckler
20
Bruce
Buckheit
21
Linda
Buckel
22
Thomas
Buck
23
Jay
Buck
24
Blaine
Buck
25
Barbara
Buck
26
Mark M
Buchman
27
Ida
Bucher
28
Elizabeth
Buchanek
29
John
Buchanan
...
...
...
96
DIANA
ABSHIER
97
KEVIN
ABREU
98
NIRA
ABRAMOWITZ
99
MICHAEL
ABRAMS
100
KEN
ABRAMOWITZ
101
NASAR
ABOUBAKARE
102
PATRICIA
ABEGG
103
THOMAS
ABDELLA
104
WELDON
ABBOTT
105
GERALD
ABBOTT
106
ZAINUL
ABEDIN
107
SYBIL
ABBOTT
108
RONALD
ABBOTT
109
ROBERT
ABBOTT
110
MIKE
ABBOTT
111
DAVID
ABBOT
112
PAULINE
ABBO
113
MARIA
ABATE
114
PETER
ABAIR
115
SHIRLEY
ABACHERLI
116
CHARLES
AARONS
117
JOHN
ABEL
118
MARLING
ABEL
119
RUDOLPH
ABEL
120
RODNEY
ABELE
121
DENIS
ABERCROMBIE
122
MERRILL
ABESHAUS
123
GEORGE
ABRAHAM
124
PETER
ABRAHAMSON
125
SALEM
ABRAHAM
126 rows × 2 columns
In [12]:
cursor.execute('''ALTER TABLE contributors ADD COLUMN full_name TEXT''')
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")]
viz_tables(contributor_cols, '''SELECT * FROM contributors''')
Out[12]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
full_name
0
1
Agee
Steven
549 Laurel Branch Road
Floyd
VA
24091
500.0
2007-06-30
16
None
1
2
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
250.0
2007-05-16
16
None
2
3
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
50.0
2007-06-18
16
None
3
4
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
100.0
2007-06-21
16
None
4
5
Akin
Charles
10187 Sugar Creek Road
Bentonville
AR
72712
100.0
2007-06-16
16
None
5
6
Akin
Mike
181 Baywood Lane
Monticello
AR
71655
1500.0
2007-05-18
16
None
6
7
Akin
Rebecca
181 Baywood Lane
Monticello
AR
71655
500.0
2007-05-18
16
None
7
8
Aldridge
Brittni
808 Capitol Square Place, SW
Washington
DC
20024
250.0
2007-06-06
16
None
8
9
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1000.0
2007-06-11
16
None
9
10
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1300.0
2007-06-29
16
None
10
11
Allison
John W.
P.O. Box 1089
Conway
AR
72033
1000.0
2007-05-18
16
None
11
12
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
1000.0
2007-04-25
16
None
12
13
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
200.0
2007-06-12
16
None
13
14
Altes
R.D.
8600 Moody Road
Fort Smith
AR
72903
2300.0
2007-06-21
16
None
14
15
Andres
Dale
1160 Glen Oaks Drive
West Des Moines
IA
50266
250.0
2007-06-06
16
None
15
16
Anthony
John
211 Long Island Drive
Hot Springs
AR
71913
2300.0
2007-06-12
16
None
16
17
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
500.0
2007-04-08
16
None
17
18
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
100.0
2007-06-22
16
None
18
19
Ardle
William
412 Dakota Avenue
Springfield
OH
45504
50.0
2007-06-28
16
None
19
20
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-05-18
16
None
20
21
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-06-27
16
None
21
22
Baker
David
2550 Adamsbrooke Drive
Conway
AR
72034
2300.0
2007-04-11
16
None
22
23
Bancroft
David
2934 Broderick Street
San Francisco
CA
94123
250.0
2007-04-24
16
None
23
24
Banks
Charles
P.O. Box 251310
Little Rock
AR
72225
1000.0
2007-05-14
16
None
24
25
Barbee
John
516 Kellyridge Drive
Apex
NC
27502
500.0
2007-05-23
16
None
25
26
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
50.0
2007-07-30
20
None
26
27
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
25.0
2007-08-16
20
None
27
28
Buckheit
Bruce
8904 KAREN DR
FAIRFAX
VA
220312731
100.0
2007-09-19
20
None
28
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
None
29
30
Buckel
Linda
PO Box 683130
Park City
UT
840683130
-2300.0
2007-08-14
20
None
...
...
...
...
...
...
...
...
...
...
...
...
...
...
145
146
ABDELLA
THOMAS
M.
4231 MONUMENT WALL WAY #340
FAIRFAX
VA
220308440
50.0
2007-09-30
35
None
146
147
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
100.0
2007-09-29
35
None
147
148
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
50.0
2007-08-09
35
None
148
149
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-09-15
35
None
149
150
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-08-15
35
None
150
151
ABEDIN
ZAINUL
715 N. CENTRAL AVENUE
SUITE 212
GLENDALE
CA
912031164
500.0
2008-01-21
37
None
151
152
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
75.0
2008-01-08
37
None
152
153
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
50.0
2008-01-08
37
None
153
154
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
200.0
2008-01-31
37
None
154
155
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
100.0
2008-01-08
37
None
155
156
ABBOTT
ROBERT
A.
3061 LOREE ROAD
DECKERVILLE
MI
484279763
500.0
2008-01-21
37
None
156
157
ABBOTT
MIKE
E.
4516 OSPREY LNDG
NICEVILLE
FL
325786810
1000.0
2008-01-15
37
None
157
158
ABBOT
DAVID
M.
56 SALEM STREET
ANDOVER
MA
18102114
200.0
2008-01-21
37
None
158
159
ABBO
PAULINE
MORENCY
10720 JACOB LANE
WHITE LAKE
MI
483862274
35.0
2008-01-07
37
None
159
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.0
2008-01-25
37
None
160
161
ABAIR
PETER
40 EVANS STREET
WATERTOWN
MA
24722150
25.0
2008-01-09
37
None
161
162
ABACHERLI
SHIRLEY
M.
29875 NEWPORT ROAD
MENIFEE
CA
925849524
150.0
2008-01-28
37
None
162
163
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
300.0
2008-01-30
37
None
163
164
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
410.0
2008-01-15
37
None
164
165
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
500.0
2008-01-09
37
None
165
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.0
2008-01-22
37
None
166
167
ABEL
MARLING
L.
14 HANGING MOSS LANE
GREENVILLE
SC
296155069
100.0
2008-01-22
37
None
167
168
ABEL
RUDOLPH
4532 OCEAN BLVD.
# 108
SARASOTA
FL
342421337
100.0
2008-01-08
37
None
168
169
ABELE
RODNEY
3620 METAIRIE HEIGHTS AVENUE
METAIRIE
LA
700021823
500.0
2008-01-15
37
None
169
170
ABERCROMBIE
DENIS
11811 WATER OAK CT
MAGNOLIA
TX
773546270
500.0
2008-01-30
37
None
170
171
ABESHAUS
MERRILL
M.
1801 N. HEREFORD DRIVE
FLAGSTAFF
AZ
860011121
120.0
2008-01-16
37
None
171
172
ABRAHAM
GEORGE
P.O. BOX 1504
LAKE CHARLES
LA
706021504
800.0
2008-01-17
37
None
172
173
ABRAHAMSON
PETER
J.
1030 W. ROSCOE STREET
CHICAGO
IL
606572207
50.0
2008-01-25
37
None
173
174
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1000.0
2008-01-17
37
None
174
175
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1300.0
2008-01-30
37
None
175 rows × 13 columns
In [13]:
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")] # regenerate columns with full_name
query = '''SELECT id, last_name, first_name FROM contributors''' # Select a few columns
full_name_id= [(attr[1] + ", " + attr[2],attr[0]) for attr in cursor.execute(query).fetchall()] # List of tuples: (full_name, id)
update = '''UPDATE contributors SET full_name = ? WHERE id=?''' # Update the table
for row in full_name_id:
cursor.execute(update, row)
query = '''SELECT * FROM contributors'''
viz_tables(contributor_cols, query)
Out[13]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
full_name
0
1
Agee
Steven
549 Laurel Branch Road
Floyd
VA
24091
500.0
2007-06-30
16
Agee, Steven
1
2
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
250.0
2007-05-16
16
Ahrens, Don
2
3
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
50.0
2007-06-18
16
Ahrens, Don
3
4
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
100.0
2007-06-21
16
Ahrens, Don
4
5
Akin
Charles
10187 Sugar Creek Road
Bentonville
AR
72712
100.0
2007-06-16
16
Akin, Charles
5
6
Akin
Mike
181 Baywood Lane
Monticello
AR
71655
1500.0
2007-05-18
16
Akin, Mike
6
7
Akin
Rebecca
181 Baywood Lane
Monticello
AR
71655
500.0
2007-05-18
16
Akin, Rebecca
7
8
Aldridge
Brittni
808 Capitol Square Place, SW
Washington
DC
20024
250.0
2007-06-06
16
Aldridge, Brittni
8
9
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1000.0
2007-06-11
16
Allen, John D.
9
10
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1300.0
2007-06-29
16
Allen, John D.
10
11
Allison
John W.
P.O. Box 1089
Conway
AR
72033
1000.0
2007-05-18
16
Allison, John W.
11
12
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
1000.0
2007-04-25
16
Allison, Rebecca
12
13
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
200.0
2007-06-12
16
Allison, Rebecca
13
14
Altes
R.D.
8600 Moody Road
Fort Smith
AR
72903
2300.0
2007-06-21
16
Altes, R.D.
14
15
Andres
Dale
1160 Glen Oaks Drive
West Des Moines
IA
50266
250.0
2007-06-06
16
Andres, Dale
15
16
Anthony
John
211 Long Island Drive
Hot Springs
AR
71913
2300.0
2007-06-12
16
Anthony, John
16
17
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
500.0
2007-04-08
16
Arbogast, Robert
17
18
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
100.0
2007-06-22
16
Arbogast, Robert
18
19
Ardle
William
412 Dakota Avenue
Springfield
OH
45504
50.0
2007-06-28
16
Ardle, William
19
20
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-05-18
16
Atiq, Omar
20
21
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-06-27
16
Atiq, Omar
21
22
Baker
David
2550 Adamsbrooke Drive
Conway
AR
72034
2300.0
2007-04-11
16
Baker, David
22
23
Bancroft
David
2934 Broderick Street
San Francisco
CA
94123
250.0
2007-04-24
16
Bancroft, David
23
24
Banks
Charles
P.O. Box 251310
Little Rock
AR
72225
1000.0
2007-05-14
16
Banks, Charles
24
25
Barbee
John
516 Kellyridge Drive
Apex
NC
27502
500.0
2007-05-23
16
Barbee, John
25
26
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
50.0
2007-07-30
20
Buckler, Steve
26
27
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
25.0
2007-08-16
20
Buckler, Steve
27
28
Buckheit
Bruce
8904 KAREN DR
FAIRFAX
VA
220312731
100.0
2007-09-19
20
Buckheit, Bruce
28
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
Buckel, Linda
29
30
Buckel
Linda
PO Box 683130
Park City
UT
840683130
-2300.0
2007-08-14
20
Buckel, Linda
...
...
...
...
...
...
...
...
...
...
...
...
...
...
145
146
ABDELLA
THOMAS
M.
4231 MONUMENT WALL WAY #340
FAIRFAX
VA
220308440
50.0
2007-09-30
35
ABDELLA, THOMAS
146
147
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
100.0
2007-09-29
35
ABBOTT, WELDON
147
148
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
50.0
2007-08-09
35
ABBOTT, WELDON
148
149
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-09-15
35
ABBOTT, GERALD
149
150
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-08-15
35
ABBOTT, GERALD
150
151
ABEDIN
ZAINUL
715 N. CENTRAL AVENUE
SUITE 212
GLENDALE
CA
912031164
500.0
2008-01-21
37
ABEDIN, ZAINUL
151
152
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
75.0
2008-01-08
37
ABBOTT, SYBIL
152
153
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
50.0
2008-01-08
37
ABBOTT, SYBIL
153
154
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
200.0
2008-01-31
37
ABBOTT, RONALD
154
155
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
100.0
2008-01-08
37
ABBOTT, RONALD
155
156
ABBOTT
ROBERT
A.
3061 LOREE ROAD
DECKERVILLE
MI
484279763
500.0
2008-01-21
37
ABBOTT, ROBERT
156
157
ABBOTT
MIKE
E.
4516 OSPREY LNDG
NICEVILLE
FL
325786810
1000.0
2008-01-15
37
ABBOTT, MIKE
157
158
ABBOT
DAVID
M.
56 SALEM STREET
ANDOVER
MA
18102114
200.0
2008-01-21
37
ABBOT, DAVID
158
159
ABBO
PAULINE
MORENCY
10720 JACOB LANE
WHITE LAKE
MI
483862274
35.0
2008-01-07
37
ABBO, PAULINE
159
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.0
2008-01-25
37
ABATE, MARIA
160
161
ABAIR
PETER
40 EVANS STREET
WATERTOWN
MA
24722150
25.0
2008-01-09
37
ABAIR, PETER
161
162
ABACHERLI
SHIRLEY
M.
29875 NEWPORT ROAD
MENIFEE
CA
925849524
150.0
2008-01-28
37
ABACHERLI, SHIRLEY
162
163
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
300.0
2008-01-30
37
AARONS, CHARLES
163
164
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
410.0
2008-01-15
37
AARONS, CHARLES
164
165
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
500.0
2008-01-09
37
AARONS, CHARLES
165
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.0
2008-01-22
37
ABEL, JOHN
166
167
ABEL
MARLING
L.
14 HANGING MOSS LANE
GREENVILLE
SC
296155069
100.0
2008-01-22
37
ABEL, MARLING
167
168
ABEL
RUDOLPH
4532 OCEAN BLVD.
# 108
SARASOTA
FL
342421337
100.0
2008-01-08
37
ABEL, RUDOLPH
168
169
ABELE
RODNEY
3620 METAIRIE HEIGHTS AVENUE
METAIRIE
LA
700021823
500.0
2008-01-15
37
ABELE, RODNEY
169
170
ABERCROMBIE
DENIS
11811 WATER OAK CT
MAGNOLIA
TX
773546270
500.0
2008-01-30
37
ABERCROMBIE, DENIS
170
171
ABESHAUS
MERRILL
M.
1801 N. HEREFORD DRIVE
FLAGSTAFF
AZ
860011121
120.0
2008-01-16
37
ABESHAUS, MERRILL
171
172
ABRAHAM
GEORGE
P.O. BOX 1504
LAKE CHARLES
LA
706021504
800.0
2008-01-17
37
ABRAHAM, GEORGE
172
173
ABRAHAMSON
PETER
J.
1030 W. ROSCOE STREET
CHICAGO
IL
606572207
50.0
2008-01-25
37
ABRAHAMSON, PETER
173
174
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1000.0
2008-01-17
37
ABRAHAM, SALEM
174
175
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1300.0
2008-01-30
37
ABRAHAM, SALEM
175 rows × 13 columns
In [14]:
update = '''UPDATE contributors SET full_name = "Too Much" WHERE amount>10000'''
cursor.execute(update)
viz_tables(contributor_cols, query)
Out[14]:
id
last_name
first_name
middle_name
street_1
street_2
city
state
zip
amount
date
candidate_id
full_name
0
1
Agee
Steven
549 Laurel Branch Road
Floyd
VA
24091
500.0
2007-06-30
16
Agee, Steven
1
2
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
250.0
2007-05-16
16
Ahrens, Don
2
3
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
50.0
2007-06-18
16
Ahrens, Don
3
4
Ahrens
Don
4034 Rennellwood Way
Pleasanton
CA
94566
100.0
2007-06-21
16
Ahrens, Don
4
5
Akin
Charles
10187 Sugar Creek Road
Bentonville
AR
72712
100.0
2007-06-16
16
Akin, Charles
5
6
Akin
Mike
181 Baywood Lane
Monticello
AR
71655
1500.0
2007-05-18
16
Akin, Mike
6
7
Akin
Rebecca
181 Baywood Lane
Monticello
AR
71655
500.0
2007-05-18
16
Akin, Rebecca
7
8
Aldridge
Brittni
808 Capitol Square Place, SW
Washington
DC
20024
250.0
2007-06-06
16
Aldridge, Brittni
8
9
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1000.0
2007-06-11
16
Allen, John D.
9
10
Allen
John D.
1052 Cannon Mill Drive
North Augusta
SC
29860
1300.0
2007-06-29
16
Allen, John D.
10
11
Allison
John W.
P.O. Box 1089
Conway
AR
72033
1000.0
2007-05-18
16
Allison, John W.
11
12
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
1000.0
2007-04-25
16
Allison, Rebecca
12
13
Allison
Rebecca
3206 Summit Court
Little Rock
AR
72227
200.0
2007-06-12
16
Allison, Rebecca
13
14
Altes
R.D.
8600 Moody Road
Fort Smith
AR
72903
2300.0
2007-06-21
16
Altes, R.D.
14
15
Andres
Dale
1160 Glen Oaks Drive
West Des Moines
IA
50266
250.0
2007-06-06
16
Andres, Dale
15
16
Anthony
John
211 Long Island Drive
Hot Springs
AR
71913
2300.0
2007-06-12
16
Anthony, John
16
17
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
500.0
2007-04-08
16
Arbogast, Robert
17
18
Arbogast
Robert
12900 State Route 56 SE
Mount Sterling
OH
43143
100.0
2007-06-22
16
Arbogast, Robert
18
19
Ardle
William
412 Dakota Avenue
Springfield
OH
45504
50.0
2007-06-28
16
Ardle, William
19
20
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-05-18
16
Atiq, Omar
20
21
Atiq
Omar
7200 S Hazel Street
Pine Bluff
AR
71603
1000.0
2007-06-27
16
Atiq, Omar
21
22
Baker
David
2550 Adamsbrooke Drive
Conway
AR
72034
2300.0
2007-04-11
16
Baker, David
22
23
Bancroft
David
2934 Broderick Street
San Francisco
CA
94123
250.0
2007-04-24
16
Bancroft, David
23
24
Banks
Charles
P.O. Box 251310
Little Rock
AR
72225
1000.0
2007-05-14
16
Banks, Charles
24
25
Barbee
John
516 Kellyridge Drive
Apex
NC
27502
500.0
2007-05-23
16
Barbee, John
25
26
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
50.0
2007-07-30
20
Buckler, Steve
26
27
Buckler
Steve
24351 Armada Dr
Dana Point
CA
926291306
25.0
2007-08-16
20
Buckler, Steve
27
28
Buckheit
Bruce
8904 KAREN DR
FAIRFAX
VA
220312731
100.0
2007-09-19
20
Buckheit, Bruce
28
29
Buckel
Linda
PO Box 683130
Park City
UT
840683130
2300.0
2007-08-14
20
Buckel, Linda
29
30
Buckel
Linda
PO Box 683130
Park City
UT
840683130
-2300.0
2007-08-14
20
Buckel, Linda
...
...
...
...
...
...
...
...
...
...
...
...
...
...
145
146
ABDELLA
THOMAS
M.
4231 MONUMENT WALL WAY #340
FAIRFAX
VA
220308440
50.0
2007-09-30
35
ABDELLA, THOMAS
146
147
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
100.0
2007-09-29
35
ABBOTT, WELDON
147
148
ABBOTT
WELDON
S.
777 EAST SOUTH TEMPLE 4E
SALT LAKE CITY
UT
841021269
50.0
2007-08-09
35
ABBOTT, WELDON
148
149
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-09-15
35
ABBOTT, GERALD
149
150
ABBOTT
GERALD
F.
389 BENEFIT STREET
PROVIDENCE
RI
29032946
100.0
2007-08-15
35
ABBOTT, GERALD
150
151
ABEDIN
ZAINUL
715 N. CENTRAL AVENUE
SUITE 212
GLENDALE
CA
912031164
500.0
2008-01-21
37
ABEDIN, ZAINUL
151
152
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
75.0
2008-01-08
37
ABBOTT, SYBIL
152
153
ABBOTT
SYBIL
F.
446 GAMES DRIVE
RENO
NV
895093326
50.0
2008-01-08
37
ABBOTT, SYBIL
153
154
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
200.0
2008-01-31
37
ABBOTT, RONALD
154
155
ABBOTT
RONALD
LEANDER
5453 HAWTHORNE STREET
MONTCLAIR
CA
917632551
100.0
2008-01-08
37
ABBOTT, RONALD
155
156
ABBOTT
ROBERT
A.
3061 LOREE ROAD
DECKERVILLE
MI
484279763
500.0
2008-01-21
37
ABBOTT, ROBERT
156
157
ABBOTT
MIKE
E.
4516 OSPREY LNDG
NICEVILLE
FL
325786810
1000.0
2008-01-15
37
ABBOTT, MIKE
157
158
ABBOT
DAVID
M.
56 SALEM STREET
ANDOVER
MA
18102114
200.0
2008-01-21
37
ABBOT, DAVID
158
159
ABBO
PAULINE
MORENCY
10720 JACOB LANE
WHITE LAKE
MI
483862274
35.0
2008-01-07
37
ABBO, PAULINE
159
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
MIAMI SPRINGS
FL
331663832
2600.0
2008-01-25
37
ABATE, MARIA
160
161
ABAIR
PETER
40 EVANS STREET
WATERTOWN
MA
24722150
25.0
2008-01-09
37
ABAIR, PETER
161
162
ABACHERLI
SHIRLEY
M.
29875 NEWPORT ROAD
MENIFEE
CA
925849524
150.0
2008-01-28
37
ABACHERLI, SHIRLEY
162
163
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
300.0
2008-01-30
37
AARONS, CHARLES
163
164
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
410.0
2008-01-15
37
AARONS, CHARLES
164
165
AARONS
CHARLES
1730 SHORE DRIVE
ANCHORAGE
AK
995153207
500.0
2008-01-09
37
AARONS, CHARLES
165
166
ABEL
JOHN
H.
422 THOMAS STREET
BETHLEHEM
PA
180153316
200.0
2008-01-22
37
ABEL, JOHN
166
167
ABEL
MARLING
L.
14 HANGING MOSS LANE
GREENVILLE
SC
296155069
100.0
2008-01-22
37
ABEL, MARLING
167
168
ABEL
RUDOLPH
4532 OCEAN BLVD.
# 108
SARASOTA
FL
342421337
100.0
2008-01-08
37
ABEL, RUDOLPH
168
169
ABELE
RODNEY
3620 METAIRIE HEIGHTS AVENUE
METAIRIE
LA
700021823
500.0
2008-01-15
37
ABELE, RODNEY
169
170
ABERCROMBIE
DENIS
11811 WATER OAK CT
MAGNOLIA
TX
773546270
500.0
2008-01-30
37
ABERCROMBIE, DENIS
170
171
ABESHAUS
MERRILL
M.
1801 N. HEREFORD DRIVE
FLAGSTAFF
AZ
860011121
120.0
2008-01-16
37
ABESHAUS, MERRILL
171
172
ABRAHAM
GEORGE
P.O. BOX 1504
LAKE CHARLES
LA
706021504
800.0
2008-01-17
37
ABRAHAM, GEORGE
172
173
ABRAHAMSON
PETER
J.
1030 W. ROSCOE STREET
CHICAGO
IL
606572207
50.0
2008-01-25
37
ABRAHAMSON, PETER
173
174
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1000.0
2008-01-17
37
ABRAHAM, SALEM
174
175
ABRAHAM
SALEM
A.
P.O. BOX 7
CANADIAN
TX
790140007
1300.0
2008-01-30
37
ABRAHAM, SALEM
175 rows × 13 columns
In [23]:
contributor_cols = [col[1] for col in cursor.execute("PRAGMA table_info(contributors)")] # You've already done this part. I just need to do it here b/c I haven't yet.
function = '''SELECT COUNT(amount>1000) FROM contributors'''
q=cursor.execute(function).fetchall()
print(q)
[(175,)]
In [27]:
function="SELECT AVG(amount) FROM contributors"
q=cursor.execute(function).fetchall()
print(q)
[(3.418114285714276,)]
In [31]:
function="SELECT state,AVG(amount) FROM contributors GROUP BY state"
q=cursor.execute(function).fetchall()
pd.DataFrame(q)
Out[31]:
0
1
0
-500.000000
1
AK
403.333333
2
AR
1183.333333
3
AZ
120.000000
4
CA
-217.988261
5
CO
-1455.750000
6
CT
2300.000000
7
DC
-309.982000
8
FL
-135.000000
9
IA
250.000000
10
ID
-261.000000
11
IL
-931.133333
12
KS
-330.000000
13
KY
-200.000000
14
LA
650.000000
15
MA
-13.833333
16
MD
150.000000
17
ME
630.000000
18
MI
-253.000000
19
MN
107.333333
20
MO
100.000000
21
NC
500.000000
22
NH
-24.600000
23
NJ
-408.725000
24
NV
181.250000
25
NY
-809.312500
26
OH
112.500000
27
OK
266.666667
28
PA
-429.200000
29
RI
100.000000
30
SC
800.000000
31
TN
-25.000000
32
TX
220.582222
33
UT
459.090909
34
VA
103.184000
35
WA
-166.666667
In [33]:
deletion = '''DELETE FROM contributors WHERE last_name==Ahrens'''
In [34]:
query = '''SELECT * FROM contributors ORDER BY amount DESC LIMIT 10'''
viz_tables(candidate_cols, query)
Out[34]:
id
first_name
last_name
middle_init
party
0
31
Buckel
Linda
PO Box 683130
1
160
ABATE
MARIA
ELENA
1291 NIGHTINGALE AVENUE
2
14
Altes
R.D.
8600 Moody Road
3
16
Anthony
John
211 Long Island Drive
4
22
Baker
David
2550 Adamsbrooke Drive
5
29
Buckel
Linda
PO Box 683130
6
34
Buck
Blaine
M
45 Eaton Ave
7
136
ABRAMOWITZ
NIRA
411 HARBOR ROAD
8
6
Akin
Mike
181 Baywood Lane
9
10
Allen
John D.
1052 Cannon Mill Drive
In [35]:
query = '''SELECT * FROM contributors WHERE amount>0 ORDER BY amount LIMIT 10'''
viz_tables(candidate_cols, query)
Out[35]:
id
first_name
last_name
middle_init
party
0
27
Buckler
Steve
24351 Armada Dr
1
50
Harrison
Ryan
2247 3rd St
2
102
Aarons
Elaine
481 Buck Island Rd Apt 17A
3
141
ABEGG
PATRICIA
T.
1862 E. 5150 S.
4
144
ABEGG
PATRICIA
T.
1862 E. 5150 S.
5
145
ABEGG
PATRICIA
T.
1862 E. 5150 S.
6
161
ABAIR
PETER
40 EVANS STREET
7
159
ABBO
PAULINE
MORENCY
10720 JACOB LANE
8
3
Ahrens
Don
4034 Rennellwood Way
9
19
Ardle
William
412 Dakota Avenue
In [36]:
db.commit()
db.close()
Content source: crystalzhaizhai/cs207_yi_zhai
Similar notebooks: