Skip to content

Instantly share code, notes, and snippets.

@stedn

stedn/.block

Last active March 29, 2024 12:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save stedn/ccd784c9c52ce76d01e1569cd60d213e to your computer and use it in GitHub Desktop.
Save stedn/ccd784c9c52ce76d01e1569cd60d213e to your computer and use it in GitHub Desktop.
Download ZIP
Timeline Streamgraph from Google Sheets Data
Raw
.block
license:cc-by-sa-4.0
height:600
scrolling:yes
Raw
README.md

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
Raw
index.html
<!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