Skip to content

Instantly share code, notes, and snippets.

@stedn stedn/.block

Last active May 11, 2020
Embed
What would you like to do?
Timeline Streamgraph from Google Sheets Data
license:cc-by-sa-4.0
height:600
scrolling:yes

Timeline Streamgraph from Google Sheets Data

A timeline streamgraph is a modification of a conventional streamgraph, flipped on its side so that time flows from bottom to top, similar to how we view timelines on social media and blogs. To learn more check out my blog post.

This code is also available on codepen here

How to Make a Timeline Streamgraph

For a full tutorial check out my blog post.

To modify it you'll need to publish a google sheet with your data and add the sheet key to the beginning of the index.html file.

The data needs to be in the same format as this one with 3 columns labeled key, date, value.

For this demo, I wrote a query to get political ad spend from Google Ads data on BigQuery.

SELECT a.advertiser_name, a.week_start_date, SUM(a.spend_usd) FROM `bigquery-public-data.google_political_ads.advertiser_weekly_spend` a
INNER JOIN (SELECT advertiser_id FROM (SELECT advertiser_id, SUM(spend_usd)as total_2020 FROM `bigquery-public-data.google_political_ads.advertiser_weekly_spend` WHERE week_start_date > '2020-01-01' GROUP BY advertiser_id)
ORDER BY total_2020 DESC LIMIT 50) c on a.advertiser_id = c.advertiser_id
GROUP BY a.advertiser_name, a.week_start_date

