Retrieve and visualize Bitcoin network transactions associated with the Pizza transaction sending address.
Data are from Google BigQuery public dataset (free).
Produces an image as shown below (scroll down).
R code:
library(plyr)
library(igraph)
library(bigrquery)
library(AggregateR)
project = 'bitcoin-bigquery'
laszlo = '1XPTgDRhN8RFnzniWCddobD9iKZatrvH4'
base_query_up = "SELECT
timestamp/1000 AS tt,
inputs.input_pubkey_base58 AS input_key,
outputs.output_pubkey_base58 AS output_key,
outputs.output_satoshis * 0.00000001 AS btc
FROM
`bigquery-public-data.bitcoin_blockchain.transactions`
JOIN UNNEST (inputs) AS inputs
JOIN UNNEST (outputs) AS outputs
WHERE
outputs.output_pubkey_base58 = 'ADDRESS'
AND
output_satoshis >= 10000000
AND
timestamp < TIMESTAMP * 1000
AND
inputs.input_pubkey_base58 IS NOT NULL
GROUP BY tt, input_key, output_key, btc"
frontier = data.frame()
dig = function(k,depth,tt,direction) {
base_query = base_query_up
query <- sub('ADDRESS',k,base_query)
query <- sub('TIMESTAMP',tt,query)
if (depth <= max_depth) {
cat(depth,k,"\n")
if (query_exec(
paste("SELECT COUNT(*) FROM (",query,") AS x"),
project = project,
use_legacy_sql=F)$f0_ > 0
) {
frontier <- query_exec(query, project = project, use_legacy_sql=F)
tx0 <<- rbind(tx0, frontier)
for (ikey in unique(frontier$input_key)) {
dig(ikey, depth+1, max(frontier$tt[frontier$input_key==ikey]),direction)
}
}
}
}
max_depth = 2
tx0 = data.frame()
dig(laszlo,0,999999999999999,-1)
btc = ddply(tx0, c("input_key","output_key"), summarize, value=btc)
btc.net <- graph.data.frame(btc, directed=T)
V(btc.net)$color <- "blue"
V(btc.net)$color[unlist(V(btc.net)$name) == laszlo] <- "red"
mytitle = paste("BTC inputs upstream of pizza purchase, depth=",(max_depth+2),sep="")
nodes <- unlist(V(btc.net)$name)
E(btc.net)$width <- ceiling(E(btc.net)$value/800)+.1
plot.igraph(btc.net,
main=mytitle,
vertex.size=5,
edge.arrow.size=0.5,
vertex.label=NA,
layout=layout_nicely,
rescale=T
)
