That’ll be ₱1 billion please
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
The pandemic provoked a lot of experimentation in Philippine urban transport policy. Some were sensible, like rationalizing bus stops along EDSA. Some were, uh, destined to be hallmarks of the time. One of the more forward-thinking was the elevation of bicycles as a bona fide mode of transport. What’s not to love: they take little road space, they’re cheap, they’re green, and they keep you fit.1 Biking infrastructure is an actionable investment while waiting for the big-ticket rail projects to crawl towards completion.
Funding for the construction of bike lanes in Metro Manila, Metro Cebu, and Metro Davao was included in the pandemic recovery bill. The transportation and public works departments, tasked with implementation, received technical assistance from the World Bank and the Netherlands Embassy to ensure a “Safe System approach” and “Dutch Cycling Infrastructure principles”. Contracts were drawn up and opened to the private sector for bidding. By June 2021, almost 500 kilometers of bike lanes were completed at the cost of over ₱1 billion ($19.6 million), or about ₱2.1 million ($40,000) per kilometer.
Well. Here’s what the lane closest to where I live looks like:
It’s a little underwhelming? No bollards, no buffers, just some hard-to-see paint on the ground and a dash of prayer. Granted, there are sections that are less brutalist, but this is not unrepresentative either.
As a tax-funded project, it’s worthwhile to look closely into the procurement process that resulted in these bike lanes. This is exactly the subject of an illuminating paper from WeSolve Foundation, published last week. They’ve made their dataset available, and in this post, I’d like to visualize some of its more interesting aspects. The authors attach a disclaimer that their findings “cannot and should not be used to conclude fraud or corruption”, and indeed, the work was made in partnership with the Philippine Government Electronic Procurement System, who provided some data. I do believe that an imperfect system need not necessarily imply deliberate wrongdoing, so the same disclaimer applies in this post.
Let’s load up the dataset in R.
library(tidyverse)
library(here)
library(readxl)
file <- "[PUBLIC DATASET] Empowering Citizens to Build Better Bike Lanes through Open Contracting.xlsx"
contract <- here("datasets", "bikelanes", file) %>% read_excel(sheet = "contract", skip = 1)
item <- here("datasets", "bikelanes", file) %>% read_excel(sheet = "item")
bidder <- here("datasets", "bikelanes", file) %>% read_excel(sheet = "bidder")
The following table contains key information on the 12 contracts the study covered. There are 10 in Metro Manila and one each in Cebu and Davao. The contract advertising date (date_ad), the notice-to-proceed date (date_ntp), the winning bidder (noa_supplier), and the approved final value (noa_award_value) are given for each contract.
Code
library(reactable)
contract %>%
select(contract_no, contract_title, contract_location, proc_entity, date_ad, date_ntp, noa_supplier, noa_award_value) %>%
reactable(
defaultColDef = colDef(
minWidth = 125,
align = "left",
headerStyle = list(fontFamily = "Karla", background = "#f7f7f8"),
),
columns = list(
contract_title = colDef(minWidth = 350),
contract_location = colDef(minWidth = 175),
proc_entity = colDef(minWidth = 175),
date_ad = colDef(align = "center", cell = function(x) format(x, "%Y-%m-%d")),
date_ntp = colDef(align = "center", cell = function(x) format(x, "%Y-%m-%d")),
noa_supplier = colDef(minWidth = 225),
noa_award_value = colDef(minWidth = 150, align = "right", format = colFormat(prefix = "₱", separators = TRUE, digits = 0))
),
defaultPageSize = 4,
bordered = TRUE,
highlight = TRUE
)
Let’s start by visualizing where these bike lanes are located. The dataset does not actually have information on routes, so I’ve had to infer them from the contract title, the stipulated length, and my educated guesses. I drew the routes on Google My Maps and exported them as a KML file, which I then uploaded on mapshaper to consolidate the features and convert to topoJSON format. Because this took a bit of work, I’m focusing here only on the Metro Manila contracts. One of the contracts (20O0096) has an uninformative title so I was unable to draw it, leaving me with nine routes in all. For the administrative boundaries of Metro Manila and surrounding areas, I used the topoJSON files of James Faeldon.
Code
html`
<div class="ojs-title">Life in the bike lane</div>
<div class="ojs-subtitle">Contracts for Metro Manila bike lanes, 2020-2021</div>
`
{
// Parameters
const width = 920;
const height = 620;
const legendBoxWidth = 100;
const legendBoxHeight = 35;
const legendBoxGap = 55;
const insetLeft = 5;
const insetTop = 115;
const marginRight = 10;
const colorNCR = "#c0d7df"
const colorBG = "#eeeeee"
const colorLegend = "#84b0c5"
const colorLegendSelect = "#f697bb"
const colorLanes = "#84b0c5"
const colorLanesSelect = "#B13D70"
// Canvas
const svg = d3.create("svg")
.attr("width", width)
.attr("height", height)
.attr("viewBox", [0, 0, width, height])
.attr("style", "max-width: 100%; height: auto; height: intrinsic;");
// Map
const projection = d3.geoMercator()
.scale([100000])
.center([120.89, 14.64])
const pathGenerator = d3.geoPath(projection);
const map = svg.append("g")
map.selectAll("bg")
.data(bgFeatures)
.join("path")
.attr("d", pathGenerator)
.attr("fill", colorBG)
map.selectAll("boundaries")
.data(ncrFeatures)
.join("path")
.attr("d", pathGenerator)
.attr("fill", colorNCR)
.attr("opacity", .8)
.attr("stroke", "#ffffff")
.attr("stroke-width", 1)
// Bike lanes
map.selectAll("lanes")
.data(contractsFeatures)
.join("path")
.attr("d", pathGenerator)
.attr("class", (d) => "lanes lane-" + d.properties.contract)
.attr("fill", "none")
.attr("opacity", .8)
.attr("stroke", colorLanes)
.attr("stroke-width", 3);
// Legend
const mouseenter = function(event, d) {
legend.select(".legendbox-" + d)
.transition().duration(100)
.attr("fill", colorLegendSelect)
.attr("opacity", 1);
map.select(".lane-" + d)
.transition().duration(100)
.attr("opacity", 1)
.attr("stroke", colorLanesSelect)
.attr("stroke-width", 4);
d3.select(".detail-" + d)
.transition().duration(100)
.attr("opacity", 1);
d3.select(event.target)
.style("cursor", "pointer");
}
const mouseleave = function(event) {
legend.selectAll(".legendbox")
.transition().duration(100)
.attr("fill", colorLegend)
.attr("opacity", .25);
map.selectAll(".lanes")
.transition().duration(800)
.attr("opacity", .8)
.attr("stroke", colorLanes)
.attr("stroke-width", 3);
d3.selectAll(".details")
.transition().duration(100)
.attr("opacity", .25);
d3.select(event.target)
.style("cursor", "default");
}
const legend = svg.selectAll("boxes")
.data(contractNames)
.join("g")
.on("mouseenter", mouseenter)
.on("mouseleave", mouseleave);
legend.append("rect")
.attr("class", (d) => "legendbox legendbox-" + d)
.attr("width", legendBoxWidth)
.attr("height", legendBoxHeight)
.attr("fill", colorLegend)
.attr("rx", ".25em")
.attr("ry", ".25em")
.attr("opacity", .25)
.attr("x", insetLeft)
.attr("y", (d, i) => (insetTop + i * legendBoxGap));
legend.append("text")
.text(d => d)
.attr("text-anchor", "middle")
.style("alignment-baseline", "middle")
.attr("x", insetLeft + legendBoxWidth / 2)
.attr("y", (d, i) => (insetTop + i * legendBoxGap))
.attr("dy", legendBoxHeight / 2 + 1);
// Details
const details = svg.selectAll("details")
.data(contractDetails)
.join("g")
.attr("class", (d, i) => "details detail-" + contractDetails.map(dd => dd.contract_no)[i])
.attr("opacity", .25)
details.append("text")
.text((d, i) => "Bidder: " + contractDetails.map(dd => dd.noa_supplier)[i])
.attr("x", insetLeft + legendBoxWidth + marginRight)
.attr("y", (d, i) => (insetTop + i * legendBoxGap))
.attr("dy", 5)
.style("font-size", ".75rem")
.style("alignment-baseline", "hanging")
details.append("text")
.text((d, i) => "Value: ₱" + contractDetails.map(dd => d3.format(",.2r")(dd.noa_award_value / 1000000))[i] + " million")
.attr("class", (d, i) => "values value-" + contractDetails.map(dd => dd.contract_no)[i])
.attr("x", insetLeft + legendBoxWidth + marginRight)
.attr("y", (d, i) => (insetTop + i * legendBoxGap))
.attr("dy", 20)
.style("font-size", ".75rem")
.style("alignment-baseline", "hanging")
// Tip
const tip = svg.append("text")
.text("Hover over a contract to see it on the map")
.attr("x", insetLeft + 5)
.attr("y", insetTop - 30)
.style("font-weight", "bold");
return svg.node();
}
The network is certainly comprehensive, covering most major roads in the metro. These do not yet include bike lanes built by local government units, which traverse secondary roads. On paper at least, cycling infrastructure in Metro Manila has become fairly well-connected. But things are not so rosy on the ground. As shown in the picture above, these lanes are often unprotected for long stretches, even in major highways. Fatal accidents are frequent. And without the vigilance of activists, there is a tendency for car-centric policies to creep back in.
You may notice that several of the contracts were won by one company, Philippine Chemsteel Industries. The idea of a public bidding is meant to ensure transparency and competitiveness in government procurement. Following economic theory, the presence of many sellers helps drive prices down to marginal cost. The fact that one company seems to have dominated these bids hints at the possible breakdown of this assumption. We can check this by visualizing WeSolve’s data in a network chart.
bidders <- bidder %>%
filter(!is.na(total_calc_bid)) %>%
select(contract_no, bidder_name, total_calc_bid, flag_lcb)
nodes <- c(unique(bidders$bidder_name), unique(bidders$contract_no))
biddersNodes <- tibble(
id = 1:length(nodes),
name = nodes,
type = case_when(
str_detect(name, "20") ~ "contract",
TRUE ~ "bidder"
))
biddersLinks <- bidders %>%
left_join(biddersNodes, by = c("contract_no" = "name")) %>%
left_join(biddersNodes, by = c("bidder_name" = "name")) %>%
select(source = id.y, target = id.x, bid = total_calc_bid, win = flag_lcb)
# Correction
biddersLinks$source[biddersLinks$target == 18] <- c(1, 6, 5)
biddersLinks$source[biddersLinks$target == 20] <- c(1, 5)
Disqualified bids (due to, for example, incomplete requirements) are excluded. I am also correcting what looks like an encoding error in the WeSolve dataset where Philippine Chemsteel Industries and Newbig Four J Construction were switched by mistake. The official documents confirm that it was Philippine Chemsteel, not Newbig, who won in the two contracts they competed in.
Code
html`
<div class="ojs-title">Slim pickings</div>
<div class="ojs-subtitle">Network of <span style="font-weight:bold; color:#7fc6a4">contracts</span> and their <span style="font-weight:bold; color:#4889ab">bidders</span>, with winning bids <b>identified</b> </span></div>
`
{
// Parameters
const width = 790
const height = 350
const colorLinks = d3.scaleOrdinal()
.domain([0, 1])
.range(["#cecece", "#2B303A"]);
const strokeLinks = d3.scaleOrdinal()
.domain([0, 1])
.range([1, 3]);
const colorNodes = d3.scaleOrdinal()
.domain(["bidder", "contract"])
.range(["#4889ab", "#7fc6a4"]);
// Data
const nodes = biddersNodes.map(d => Object.create(d));
const links = biddersLinks.map(d => Object.create(d));
// Define forces
const simulation = d3.forceSimulation(nodes)
.force("link", d3.forceLink(links).id(d => d.id))
.force("charge", d3.forceManyBody().strength(-200))
.force("x", d3.forceX())
.force("y", d3.forceY())
.force("limit", d3.forceLimit().x0(-width / 2).x1(width / 2).y0(-height / 2).y1(height / 2))
.on("tick", () => {
link
.attr("x1", d => d.source.x)
.attr("y1", d => d.source.y - 20)
.attr("x2", d => d.target.x)
.attr("y2", d => d.target.y - 20);
node
.attr("transform", d => `translate(${d.x}, ${d.y - 20})`);
});
// Canvas
const svg = d3.create("svg")
.attr("width", width)
.attr("height", height)
.attr("viewBox", [-width / 2, -height / 2, width, height])
.attr("style", "max-width: 100%; height: auto; height: intrinsic;");
// Tooltip
const tooltip = d3.select("body")
.append("div")
.attr("class", "toolTip")
.style("display", "none")
.style("position", "absolute")
.style("width", 100)
.style("height", 20)
.style("background", "#f7f7f7")
.style("border", "1px solid #cecece")
.style("opacity", .9)
.style("padding", ".2em .45em")
.style("font-size", ".85rem");
const showTooltip = function(event, d) {
d3.select(this)
.transition().duration(50)
.attr("transform", "scale(1.25)");
tooltip
.style("left", event.pageX + 18 + "px")
.style("top", event.pageY + 18 + "px")
.style("display", "block")
.text(d.name)
d3.select(event.target)
.style("cursor", "pointer");
}
const hideTooltip = function(event) {
d3.select(this)
.transition().duration(50)
.attr("transform", "scale(1)");
tooltip
.style("display", "none");
d3.select(event.target)
.style("cursor", "default");
}
const link = svg.append("g")
.attr("fill", "none")
.attr("stroke-width", 1.5)
.selectAll("line")
.data(links)
.join("line")
.attr("stroke", d => colorLinks(d.win))
.attr("stroke-width", d => strokeLinks(d.win))
const node = svg.append("g")
.attr("stroke-linecap", "round")
.attr("stroke-linejoin", "round")
.selectAll("g")
.data(nodes)
.join("g")
.call(drag(simulation));
node.append("circle")
.attr("stroke", "white")
.attr("stroke-width", 1.5)
.attr("fill", d => colorNodes(d.type))
.attr("r", 10)
.on("mousemove", showTooltip)
.on("mouseleave", hideTooltip);
invalidation.then(() => simulation.stop());
return svg.node();
}
html`<div class="ojs-caption">Click and drag to highlight relationships.</div>`
Seven of the 11 contracts with bidders data2 had more than one valid bid, which, by WeSolve’s reckoning, saved the government an average of 1.6%. It’s not exactly a blockbuster amount, and looking at the network, it’s pretty clear why. Adam Smith’s marketplace this is not. The ten Metro Manila contracts received just 1-3 bidders each; Philippine Chemsteel won all six contracts for which it submitted a bid. The Metro Davao contract was the most competitive at four bidders, though the winning bid was a mere 0.1% less than the budgeted amount.
What exactly did these companies spend on? Luckily, the WeSolve dataset also contains item-level price and quantity information for each winning bid. Let’s prep these for charting on a stacked bar plot.
items <- item %>%
select(contract_no, item_description, item_amt) %>%
group_by(contract_no) %>%
arrange(desc(item_amt), .by_group = TRUE) %>%
mutate(
rank = 1:n(),
item = case_when(
rank <= 9 ~ item_description,
TRUE ~ "Others"
)) %>%
group_by(contract_no, item) %>%
summarize(
value = sum(item_amt),
rank = sum(rank)
) %>%
mutate(value_norm = value / sum(value)) %>%
arrange(rank, .by_group = TRUE) %>%
mutate(rank = 1:n()) %>%
ungroup()
itemsWide <- items %>%
select(contract_no, rank, value_norm) %>%
pivot_wider(names_from = rank, values_from = value_norm)
itemsDesc <- items %>%
select(contract_no, rank, item, value, value_norm)
To keep the chart manageable, I’m only identifying the nine biggest items in each contract, with the rest aggregated into an “others” category. Those steeped in the philosophy of tidy data might be wondering what sort of abomination am I trying to create with itemsWide. This step is necessary because making stacked bar charts in D3 requires data in wide format.
Code
html`
<div class="ojs-title">Is that everything?</div>
<div class="ojs-subtitle">Itemized breakdown of winning contract bid</div>
`
{
// Parameters
const margin = { top: 0, right: 20, bottom: 30, left: 80 };
const width = 790 - margin.left - margin.right;
const height = 500 - margin.top - margin.bottom;
const subgroups = itemsWide.columns.slice(1);
const groups = itemsWide.map(d => d.contract_no);
const colors = [
"#0C6291",
"#4889ab",
"#B13D70",
"#f697bb",
"#307351",
"#7fc6a4",
"#FCB13B",
"#F7DD72",
"#c0d7df",
"#f7f7f7"
]
const color = d3.scaleOrdinal()
.domain(subgroups)
.range(colors);
// Canvas
const svg = d3.create("svg")
.attr("width", width + margin.left + margin.right)
.attr("height", height + margin.top + margin.bottom)
.attr("viewBox", [0, 0, width + margin.left + margin.right, height + margin.top + margin.bottom])
.attr("style", "max-width: 100%; height: auto; height: intrinsic;");
const panel = svg.append("g")
.attr("transform", `translate(${margin.left},${margin.top})`);
// X axis
const x = d3.scaleLinear()
.domain([0, 1])
.range([ 0, width ]);
panel.append("g")
.attr("transform", `translate(0, ${height})`)
.call(d3.axisBottom(x).ticks(4, "%").tickPadding([8]))
.style("font-size", ".75rem");
// Y axis
const y = d3.scaleBand()
.domain(groups)
.range([0, height])
.padding([.3]);
panel.append("g")
.call(d3.axisLeft(y).tickSize([0]).tickPadding([8]))
.style("font-size", ".75rem");
const stackedData = d3.stack()
.keys(subgroups)
(itemsWide);
// Tooltip
const tooltip = d3.select("body")
.append("div")
.attr("class", "toolTip")
.style("display", "none")
.style("position", "absolute")
.style("width", 100)
.style("height", 20)
.style("background", "#f7f7f7")
.style("border", "1px solid #cecece")
.style("opacity", .9)
.style("padding", ".2em .45em")
.style("font-size", ".85rem")
.style("line-height", 1.4);
const showTooltip = function(event, d) {
const contract = d.data.contract_no;
const itemShare = d3.format(".1%")(d[1] - d[0]);
const itemRow = itemsDesc.filter(D => D.contract_no === contract && d3.format(".4")(D.value_norm) === d3.format(".4")(d[1] - d[0]))[0];
const itemName = itemRow.item;
const itemValue = d3.format(",.0f")(itemRow.value);
tooltip
.style("left", event.pageX + 18 + "px")
.style("top", event.pageY + 18 + "px")
.style("display", "block")
.html(`${itemName}<br>₱${itemValue} (${itemShare})`);
d3.select(this)
.attr("opacity", 1)
.attr("stroke", "#2B303A")
.attr("stroke-width", 3);
d3.select(event.target).style("cursor", "pointer");
}
const hideTooltip = function(event) {
tooltip.style("display", "none");
d3.select(this)
.attr("opacity", .8)
.attr("stroke", "none");
d3.select(event.target).style("cursor", "default");
}
// Build the chart
panel.append("g")
.selectAll("g")
.data(stackedData)
.join("g")
.attr("fill", d => color(d.key))
.selectAll("rect")
.data(d => d)
.join("rect")
.attr("x", d => x(d[0]))
.attr("y", d => y(d.data.contract_no))
.attr("width", d => x(d[1]) - x(d[0]))
.attr("height", y.bandwidth())
.attr("opacity", .8)
.on("mousemove", showTooltip)
.on("mouseleave", hideTooltip);
return svg.node()
}
html`<div class="ojs-caption">Hover to see more details.</div>`
Among the largest components are solar pavement markers, “reflectorized thermoplastic pavement markings” (read: paint), and an assortment of road signs. Here and there are provisions for constructing bicycle racks, though I don’t think I’ve ever seen one in the wild. It’s clear from these line items that the plan had always been to build bike lanes of the most basic sort: painted strips that awkwardly take up two-thirds of the rightmost car lane.
With this bike lane building program, the government has seemingly spent ₱1 billion to make everyone unhappy — cyclists because they find the lanes inadequate, motorists because they resent the intrusion into their space. But that’s fine! It’s a start! There are bike lanes now where there weren’t before. Making cycling a truly viable mode of transport will take several more billions yet. And in those future rounds of procurement, I hope that the government commits to improving its policy of transparency. The WeSolve paper notes several gaps in officially available data; even the very routes these contracts covered, I was frustrated to learn, are nowhere to be found. Let the data flow free! Don’t you know I love nothing more than learning how much fancy paint my taxes were able to buy?
Data
Appendix: D3 / Observable
Code
d3 = require("d3@7", "d3-force-limit");
// Data
ncr1339 = FileAttachment("../../datasets/philippines-json-maps/municities-province-ph133900000.topo.0.1.json").json();
ncr1374 = FileAttachment("../../datasets/philippines-json-maps/municities-province-ph137400000.topo.0.1.json").json();
ncr1375 = FileAttachment("../../datasets/philippines-json-maps/municities-province-ph137500000.topo.0.1.json").json();
ncr1376 = FileAttachment("../../datasets/philippines-json-maps/municities-province-ph137600000.topo.0.1.json").json();
centralLuzon = FileAttachment("../../datasets/philippines-json-maps/provinces-region-ph030000000.topo.0.1.json").json();
calabarzon = FileAttachment("../../datasets/philippines-json-maps/provinces-region-ph040000000.topo.0.1.json").json();
ncrFeatures = {
const ncr1339Feature = topojson.feature(ncr1339, ncr1339["objects"]["municities-province-ph133900000.0.1"]).features
const ncr1374Feature = topojson.feature(ncr1374, ncr1374["objects"]["municities-province-ph137400000.0.1"]).features
const ncr1375Feature = topojson.feature(ncr1375, ncr1375["objects"]["municities-province-ph137500000.0.1"]).features
const ncr1376Feature = topojson.feature(ncr1376, ncr1376["objects"]["municities-province-ph137600000.0.1"]).features
return [...ncr1339Feature, ...ncr1374Feature, ...ncr1375Feature, ...ncr1376Feature];
}
bgFeatures = {
const r3Features = topojson.feature(centralLuzon, centralLuzon["objects"]["provinces-region-ph030000000.0.1"]).features
const r4AFeatures = topojson.feature(calabarzon, calabarzon["objects"]["provinces-region-ph040000000.0.1"]).features
return [...r3Features, ...r4AFeatures];
}
contractsMap = FileAttachment("../../datasets/bikelanes/contractsMap.json").json();
contractsFeatures = topojson.feature(contractsMap, contractsMap.objects.contractsMap).features;
contractNames = d3.map(contractsFeatures, d => d.properties.contract).sort();
contractsDF = FileAttachment("../../datasets/bikelanes/contracts.csv").csv({ typed: true });
contractDetails = contractsDF.filter(d => contractNames.includes(d.contract_no));
biddersNodes = FileAttachment("../../datasets/bikelanes/biddersNodes.csv").csv({ typed: true });
biddersLinks = FileAttachment("../../datasets/bikelanes/biddersLinks.csv").csv({ typed: true });
itemsWide = FileAttachment("../../datasets/bikelanes/itemsWide.csv").csv({ typed: true });
itemsDesc = FileAttachment("../../datasets/bikelanes/itemsDesc.csv").csv({ typed: true });
// Drag function
// Source: https://observablehq.com/@d3/mobile-patent-suits
drag = simulation => {
function dragstarted(event, d) {
if (!event.active) simulation.alphaTarget(0.3).restart();
d.fx = d.x;
d.fy = d.y;
}
function dragged(event, d) {
d.fx = event.x;
d.fy = event.y;
}
function dragended(event, d) {
if (!event.active) simulation.alphaTarget(0);
d.fx = null;
d.fy = null;
}
return d3.drag()
.on("start", dragstarted)
.on("drag", dragged)
.on("end", dragended);
}
Footnotes
So I’ve heard. I don’t actually know how to ride a bike.↩︎
The one contract without bidders data was for Metro Cebu.↩︎
Reuse
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.