1 of 25

Code: Web App Data Visualization

Getting Started

Create the HTML, CSS, and Javascript files below and you are off to the races. Click on the file name to get the code content for each.

Ensure that your actual JavaScript filename matches the script element in the HTML <script src="code.js"></script>

<!DOCTYPE html>
<html>

<head>
	<meta charset="utf-8">
	<meta name="viewport" content="width=device-width">
	<title>Dashboard Template</title>
	<link href="style.css" rel="stylesheet" type="text/css" />
</head>

<body>
	<h1>Data Dashboard</h1>
	<div id='page'>
    	<div class="card">
			<div id="chart1"></div>
		</div>
		<div class="card">
			<div id="chart2"></div>
		</div>
	</div>

	<!--Javascript-->
	<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>
	<script src='https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js'></script>
	<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
	<script src="https://cdn.jsdelivr.net/alasql/0.2/alasql.min.js"></script>
	<script src="https://cdn.jsdelivr.net/gh/idewcomputing/code-dataviz-dashboard@master/src/viz.js"></script>
	<script src="code.js"></script>
</body>

</html>

function setup() {
  var degreesLink = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQUwF7K2lCH8CxQPJW-X7NqENwuwUM4eAeNUKw3j6yppO0tipC6yUB2hQPtRBZf19mmVbM5TdkkengZ/pub?gid=1030028695&single=true&output=csv';
  viz.loadTable(degreesLink,"Degrees").then(composeDegreeCharts);
  window.onresize = composeDegreeCharts;
}

function composeDegreeCharts() {
  viz.chart({
    sql:"SELECT Major, StartingMedianSalary FROM Degrees",
    chartType:"BarChart",
    containerId:"chart1"
  });

  viz.chart({
    sql:"SELECT * FROM Degrees",
    chartType:"Table",
    containerId:"chart2"
  });
}

window.onload = setup;

body {
  padding: 15px;
  margin: 0;
  background: #eee;
}

#page {
  display: grid;
  /* grid-template-columns: repeat(auto-fill, minmax(400px, 1fr)); */
  grid-gap: 15px;
}

h1 {
  font-family: 'Arial';
  text-align: center;
}

.card {
  box-shadow: 0 4px 8px 0 rgba(0, 0, 0, 0.4), 0 6px 20px 0 rgba(0, 0, 0, 0.4);
  overflow: hidden;
  background: white;
}

.description {
  padding: 15px 7px;
}

Note: August 30, 2021 the Papaparse Javascript library was added to the template in the HTML above with the following line ...

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

Copyright and License

This material is part of the Computing by Design high school computer science curriculum developed for the Informatics Diversity-Enhanced Workforce (iDEW) program, an award-winning community partnership in central Indiana that is broadening student participation in computing and helping students develop the skills essential for success in the 21st century workplace. The iDEW program is managed by the Indiana University School of Informatics and Computing at IUPUI.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. You are free to use, share, or adapt this material for noncommercial purposes as long as you provide proper attribution and distribute any copies or adaptations under this same license.

Dashboard Template Tutorial

1 - First Look at Template

The dashboard template provides the HTML/CSS/JS needed to get started with web-based data visualization. The template uses the following tools to make things easier for us.

Google Sheets - This will function as our "database" of tables.
Structured Query Language (SQL) - SQL will be used to pull the right data from the database for visualizing.
- This library will allow us to create charts in our web app.

Instructions

1 - Create files in a new Repl.it or other code environment.

Use the code from to create your HTML, Javascript, and CSS files. Verify that the dashboard displays an example bar chart and table.

2 - Review the HTML

Notice the basic page structure for the "cards" that contain elements for the charts. See below for reference.

3 - Review the CSS

Notice the "card" class that creates the shadow effect you see with the charts.

4 - Review the Javascript

The function composeDegreeCharts() runs automatically once the data is loaded.
In composeDegreeCharts() we create our charts by specifying SQL (more on this later), the chart type, and the HTML element it should be placed.

That's the quick overview of the template. We will dig into the details and how to customize things next.

2 - SQL Introduction

is a standard language for accessing and manipulating databases. Databases are just a group of data tables. Below is an example of a table called "Degrees" that could be in a database.

We can then use SQL to "filter" the table. For example the following SQL statement..

