Skip to content

Instantly share code, notes, and snippets.

View sessionize-ethereum.pl
#!/usr/bin/perl
=pod
--root level tx
SELECT from_address AS u
, ARRAY_AGG(DISTINCT(UNIX_SECONDS(block_timestamp))) AS ss_array
FROM `bigquery-public-data.crypto_ethereum.traces`
WHERE trace_address IS NULL
GROUP BY u
=cut
use strict;
@allenday
allenday / Makefile
Last active Aug 6, 2020
coingecko-etl
View Makefile
%.ohlc.csv :
perl ./coingecko-csv.pl ${@:.ohlc.csv=} > $@
%.meta.jsonl :
perl ./coingecko-metadata.pl ${@:.meta.jsonl=} > $@
all.meta.jsonl :
cat `ls *.meta.jsonl | grep -v $@` | perl -ne 'print lc' | sort > $@
all.ohlc.csv :
cat `ls *.ohlc.csv | grep -v $@` > $@
all :
View tokens.sql
SELECT transfers.block_timestamp,transfers.from_address, transfers.to_address, tokens.symbol, tokens.address, SAFE_CAST(transfers.value AS INT64) / POW(10,SAFE_CAST(tokens.decimals AS INT64)) AS token_value
FROM
`bigquery-public-data.crypto_ethereum.token_transfers` AS transfers,
`bigquery-public-data.crypto_ethereum.tokens` AS tokens
WHERE
transfers.token_address = tokens.address
AND tokens.address IN('0x514910771af9ca656af840dff83e8264ecf986ca','0x2260fac5e5542a773aa44fbcfedf7c193bc2c599')
ORDER BY transfers.block_timestamp DESC
LIMIT 1000
View setup-mediapipe-yt8m-ubuntu-20.sh
sudo apt-get -y update
sudo apt-get -y install build-essential ca-certificates curl ffmpeg git wget unzip python3-dev python3-pip software-properties-common python3-numpy youtube-dl
#install for GPU
sudo apt-get install mesa-common-dev libegl1-mesa-dev libgles2-mesa-dev
sudo apt purge nvidia-*
sudo add-apt-repository ppa:graphics-drivers/ppa
sudo apt update
sudo apt install nvidia-384
View ethereum-sessions.sql
WITH sessions AS (
SELECT
block_timestamp,
block_number,
transaction_index,
value,
from_address,
CASE WHEN trace_type IS NULL THEN 0 ELSE 1 END AS is_from_contract,
trace_type,
SUM(is_new_session) OVER (ORDER BY from_address, block_timestamp) AS global_session_id,
View cannabis-3k_similarity-plot.R
# dependencies
library("moduleColor")
library("dynamicTreeCut")
library("RColorBrewer")
# import data
canna.z = read.csv("~/Downloads/canna-z.csv",header=T,row.names=1)
#https://github.com/mattsarrasin/canna-gen/blob/master/sample_description_list-2.txt
id2name = read.csv('~/Downloads/results-20200327-155748.csv')
@allenday
allenday / convert-to-geography
Last active Feb 4, 2020
Load GFF v3 data to BigQuery
View convert-to-geography
SELECT
seq_id,source,type,seq_start,seq_end,
ST_MAKELINE(ST_GEOGPOINT(LOG(seq_start)/LOG(10),-0.01),ST_GEOGPOINT(LOG(seq_end)/LOG(10),0.01)) AS range_log10,
score,strand,phase,attributes
FROM `project.dataset.tablename`
LIMIT 1000
View gcloud-create-gpu-instance.sh
gcloud compute instances create XXX --project=XXX --image-project=deeplearning-platform-release --image-family=tf-latest-gpu --maintenance-policy=TERMINATE --boot-disk-size 200GB --accelerator="type=nvidia-tesla-v100,count=1" --metadata="install-nvidia-driver=True" --zone="us-west1-b"
@allenday
allenday / tensorflow_json_parser.py
Last active Jan 2, 2020 — forked from sameerg07/tensorflow_json_parser.py
converts the json file downloaded using image classifer tool of dataturks to dataset folder
View tensorflow_json_parser.py
#This script has been solely created under dataturks. Copyrights are reserved
#EXAMPLE USAGE
#python3 tensorflow_json_parser.py --json_file "flower.json" --dataset_path "Dataset5/"
import json
import glob
import urllib.request
@allenday
allenday / prolific-users.sql
Created Dec 27, 2019
Which users have contributed the most relations to the current version of OpenStreetMap?
View prolific-users.sql
SELECT username, COUNT(*) AS c
FROM
`bigquery-public-data.geo_openstreetmap.planet_relations` AS relations
GROUP BY username
ORDER BY c DESC
You can’t perform that action at this time.