R Tutorial

R-studio

There are current 211 members in the Steemit Wechat Group and I want to know how rich we are as a group. Last tutorial, we talked about how to connect to STEEMSQL via RODBC, and today, we are going to exercise this a little bit with a code example – How rich is SteemIt Wechat Group?

The STEEMSQL we are looking for is something like this:

1	select savings_balance, savings_sbd_balance, balance, sbd_balance from Accounts where name='justyy'

select savings_balance, savings_sbd_balance, balance, sbd_balance from Accounts where name='justyy'

linqpad-steemsql-wallet

The values of these four fields are not pure numbers, instead, they come with the unit either SBD or STEEM. So it is sad that we cann’t do this in pure SQL via aggregate function sum But that is OK.

The members of the wechat group can be found via Github where you can submit a pull request if you believe you are not in the list.

https://raw.githubusercontent.com/DoctorLai/steemit-wechat-group/master/members.txt

Basically, it is a text file with each line containing the STEEM ID. So in R, we can just use readLines to import the data into an array.

url = "https://raw.githubusercontent.com/DoctorLai/steemit-wechat-group/master/members.txt"
members = readLines(url)

Now, we can then construct the SQL to explicitly limit the account names to be within the group, namely,

sqlQuery(conn, str_c("select savings_balance, savings_sbd_balance, balance, sbd_balance from Accounts where name in ('", paste(members, collapse = "','"), "')"))

So the SQL will become something like this:

1	select savings_balance, savings_sbd_balance, balance, sbd_balance from Accounts where name in ('justyy', 'tumutanzi' ...)

select savings_balance, savings_sbd_balance, balance, sbd_balance from Accounts where name in ('justyy', 'tumutanzi' ...)

It is better to save this in a R function:

getsbd = function(members) {
  conn <- odbcDriverConnect("Driver=SQL Server Native Client 11.0;Server=sql.steemsql.com;Database=DBSteem;Uid=steemit;Pwd=steemit")
  x <- sqlQuery(conn, str_c("select savings_balance, savings_sbd_balance, balance, sbd_balance from Accounts where name in ('", paste(members, collapse = "','"), "')"))
  close(conn)
  return(x)
}

Then, arr = getsbd(members) gets the data (four columns, i.e. dim(arr) [1] 211 4) and we can process them individually.

sum_sbd = 0.0
sum_steem = 0.0
sum_sbd_saving = 0.0
sum_steem_saving = 0.0

for(money in arr[,4]) {
  x = unlist(str_split(money, " "))[1]
  sum_sbd = sum_sbd + as.numeric(x)
}

for(money in arr[,2]) {
  x = unlist(str_split(money, " "))[1]
  sum_sbd_saving = sum_sbd_saving + as.numeric(x)
}

for(money in arr[,3]) {
  x = unlist(str_split(money, " "))[1]
  sum_steem = sum_steem + as.numeric(x)
}

for(money in arr[,1]) {
  x = unlist(str_split(money, " "))[1]
  sum_steem_saving = sum_steem_saving + as.numeric(x)
}

str_split will split the string e.g. 1.00 SBD or 2.00 STEEM to vectors so that the value and unit are separated. And the final result is:

print(paste("Total SBD = ", sum_sbd))
print(paste("Total SBD Savings = ", sum_sbd_saving))
print(paste("Total STEEM = ", sum_steem))
print(paste("Total STEEM Savings = ", sum_steem_saving))
[1] "Total SBD =  31739.145"
[1] "Total SBD Savings =  597.972"
[1] "Total STEEM =  16067.426"
[1] "Total STEEM Savings =  0.01"

Similarly, you can sum up the Steem Power, however, it is a bit tricky to convert the VESTS to SP.