Predicting the future with Google BigQuery¶
Will your plane be late tomorrow? We'll use Pearson Correlation over a 70 million flights dataset.
Watch these queries live at http://youtu.be/tqS4vZ2Rxlo
In [49]:
import pandas as pd
import numpy
First let's learn what is Pearson Correlation.
In [77]:
# temperature vs ice_cream, fill the series
temperatures = pd.Series([21,30,16,18])
icecreams_sold = pd.Series([70,90,35,40])
In [78]:
temperatures.plot()
icecreams_sold.plot()
Out[78]:
In [79]:
# x,y plot
plt.scatter(temperatures, icecreams_sold)
Out[79]:
In [80]:
# calc correlation
temperatures.corr(icecreams_sold)
Out[80]:
In [82]:
# negative correlation? fill the series
coats_sold = pd.Series([25, 10, 47, 40])
plt.scatter(temperatures, coats_sold)
Out[82]:
In [83]:
# calc corr
coats_sold.corr(temperatures)
Out[83]:
In [32]:
#bigquery imports
import bq
import datetime
import pandas as pd
client = bq.Client.Get()
We have a cool web UI too! You can run all the following queries at https://bigquery.cloud.google.com/.
In [3]:
# first 10 days of NY
query = """
SELECT date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5
ORDER BY 1
LIMIT 10
"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
data[0:10]
Out[3]:
In [4]:
# last 10 days of NY
query = """
SELECT date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5
ORDER BY 1 DESC
LIMIT 10
"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
data[0:10]
Out[4]:
In [5]:
# plot NY by day
query = """
SELECT date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5
ORDER BY 1
"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
for x in data:
x[2] = float(x[2])
frame = pd.DataFrame(data)
xx=frame.plot()
xx.set_xticklabels([frame[0][min(int(x), len(frame[0])-1)][0:7] for x in xx.get_xticks()])
Out[5]:
In [6]:
# too much data, plot NY by month
query = """
SELECT left(date, 7) date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5
ORDER BY 1
"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
for x in data:
x[2] = float(x[2])
frame = pd.DataFrame(data)
xx=frame.plot()
xx.set_xticklabels([frame[0][min(int(x), len(frame[0])-1)][0:7] for x in xx.get_xticks()])
""
Out[6]:
In [34]:
#comparing NY to NJ
query = """
SELECT a.date, a.departure_state, a.avg, b.departure_state, b.avg FROM
(
SELECT left(date, 7) date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5) a
JOIN
(
SELECT left(date, 7) date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NJ'
GROUP BY 1,2 HAVING c > 5) b
ON a.date=b.date
ORDER BY 1
"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
for x in data:
x[2] = float(x[2])
x[4] = float(x[4])
data[0:30]
Out[34]:
In [36]:
#plot NJ vs NY
frame = pd.DataFrame(data)
xx=frame.plot()
xx.set_xticklabels([frame[0][min(int(x), len(frame[0])-1)][0:7] for x in xx.get_xticks()])
Out[36]:
In [40]:
#scatter plot NY vs NJ
plt.scatter(frame[2],frame[4])
plt.xlabel('avg NY delay in minutes')
plt.ylabel('avg NJ delay in minutes')
frame[2].corr(frame[4])
Out[40]:
In [41]:
# repeat NJ vs HI
query = """
SELECT a.date, a.departure_state, b.departure_state, a.avg, b.avg corr
FROM
(SELECT LEFT(date, 7) date, departure_state , AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5 ) a
JOIN
(SELECT LEFT(date, 7) date, departure_state, AVG(departure_delay) avg, COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'HI'
GROUP BY 1,2 HAVING c > 5 ) b
ON a.date=b.date
ORDER BY a.date;
"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
for x in data:
x[3] = float(x[3])
x[4] = float(x[4])
In [42]:
#NY vs HI
frame = pd.DataFrame(data)
xx=frame.plot()
xx.set_xticklabels([frame[0][min(int(x), len(frame[0])-1)][0:7] for x in xx.get_xticks()])
frame[3].corr(frame[4])
Out[42]:
In [45]:
plt.scatter(frame[3],frame[4])
plt.xlabel('avg NY delay in minutes')
plt.ylabel('avg Hawaii delay in minutes')
print "Correlation: %s" % frame[3].corr(frame[4])
In [13]:
# The best correlations
query = """SELECT a.departure_state, b.departure_state, corr(a.avg, b.avg) corr, COUNT(*) c
FROM
(SELECT date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
GROUP BY 1,2 HAVING c > 5
) a
JOIN
(SELECT date, departure_state ,
AVG(departure_delay) avg, COUNT(*) c FROM [bigquery-samples:airline_ontime_data.flights]
GROUP BY 1,2 HAVING c > 5 ) b
ON a.date=b.date
WHERE a.departure_state < b.departure_state
GROUP EACH BY 1, 2
HAVING c > 5
ORDER BY corr DESC;"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
data[0:30]
Out[13]:
In [14]:
# The best predictors
query = """SELECT a.departure_state, b.departure_state, corr(a.avg, b.avg) corr, COUNT(*) c
FROM
(SELECT date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
GROUP BY 1,2 HAVING c > 5
) a
JOIN
(SELECT DATE(DATE_ADD(timestamp(date), 1, 'DAY')) date, departure_state ,
AVG(departure_delay) avg, COUNT(*) c FROM [bigquery-samples:airline_ontime_data.flights]
GROUP BY 1,2 HAVING c > 5 ) b
ON a.date=b.date
GROUP EACH BY 1, 2
HAVING c > 5
ORDER BY corr DESC;"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
data[0:30]
Out[14]:
In [19]:
# The best NY predictors
query = """SELECT a.departure_state, b.departure_state, corr(a.avg, b.avg) corr, COUNT(*) c
FROM
(SELECT date, departure_state, AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2 HAVING c > 5
) a
JOIN
(SELECT DATE(DATE_ADD(timestamp(date), 1, 'DAY')) date, departure_state ,
AVG(departure_delay) avg, COUNT(*) c FROM [bigquery-samples:airline_ontime_data.flights]
GROUP BY 1,2 HAVING c > 5 ) b
ON a.date=b.date
GROUP EACH BY 1, 2
HAVING c > 5
ORDER BY corr DESC;"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
data[0:30]
Out[19]:
In [20]:
# The best NY predictors, by quarter
query = """SELECT a.q, a.departure_state, b.departure_state, corr(a.avg, b.avg) corr, COUNT(*) c
FROM
(SELECT QUARTER(timestamp(date)) q, date, departure_state , AVG(departure_delay) avg , COUNT(*) c
FROM [bigquery-samples:airline_ontime_data.flights]
WHERE departure_state = 'NY'
GROUP BY 1,2,3 HAVING c > 5
) a
JOIN
(SELECT QUARTER(timestamp(date)) q, DATE(DATE_ADD(timestamp(date), 1, 'DAY')) date, departure_state ,
AVG(departure_delay) avg, COUNT(*) c FROM [bigquery-samples:airline_ontime_data.flights]
GROUP BY 1,2,3 HAVING c > 5 ) b
ON a.date=b.date AND a.q = b.q
GROUP EACH BY 1, 2, 3
HAVING c > 5
ORDER BY corr DESC;"""
fields, data = client.ReadSchemaAndRows(client.Query(query)['configuration']['query']['destinationTable'], max_rows = 1000)
data[0:30]
Out[20]:
In [87]:
# Bonus: When is a correlation a good correlation?
nnn = 1100 # Move it from 2 to 4000
s1 = pd.Series(np.random.random(nnn))
s2 = pd.Series(np.random.random(nnn))
plt.scatter(s1, s2)
s1.corr(s2)
Out[87]:
Follow the most interesting BigQuery news at http://www.reddit.com/r/bigquery. Ask your technical questions using the [google-bigquery] tag on Stack Overflow.