Hubway Capstone Project-- Database Connection Test¶

Hubway is a bike-share program collectively owned by the metro Boston cities; Boston, Cambridge, Somerville, and Brookline. It is operated by Motivate, who manages similar initiatives in NYC, Portland, Chicago, Washington DC, and several other metro areas in Ohio, Tennessee, and New Jersey. They are opening up operations in San Francisco during the month of June, 2017. Hubway currently exists as a system of 188 stations with 1,800 bikes.

• For this project, I investigated shared data for the months of January, May, June, July, and October during the years of 2015 and 2016.
• Of concern were the questions of;
  
  • How do riders use the bike-share service?
    
  • Are the bikes used as a conveyance or for recreation?
    
  • What type of customer uses the service?
    

Below is a connection test to the local database.

Library import¶

In [12]:

import numpy as np
#import scipy.stats as stats
#import seaborn as sns
#import matplotlib.pyplot as plt
import pandas as pd
import sqlite3

Connection to Database¶

In [13]:

import sqlite3
hubway_db = './database/hubway_full.db'
conn = sqlite3.connect(hubway_db) 
c = conn.cursor()

Verification of table in a DataFrame¶

In [14]:

hubway_df = pd.read_sql('SELECT * FROM hubway_db LIMIT 10', con=conn)
hubway_df

Out[14]:

	tripduration	starttime	stoptime	start station id	start station name	start station latitude	start station longitude	end station id	end station name	end station latitude	end station longitude	bikeid	usertype	birth year	gender
0	542	2015-01-01 00:21:44	2015-01-01 00:30:47	115	Porter Square Station	42.387995	-71.119084	96	Cambridge Main Library at Broadway / Trowbridg...	42.373379	-71.111075	277	Subscriber	1984	1
1	438	2015-01-01 00:27:03	2015-01-01 00:34:21	80	MIT Stata Center at Vassar St / Main St	42.361962	-71.092053	95	Cambridge St - at Columbia St / Webster Ave	42.372969	-71.094445	648	Subscriber	1985	1
2	254	2015-01-01 00:31:31	2015-01-01 00:35:46	91	One Kendall Square at Hampshire St / Portland St	42.366277	-71.091690	68	Central Square at Mass Ave / Essex St	42.365070	-71.103100	555	Subscriber	1974	1
3	432	2015-01-01 00:53:46	2015-01-01 01:00:58	115	Porter Square Station	42.387995	-71.119084	96	Cambridge Main Library at Broadway / Trowbridg...	42.373379	-71.111075	1307	Subscriber	1987	1
4	735	2015-01-01 01:07:06	2015-01-01 01:19:21	105	Lower Cambridgeport at Magazine St/Riverside Rd	42.356954	-71.113687	88	Inman Square at Vellucci Plaza / Hampshire St	42.374035	-71.101427	177	Customer	1986	2
5	311	2015-01-01 01:28:27	2015-01-01 01:33:38	88	Inman Square at Vellucci Plaza / Hampshire St	42.374035	-71.101427	76	Central Sq Post Office / Cambridge City Hall a...	42.366426	-71.105495	685	Subscriber	1989	1
6	1259	2015-01-01 01:34:54	2015-01-01 01:55:54	91	One Kendall Square at Hampshire St / Portland St	42.366277	-71.091690	118	Linear Park - Mass. Ave. at Cameron Ave.	42.397828	-71.130516	940	Subscriber	1964	1
7	338	2015-01-01 02:32:35	2015-01-01 02:38:13	68	Central Square at Mass Ave / Essex St	42.365070	-71.103100	95	Cambridge St - at Columbia St / Webster Ave	42.372969	-71.094445	656	Subscriber	1981	1
8	429	2015-01-01 04:41:58	2015-01-01 04:49:08	117	Binney St / Sixth St	42.366095	-71.086388	76	Central Sq Post Office / Cambridge City Hall a...	42.366426	-71.105495	134	Customer	1989	1
9	289	2015-01-01 08:00:15	2015-01-01 08:05:04	67	MIT at Mass Ave / Amherst St	42.358100	-71.093198	75	Lafayette Square at Mass Ave / Main St / Colum...	42.363465	-71.100573	23	Subscriber	1993	1

The script connected to database locally and returned results quickly¶

In [15]:

sql_query = """
PRAGMA table_info(hubway_db)
"""

pd.read_sql(sql_query, con=conn)

Out[15]:

	cid	name	type	notnull	dflt_value	pk
0	0	tripduration	BIGINT	0	None	0
1	1	starttime	TEXT	0	None	0
2	2	stoptime	TEXT	0	None	0
3	3	start station id	BIGINT	0	None	0
4	4	start station name	TEXT	0	None	0
5	5	start station latitude	FLOAT	0	None	0
6	6	start station longitude	FLOAT	0	None	0
7	7	end station id	BIGINT	0	None	0
8	8	end station name	TEXT	0	None	0
9	9	end station latitude	FLOAT	0	None	0
10	10	end station longitude	FLOAT	0	None	0
11	11	bikeid	BIGINT	0	None	0
12	12	usertype	TEXT	0	None	0
13	13	birth year	TEXT	0	None	0
14	14	gender	BIGINT	0	None	0

In [18]:

conn.close()

In [ ]:

Back to Executive Summary