README.md

InsightFL Presentation @ Insight Data Science

Snippets of files used to create a data table and bubble chart.

bubblechart.html

{% extends "layout/base.html" %}

{% block head %}
  <link href="{{ url_for('static', filename='css/world.css') }}" rel="stylesheet">
{% endblock %}

{% block content %}
<div id="wrap">
  <div class="container">
    <div class="slider-form">
      <!-- Query builder form -->
      <form id="form" class="form-inline" role="form">
        <div>
          <input id="edu" class="slider" type="range" min="0.000" max="0.990" step="0.001" value="{{settings['edu_index']}}" name="edu_index" />
          <input id="edu-textbox" class="textbox" type="text" value="{{settings['edu_index']}}" maxlength="5" size="20" />
        </div>

        <div>
          <input id="age" class="slider" type="range" min="0" max="44" value="{{settings['median_age']}}" name="median_age" />
          <input id="age-textbox" class="textbox" type="text" value="{{settings['median_age']}}" maxlength="2" />
        </div>

        <div>
          <input id="gdp" class="slider" type="range" min="0" max="1000000" step="10000" value="{{settings['gdp']}}" name="gdp" />
          <input id="gdp-textbox" class="textbox" type="text" value="{{settings['gdp']}}" maxlength="10" size="20" />
        </div>

        <select id="order_by" name="order_by">
          <option value="country" {% if order_by == "country" %} selected="selected" {% endif %}>Country</option>
          <option value="edu_index" {% if order_by == "edu_index" %} selected="selected" {% endif %}>Education Index</option>
          <option value="median_age" {% if order_by == "median_age" %} selected="selected" {% endif %}>Median Age</option>
          <option value="gdp" {% if order_by == "gdp" %} selected="selected" {% endif %}>GDP</option>
        </select>

        <select id="sort" name="sort">
          <option value="ASC" {% if sort == "ASC" %} selected="selected" {% endif %}>Ascending</option>
          <option value="DESC" {% if sort == "DESC" %} selected="selected" {% endif %}>Descending</option>
        </select>
        <button id="reset" type="submit" class="btn btn-default">Reset</button>
      </form>
    </div>

    <!-- Bubblechart goes here -->
    <div id="chart"></div>

    <!-- Tooltip div that appears when mousing over points on the Bubblechart -->
    <div id="tooltip" class="hidden">
      <p style="text-align: center;"><strong>World Index</strong></p>
      <p><span id="value"></span></p>
    </div>

  </div>
</div>
{% endblock %}

{% block footer %}
  {% include "include/footer.html" %}
{% endblock %}

{% block scripts %}
  <script>
    // Passing data to the global window.
    var data = {{ settings['data'] | safe }};
  </script>
  <script src="{{ url_for('static', filename='js/form-controls.js') }}"></script>
  <script src="{{ url_for('static', filename='js/bubblechart.js') }}"></script>
{% endblock %}

bubblechart.js

