Python/v3 > CartoDB and Plotly
Suggest an edit to this page

CartoDB and Plotly in Python/v3

CartoDB and Plotly mashup using the Plotly, CartoDB, Pandas, and IPython Notebooks


Note: this page is part of the documentation for version 3 of Plotly.py, which is not the most recent version.
See our Version 4 Migration Guide for information about how to upgrade.
+

CartoDB lets you easily make web-based maps driven by a PostgreSQL/PostGIS backend, so data management is easy. Plotly is a cloud-based graphing and analytics platform with Python, R, & MATLAB Open Source Graphing Libraries where collaboration is easy. This IPython Notebook shows how to use them together to analyze earthquake data.

In [1]:
# Import needed libraries
%pylab inline
import pandas as pd
import plotly.plotly as py
from plotly.graph_objs import *
import plotly.tools as tls

Populating the interactive namespace from numpy and matplotlib

Getting started

  1. Setup a free CartoDB account at https://cartodb.com/signup or use data linked in this notebook
  2. Use Plotly's sandbox account, or sign-up. No downloads required.

Pandas's read_csv allows import via HTTP, FTP, etc. It's perfect for CartoDB's SQL API, which has the following template:

http://{account_name}.cartodb.com/api/v2/sql?q={custom_sql_statement}&format=csv

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.

SELECT
   mag,
   magtype,
   type,
   to_char(time,'yyyy-mm-DD HH24:MI:SS') AS time_plotly,
   place,
   depth
FROM
  all_month

All we need to do is replace the white space with %20 so the URL is properly encoded.

In [3]:
url = "http://andye.cartodb.com/api/v2/sql?"\
      "q=SELECT%20mag,magtype,type,to_char(time,'yyyy-mm-DD%20HH24:MI:SS')%20AS%20time_plotly,place,depth%20FROM%20all_month"\
      "&format=csv"
df = pd.read_csv(url)
df = df.sort(['mag'], ascending=[0]);
In [4]:
df.head()
Out[4]:
mag magtype type time_plotly place depth
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

Let's take a look at the magnitude in a histogram.

In [5]:
mag_histogram_plot = [{'x': df['mag'], 
                 'type': 'histogram'
}]
In [6]:
data_histogram = Data(mag_histogram_plot)

fig_histogram = Figure(data=data_histogram)
In [7]:
py.iplot(fig_histogram, filename='magnitude_histogram')
Out[7]:

Let's check out the same data in a box plot.

In [8]:
mag_jitter_plot = [{'y': df['mag'], 
                 'name': 'Earthquake Magnitude',
                 'type': 'box',
                 'boxpoints': 'outliers', 
                 'jitter': 0.9,
}]
In [9]:
data_jitter = Data(mag_jitter_plot)

fig_jitter = Figure(data=data_jitter)
In [10]:
py.iplot(fig_jitter, filename='boxplot_with_jitter')
Out[10]:

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.


Let's take another pass at it, and this time put both magnitude and depth in the same plot.

In [11]:
location = df['place']  # manages serialization in early versions of Plotly Python client
for i in range(len(location)):
    try:
        location[i] = str(location[i]).decode('utf-8')
    except:
        location[i] = 'Country name decode error'
In [12]:
trace1 = Scatter(
    x=df['depth'],
    y=df['mag'],
    text=location,
    mode='markers',
    marker=Marker(
            color='rgba(31, 119, 180, 0.15)',  # add opacity for visibility
        )
)
layout = Layout(
    title='Earthquake Magnitude vs. Depth',
    xaxis=XAxis( type='log', title='depth' ),
    yaxis=YAxis( type='log', title='magnitude' ),
    hovermode="closest",
)
data = Data([trace1])
fig = Figure(data=data, layout=layout)
py.iplot(fig, filename='Earthquake_basic')
Out[12]:

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.

In [13]:
depth_time_plot = [Scatter({'y': df['mag'], 
               'x': df['time_plotly'],
               'name': 'Earthquake Depth',
               'mode': 'markers',
               'text': df['place'],
               'marker': {
                    'size': 20.0 * (df['depth'] + abs(df['depth'].min())) / (df['depth'].max() + abs(df['depth'].min()))
                }})]
In [14]:
data_depth = Data(depth_time_plot)

