Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Retrieve and visualize Bitcoin network transactions associated with the Pizza transaction sending address.

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
            )
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
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.