(function (data) {
    'use strict';

    /* Bubble Chart */
    var margin,
        width,
        height,
        x,
        y,
        color,
        xAxis,
        yAxis,
        svg,
        text,
        bubbles;

    // Establishing chart margins, width, and height.
    margin = { top: 20, right: 20, bottom: 50, left: 100 };
    width = 960 - margin.left - margin.right;
    height = 500 - margin.top - margin.bottom;

    // Creating the x scale.
    x = d3.scale.linear().range([0, width]);

    // Creating the y scale.
    y = d3.scale.linear().range([height, 0]);

    // Built in d3 color function.
    color = d3.scale.category20();

    // x axis.
    xAxis = d3.svg.axis().scale(x).orient("bottom");

    // y axis.
    yAxis = d3.svg.axis().scale(y).orient("left");

    // SVG canvas.
    svg = d3.select("#chart").append("svg")
        .attr("width", width + margin.left + margin.right)
        .attr("height", height + margin.top + margin.bottom)
        .append("g")
        .attr("transform", "translate(" + margin.left + "," + margin.top + ")");

    data.forEach(function (d) {
        d.country = d.country;
        d.gdp = +d.gdp;
        d.edu_index = +d.edu_index;
        d.median_age = +d.median_age;
    });

    x.domain(d3.extent(data, function (d) { return d.median_age; }));
    y.domain(d3.extent(data, function (d) { return d.edu_index; }));

    // Creating the x axis.
    svg.append("g")
        .attr("class", "x axis")
        .attr("transform", "translate(0," + height + ")")
        .call(xAxis)
        // x axis label
        .append("text")
        .attr("class", "x label")
        .attr("x", function () { return width/2; })
        .attr("y", 30)
        .attr("dy", ".71em")
        .style("text-anchor", "middle")
        .text("Median Age");

    // Creating the y axis.
    svg.append("g")
        .attr("class", "y axis")
        .call(yAxis)
        // y axis label
        .append("text")
        .attr("class", "y label")
        .attr("transform", "rotate(-90)")
        .attr("y", -60)
        .attr("x", -height/2)
        .attr("dy", ".71em")
        .style("text-anchor", "middle")
        .text("Education Index");

    /* Creating clipPath which prevents bubbles from being drawn
     * outside the SVG container window.
     */
    svg.append("clipPath")
        .attr("id", "chart-area")
        .append("rect")
        .attr("x", 0)
        .attr("y", 0)
        .attr("width", width)
        .attr("height", height);

    // Creating the bubble chart.
    bubbles = svg.append("g")
        .attr("id", "circles")
        .attr("clip-path", "url(#chart-area)")
        .selectAll(".country").append("circle")
        .data(data)
        .enter()
        .append("circle")
        .attr("class", function (d) { return d.country; })
        .style("fill", function (d) { return color(d.country); });

    // Calculating the position of the bubbles on the x and y axis.
    bubbles
        .attr("cx", function (d) { return x(d.median_age); })
        .attr("cy", function (d) { return y(d.edu_index); })
        .attr("r", function (d) { return Math.log(d.gdp); });

    // Creating the tooltips for the bubble chart.
    bubbles
        .on("mouseover", function (d) {
            var mousemove = { left: d3.event.pageX, top: d3.event.pageY},
                scrolltop = document.body.scrollTop,
                hh = d3.select('#tooltip')[0][0].scrollHeight;

            d3.select("#tooltip")
                .style('top', mousemove.top - scrolltop - hh/2 + 'px')
                .style('left', mousemove.left + 20 + 'px')
                .select("#value")
                .html(
                    "Country: " + d.country +
                        "<br>Education Index: " + d.edu_index +
                        "<br>Median Age: " + d.median_age +
                        "<br>GDP: $" + numberWithCommas(d.gdp) + "M"
                );
            d3.select("#tooltip").classed("hidden", false);
        })
        .on("mouseout", function () {
            d3.select("#tooltip").classed("hidden", true);
        });

    function numberWithCommas(x) {
        return x.toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
    }

    return svg;
}( window.data ));

database.py

import pymysql
import sys
import simplejson as json


# Returns MySQL database connection
def con_db(host, port, user, passwd, db):
    try:
        con = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)

    except pymysql.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)

    return con


def query_db(con, dict):
    data_array = []

    # Request args
    edu_index = dict["edu_index"]
    median_age = dict["median_age"]
    gdp = dict["gdp"]
    order_by = dict["order_by"]
    sort = dict["sort"]

    # Query database
    cur = con.cursor()
    cur.execute(
        """
        SELECT id, country, median_age, gdp, edu_index
        FROM world_index
        WHERE edu_index >= {0}
        AND median_age >= {1}
        AND gdp >= {2}
        ORDER BY {3} {4}
        """.format(edu_index, median_age, gdp, order_by, sort)
    )

    data = cur.fetchall()
    for country in data:
        index = {}

        index["id"] = country[0]
        index["country"] = country[1]
        index["median_age"] = float(json.dumps(country[2]))
        index["gdp"] = country[3]
        index["edu_index"] = float(json.dumps(country[4]))

        data_array.append(index)

    cur.close()
    con.close()
    return data_array