layout_depth = Layout(yaxis=YAxis(title='Magnitude of the Event'),xaxis=XAxis(title='Date of Event'),hovermode='closest')

fig_depth = Figure(data=data_depth, layout=layout_depth )
In [15]:
py.iplot(fig_depth)
Out[15]:

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://andye.cartodb.com/api/v2/sql?q=SELECT%20*%20FROM%20all_month&format=csv&filename=earthquake_data_plotly

This just uses the CartoDB SQL API again, with the additional parameter filename that specifices the name of the datatable on import.

By selecting the Torque in the Visualization Wizard 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.

In [4]:
from IPython.display import HTML
HTML('<iframe width=100% height=520 frameborder=0 src=https://andye.cartodb.com/viz/e44ac140-b8ad-11e4-b156-0e4fddd5de28/embed_map allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen></iframe>')
Out[4]:
/** Torque visualization */
Map {
-torque-frame-count:512;
-torque-animation-duration:30;
-torque-time-attribute:"time";
-torque-aggregation-function:"max(mag)";
-torque-resolution:2;
-torque-data-aggregation:linear;
}

#earthquake_data_plotly{
  comp-op: lighter;
  marker-fill-opacity: 0.9;
  marker-line-color: #FFF;
  marker-line-width: 0;
  marker-line-opacity: 1;
  marker-type: ellipse;
  marker-width: 6;
  marker-fill: #3E7BB6;
}

#earthquake_data_plotly[value >7] {
  marker-width: 20;
  marker-fill: #3e7bb6;
  [frame-offset=1] {
    marker-width:19;
    marker-fill-opacity:0.8;
  }
  [frame-offset=2] {
    marker-width:18;
    marker-fill-opacity:0.7; 
  }
  [frame-offset=3] {
    marker-width:17;
    marker-fill-opacity:0.6; 
  }
  [frame-offset=4] {
    marker-width:16;
    marker-fill-opacity:0.5; 
  }
  [frame-offset=5] {
    marker-width:15;
    marker-fill-opacity:0.4;
  }
}

#earthquake_data_plotly[value<=7][value>6] {
  marker-width: 16;
  marker-fill: #C3CEFF;
  [frame-offset=1] {
    marker-width:14;
    marker-fill-opacity:0.7;
  }
  [frame-offset=2] {
    marker-width:13;
    marker-fill-opacity:0.6; 
  }
  [frame-offset=3] {
    marker-width:12;
    marker-fill-opacity:0.5; 
  }
  [frame-offset=4] {
    marker-width:11;
    marker-fill-opacity:0.4; 
  }
}

#earthquake_data_plotly[value<=6][value>5] {
  marker-width: 12;
  marker-fill: #FFFFFF;
  [frame-offset=1] {
    marker-width:10;
    marker-fill-opacity:0.6;
  }
  [frame-offset=2] {
    marker-width:8;
    marker-fill-opacity:0.5; 
  }
  [frame-offset=3] {
    marker-width:6;
    marker-fill-opacity:0.4;
  }
}

#earthquake_data_plotly[value<=5][value>4] {
  marker-width: 6;
  marker-fill: yellow; 
  [frame-offset=1] {
    marker-width:4;
    marker-fill-opacity:0.5;
  }
  [frame-offset=2] {
    marker-width:2;
    marker-fill-opacity:0.4;
  }
}

#earthquake_data_plotly[value <= 4][value > 3] {
  marker-width: 3;
  marker-fill: orange;
  [frame-offset=1] {
    marker-width:2;
    marker-fill-opacity:0.4;
  }
  [frame-offset=2] {
    marker-width:1;
    marker-fill-opacity:0.3;
  }
}

#earthquake_data_plotly[value <= 3][value > 2] {
  marker-width: 2;
  marker-fill: red;
  [frame-offset=1] {
    marker-width:1.5;
    marker-fill-opacity:0.3;
  }
  [frame-offset=2] {
    marker-width:1;
    marker-fill-opacity:0.2;
  }
}

#earthquake_data_plotly[value <= 2] {
  marker-fill: #850200;
  marker-width: 0.5;
  [frame-offset=1] {
    marker-width:0;
    marker-fill-opacity:0;
  }
}