The objective is to analyse blockchain data available via BlockchainBlockData .
Once blocks data are collected and transformed in tabular format we could use SQL functions to structure, transform , aggregate and visualize blocks attributes.
SQL functions for Wolfram are described and defined in previous post (https://community.wolfram.com/groups/-/m/t/2195893)
In order to load SQL functions evaluate “Main Code” cell from SQL_Operators notebook ( https://www.wolframcloud.com/obj/damcalrom/Published/SQL_Operators.nb )
Once blocks data are collected and transformed in tabular format we could use SQL functions to structure, transform , aggregate and visualize blocks attributes.
SQL functions for Wolfram are described and defined in previous post (https://community.wolfram.com/groups/-/m/t/2195893)
In order to load SQL functions evaluate “Main Code” cell from SQL_Operators notebook ( https://www.wolframcloud.com/obj/damcalrom/Published/SQL_Operators.nb )
Get the last available block number
In[]:=
lastBlockNo=BlockchainBlockData[-1,{"BlockNumber"}]//First
Out[]=
687937
Check the structure of bitcoin block
In[]:=
BlockchainBlockData[lastBlockNo]//Dataset
Out[]=
Select some attributes of interests
In[]:=
props={"Timestamp","BlockNumber","TotalTransactions","ByteCount","Amounts"};
Get the block attributes into a sql table structure. - We take the last 1000 generated blocks starting from last block backwards (takes aprox 250 seconds , most of the time being spent in block retrieval func BlockchainBlockData) - Converts the association type column “Amount” into a values list and then creates corresponding columns
In[]:=
tbLastBlocks=Range[lastBlockNo-1000,lastBlockNo]// Map[BlockchainBlockData[#,props]&,#]&// tableSQL[#,props]&// rowMapSQL[#,{All},ReplaceAll[#,x_Association({x//Values})]&]&// unnestSQL[#,{Amounts},{"BlockReward","TotalFee","TotalInput","TotalOutput"}]&;//AbsoluteTiming
Out[]=
{290.413,Null}
Browse sql table data as a dataset
In[]:=
tbLastBlocks//tableSQLAsDataset
Out[]=
Once retrieved in table structure data could be exported locally in csv file to avoid reusing BlockchainBlockData.
The blocks in blockchain are immutable.
The blocks in blockchain are immutable.
tbLastBlocks["data"]//Prepend[#,tbLastBlocks["header"]//Keys]&//Export["Blockchain.csv",#,"CSV","FieldSeparators"";"]&
Out[]=
Blockchain.csv
Out[]=
C:\Users\relat
Aggregate data by hour
In[]:=
tbLastBlocks//groupBySQL[#,{DateObject[Timestamp,"Hour"]}]&//summarySQL[#,Association["BlocksMined"(BlockNumber//Length), "TotalTransactions"(TotalTransactions//Total), "BlockReward"(BlockReward//Total)]]&//showTableSQL
Total Rows:193 Elapsed:0.0581299
Out[]//TableForm=
Aggregate data by day
In[]:=
tbLastBlocks//groupBySQL[#,{DateObject[Timestamp,"Day"]}]&//summarySQL[#,Association["BlocksMined"(BlockNumber//Length), "TotalTransactions"(TotalTransactions//Total), "BlockReward"(BlockReward//Total)]]&//showTableSQL
Total Rows:9 Elapsed:0.0409658
Out[]//TableForm=
Timestamp | BlocksMined | TotalTransactions | BlockReward |
73 | 146972 | Missing[NotAvailable]+ | |
124 | 266379 | 6Missing[NotAvailable]+ | |
104 | 218358 | 3Missing[NotAvailable]+ | |
130 | 223244 | 4Missing[NotAvailable]+ | |
128 | 185447 | 8Missing[NotAvailable]+ | |
132 | 226511 | 8Missing[NotAvailable]+ | |
140 | 255658 | 5Missing[NotAvailable]+ | |
123 | 256886 | Missing[NotAvailable]+ | |
47 | 74370 |
We have some missing values . Is it because the blocks are invalid ? or BlockchainBlockData function retrieval issue? Have no idea :)
Here some blocks having missing rewards
Here some blocks having missing rewards
In[]:=
tbLastBlocks//whereSQL[#,MissingQ[BlockReward]]&//showTableSQL
Total Rows:36 Elapsed:0.0045233
Out[]//TableForm=
Plot daily data aggregations
Even is irrelevant we check some correlation between block attribute and BTC price as an exercise