filters.py

# Formats number values into dollar currency
# e.g. 123690 is changed to $123,690M
def format_currency(value):
    return "${:,}M".format(value)

form-controls.js

// jQuery code for querying/filtering data based on form controls

// Updates the slider when input text values are changed.
$("#edu-textbox, #age-textbox, #gdp-textbox").on("change", function() {
    var eduChange = $("#edu-textbox").val(),
        ageChange = $("#age-textbox").val(),
        gdpChange = $("#gdp-textbox").val();

    $("#edu").val(eduChange);
    $("#age").val(ageChange);
    $("#gdp").val(gdpChange);
});

// Submits form when slider is moved.
$("#edu, #age, #gdp, #order_by, #sort").on("change", function() {
    $("#form").submit();
});

// Reset form to default values.
$("#reset").on("click", function() {
    // Set slider values to 0.
    $("#edu").val(0);
    $("#age").val(0);
    $("#gdp").val(0);

    // Default order and sort by
    $("#order_by").val("edu_index");
    $("#sort").val("DESC");

    // Submit form
    $("#form").submit();
});

table.html

{% extends "layout/base.html" %}

{% block content %}
  <div id="wrap">
    <div class="container">
      <div id="table">
        <div class="table-responsive">
          <table class="table table-bordered table-condensed table-striped">
            <thead>
              <th>Index</th>
              <th>Country</th>
              <th>Education Index</th>
              <th>Median Age</th>
              <th>GDP</th>
            </thead>
            <tbody>
              {% for line in settings['data'] %}
              <tr>
                <td>{{loop.index}}</td>
                <td class="{{line.country}}">{{line.country}}</td>
                <td>{{line.edu_index}}</td>
                <td>{{line.median_age}}</td>
                <td>{{line.gdp | format_currency}}</td>
              </tr>
              {% endfor %}
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
{% endblock %}

{% block footer %}
  {% include "include/footer.html" %}
{% endblock %}

views.py

...
from app.helpers.database import con_db, query_db
from app.helpers.filters import format_currency
import jinja2


# ROUTING/VIEW FUNCTIONS
@app.route('/', methods=['GET'])
def index():
    # Create database connection
    con = con_db(host, port, user, passwd, db)

    # Add custom filter to jinja2 env
    jinja2.filters.FILTERS['format_currency'] = format_currency

    var_dict = {
        "country": request.args.get("country"),
        "edu_index": request.args.get("edu_index", '0'),
        "median_age": request.args.get("median_age", '0'),
        "gdp": request.args.get("gdp", '0'),
        "order_by": request.args.get("order_by", "edu_index"),
        "sort": request.args.get("sort", "DESC")
    }

    # Query the database
    data = query_db(con, var_dict)

    # Add data to dictionary
    var_dict["data"] = data

    return render_template('table.html', settings=var_dict)
...

world.css

.slider-form {
  margin-bottom: 10px;
}

input.slider {
  width: 25%;
  display: inline-block;
}

div#table {
    height: 100%;
    overflow: scroll;
}

body {
    /*font: 20px sans-serif;*/
    fill: #444;
    overflow: scroll;
}

.bubbles {
    font: 10px sans-serif;
}

.description > p {
    color: #444;
    text-align: left;
    margin-left: 100px;
}

#tooltip {
    position: absolute;
    width: auto;
    height: auto;
    padding: 10px;
    background-color: white;
    -webkit-border-radius: 10px;
    -moz-border-radius: 10px;
    border-radius: 10px;
    -webkit-box-shadow: 4px 4px 10px rgba(0, 0, 0, 0.4);
    -moz-box-shadow: 4px 4px 10px rgba(0, 0, 0, 0.4);
    box-shadow: 4px 4px 10px rgba(0, 0, 0, 0.4);
    pointer-events: none;
}