would generate the following table.

We simply SELECT columns FROM a table. Above we selected the columns "Major" and "StartingMedianSalary" from the "Degrees" table.

We can also use * (a wild card) for the columns to get all the columns. So, SELECT * FROM Degrees will result in the full table.

Try It OUT

1 - Find the following Javascript in your code.js file.

Notice how this code generates the table in your web view, where the table has a lot of columns (actually, all of them).

2 - Now change the SQL to the following to select only two columns.

SELECT Major, StartingMedianSalary FROM Degrees

Run your web app and notice how the table has changed.

3 - Now try the following SQL to get all the columns from a different table.

SELECT * FROM States

This gets a different table, "States", that can be found in our example database.

You will use SQL to get the right data from a database (Google Sheets file) for the charts you want to display. Next, let's look at producing the actual charts.

3 - Producing Charts

Making basic charts with the "viz" library is pretty easy. Let's get started.

Try It Out

1 - Find the following Javascript in your code.js file.

This generates a bar chart (chartType: "BarChart") using data specified by the SQL statement(sql: "SELECT Major, StartingMedianSalary FROM Degrees") and places the chart in an HTML element with an id of "chart1" (containerId:"chart1"). Notice that you should have a <div> tag with that id in your HTML file.

2 - Change the chartType to "ColumnChart"

In your Javascript, change the chart type to "ColumnChart". Run the web app and notice how the chart changes. It may not look good since there are so many columns squished together. Choosing the right way to visualize the data will be part of the project challenge.

3 - Change the chartType to "Histogram"

In your Javascript, change the chart type to "Histogram". Run the web app and notice how the chart changes.

4 - Add Another Chart that Uses a Different Data Table

Copy and add the following Javascript into your composeCharts( ) function.

You will also want to add the following HTML to your index.html file to provide a place for your chart.

More Charts and More Options

4 - Creating Your Own Database (Google Sheet)

⚠️ Important Note: This page was updated significantly August 30, 2021 to fix a new problem with loading spreadsheet data.

For reference, when you want to find real-world data, consider using this resource.

1 - Create a Google Sheets File and Add Data

Create a Google Sheet file then create a simple table, like below, that could be used for a Scatter Chart. Also, name your individual sheet (the tab at the bottom) something like "myData".

2 - Publish Your Google Sheets File

Create a Google Sheets file and publish it by selection file>Publish to the web... then click the Publish button.

3 - Get the Public Link for a Table

4 - Load Your Table in Javascript

Copy the code below and paste it just after this line. Now replace the link with your table link copied in the previous step and change the tab label (e.g. 'myData') to match your sheet tab label. You can change the composeMyCharts function name as you wish or keep it the same.

Now you can access your database in your SQL statements. Remember that your individual sheet names are the tables you can select from in SQL. So if you want to get all the columns from a sheet (table) called "myData", then you would use the following:SELECT * FROM myData

5 - Create Charts from Your New Table

Create a function in your Javascript that will then define the charts for your new table. You can use the example below as a template, but you want the ensure the function name used in step 3 (e.g. composeMyCharts) matches this function name. You will also want to change the SQL and ensure that you have HTML elements with ID's to display the charts.

For any additional tables (Spreadsheet Tabs) you want to incorporate, you will repeat steps 2 through 5 above with the appropriate link, tab name, and additional function for composing charts.

SQL Examples

WHERE

The WHERE clause is used to filter records based on a condition. The following SQL statement will return a table that only contains rows where the "StartingMedianSalary" is greater than 60000.

You can use the following operators for the comparison.

LIKE

The LIKE operator is used with the WHERE clause and searches for a string pattern in a column. The statement below will return only the rows where the "Major" is "Accounting".

The statement below uses "%" as wildcard. So any row with a "Major" that begins with "A" will be returned--Accounting, Agriculture, Art, etc.

ORDER BY

The "ORDER BY" keyword simply sorts the rows of the table based on the column name provided. You can follow the ORDER BY columnName with "ASC" or "DESC" (for ascending or descending) to dictate the direction of the sort.

LIMIT

Use "LIMIT" at the end to return only a defined number of rows from the top of the result. It is often used with "ORDER BY" like below.

Chart Examples

Bar Charts

The "options" parameter included below demonstrates many style changes you can make to a bar chart.

