Skip to content

Instantly share code, notes, and snippets.

View mportocarrero's full-sized avatar

mportocarrero

1 follower · 0 following
View GitHub Profile
@mportocarrero
mportocarrero / Query 14 - What was the average, the maximum and the minimum number of people that cycle everyday to work in 2001 and 2011 and where do they live?
Created January 12, 2014 13:34
Query 14 - What was the average, the maximum and the minimum number of people that cycle everyday to work in 2001 and 2011 and where do they live? This query allows the reader to understand in average how many people cycle everyday to work in all boroughs and where the people that cycle the most and the least were living both in 2001 and 2011.
Query in MySql:
2001:
mysql> select avg(bike_2001), max(bike_2001), min(bike_2001) from cycling;
+----------------+----------------+----------------+
| avg(bike_2001) | max(bike_2001) | min(bike_2001) |
+----------------+----------------+----------------+
| 2448.4000 | 4929 | 1351 |
+----------------+----------------+----------------+
@mportocarrero
mportocarrero / Query 13 - What is the evolution of people cycling everyday to work from each borough between 2001 and 2011?
Created January 11, 2014 22:22
Query 13 - What is the evolution of people cycling everyday to work from each borough between 2001 and 2011? This query allows the reader to see if the percentage of people cycling everyday to work in each borough increased or decreased between 2001 and 2011 and can be complemented with the percentage change between those two periods.
Query in MySql:
mysql> select borough, percentage_2001, percentage_2011 from cycling group by borough;
+---------------+-----------------+-----------------+
| borough | percentage_2001 | percentage_2011 |
+---------------+-----------------+-----------------+
| Croydon | 1.0 | 1.3 |
| Greenwich | 1.5 | 2.3 |
| Hackney | 6.2 | 9.9 |
| Lewisham | 1.8 | 4.0 |
@mportocarrero
mportocarrero / Query 12 - What was the total of people working and cycling to work in the five boroughs in 2001 and 2011?
Last active January 2, 2016 23:19
Query 12 - What was the total of people working and cycling to work in the five boroughs in 2001 and 2011? This query allows the reader to see in those five boroughs how many people work from 16 to 74 years old and how many of them cycle everyday for work. This also enables to spot the difference between those two categories and to understand th…
Query in MySql:
mysql> select SUM(work_2001), SUM(Bike_2001), SUM(Work_2011), SUM(Bike_2011) from cycling;
+----------------+----------------+----------------+----------------+
| SUM(work_2001) | SUM(Bike_2001) | SUM(Work_2011) | SUM(Bike_2011) |
+----------------+----------------+----------------+----------------+
| 516155 | 12242 | 666294 | 35709 |
+----------------+----------------+----------------+----------------+
1 row in set (0.00 sec)
@mportocarrero
mportocarrero / Query 11 - In which areas of Lewisham were the bikes stolen?
Created January 11, 2014 18:33
Query 11 - In which areas of Lewisham were the bikes stolen? This query shows in which areas of Lewisham the bikes were stolen and how many per area.
Query in MySql:
mysql> select area, count(*) as count from bike where borough = 'Lewisham' group by area;
+---------------------------------+-------+
| area | count |
+---------------------------------+-------+
| Lewisham (underground) | 6 |
| On or near Bus/coach Station | 2 |
| On or near Catford Broadway | 1 |
| On or near Lewis Grove | 3 |
@mportocarrero
mportocarrero / Query 10 - How is the police acting concerning to the bike theft cases in Lewisham?
Created January 11, 2014 18:07
Query 10 - How is the police acting concerning to the bike theft cases in Lewisham? This query shows what was the police resolution for the bike theft cases reported in Lewisham.
Query in MySql:
mysql> select borough, investigation, count(*) from bike where borough = 'Lewisham' group by investigation;
+----------+-----------------------------------+----------+
| borough | investigation | count(*) |
+----------+-----------------------------------+----------+
| Lewisham | No further action at this time | 10 |
| Lewisham | Offender given community sentence | 1 |
| Lewisham | Under investigation | 13 |
+----------+-----------------------------------+----------+
@mportocarrero
mportocarrero / Query 9 - How many bikes were stolen in average per month in Lewisham from May to October 2013?
Last active January 2, 2016 23:09
Query 9 - How many bikes were stolen in average per month in Lewisham from May to October 2013? This query shows the average of bikes stolen per month in Lewisham.
Query in MySql:
mysql> select borough, date, count(*) as count from bike where borough = 'Lewisham' group by date;
+----------+------------+-------+
| borough | date | count |
+----------+------------+-------+
| Lewisham | 2013-05-01 | 4 |
| Lewisham | 2013-06-01 | 1 |
| Lewisham | 2013-07-01 | 2 |
| Lewisham | 2013-08-01 | 6 |
@mportocarrero
mportocarrero / Query 8 - How many bikes were stolen per month in Lewisham from May to October 2013?
Created January 11, 2014 17:51
Query 8 - How many bikes were stolen per month in Lewisham from May to October 2013? This query shows us the evolution of bike theft in Lewisham in this period.
Query in MySql:
mysql> select borough, date, count(*) from bike where borough = 'Lewisham' group by date;
+----------+------------+----------+
| borough | date | count(*) |
+----------+------------+----------+
| Lewisham | 2013-05-01 | 4 |
| Lewisham | 2013-06-01 | 1 |
| Lewisham | 2013-07-01 | 2 |
| Lewisham | 2013-08-01 | 6 |
@mportocarrero
mportocarrero / Query 6 - How many bikes were stolen in average each month from May to October 2013?
Created January 11, 2014 17:40
Query 6 - How many bikes were stolen in average each month from May to October 2013? This query allows the reader to know in average how many bikes are stolen in those five boroughs.
mysql> select date, count(*) as count from bike group by date;
+------------+-------+
| date | count |
+------------+-------+
| 2013-05-01 | 45 |
| 2013-06-01 | 68 |
| 2013-07-01 | 53 |
| 2013-08-01 | 40 |
| 2013-09-01 | 54 |
| 2013-10-01 | 61 |
@mportocarrero
mportocarrero / Populating new table - MySql and Python
Last active June 1, 2018 08:26
Populating new table - MySql and Python To insert data into the new table I used MySql. I could have used Python as well as both ways work, but MySql makes it more visual and easier to use.
mysql> LOAD DATA LOCAL INFILE "cycling.csv" INTO TABLE cycling FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r' IGNORE 1 LINES;
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 1
mysql> select * from cycling;
+---------------+-----------+-----------+-----------------+-----------+-----------+-----------------+-------------------+
| Borough | Work_2001 | Bike_2001 | Percentage_2001 | Work_2011 | Bike_2011 | Percentage_2011 | Percentage_change |
+---------------+-----------+-----------+-----------------+-----------+-----------+-----------------+-------------------+
| Croydon | 156838 | 1636 | 1.0 | 172987 | 2172 | 1.3 | 0.3 |
| Greenwich | 91575 | 1351 | 1.5 | 117821 | 2738 | 2.3 | 0.8 |
@mportocarrero
mportocarrero / Creating another table - Python
Last active January 2, 2016 22:59
Creating another table - Python I decided to create another table called "cycling" to introduce data about the percentage of people that cycles to work everyday in the same boroughs. To create the table I used Python and checked it with My Sql.
Creating table in Python
import pymysql, sys
try:
conn = pymysql.connect(host='localhost',
port=3306,
user='co302mc',
passwd='mafol597',
db='co302mc_bike_theft')