<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Before you turn this problem in, make sure everything runs as expected. First, restart the kernel (in the menubar, select Kernel$\rightarrow$Restart) and then run all cells (in the menubar, select Cell$\rightarrow$Run All).
Make sure you fill in any place that says YOUR CODE HERE or "YOUR ANSWER HERE", as well as your name and collaborators below
Step1:  Exercise 03 - Due Friday, September 30 at 12pm
Objectives
Step2:  Verify that it's what we think it is on the commandline
Step3:  Looks right.  How many records are there?
Step4:  So that should be 145, counting the header.  And the basic stats
Step5:  Looks about right!  
Note, though, that the column names have spaces, punctuation, and Upper Cased Names.  That's annoying!  First let's rename the header line.
Step6:  Okay, using output redirection and tail we can write a new header line.
Step7:  Much easier to work with now.
Next we convert the updated csv file into a SQLite database using CSVkit.
Step8:  To work with it, we'll need the ipython-sql extension loaded, and then we'll need to connect to the db.
Step9:  Note if you see a pink box above with six lines of "ShimWarning" and "UserWarning", don't panic.  This is just a warning message from the ipython-sql package.  You are good to go.
Step10:  Looks like the same number of rows!  We're good to go.
Basic queries
In the following queries, we'll do some basic exploration of the data.  Let's first see what a few records look like.
Step11:  This should look familiar!
Let's look at just the "change" types.
Step12:  How many is that, and which year had the most?
Step13:  Which year had the most of these transactions?
Step14:  ...alright, your turn.
Question 1
Use DISTINCT to determine the unique set of years in this dataset.
Step15:  Question 2
Use DISTINCT to determine the unique set of transaction types in this dataset.
Step16:  Question 3
Use GROUP BY to determine the overall number of transactions (across all types) per year.
Step17:  Question 4
Use SUM and GROUP BY to determine the overall number of transactions (across all years) per type.
Step18:  Question 5
Use ORDER BY and LIMIT to determine the top five types of transactions overall.
Step19:  Question 6
Using ORDER BY and LIMIT again, what were the top five types of transactions in 2010?
Step20:  Question 7
Use a wildcard search to determine how many transactions in 2012 were renewals.
Step21:  Question 8
How many transactions overall involve canoes?
Step22:  Question 9
How many transactions in 2011 involved a new registration (as opposed to a renewal or change of type)?
Step23:  Question 10
How do the transaction trends over time involving pontoons compare to overall boating transaction activity?  Discuss as appropriate, adding Markdown cells for your discussion after your exploratory queries.
<ASSISTANT_TASK:>
Python Code:
NAME = "dchud"
COLLABORATORS = ""
Explanation: Before you turn this problem in, make sure everything runs as expected. First, restart the kernel (in the menubar, select Kernel$\rightarrow$Restart) and then run all cells (in the menubar, select Cell$\rightarrow$Run All).
Make sure you fill in any place that says YOUR CODE HERE or "YOUR ANSWER HERE", as well as your name and collaborators below:
End of explanation
!wget --quiet -O boating.csv "https://data.ct.gov/api/views/mrb6-7ee5/rows.csv?accessType=DOWNLOAD"
Explanation: Exercise 03 - Due Friday, September 30 at 12pm
Objectives:  Gain experience loading a CSV dataset into a database and using SQL to explore its contents.  Write and execute a number of SQL queries using common syntax and functions.
Grading criteria: The tasks should all be completed, and questions should all be answered with SQL queries in the space provided, unless a text answer is requested.  Results should be correct, according to the embedded tests.  The notebook itself should be completely reproducible; from start to finish, another person should be able to use the same code to obtain the same results as yours.
Deadline: Friday, September 30, 12pm.  Submit your notebook to Blackboard and push it to your GitHub repository.
In this notebook we'll download a clean CSV dataset from data.gov, load it into a SQLite database, and perform a series of queries to answer several questions.  For each problem, write and execute queries that provides the answer in the cells provided, with your SQL queries in the places marked.  For each problem after executing your query, immediately execute the following test cell.  If the tests complete without error, you have completed that question successfully.  If errors arise, you might be missing something.  Do not change the tests, just execute them as they are, and update or refine your query until the tests pass.  
For this assignment, you need not add narrative description to most of your queries (except where explicitly noted), although you may do so if something you see in the data prompts you.  If you do, add new text cells and use Markdown formatting.
Suggestion: if you have worked through the Software Carpentry SQL lessons and have run through the last two lecture notes notebooks, this should all be fairly easy.  If you have done neither, do them now, before you begin.
Setup - obtain data and create database
The Connecticut DMV Boating Registrations dataset comprises several years of summary records.  It is available from data.gov.
First we download the dataset (note: it might take several seconds):
End of explanation
!head boating.csv | csvlook
Explanation: Verify that it's what we think it is on the commandline:
End of explanation
!wc -l boating.csv
Explanation: Looks right.  How many records are there?
End of explanation
!csvstat boating.csv
Explanation: So that should be 145, counting the header.  And the basic stats:
End of explanation
!mv boating.csv boating-orig.csv
Explanation: Looks about right!  
Note, though, that the column names have spaces, punctuation, and Upper Cased Names.  That's annoying!  First let's rename the header line.
End of explanation
!echo "year,tx_type,num" > boating.csv
!tail -n +2 boating-orig.csv >> boating.csv
!head boating.csv | csvlook
Explanation: Okay, using output redirection and tail we can write a new header line.
End of explanation
!csvsql --db sqlite:///boating.db --insert boating.csv
Explanation: Much easier to work with now.
Next we convert the updated csv file into a SQLite database using CSVkit.
End of explanation
%load_ext sql
Explanation: To work with it, we'll need the ipython-sql extension loaded, and then we'll need to connect to the db.
End of explanation
%sql sqlite:///boating.db
%%sql
SELECT COUNT(*)
FROM boating;
Explanation: Note if you see a pink box above with six lines of "ShimWarning" and "UserWarning", don't panic.  This is just a warning message from the ipython-sql package.  You are good to go.
End of explanation
%%sql
SELECT *
FROM boating
LIMIT 10;
Explanation: Looks like the same number of rows!  We're good to go.
Basic queries
In the following queries, we'll do some basic exploration of the data.  Let's first see what a few records look like.
End of explanation
%%sql
SELECT *
FROM boating
WHERE tx_type = "BOAT CHANGE OF TYPE";
Explanation: This should look familiar!
Let's look at just the "change" types.
End of explanation
%%sql
SELECT COUNT(*)
FROM boating
WHERE tx_type = "BOAT CHANGE OF TYPE";
Explanation: How many is that, and which year had the most?
End of explanation
%%sql
SELECT *
FROM boating
WHERE tx_type = "BOAT CHANGE OF TYPE"
ORDER BY num DESC;
Explanation: Which year had the most of these transactions?
End of explanation
%%sql
SELECT year
FROM boating;
df = _.DataFrame()
for y in [2008, 2009, 2010, 2011, 2012, 2013, 2014]:
    assert y in df.year.values