viz.chart({
    sql: "SELECT Major, StartingMedianSalary, MidCareerMedianSalary FROM Degrees",
    chartType: "BarChart",
    containerId: "chart1",
    options: {
      title: "Salaries by Degree",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
      legend: {
        position: "right"
      },
      colors: ["#bb0000", "#ffaa00"],
      hAxis: {
        title: "Annual Salary",
        format: "currency"
      }
    }
});

Column Charts

The "options" parameter included below demonstrates many style changes you can make to a Column chart.

viz.chart({
    sql: "SELECT Major, StartingMedianSalary, MidCareerMedianSalary FROM Degrees",
    chartType: "ColumnChart",
    containerId: "chart1",
    options: {
      title: "Salaries by Degree",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
      legend: {
        position: "right"
      },
      colors: ["#bb0000", "#ffaa00"],
      vAxis: {
        title: "Annual Salary",
        format: "currency"
      }
    }
});

Pie/Donut Chart

  viz.chart({
    sql: "SELECT Year, Total FROM Trend2",
    chartType: "PieChart",
    containerId: "chart1",
    options: {
      title: "Title Text",
      pieHole: 0.4,
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
      legend: {
        position: "right"
      }
    }
  });

Histograms

The "options" parameter included below demonstrates many style changes you can make to a Histogram chart.

viz.chart({
    sql: "SELECT Major, StartingMedianSalary, MidCareerMedianSalary FROM Degrees WHERE StartingMedianSalary <> 46000",
    chartType: "Histogram",
    containerId: "chart1",
    options: {
      title: "Salaries by Degree",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000", "#ffaa00"],
      vAxis: {
        title: "Frequency",
      }
    }
});

Scatter Charts

The "options" parameter included below demonstrates many style changes you can make to a Scatter Chart.

  viz.chart({
    sql: "SELECT Year, Total FROM Trend", 
    chartType: "ScatterChart",
    containerId: "chart3",
    options: {
      title: "Trend in Tech Jobs",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000"],
      vAxis: {
        title: "Jobs in US \n(in Thousands)"
      },
      hAxis: {
        title: "Year",
        format: "####"
      }
    }
  });

Line Charts

The "options" parameter included below demonstrates many style changes you can make to a Line Chart.

viz.chart({
    sql: "SELECT Year, Total FROM Trend", 
    chartType: "LineChart",
    containerId: "chart3",
    options: {
      title: "Trend in Tech Jobs",
      titleTextStyle: {
        color: "#777777",
        fontName: "Arial",
        fontSize: 18
      },
        legend: {
        position: "right"
      },
      colors: ["#bb0000"],
      vAxis: {
        title: "Jobs in US \n(in Thousands)"
      },
      hAxis: {
        title: "Year",
        format: "####"
      }
    }
  });

Bubble Charts

The "options" parameter included below demonstrates many style changes you can make to a Bubble Chart.

Geo Charts

Geo Charts (Markers)

Tree Maps

Dashboard Layout Examples

Selectable Chart Data

This example demonstrates how you can use a dropdown menu to allow a user to select, or filter, the data displayed on a column chart.

Uses, the , and the.

HTML

The select element will be used to filter the data for a column chart that will be inserted in the "chart2" div.

Javascript

Add the following code to the bottom of the setup() function. This will "listen" for changes to the dropdown selector and re-run the composeCharts() function when it is changed.

The following "viz.chart" function defines a column chart that will use the value selected by the user in the SQL to retrieve the right column of data to chart.

Custom Grid 1

To create a grid layout like the sketch above, adjust your HTML and CSS like the following examples. But be careful not to simply copy and paste everything here.

HTML

Notice that the "card" for the first chart also has a class called "full-width". We will use this class to make a chart span the full width of the page, while the others will fall into a grid with 3 charts per row.

CSS

First, notice the id="page" (#page) style defines the normal grid design of 3 elements (or 3 columns) per row. grid-template-columns: 1fr 1fr 1fr; This applies to all the grid elements inside the id = "page" element.

Next, any element with the class "full-width" will start at the first position and span 3 columns. grid-column: 1 / span 3;

We can also add a style (look for @media ... below ) that makes our dashboard mobile-friendly where all the charts just stack on top of each other if the screen is less than 700px wide.