#tooltip.hidden {
    display: none;
}

#tooltip p {
    margin: 0;
    font-family: sans-serif;
    font-size: 16px;
}

.axis path,
.axis line {
    fill: none;
    stroke: grey;
    shape-rendering: crispEdges;
}

world.html

{% extends "layout/base.html" %}

{% block head %}
  <link href="{{ url_for('static', filename='css/world.css') }}" rel="stylesheet">
{% endblock %}

{% block content %}
  <div id="wrap">
    <div class="container">
      <div class="slider-form">
        <form id="form" class="form-inline" role="form">
          <div>
            <input id="edu" class="slider"
                   type="range" min="0.000" max="0.990" step="0.001" value="{{settings['edu_index']}}" name="edu_index" />
            <input id="edu-textbox" class="textbox" type="text" value="{{settings['edu_index']}}" maxlength="5" size="20" />
          </div>

          <div>
            <input id="age" class="slider" type="range" min="0" max="44" value="{{settings['median_age']}}" name="median_age" />
            <input id="age-textbox" class="textbox" type="text" value="{{settings['median_age']}}" maxlength="2" />
          </div>

          <div>
            <input id="gdp" class="slider" type="range" min="0" max="1000000" step="10000" value="{{settings['gdp']}}" name="gdp" />
            <input id="gdp-textbox" class="textbox" type="text" value="{{settings['gdp']}}" maxlength="10" size="20" />
          </div>

          <select id="order_by" name="order_by">
            <option value="country" {% if order_by == "country" %} selected="selected" {% endif %}>
              Country
            </option>
            <option value="edu_index" {% if order_by == "edu_index" %} selected="selected" {% endif %}>
              Education Index
            </option>
            <option value="median_age" {% if order_by == "median_age" %} selected="selected" {% endif %}>
              Median Age
            </option>
            <option value="gdp" {% if order_by == "gdp" %} selected="selected" {% endif %}>
              GDP
            </option>
          </select>

          <select id="sort" name="sort">
            <option value="ASC" {% if sort == "ASC" %} selected="selected" {% endif %}>Ascending</option>
            <option value="DESC" {% if sort == "DESC" %} selected="selected" {% endif %}>Descending</option>
          </select>
          <button id="reset" type="submit" class="btn btn-default">Reset</button>
        </form>
      </div>

      <div id="chart"></div>
      <div id="tooltip" class="hidden">
        <p style="text-align: center;"><strong>World Index</strong></p>
        <p><span id="value"></span></p>
      </div>

      <div id="table">
        <div class="table-responsive">
          <table class="table table-bordered table-condensed table-striped">
            <thead>
              <th>Index</th>
              <th>Country</th>
              <th>Education Index</th>
              <th>Median Age</th>
              <th>GDP</th>
            </thead>
            <tbody>
              {% for line in settings['data'] %}
              <tr>
                <td>{{loop.index}}</td>
                <td class="{{line.country}}">{{line.country}}</td>
                <td>{{line.edu_index}}</td>
                <td>{{line.median_age}}</td>
                <td>{{line.gdp | format_currency}}</td>
              </tr>
              {% endfor %}
            </tbody>
          </table>
        </div>
      </div>
    </div>
  </div>
{% endblock %}

{% block footer %}
  {% include "include/footer.html" %}
{% endblock %}

{% block scripts %}
  <script src="http://d3js.org/d3.v3.min.js" charset="utf-8"></script>
  <script>
    // Passing data to global.window; data is global.
    var data = {{ settings['data'] | safe }};
  </script>
  <script src="{{ url_for('static', filename='js/bubblechart.js') }}"></script>
  <script src="{{ url_for('static', filename='js/form-control.js') }}"></script>
{% endblock %}