{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "
+
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "[CartoDB](http://cartodb.com/) lets you easily make web-based maps driven by a PostgreSQL/PostGIS backend, so data management is easy. [Plotly](https://plotly.com) is a cloud-based graphing and analytics platform with [Python, R, & MATLAB APIs](https://plotly.com/api) where collaboration is easy. This IPython Notebook shows how to use them together to analyze earthquake data." ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Populating the interactive namespace from numpy and matplotlib\n" ] } ], "source": [ "# Import needed libraries\n", "%pylab inline\n", "import pandas as pd\n", "import plotly.plotly as py\n", "from plotly.graph_objs import *\n", "import plotly.tools as tls" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "**Getting started**\n", "\n", "1. Setup a free CartoDB account at [https://cartodb.com/signup](https://cartodb.com/signup) or use data linked in this notebook\n", "2. Use Plotly's sandbox account, or [sign-up](https://plotly.com/python/getting-started/). No downloads required." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Pandas's [`read_csv`](http://pandas.pydata.org/pandas-docs/dev/generated/pandas.io.parsers.read_csv.html) allows import via HTTP, FTP, etc. It's perfect for CartoDB's [SQL API](), which has the following template:\n", "```\n", "http://{account_name}.cartodb.com/api/v2/sql?q={custom_sql_statement}&format=csv\n", "```\n", "\n", "To get data from the data table in my CartoDB account, the following query grabs values we can graph, and converts the timestamp to work easily with plotly.\n", "\n", "```sql\n", "SELECT\n", " mag,\n", " magtype,\n", " type,\n", " to_char(time,'yyyy-mm-DD HH24:MI:SS') AS time_plotly,\n", " place,\n", " depth\n", "FROM\n", " all_month\n", "```\n", "\n", "All we need to do is replace the white space with `%20` so the URL is properly encoded." ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "url = \"http://andye.cartodb.com/api/v2/sql?\"\\\n", " \"q=SELECT%20mag,magtype,type,to_char(time,'yyyy-mm-DD%20HH24:MI:SS')%20AS%20time_plotly,place,depth%20FROM%20all_month\"\\\n", " \"&format=csv\"\n", "df = pd.read_csv(url)\n", "df = df.sort(['mag'], ascending=[0]);" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "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", "
magmagtypetypetime_plotlyplacedepth
3749 7.3 mww earthquake 2014-10-14 03:51:35 67km WSW of Jiquilillo, Nicaragua 40.00
1686 7.1 mww earthquake 2014-10-09 02:14:32 Southern East Pacific Rise 15.50
4602 7.1 mwc earthquake 2014-11-01 18:57:22 141km NE of Ndoi Island, Fiji 434.41
2855 6.6 mww earthquake 2014-10-09 02:32:05 Southern East Pacific Rise 10.00
7186 6.3 mwp earthquake 2014-10-11 02:35:46 154km ENE of Hachinohe, Japan 13.48
\n", "
" ], "text/plain": [ " mag magtype type time_plotly \\\n", "3749 7.3 mww earthquake 2014-10-14 03:51:35 \n", "1686 7.1 mww earthquake 2014-10-09 02:14:32 \n", "4602 7.1 mwc earthquake 2014-11-01 18:57:22 \n", "2855 6.6 mww earthquake 2014-10-09 02:32:05 \n", "7186 6.3 mwp earthquake 2014-10-11 02:35:46 \n", "\n", " place depth \n", "3749 67km WSW of Jiquilillo, Nicaragua 40.00 \n", "1686 Southern East Pacific Rise 15.50 \n", "4602 141km NE of Ndoi Island, Fiji 434.41 \n", "2855 Southern East Pacific Rise 10.00 \n", "7186 154km ENE of Hachinohe, Japan 13.48 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's take a look at the magnitude in a histogram. " ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "mag_histogram_plot = [{'x': df['mag'], \n", " 'type': 'histogram'\n", "}]" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [], "source": [ "data_histogram = Data(mag_histogram_plot)\n", "\n", "fig_histogram = Figure(data=data_histogram)" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "py.iplot(fig_histogram, filename='magnitude_histogram')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's check out the same data in a box plot. " ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [], "source": [ "mag_jitter_plot = [{'y': df['mag'], \n", " 'name': 'Earthquake Magnitude',\n", " 'type': 'box',\n", " 'boxpoints': 'outliers', \n", " 'jitter': 0.9,\n", "}]" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [], "source": [ "data_jitter = Data(mag_jitter_plot)\n", "\n", "fig_jitter = Figure(data=data_jitter)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "py.iplot(fig_jitter, filename='boxplot_with_jitter')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If we want to put the plot in a report, email, or presentation we can export the static version. The plot URL contains the data, code to reproduce the plot with MATLAB, R, and Python, and can be embedded. \n", "
\n", "
\n", "
\n", "- https://plotly.com/~Python-Demo-Account/1534.png\n", "- https://plotly.com/~Python-Demo-Account/1534.svg\n", "- https://plotly.com/~Python-Demo-Account/1534.pdf\n", "- https://plotly.com/~Python-Demo-Account/1534.eps\n", "- https://plotly.com/~Python-Demo-Account/1534.m\n", "- https://plotly.com/~Python-Demo-Account/1534.py\n", "- https://plotly.com/~Python-Demo-Account/1534.r\n", "- https://plotly.com/~Python-Demo-Account/1534.jl\n", "- https://plotly.com/~Python-Demo-Account/1534.json\n", "- https://plotly.com/~Python-Demo-Account/1534.embed\n", "
\n", "
\n", "
\n", "You and others you share the plot with can also collaborate and style the plot in the GUI. \n", "
\n", "
\n", "
\n", "\n", "
\n", "
\n", "
" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let's take another pass at it, and this time put both magnitude and depth in the same plot. " ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "location = df['place'] # manages serialization in early versions of Plotly Python client\n", "for i in range(len(location)):\n", " try:\n", " location[i] = str(location[i]).decode('utf-8')\n", " except:\n", " location[i] = 'Country name decode error'" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "trace1 = Scatter(\n", " x=df['depth'],\n", " y=df['mag'],\n", " text=location,\n", " mode='markers',\n", " marker=Marker(\n", " color='rgba(31, 119, 180, 0.15)', # add opacity for visibility\n", " )\n", ")\n", "layout = Layout(\n", " title='Earthquake Magnitude vs. Depth',\n", " xaxis=XAxis( type='log', title='depth' ),\n", " yaxis=YAxis( type='log', title='magnitude' ),\n", " hovermode=\"closest\",\n", ")\n", "data = Data([trace1])\n", "fig = Figure(data=data, layout=layout)\n", "py.iplot(fig, filename='Earthquake_basic')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "If you click and drag, you can zoom in on the plot. Hover your mouse to see data about each earthquake. Now, for our final plot, we can make a scatter plot over time, showing the magnitude on the y axis with the point sized for depth. " ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [], "source": [ "depth_time_plot = [Scatter({'y': df['mag'], \n", " 'x': df['time_plotly'],\n", " 'name': 'Earthquake Depth',\n", " 'mode': 'markers',\n", " 'text': df['place'],\n", " 'marker': {\n", " 'size': 20.0 * (df['depth'] + abs(df['depth'].min())) / (df['depth'].max() + abs(df['depth'].min()))\n", " }})]" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [], "source": [ "data_depth = Data(depth_time_plot)\n", "\n", "layout_depth = Layout(yaxis=YAxis(title='Magnitude of the Event'),xaxis=XAxis(title='Date of Event'),hovermode='closest')\n", "\n", "fig_depth = Figure(data=data_depth, layout=layout_depth )" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "py.iplot(fig_depth)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Moving over to CartoDB, you can import the data table into your account by copying the following URL and pasting it into the [CartoDB Importer](http://docs.cartodb.com/cartodb-editor.html#importing-data):\n", "\n", " http://andye.cartodb.com/api/v2/sql?q=SELECT%20*%20FROM%20all_month&format=csv&filename=earthquake_data_plotly\n", "\n", "This just uses the CartoDB [SQL API](http://docs.cartodb.com/cartodb-platform/sql-api.html) again, with the additional parameter `filename` that specifices the name of the datatable on import.\n", "\n", "By selecting the Torque in the [Visualization Wizard](http://docs.cartodb.com/cartodb-editor.html#wizards) you can get an animated map of the earthquakes over time. Make sure to select the `time` column in the wizard. By clicking on the `CSS` tab, you can customize your map further. Copy & Past the CartoCSS below the map to reproduce it's style." ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from IPython.display import HTML\n", "HTML('')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```css\n", "/** Torque visualization */\n", "Map {\n", "-torque-frame-count:512;\n", "-torque-animation-duration:30;\n", "-torque-time-attribute:\"time\";\n", "-torque-aggregation-function:\"max(mag)\";\n", "-torque-resolution:2;\n", "-torque-data-aggregation:linear;\n", "}\n", "\n", "#earthquake_data_plotly{\n", " comp-op: lighter;\n", " marker-fill-opacity: 0.9;\n", " marker-line-color: #FFF;\n", " marker-line-width: 0;\n", " marker-line-opacity: 1;\n", " marker-type: ellipse;\n", " marker-width: 6;\n", " marker-fill: #3E7BB6;\n", "}\n", "\n", "#earthquake_data_plotly[value >7] {\n", " marker-width: 20;\n", " marker-fill: #3e7bb6;\n", " [frame-offset=1] {\n", " marker-width:19;\n", " marker-fill-opacity:0.8;\n", " }\n", " [frame-offset=2] {\n", " marker-width:18;\n", " marker-fill-opacity:0.7; \n", " }\n", " [frame-offset=3] {\n", " marker-width:17;\n", " marker-fill-opacity:0.6; \n", " }\n", " [frame-offset=4] {\n", " marker-width:16;\n", " marker-fill-opacity:0.5; \n", " }\n", " [frame-offset=5] {\n", " marker-width:15;\n", " marker-fill-opacity:0.4;\n", " }\n", "}\n", "\n", "#earthquake_data_plotly[value<=7][value>6] {\n", " marker-width: 16;\n", " marker-fill: #C3CEFF;\n", " [frame-offset=1] {\n", " marker-width:14;\n", " marker-fill-opacity:0.7;\n", " }\n", " [frame-offset=2] {\n", " marker-width:13;\n", " marker-fill-opacity:0.6; \n", " }\n", " [frame-offset=3] {\n", " marker-width:12;\n", " marker-fill-opacity:0.5; \n", " }\n", " [frame-offset=4] {\n", " marker-width:11;\n", " marker-fill-opacity:0.4; \n", " }\n", "}\n", "\n", "#earthquake_data_plotly[value<=6][value>5] {\n", " marker-width: 12;\n", " marker-fill: #FFFFFF;\n", " [frame-offset=1] {\n", " marker-width:10;\n", " marker-fill-opacity:0.6;\n", " }\n", " [frame-offset=2] {\n", " marker-width:8;\n", " marker-fill-opacity:0.5; \n", " }\n", " [frame-offset=3] {\n", " marker-width:6;\n", " marker-fill-opacity:0.4;\n", " }\n", "}\n", "\n", "#earthquake_data_plotly[value<=5][value>4] {\n", " marker-width: 6;\n", " marker-fill: yellow; \n", " [frame-offset=1] {\n", " marker-width:4;\n", " marker-fill-opacity:0.5;\n", " }\n", " [frame-offset=2] {\n", " marker-width:2;\n", " marker-fill-opacity:0.4;\n", " }\n", "}\n", "\n", "#earthquake_data_plotly[value <= 4][value > 3] {\n", " marker-width: 3;\n", " marker-fill: orange;\n", " [frame-offset=1] {\n", " marker-width:2;\n", " marker-fill-opacity:0.4;\n", " }\n", " [frame-offset=2] {\n", " marker-width:1;\n", " marker-fill-opacity:0.3;\n", " }\n", "}\n", "\n", "#earthquake_data_plotly[value <= 3][value > 2] {\n", " marker-width: 2;\n", " marker-fill: red;\n", " [frame-offset=1] {\n", " marker-width:1.5;\n", " marker-fill-opacity:0.3;\n", " }\n", " [frame-offset=2] {\n", " marker-width:1;\n", " marker-fill-opacity:0.2;\n", " }\n", "}\n", "\n", "#earthquake_data_plotly[value <= 2] {\n", " marker-fill: #850200;\n", " marker-width: 0.5;\n", " [frame-offset=1] {\n", " marker-width:0;\n", " marker-fill-opacity:0;\n", " }\n", "}\n", "```" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "data": { "text/html": [ "" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stdout", "output_type": "stream", "text": [ "Requirement already up-to-date: publisher in /Users/chelsea/venv/venv2.7/lib/python2.7/site-packages\r\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "/Users/chelsea/venv/venv2.7/lib/python2.7/site-packages/IPython/nbconvert.py:13: ShimWarning: The `IPython.nbconvert` package has been deprecated since IPython 4.0. You should import from nbconvert instead.\n", " \"You should import from nbconvert instead.\", ShimWarning)\n", "/Users/chelsea/venv/venv2.7/lib/python2.7/site-packages/publisher/publisher.py:53: UserWarning: Did you \"Save\" this notebook before running this command? Remember to save, always save.\n", " warnings.warn('Did you \"Save\" this notebook before running this command? '\n" ] } ], "source": [ "from IPython.display import display, HTML\n", "\n", "display(HTML(''))\n", "display(HTML(''))\n", "\n", "! pip install publisher --upgrade\n", "import publisher\n", "publisher.publish(\n", " 'cartodb.ipynb', 'ipython-notebooks/cartodb/', 'CartoDB and Plotly', \n", " 'CartoDB and Plotly mashup using the Plotly, CartoDB, Pandas, and IPython Notebooks',\n", " name='CartoDB and Plotly')" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": true }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 2", "language": "python", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.10" } }, "nbformat": 4, "nbformat_minor": 1 }