DIY Steemit Statistics with Python: Part 5 - Reputation

Our next order of business: the distribution of reputation scores among active Steemit users.

Before we start, we prepare the workspace as usual (see the previous posts in the series for additional context: 1, 2, 3, 4):

%matplotlib inline
import sqlalchemy as sa, pandas as pd, seaborn as sns, matplotlib.pyplot as plt

sns.set_style()
e = sa.create_engine('mssql+pymssql://steemit:[email protected]/DBSteem')

def sql(query, index_col=None):
    return pd.read_sql(query, e, index_col=index_col)

 
The reputation of each user is updated dynamically with each vote they receive. In principle, we could recover it by processing the TxVotes blockchain table and accounting for all the votes and flags one by one. This would probably be computationally quite heavy, however. Instead, we can rely on the fact that SteemSQL is helpfully tracking various current user-related metrics in a dedicated table for us already.

Indeed, let us take a look at all the tables currently available to us at SteemSQL:

sql("select * from information_schema.tables")

---

---

Scanning through the list, we can guess that Accounts is most probably the table we are interested in:

sql("select top 3 * from Accounts")

 
From the results of this query (omitted) can see that there's a reputation field indeed. It is, however, given as a long, unfamiliar number - not the short value we are used to seeing in the profile page. It turns out we can convert this "raw" value to a "usual" reputation score using the following formula:

Let us test it:

sql("""
select 
    name, 
    reputation as raw_reputation,
    cast(log10(reputation)*9 - 56 as int) as reputation
from Accounts 
where name = 'konstantint'""")

---

---

This worked as expected. Now let us finally count how many accounts are there per each reputation score (dropping the scores below 26, just like @arcange does it):

reputations = sql("""
with Data as 
    (select 
       cast(log10(isnull(reputation, 0))*9 - 56 as int) as Reputation
     from Accounts
     where reputation > 0)

select 
    Reputation, count(*) as Count
from Data 
group by Reputation
having Reputation > 25
order by Reputation desc""", "Reputation")

 
For the sake of variety, we made use of a Common Table Expression in our query this time.

Let us conclude by plotting the results:

reputations.plot.bar(figsize=(8, 4));

The source code of this post is also available as a Jupyter notebook.

Next time we will plot the voting power distribution and learn to insert fishy images into our barplots.