assert len(df) == 7
Explanation: ...alright, your turn.
Question 1
Use DISTINCT to determine the unique set of years in this dataset.
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
assert len(df) == 21
for tx_type in ["BOAT, DUPLICATE REGISTRATION", "REN BOAT PONTOON", "BOAT REG, CANOE"]:
    assert tx_type in df.tx_type.values
Explanation: Question 2
Use DISTINCT to determine the unique set of transaction types in this dataset.
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
df.rename(columns=lambda x: "year" if x == "year" else "count", inplace=True)
assert (df.loc[df['year'] == 2009]['count'] == 21).all()
assert (df.loc[df['year'] == 2013]['count'] == 20).all()
Explanation: Question 3
Use GROUP BY to determine the overall number of transactions (across all types) per year.
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
df.rename(columns=lambda x: "tx_type" if x == "tx_type" else "sum", inplace=True)
assert (df.loc[df['tx_type'] == "BOAT REG, CANOE"]['sum'] == 1129).all()
assert (df.loc[df['tx_type'] == "REN BOAT REGISTRATION"]['sum'] == 640790).all()
Explanation: Question 4
Use SUM and GROUP BY to determine the overall number of transactions (across all years) per type.
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
df.rename(columns=lambda x: "tx_type" if x == "tx_type" else "sum_num", inplace=True)
assert len(df) == 5
assert (df.loc[df['tx_type'] == "REN BOAT PONTOON"]['sum_num'] == 15556).all()
assert (df.loc[df['tx_type'] == "REN BOAT REGISTRATION"]['sum_num'] == 640790).all()
assert df['sum_num'].max() == 640790
assert df['sum_num'].min() == 2390
Explanation: Question 5
Use ORDER BY and LIMIT to determine the top five types of transactions overall.
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
assert len(df) == 5
assert (df.loc[df['tx_type'] == "REN BOAT PONTOON"]['num'] == 2118).all()
assert (df.loc[df['tx_type'] == "BOAT REG, NEW"]['num'] == 12569).all()
assert df['num'].max() == 94005
assert df['num'].min() == 555
Explanation: Question 6
Using ORDER BY and LIMIT again, what were the top five types of transactions in 2010?
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
assert df.values[0][0] in [7, 9]
Explanation: Question 7
Use a wildcard search to determine how many transactions in 2012 were renewals.
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
assert df.values[0][0] == 14
Explanation: Question 8
How many transactions overall involve canoes?
End of explanation
-- YOUR CODE HERE
df = _.DataFrame()
assert df.values[0][0] in [12071, 14466]
Explanation: Question 9
How many transactions in 2011 involved a new registration (as opposed to a renewal or change of type)?
End of explanation
-- YOUR CODE HERE
Explanation: Question 10
How do the transaction trends over time involving pontoons compare to overall boating transaction activity?  Discuss as appropriate, adding Markdown cells for your discussion after your exploratory queries.
End of explanation
<END_TASK>