Report not found

There was a problem finding this report.
Bar Pinwheels of Overrepresented Category Colors (uses full hsl)
February 10, 2016 · Refreshed over 1 year ago
Description
The height of the bar shows how many pictures are in the wedge of the color wheel. The triangle is colored with the average color that appears in that wedge for that account.

Collaborators

Run History
SELECT * FROM benn.instagram_colors_2015
SELECT * FROM benn.instagram_colors_2015
WITH global_hsl AS ( SELECT i.account_type AS major_category, t.t, FLOOR(hue*36) * 10 AS h, ROUND(saturation*10) * 10 AS s, ROUND(luminance*10) * 10 AS l, COUNT(*)/t::FLOAT AS percent FROM benn.instagram_colors_2015 i JOIN ( SELECT account_type, COUNT(*) AS t FROM benn.instagram_colors_2015 GROUP BY 1 ) t ON t.account_type = i.account_type GROUP BY 1,2,3,4,5 ), account_hsl AS ( SELECT i.account_type AS major_category, i.account_type || '-' || i.account_category || '-' || i.username AS username, i.account_type || ' ' || i.account_category AS category, t.t, ROUND(hue*36) * 10 AS h, ROUND(saturation*10) * 10 AS s, ROUND(luminance*10) * 10 AS l, AVG(hue) AS hue, AVG(saturation) AS saturation, AVG(luminance) AS luminance, COUNT(*) AS pics, COUNT(*)/t.t::FLOAT AS percent FROM benn.instagram_colors_2015 i JOIN ( SELECT username, COUNT(*) AS t FROM benn.instagram_colors_2015 GROUP BY 1) t ON t.username = i.username GROUP BY 1,2,3,4,5,6,7 ), diffs AS ( SELECT a.username AS account_type, a.category, a.h, a.s, a.l, a.hue, a.saturation, a.luminance, a.pics, a.percent - g.percent AS percent_diff, a.percent/g.percent::FLOAT AS percent, ROW_NUMBER() OVER (PARTITION BY a.username ORDER BY a.percent/g.percent::FLOAT DESC) AS diff_rank FROM account_hsl a JOIN global_hsl g ON g.h = a.h AND g.s = a.s AND g.l = a.l AND g.major_category = a.major_category ) SELECT *, CASE WHEN x > 0 AND y > 0 THEN atan(y/x::FLOAT)*(180/pi()) WHEN x < 0 AND y > 0 THEN atan(y/x::FLOAT)*(180/pi()) + 180 WHEN x < 0 AND y < 0 THEN atan(y/x::FLOAT)*(180/pi()) + 180 WHEN x > 0 AND y < 0 THEN atan(y/x::FLOAT)*(180/pi()) + 360 ELSE 0 END AS weight FROM ( SELECT *, AVG(percent * sin(angle * pi()/180)) OVER (PARTITION BY account_type)::FLOAT AS x, AVG(percent * cos(angle * pi()/180)) OVER (PARTITION BY account_type)::FLOAT AS y FROM ( SELECT account_type, category, h AS hue_bucket, h AS angle, -- (90 - h) AS angle, SUM(pics), SUM(hue*percent)/SUM(percent)::FLOAT AS hue, SUM(saturation*percent)/SUM(percent)::FLOAT AS saturation, SUM(luminance*percent)/SUM(percent)::FLOAT AS luminance, SUM(percent) AS percent FROM diffs WHERE diff_rank <= 100 GROUP BY 1,2,3,4 ) a ) b ORDER BY 2,12
WITH global_hsl AS ( SELECT i.account_type AS major_category, t.t, FLOOR(hue*36) * 10 AS h, ROUND(saturation*10) * 10 AS s, ROUND(luminance*10) * 10 AS l, COUNT(*)/t::FLOAT AS percent FROM benn.instagram_colors_2015 i JOIN ( SELECT account_type, COUNT(*) AS t FROM benn.instagram_colors_2015 GROUP BY 1 ) t ON t.account_type = i.account_type GROUP BY 1,2,3,4,5 ), account_hsl AS ( SELECT i.account_type AS major_category, i.account_type || '-' || i.account_category || '-' || i.username AS username, i.account_type || ' ' || i.account_category AS category, t.t, ROUND(hue*36) * 10 AS h, ROUND(saturation*10) * 10 AS s, ROUND(luminance*10) * 10 AS l, AVG(hue) AS hue, AVG(saturation) AS saturation, AVG(luminance) AS luminance, COUNT(*) AS pics, COUNT(*)/t.t::FLOAT AS percent FROM benn.instagram_colors_2015 i JOIN ( SELECT username, COUNT(*) AS t FROM benn.instagram_colors_2015 GROUP BY 1) t ON t.username = i.username GROUP BY 1,2,3,4,5,6,7 ), diffs AS ( SELECT a.username AS account_type, a.category, a.h, a.s, a.l, a.hue, a.saturation, a.luminance, a.pics, a.percent - g.percent AS percent_diff, a.percent/g.percent::FLOAT AS percent, ROW_NUMBER() OVER (PARTITION BY a.username ORDER BY a.percent/g.percent::FLOAT DESC) AS diff_rank FROM account_hsl a JOIN global_hsl g ON g.h = a.h AND g.s = a.s AND g.l = a.l AND g.major_category = a.major_category ) SELECT *, CASE WHEN x > 0 AND y > 0 THEN atan(y/x::FLOAT)*(180/pi()) WHEN x < 0 AND y > 0 THEN atan(y/x::FLOAT)*(180/pi()) + 180 WHEN x < 0 AND y < 0 THEN atan(y/x::FLOAT)*(180/pi()) + 180 WHEN x > 0 AND y < 0 THEN atan(y/x::FLOAT)*(180/pi()) + 360 ELSE 0 END AS weight FROM ( SELECT *, AVG(percent * sin(angle * pi()/180)) OVER (PARTITION BY account_type)::FLOAT AS x, AVG(percent * cos(angle * pi()/180)) OVER (PARTITION BY account_type)::FLOAT AS y FROM ( SELECT account_type, category, h AS hue_bucket, h AS angle, -- (90 - h) AS angle, SUM(pics), SUM(hue*percent)/SUM(percent)::FLOAT AS hue, SUM(saturation*percent)/SUM(percent)::FLOAT AS saturation, SUM(luminance*percent)/SUM(percent)::FLOAT AS luminance, SUM(percent) AS percent FROM diffs WHERE diff_rank <= 100 GROUP BY 1,2,3,4 ) a ) b ORDER BY 2,12
<style> .gridline { stroke: #eeeeee; stroke-width: 1px; fill: none; } .color-line { /*stroke: black;*/ stroke-width: 2px; fill: none; } .burst { float: left; padding: 10px; } </style> <div id="graphic"></div> <script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.8.3/underscore-min.js"></script> <script> console.log("here") var ds = dataset.content, users = _.uniq(_.pluck(ds,"account_type")); var bucketSize = 360 - d3.max(ds, function(d) { return d.hue_bucket; }) ; var width = 200, height = 200; // drawWheel("all") // drawWheel("brand") // drawWheel("photographer") // drawWheel("celebrity") users.forEach(function(u) { if (["all","brand","photographer","celebrity"].indexOf(u) == -1) { drawWheel(u); } }) function drawWheel(category) { var data = _.where(ds, {"account_type":category} ); $("#graphic").append("<div class='burst mode-header' id='b-" + category + "'></div>") $("#b-" + category).append("<p>" + category + "</p>") var r = d3.scale.linear() .domain([0,d3.max(data, function(d) { return d.percent; })]) // .domain([1,d3.max(ds, function(d) { return d.percent; })]) .range([.1*width/2,.9*width/2]) .clamp(true); var g1 = [ {x: 0, y:0}, {x: 180, y:180} ], g2 = [ {x: 30, y:30}, {x: 210, y:210} ], g3 = [ {x: 60, y:60}, {x: 240, y:240} ], g4 = [ {x: 90, y:90}, {x: 270, y:270} ], g5 = [ {x: 120, y:120}, {x: 300, y:300} ], g6 = [ {x: 150, y:150}, {x: 330, y:330} ]; var gridline = d3.svg.line() .x(function(d) { return toCoordinates(d.x,width/2)[0]; }) .y(function(d) { return toCoordinates(d.y,width/2)[1]; }) .interpolate("linear"); var svg = d3.select("#b-" + category).append("svg") .attr("width",width) .attr("height",height); // svg.append("path").attr("d", gridline(g1)).attr("class","gridline"); // svg.append("path").attr("d", gridline(g2)).attr("class","gridline"); // svg.append("path").attr("d", gridline(g3)).attr("class","gridline"); // svg.append("path").attr("d", gridline(g4)).attr("class","gridline"); // svg.append("path").attr("d", gridline(g5)).attr("class","gridline"); // svg.append("path").attr("d", gridline(g6)).attr("class","gridline"); svg.selectAll(".wedge-" + category) .data(data) .enter().append("polygon") .attr("class","wedge-" + category) .attr("points", function(d) { return points(d.hue_bucket, r(d.percent)); }) .attr("class","color-line") .style("fill", function(d) { return color(d); }) .attr("stroke","none") svg.selectAll(".dot-" + category) .data(data) .enter().append("circle") .attr("class","dot-" + category) .attr("cx", function(d) { return toCoordinates(450 - d.weight, r(Math.sqrt(Math.pow(d.x,2) + Math.pow(d.y,2))))[0]; }) .attr("cy", function(d) { return toCoordinates(450 - d.weight, r(Math.sqrt(Math.pow(d.x,2) + Math.pow(d.y,2))))[1]; }) .attr("r",0) .style("fill","red") .attr("stroke","red"); } function color(row) { var h = Math.round(row.hue * 360), s = Math.round(row.saturation * 100), l = Math.round(row.luminance * 100), hsl = "hsl(" + h + "," + s + "%," + l + "%)"; return hsl; } function points(hueBucket,percent) { var p0 = width/2 + "," + height/2; var p1 = toCoordinates(hueBucket, percent), p2 = toCoordinates(hueBucket + bucketSize, percent); var string = p0 + " " + p1[0] + "," + p1[1] + " " + p2[0] + "," + p2[1]; return string; } function toCoordinates(degrees, radius) { var cx = width/2, cy = height/2; if (degrees >= 360) { degrees = degrees - 360; } var sub90 = degrees - Math.floor(degrees/90)*90, radians = sub90 * Math.PI/180; if (degrees < 90) { x = cx + Math.sin(radians) * radius; y = cy - Math.cos(radians) * radius; } else if (degrees < 180) { x = cx + Math.cos(radians) * radius; y = cy + Math.sin(radians) * radius; } else if (degrees < 270) { x = cx - Math.sin(radians) * radius; y = cy + Math.cos(radians) * radius; } else { x = cx - Math.cos(radians) * radius; y = cy - Math.sin(radians) * radius; } return [x,y]; } </script>
{{ dataSourceName(params.queryId) }}

The dataset is too large to view in browser

Export

Looks like something went wrong with your query.

{{ DS.queryRuns[params.queryId].errorMessage }}
This query was cancelled