Summary

Given an SQLite database containing information about the biodiversity of specific bacterial cultures, I was tasked with creating a dynamic dashboard that queries the database and outputs visualizations based on specific set selections. So, this assignment required an understanding of how to curate a front-end and a back-end. This task was accomplished using a combination of flask and javascript. The app was ultimately hosted on Heroku.

I used MySQL workbench to get a basic idea of what the data looks like. The columns afforded included often repeated information: i.e., the infromation ascribed to the set itself (the metadata). It also included single rows for every bacteria, with id, labels, and values ascribed to each one.

The Python app itself relies on SQLAlchemy to quickly and efficiently query the database. Using the selected sample, I needed to harvest the metadata and the sample information for each bacteria. To this end, I created two separate flask paths. They each returned json objects. The sample data was returned as a list of values.

The Javascript consisted mainly of taking the already created json objects, and converting them into traces that could be used by plotly to create the charts of interest.


Solution


Python




        import os

import pandas as pd
import numpy as np

import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

from flask import Flask, jsonify, render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)


#################################################
# Database Setup
#################################################

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///db/bellybutton.sqlite"
db = SQLAlchemy(app)

# reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(db.engine, reflect=True)

# Save references to each table
Samples_Metadata = Base.classes.sample_metadata
Samples = Base.classes.samples


@app.route("/")
def index():
    """Return the homepage."""
    return render_template("index.html")


@app.route("/names")
def names():
    """Return a list of sample names."""

    # Use Pandas to perform the sql query
    stmt = db.session.query(Samples).statement
    df = pd.read_sql_query(stmt, db.session.bind)

    # Return a list of the column names (sample names)
    return jsonify(list(df.columns)[2:])


@app.route("/metadata/")
def sample_metadata(sample):
    """Return the MetaData for a given sample."""
    sel = [
        Samples_Metadata.sample,
        Samples_Metadata.ETHNICITY,
        Samples_Metadata.GENDER,
        Samples_Metadata.AGE,
        Samples_Metadata.LOCATION,
        Samples_Metadata.BBTYPE,
        Samples_Metadata.WFREQ,
    ]

    results = db.session.query(*sel).filter(Samples_Metadata.sample == sample).all()

    # Create a dictionary entry for each row of metadata information
    sample_metadata = {}
    for result in results:
        sample_metadata["sample"] = result[0]
        sample_metadata["ETHNICITY"] = result[1]
        sample_metadata["GENDER"] = result[2]
        sample_metadata["AGE"] = result[3]
        sample_metadata["LOCATION"] = result[4]
        sample_metadata["BBTYPE"] = result[5]
        sample_metadata["WFREQ"] = result[6]

    print(sample_metadata)
    return jsonify(sample_metadata)


@app.route("/samples/")
def samples(sample):
    """Return `otu_ids`, `otu_labels`,and `sample_values`."""
    stmt = db.session.query(Samples).statement
    df = pd.read_sql_query(stmt, db.session.bind)

    # Filter the data based on the sample number and
    # only keep rows with values above 1
    sample_data = df.loc[df[sample] > 1, ["otu_id", "otu_label", sample]]
    # Format the data to send as json
    data = {
        "otu_ids": sample_data.otu_id.values.tolist(),
        "sample_values": sample_data[sample].values.tolist(),
        "otu_labels": sample_data.otu_label.tolist(),
    }
    return jsonify(data)


if __name__ == "__main__":
    app.run()


    

Javascript




function buildMetadata(sample) {

  // @TODO: Complete the following function that builds the metadata panel

  // Use `d3.json` to fetch the metadata for a sample
    // Use d3 to select the panel with id of `#sample-metadata`

    d3.json(`/metadata/${sample}`).then((data) => {
      console.log(data);
      // Use d3 to select the panel with id of `#sample-metadata`
      var metadata = d3.select("#sample-metadata");
      
      // Use `.html("") to clear any existing metadata
      metadata.html("");
    
      // Use `Object.entries` to add each key and value pair to the panel
      // Hint: Inside the loop, you will need to use d3 to append new
      // tags for each key-value in the metadata.
      Object.entries(data).forEach(([key, value]) => {
        console.log(key, value);
        metadata.append("h6").text(`${key}: ${value}`);
      });
    
      // BONUS: Build the Gauge Chart
      // buildGauge(data.WFREQ);
    });

    // Use `.html("") to clear any existing metadata

    // Use `Object.entries` to add each key and value pair to the panel
    // Hint: Inside the loop, you will need to use d3 to append new
    // tags for each key-value in the metadata.

    // BONUS: Build the Gauge Chart
    // buildGauge(data.WFREQ);
};

function buildCharts(sample) {

  // @TODO: Use `d3.json` to fetch the sample data for the plots
  d3.json(`/samples/${sample}`).then((data) => {
    console.log(data);
    var otu_ids = data.otu_ids;
    var otu_labels = data.otu_labels;
    var sample_values = data.sample_values;
    // console.log(otu_ids,otu_labels,sample_values);



    // @TODO: Build a Bubble Chart using the sample data
    var bubbleLayout_1 = {
      margin:{t:0},
      hovermode: "closest",
      xaxis: {title:"OTU ID"}
    };
    let bubbleData_1 = [
      {
        x: otu_ids,
        y: sample_values,
        text: otu_labels,
        mode: "markers",
        marker: {
          size: sample_values,
          color: otu_ids,
          colorscale: "Earth"
        }
      }
    ]
    Plotly.newPlot("bubble", bubbleData_1, bubbleLayout_1);




    // @TODO: Build a Pie Chart

    var pieData_1 = [
      {
        values: sample_values.slice(0,10),
        labels: otu_ids.slice(0, 10),
        hovertext: otu_labels.slice(0,10),
        hoverinfo: "hovertext",
        type: "pie"
      }
    ];

    var pieLayout_1 = {
      margin: {t: 0, l: 0}
    };

    Plotly.newPlot("pie", pieData_1, pieLayout_1);


    // HINT: You will need to use slice() to grab the top 10 sample_values,
    // otu_ids, and labels (10 each).
  });
};

function init() {
  // Grab a reference to the dropdown select element
  var selector = d3.select("#selDataset");

  // Use the list of sample names to populate the select options
  d3.json("/names").then((sampleNames) => {
    sampleNames.forEach((sample) => {
      selector
        .append("option")
        .text(sample)
        .property("value", sample);
    });

    // Use the first sample from the list to build the initial plots
    const firstSample = sampleNames[0];
    buildCharts(firstSample);
    buildMetadata(firstSample);
  });
}

function optionChanged(newSample) {
  // Fetch new data each time a new sample is selected
  var bubbleSelector = d3.select("bubble");
  bubbleSelector.html("");
  buildCharts(newSample);
  buildMetadata(newSample);
  console.log("We are registering a change.")
  console.log(newSample)
}

// Initialize the dashboard
init();

    
>