<!DOCTYPE html>
<meta charset="utf-8">
<body>
<script src="https://d3js.org/d3.v3.js"></script>
<header class="intro" id="intro-link">
<div class="container">
<div class="row text-center">
<div class="col-lg-12 col-md-12 col-sm-12 col-xs-12 order-2 align-self-center">
<h5>Political ad spend for the top 50 advertisers from <a href="https://console.cloud.google.com/marketplace/details/transparency-report/google-political-ads?filter=solution-type:dataset&q=ad&id=0c422e45-5809-4373-b2f4-ce31204c2f4c">Google Ads data</a> as a timeline <a href="https://observablehq.com/@d3/streamgraph">streamgraph</a>. Learn more <a href="https://bonkerfield.org/2020/05/timeline-streamgraph-google-sheet/">here</a>.</h5>
</div>
</div>
</div>
</header>
<div class="chart" id="chart">
</div>
<script>
google_sheets_id = "1jMLzuZ4ssq-cvDk54vFFqeU5a3JiD1x7WjhrOqaj7YE"
y_height = 5000
chart();
function chart() {
var format = d3.time.format("%Y-%m-%d");
var margin = {top: 20, right: 40, bottom: 30, left: 30};
var width = document.body.clientWidth - margin.left - margin.right;
var height = y_height - margin.top - margin.bottom;
var tooltip = d3.select(".chart")
.append("div")
.attr("class", "remove")
.style("position", "absolute")
.style("z-index", "20")
.style("visibility", "hidden");
var x = d3.scale.linear()
.range([0, width]);
var y = d3.time.scale()
.range([height-10, 0]);
var yAxis = d3.svg.axis()
.scale(y);
var stack = d3.layout.stack()
.offset("silhouette")
.values(function(d) { return d.values; })
.x(function(d) { return d.date; })
.y(function(d) { return d.value; });
var nest = d3.nest()
.key(function(d) { return d.key; });
var area = d3.svg.area()
.interpolate("basis")
.y(function(d) { return y(d.date); })
.x0(function(d) { return x(d.y0); })
.x1(function(d) { return x(d.y0 + d.y); });
var svg = d3.select(".chart").append("div")
.classed("svg-container", true) //container class to make it responsive
.append("svg")
.attr("preserveAspectRatio", "xMinYMin meet")
.attr("viewBox", "0 0 "+width+" " +height)
.classed("svg-content-responsive", true)
.append("g")
.attr("transform", "translate(" + margin.left + "," + margin.top + ")");
var url_data = "https://spreadsheets.google.com/feeds/list/"+google_sheets_id+"/1/public/values?alt=json"
var url_metadata = "https://spreadsheets.google.com/feeds/list/"+google_sheets_id+"/2/public/values?alt=json"
d3.json(url_metadata, function (meta_result) {
if (meta_result != null){
var metadata = {};
for (var i = 0; i < meta_result.feed.entry.length; i += 1) {
proj = meta_result.feed.entry[i].gsx$key.$t
metadata[proj] = {
"color": meta_result.feed.entry[i].gsx$color.$t,
}
}
} else{
metadata = null;
}
d3.json(url_data, function (result) {
var data = [];
all_dates = {};
unique_keys = {};
for (var i = 0; i < result.feed.entry.length; i += 1) {
date_val = result.feed.entry[i].gsx$date.$t;
key_val = result.feed.entry[i].gsx$key.$t;
data.push({
"date": date_val,
"value": result.feed.entry[i].gsx$value.$t,
"key": key_val
});
if (!(date_val in all_dates)){
all_dates[date_val] = true;
}
if (!(key_val in unique_keys)){
unique_keys[key_val] = {};
}
unique_keys[key_val][date_val]=true;
}
// if no colors sheet provided just assign randomly
if (metadata == null){
metadata = {}
for (p in unique_keys){
metadata[p] = {'color':"#"+Math.floor(Math.random()*16777215).toString(16)};
}
}
// zero fill if any dates are missing for a key
for (p in unique_keys){
for (dt in all_dates) {
if (!(dt in unique_keys[p])){
data.push({
"date": dt,
"value": 0,
"key": p
});
}
}
}
// map colors to keys
data.forEach(function(d) {
d.date = format.parse(d.date);
d.value = +d.value;
if (d.key in metadata){
d.color = metadata[d.key]['color']
} else {
d.color = '#111111'
}
});
// sort data
function compare( a, b ) {
if ( b.date > a.date ){
return -1;
}
if ( a.date > b.date ){
return 1;
}
return 0;
}
data.sort(compare);
var layers = stack(nest.entries(data));
y.domain(d3.extent(data, function(d) { return d.date; }));
x.domain([0, 1.05*d3.max(data, function(d) { return d.y0 + d.y; })]);
svg.selectAll(".layer")
.data(layers)
.enter().append("a")
.append("path")
.attr("class", "layer")
.attr("opacity", 0.8)
.attr("d", function(d) { return area(d.values); })
.style("fill", function(d) { return d.values[0].color; });
svg.selectAll(".layer").transition()
.duration(1)
.attr("opacity", 0.8);
// add text at largest topmost area
// this still needs some work to center the text better
prefer_x_width = 100
min_x_width = 50
x_width_step = 5
function transform_func(d){
txt_len = d.values[0].key.length
for (width_cutoff = prefer_x_width; width_cutoff > min_x_width; width_cutoff-=x_width_step){
min_y = 100000
min_xl = 0
min_xr = 0
for (i=0; i < d.values.length; i++){
y_val = y(d.values[i].date)
xl_val = x(d.values[i].y0)
xr_val = x(d.values[i].y0+d.values[i].y)
if ((xr_val-xl_val)>width_cutoff && y_val < min_y ){
min_y = y_val
min_xl = xl_val
min_xr = xr_val
}
}
min_x = (min_xl + min_xr)/2.
if (min_x >= width_cutoff) {
scale = (min_xr-min_xl)/Math.sqrt(txt_len)/50
return "translate("+min_x+","+(min_y-10)+") scale("+scale+") translate(-"+(4*txt_len)+",30)"
}
}
}
function visible_func(d){
min_y = 100000
min_xl = 0
min_xr = 0
txt_len = d.values[0].key.length
for (i=0; i < d.values.length; i++){
y_val = y(d.values[i].date)
xl_val = x(d.values[i].y0)
xr_val = x(d.values[i].y0+d.values[i].y)
if ((xr_val-xl_val)>min_x_width && y_val < min_y ){
min_y = y_val
min_xl = xl_val
min_xr = xr_val
}
}
return (min_xr-min_xl) < min_x_width+x_width_step ? 'hidden' : 'visible'
}
svg.selectAll("text")
.data(layers)
.enter().append("text")
.attr('class','nohover')
.attr("fill", '#GGG')
.attr("visibility", visible_func)
.attr("transform", transform_func)
.text((d) => d.values[0].key);
// add date axis
max_date = d3.max(data, function(d) { return d.date; });
min_date = d3.min(data, function(d) { return d.date; });
var dateArray = d3.time.scale()
.domain([new Date(min_date.getFullYear(), 12, 1), new Date(max_date.getFullYear()-1, 12, 1)])
.ticks(d3.time.years, 1);
dateArray.push(max_date)
const monthNames = ["January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"
];
yAxis = yAxis.tickValues(dateArray)
.tickFormat(d => ('<- ' + ((d==max_date) ? (monthNames[d.getMonth()]+', '+d.getFullYear()) : (d.getFullYear()-1) + " | " + d.getFullYear() + " ->")));
svg.append("g")
.attr("class", "yaxis")
.call(yAxis.orient("left"))
.selectAll("text")
.style("text-anchor", "end")
.style("color", "#999")
.attr("dx", ".4em")
.attr("dy", "-.6em")
.attr("transform", "rotate(-90)" );
// add mouse hover animations
svg.selectAll(".layer")
.on("mouseover", function(d, i) {
svg.selectAll(".layer").transition()
.duration(100)
.attr("opacity", function(d, j) {
return j != i ? 0.8 : 1;
})})
.on("mousemove", function(d, i) {
mouse = d3.mouse(document.body);
mousex = mouse[0];
mousey = mouse[1];
f = d.values[0]
var date = f.date
txt_width = f.key.length
tooltip
.style("left", (mousex-txt_width*4) +"px")
.style("top", (mousey+100) +"px")
.html("<div class='key'>" + f.key + "</div>")
.style("visibility", "visible");
})
.on("mouseout", function(d, i) {
svg.selectAll(".layer").transition()
.duration(100)
.attr("opacity", 0.8);
tooltip.style("visibility", "hidden");
});
});
});
}
</script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0/css/bootstrap.css">
<link href="https://fonts.googleapis.com/css2?family=Exo:wght@700&display=swap" rel="stylesheet">
<link href="https://fonts.googleapis.com/css2?family=Exo:wght@300&display=swap" rel="stylesheet">
<style>
.svg-container {
display: inline-block;
position: relative;
width: 90%;
padding-bottom: 100%; /* aspect ratio */
vertical-align: top;
}
.svg-content-responsive {
display: inline-block;
position: absolute;
top: 10px;
left: 0;
}
body {
background: #222; /* Old browsers */
font-family: 'Exo', sans-serif;
text-align: center;
font-weight: 700;
color: #999;
}
.navigationBar, .navbar-light, .bg-light{
background-color: #999 !important;
color: #222;
}
.intro {
margin: 2em 0 0 0;
padding: .2em inherit;
}
.yaxis {
stroke: #999;
fill: #999;
font-weight: 300;
font-size: 1.2em;
}
.key {
margin: 0;
padding: 0;
color:#AAA;
}
.nohover {
pointer-events: none;
}
</style>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.