After seeing how to get Steem aggregated data (https://busy.org/@verodato/steem-blockchain-the-forest-and-the-trees) and how to connect to the database (https://busy.org/@verodato/connecting-to-the-steemsql-database-via-r), we’re finally going to make some data analysis and to draw a nice chart. Let’s get all the VESTS that all accounts across Steem have (remember, the code below is supposed to be run on R):
library(data.table)
sqltxt <- paste("SELECT sum(cast(replace((vesting_shares),' VESTS','') AS DECIMAL(20,6))) AS 'vs' FROM Accounts")
total_vs <- data.table(dbGetQuery(conn, sqltxt))
Now let's get all the delegations received:
sqltxt <- paste("SELECT sum(cast(replace((received_vesting_shares),' VESTS','') AS DECIMAL(20,6))) AS 'rvs' FROM Accounts")
total_rvs <- data.table(dbGetQuery(conn, sqltxt))
At this point we can check that 32% all all VESTS (or Steem Power) are delegated:
100*total_rvs/total_vs
What are the accounts that receive the most delegations? Let's check it out:
sqltxt <- paste("SELECT TOP 30 name, sum(cast(replace((received_vesting_shares),' VESTS','') AS DECIMAL(20,6))) AS 'rvs' FROM Accounts GROUP BY name ORDER BY rvs DESC",sep="")
top_rvc <- data.table(dbGetQuery(conn, sqltxt))
It turns out that the top 30 accounts that receive the most delegations concentrate 62% of all the received delegations in the Steem blockchain, as you can easily see for yourself by doing:
top_rvc[,sum(rvs)]/total_rvs
Let's present the top 30 accounts in a way that is easy to our readers to interpret the information. We could do it by showing the data in a table, but a treemap is sometimes more informative. It is quite straightforward to build one in R:
df <- data.frame( top_rvc[order(rvs,decreasing = T)] )
library(treemap)
treemap(df,
index='name',
vSize = 'rvs',
palette = "#6E6EFF",
title="Top 30 accounts that receive most delegations",
fontsize.title = 20
)
We first had to turn our data structure "data.table" into a "data.frame" and to order the accounts by how much each one had of VESTS. After that we feed the "treemap" function with the delegation data to produce the chart above, in which the size of each rectangle is proportional to how much delegation it has received. In the next post we will keep on diving in the delegation data.
Interesting breakdown. I think two additions would make this more useful, particularly for people new to the Steem blockchain:
1- Colour coding (of each block) by function, ie:
2- Colour coding (within each block) by large delegators, mainly to separate out:
Two separate charts obviously. Put one above the other and it will also help show who is funding which accounts.
These are great suggestions, @miniature-tiger. Thanks! I'll try to implement it. The problem is how to classify, in the case of those receiving delegations, services that are more than bid-bots, as @minnowbooster, for example. For those giving delegations, I think there's only two kinds of them: the big ones, like @misterdelegation, @steem, @freedom, @blocktrades, and the rest. Would there be a third class?
Trying hard to classify @tribesteemup
promotion/resteem-engine?
just another bid-bot?
Not really a bid bot, but a bot nontheless. "[TribeSteemUp] pools SP and has an automated voting list for [anarchistic] content creators". It was created by @kennyskitchen.
I am not affiliated with TribeSteemUp in any way, but I keep running into posts, so I googled and found this.
This video explains TribeSteemUp well. Very interesting. Thanks, @redsandro!
+1 for color-coding!
how's = who's
Thanks, @reko!
You got a 38.25% upvote from @upme thanks to @verodato! Send at least 3 SBD or 3 STEEM to get upvote for next round. Delegate STEEM POWER and start earning 100% daily payouts ( no commission ).
Wow!!!
This is a valuable information!!!
You got a 34.57% upvote from @upmewhale courtesy of @verodato!
Earn 100% earning payout by delegating SP to @upmewhale. Visit http://www.upmewhale.com for details!
Good content,,Nice coding!!!!
You got a 26.44% upvote from @postpromoter courtesy of @verodato!
Want to promote your posts too? Check out the Steem Bot Tracker website for more info. If you would like to support the development of @postpromoter and the bot tracker please vote for @yabapmatt for witness!
Nice work!!
great work done @verodato
Very cool information. Any generous whales out there looking to delegate to a curator? :-P
Nice analysis.
Good publication @verodato! It is very useful indeed, I hope to take advantage, thanks for sharing!
much easier to understand in an inforgraphic
You have recieved a free upvote from minnowpond, Send 0.1 -> 2 SBD with your post url as the memo to recieve an upvote from up to 100 accounts!