{ "cells": [ { "cell_type": "code", "execution_count": 10, "metadata": { "execution": { "iopub.execute_input": "2020-10-02T14:32:53.470204Z", "iopub.status.busy": "2020-10-02T14:32:53.469989Z", "iopub.status.idle": "2020-10-02T14:32:54.688123Z", "shell.execute_reply": "2020-10-02T14:32:54.687074Z", "shell.execute_reply.started": "2020-10-02T14:32:53.470180Z" } }, "outputs": [], "source": [ "import altair as alt\n", "import ibis_vega_transform\n", "\n", "import warnings\n", "try:\n", " from ibis.backends import omniscidb as ibis_omniscidb\n", "except ImportError as msg:\n", " warnings.warn(str(msg))\n", " from ibis import omniscidb as ibis_omniscidb\n", "\n", "conn = ibis_omniscidb.connect(\n", " host='metis.mapd.com', user='demouser', password='HyperInteractive',\n", " port=443, database='mapd', protocol= 'https'\n", ")" ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "execution": { "iopub.execute_input": "2020-10-02T14:32:58.530482Z", "iopub.status.busy": "2020-10-02T14:32:58.530245Z", "iopub.status.idle": "2020-10-02T14:32:59.471518Z", "shell.execute_reply": "2020-10-02T14:32:59.470771Z", "shell.execute_reply.started": "2020-10-02T14:32:58.530454Z" } }, "outputs": [], "source": [ "t = conn.table(\"flights_donotmodify\")" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "execution": { "iopub.execute_input": "2020-10-02T14:32:59.472998Z", "iopub.status.busy": "2020-10-02T14:32:59.472730Z", "iopub.status.idle": "2020-10-02T14:33:00.008831Z", "shell.execute_reply": "2020-10-02T14:33:00.008137Z", "shell.execute_reply.started": "2020-10-02T14:32:59.472965Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
flight_yearflight_monthflight_dayofmonthflight_dayofweekdeptimecrsdeptimearrtimecrsarrtimeuniquecarrierflightnum...dest_namedest_citydest_statedest_countrydest_latdest_lonorigin_merc_xorigin_merc_ydest_merc_xdest_merc_y
0200813141039104512581307XE2592...Indianapolis InternationalIndianapolisINUSA39.717331-86.294388-8256417.54967090.5-9606247.04824950.0
120081561713171220182010XE2227...George Bush IntercontinentalHoustonTXUSA29.980473-95.339722-10376860.05602735.5-10613169.03501040.0
2200811961524153016521715XE1291...Newark IntlNewarkNJUSA40.692497-74.168663-7904260.55215714.0-8256417.54967090.5
3200813141526152517421740XE3067...Memphis InternationalMemphisTNUSA35.042416-89.976669-8256417.54967090.5-10016157.04169647.0
420081711842185020572125XE2379...Louisville International-StandifordLouisvilleKYUSA38.174389-85.736000-8256417.54967090.5-9544088.04604090.5
\n", "

5 rows × 56 columns

\n", "
" ], "text/plain": [ " flight_year flight_month flight_dayofmonth flight_dayofweek deptime \\\n", "0 2008 1 31 4 1039 \n", "1 2008 1 5 6 1713 \n", "2 2008 1 19 6 1524 \n", "3 2008 1 31 4 1526 \n", "4 2008 1 7 1 1842 \n", "\n", " crsdeptime arrtime crsarrtime uniquecarrier flightnum ... \\\n", "0 1045 1258 1307 XE 2592 ... \n", "1 1712 2018 2010 XE 2227 ... \n", "2 1530 1652 1715 XE 1291 ... \n", "3 1525 1742 1740 XE 3067 ... \n", "4 1850 2057 2125 XE 2379 ... \n", "\n", " dest_name dest_city dest_state \\\n", "0 Indianapolis International Indianapolis IN \n", "1 George Bush Intercontinental Houston TX \n", "2 Newark Intl Newark NJ \n", "3 Memphis International Memphis TN \n", "4 Louisville International-Standiford Louisville KY \n", "\n", " dest_country dest_lat dest_lon origin_merc_x origin_merc_y \\\n", "0 USA 39.717331 -86.294388 -8256417.5 4967090.5 \n", "1 USA 29.980473 -95.339722 -10376860.0 5602735.5 \n", "2 USA 40.692497 -74.168663 -7904260.5 5215714.0 \n", "3 USA 35.042416 -89.976669 -8256417.5 4967090.5 \n", "4 USA 38.174389 -85.736000 -8256417.5 4967090.5 \n", "\n", " dest_merc_x dest_merc_y \n", "0 -9606247.0 4824950.0 \n", "1 -10613169.0 3501040.0 \n", "2 -8256417.5 4967090.5 \n", "3 -10016157.0 4169647.0 \n", "4 -9544088.0 4604090.5 \n", "\n", "[5 rows x 56 columns]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "t.head().execute()" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "execution": { "iopub.execute_input": "2020-10-02T14:31:08.283444Z", "iopub.status.busy": "2020-10-02T14:31:08.283225Z", "iopub.status.idle": "2020-10-02T14:31:09.389159Z", "shell.execute_reply": "2020-10-02T14:31:09.388526Z", "shell.execute_reply.started": "2020-10-02T14:31:08.283417Z" } }, "outputs": [ { "data": { "application/vnd.vega.ibis.v5+json": { "span": { "uber-trace-id": "774c81a493ac72a3:f44e275ce1b6c786:0:1" }, "spec": { "$schema": "https://vega.github.io/schema/vega-lite/v4.0.2.json", "config": { "axis": { "grid": false }, "view": { "continuousHeight": 300, "continuousWidth": 400, "strokeOpacity": 0 } }, "vconcat": [ { "hconcat": [ { "data": { "name": "ibis:7311417548080590831" }, "encoding": { "text": { "aggregate": "count", "type": "quantitative" } }, "mark": "text", "title": "Selected Rows", "transform": [ { "filter": { "selection": "selector002" } }, { "filter": { "selection": "selector003" } }, { "filter": { "selection": "selector001" } } ] }, { "data": { "name": "ibis:-7449861413293112334" }, "encoding": { "text": { "aggregate": "count", "type": "quantitative" } }, "mark": "text", "title": "Total Rows" } ] }, { "hconcat": [ { "layer": [ { "data": { "name": "ibis:-4770723134486775845" }, "encoding": { "color": { "condition": { "selection": "selector001", "value": "steelblue" }, "value": "grey" }, "x": { "aggregate": "count", "type": "quantitative" }, "y": { "field": "origin_state", "sort": { "encoding": "x", "order": "descending" }, "type": "nominal" } }, "height": 533.3333333333334, "mark": "bar", "selection": { "selector001": { "fields": [ "origin_state" ], "type": "multi" } }, "title": "Total Number of Flights by State", "transform": [ { "filter": { "selection": "selector002" } }, { "filter": { "selection": "selector003" } } ], "width": 500 }, { "data": { "name": "ibis:-4770723134486775845" }, "encoding": { "text": { "aggregate": "count", "type": "quantitative" }, "x": { "aggregate": "count", "type": "quantitative" }, "y": { "field": "origin_state", "sort": { "encoding": "x", "order": "descending" }, "type": "nominal" } }, "height": 533.3333333333334, "mark": { "dx": 20, "type": "text" }, "transform": [ { "filter": { "selection": "selector002" } }, { "filter": { "selection": "selector003" } } ], "width": 500 } ] }, { "layer": [ { "data": { "name": "ibis:3929177355154144787" }, "encoding": { "color": { "condition": { "selection": "selector002", "value": "steelblue" }, "value": "grey" }, "tooltip": [ { "field": "carrier_name", "type": "nominal" }, { "field": "depdelay", "type": "quantitative" }, { "field": "arrdelay", "type": "quantitative" } ], "x": { "field": "depdelay", "type": "quantitative" }, "y": { "field": "arrdelay", "type": "quantitative" } }, "height": 533.3333333333334, "mark": { "filled": true, "size": 200, "type": "point" }, "selection": { "selector002": { "fields": [ "carrier_name" ], "type": "multi" } }, "title": "Carrier Departure Delay by Arrival Delay (Minutes)", "transform": [ { "filter": { "selection": "selector001" } }, { "filter": { "selection": "selector003" } }, { "aggregate": [ { "as": "depdelay", "field": "depdelay", "op": "mean" }, { "as": "arrdelay", "field": "arrdelay", "op": "mean" } ], "groupby": [ "carrier_name" ] } ], "width": 500 }, { "data": { "name": "ibis:3929177355154144787" }, "encoding": { "text": { "field": "carrier_name", "type": "nominal" }, "x": { "field": "depdelay", "type": "quantitative" }, "y": { "field": "arrdelay", "type": "quantitative" } }, "height": 533.3333333333334, "mark": "text", "transform": [ { "filter": { "selection": "selector001" } }, { "filter": { "selection": "selector003" } }, { "aggregate": [ { "as": "depdelay", "field": "depdelay", "op": "mean" }, { "as": "arrdelay", "field": "arrdelay", "op": "mean" } ], "groupby": [ "carrier_name" ] } ], "width": 500 } ] } ] }, { "data": { "name": "ibis:7311417548080590831" }, "encoding": { "x": { "field": "dep_timestamp", "timeUnit": "yearmonthdate", "type": "temporal" }, "y": { "aggregate": "count", "scale": { "zero": false }, "type": "quantitative" } }, "height": 266.6666666666667, "mark": "line", "selection": { "selector003": { "encodings": [ "x" ], "fields": [ "dep_timestamp" ], "type": "interval" } }, "title": "Number of Flights by Departure Time", "transform": [ { "filter": "datum.dep_timestamp != null" }, { "filter": { "selection": "selector002" } }, { "filter": { "selection": "selector001" } } ], "width": 1050 } ] } }, "text/plain": [ "alt.VConcatChart(...)" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "\n", "states = alt.selection_multi(fields=['origin_state'])\n", "airlines = alt.selection_multi(fields=['carrier_name'])\n", "\n", "dates = alt.selection_interval(\n", " fields=['dep_timestamp'],\n", " encodings=['x'],\n", ")\n", "\n", "HEIGHT = 800\n", "WIDTH = 1000\n", "\n", "count_filter = alt.Chart(\n", " t[t.dep_timestamp, t.depdelay, t.origin_state, t.carrier_name],\n", " title=\"Selected Rows\"\n", ").transform_filter(\n", " airlines\n", ").transform_filter(\n", " dates\n", ").transform_filter(\n", " states\n", ").mark_text().encode(\n", " text='count()'\n", ")\n", "\n", "count_total = alt.Chart(\n", " t,\n", " title=\"Total Rows\"\n", ").mark_text().encode(\n", " text='count()'\n", ")\n", "\n", "flights_by_state = alt.Chart(\n", " t[t.origin_state, t.carrier_name, t.dep_timestamp],\n", " title=\"Total Number of Flights by State\"\n", ").transform_filter(\n", " airlines\n", ").transform_filter(\n", " dates\n", ").mark_bar().encode(\n", " x='count()',\n", " y=alt.Y('origin_state', sort=alt.Sort(encoding='x', order='descending')),\n", " color=alt.condition(states, alt.ColorValue(\"steelblue\"), alt.ColorValue(\"grey\"))\n", ").add_selection(\n", " states\n", ").properties(\n", " height= 2 * HEIGHT / 3,\n", " width=WIDTH / 2\n", ") + alt.Chart(\n", " t[t.origin_state, t.carrier_name, t.dep_timestamp],\n", ").transform_filter(\n", " airlines\n", ").transform_filter(\n", " dates\n", ").mark_text(dx=20).encode(\n", " x='count()',\n", " y=alt.Y('origin_state', sort=alt.Sort(encoding='x', order='descending')),\n", " text='count()'\n", ").properties(\n", " height= 2 * HEIGHT / 3,\n", " width=WIDTH / 2\n", ")\n", "\n", "carrier_delay = alt.Chart(\n", " t[t.depdelay, t.arrdelay, t.carrier_name, t.origin_state, t.dep_timestamp],\n", " title=\"Carrier Departure Delay by Arrival Delay (Minutes)\"\n", ").transform_filter(\n", " states\n", ").transform_filter(\n", " dates\n", ").transform_aggregate(\n", " depdelay='mean(depdelay)',\n", " arrdelay='mean(arrdelay)',\n", " groupby=[\"carrier_name\"]\n", ").mark_point(filled=True, size=200).encode(\n", " x='depdelay',\n", " y='arrdelay',\n", " color=alt.condition(airlines, alt.ColorValue(\"steelblue\"), alt.ColorValue(\"grey\")),\n", " tooltip=['carrier_name', 'depdelay', 'arrdelay']\n", ").add_selection(\n", " airlines\n", ").properties(\n", " height=2 * HEIGHT / 3,\n", " width=WIDTH / 2\n", ") + alt.Chart(\n", " t[t.depdelay, t.arrdelay, t.carrier_name, t.origin_state, t.dep_timestamp],\n", ").transform_filter(\n", " states\n", ").transform_filter(\n", " dates\n", ").transform_aggregate(\n", " depdelay='mean(depdelay)',\n", " arrdelay='mean(arrdelay)',\n", " groupby=[\"carrier_name\"]\n", ").mark_text().encode(\n", " x='depdelay',\n", " y='arrdelay',\n", " text='carrier_name',\n", ").properties(\n", " height=2 * HEIGHT / 3,\n", " width=WIDTH / 2\n", ")\n", "\n", "time = alt.Chart(\n", " t[t.dep_timestamp, t.depdelay, t.origin_state, t.carrier_name],\n", " title='Number of Flights by Departure Time'\n", ").transform_filter(\n", " 'datum.dep_timestamp != null'\n", ").transform_filter(\n", " airlines\n", ").transform_filter(\n", " states\n", ").mark_line().encode(\n", " alt.X(\n", " 'yearmonthdate(dep_timestamp):T',\n", " ),\n", " alt.Y(\n", " 'count():Q',\n", " scale=alt.Scale(zero=False)\n", " )\n", ").add_selection(\n", " dates\n", ").properties(\n", " height=HEIGHT / 3,\n", " width=WIDTH + 50\n", ")\n", "\n", "(\n", " (count_filter | count_total) &\n", " (flights_by_state | carrier_delay) &\n", " time\n", ").configure_axis(\n", " grid=False\n", ").configure_view(\n", " strokeOpacity=0\n", ")" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.6" } }, "nbformat": 4, "nbformat_minor": 4 }