Trying to compute Gini index on StackOverflow reputation distribution?

Question

I'm trying to compute the Gini index on the SO reputation distribution using SO Data Explorer. The equation I'm trying to implement is this: $$ G(S)=\frac{1}{n-1}\left(n+1-2\left(\frac{\sum^n_{i=1}(n+1-i)y_i}{\sum^n_{i=1}y_i}\right)\right) $$ Where: $n$ = number of users on the site; $i$ = user serial id (1 - 1,225,000); $y_i$ = reputation of user $i$.

This is how I implemented it (copied from here):

DECLARE @numUsers int
SELECT @numUsers = COUNT(*) FROM Users
DECLARE @totalRep float
SELECT @totalRep = SUM(Users.Reputation) FROM Users
DECLARE @giniNominator float
SELECT @giniNominator = SUM( (@numUsers + 1 - CAST(Users.Id as Float)) * 
                              CAST(Users.Reputation as Float)) FROM Users
DECLARE @giniCalc float
SELECT @giniCalc = (@numUsers + 1 - 2*(@giniNominator / @totalRep)) / @numUsers
SELECT @giniCalc

My result is (currently) -0.53, but it makes no sense: I'm not sure even how it could have become negative, and even in abs value, I would have expected the inequality to be much closer to 1, given how reputation grows the more you have it.

Am I unknowingly ignoring some assumption about the distribution of the reputation/users?

What do I do wrong?

Answer 1

Here is how you can calculate it with SQL:

with balances as (
    select '2018-01-01' as date, balance
    from unnest([1,2,3,4,5]) as balance -- Gini coef: 0.2666666666666667
    union all
    select '2018-01-02' as date, balance
    from unnest([3,3,3,3]) as balance -- Gini coef: 0.0
    union all
    select '2018-01-03' as date, balance
    from unnest([4,5,1,8,6,45,67,1,4,11]) as balance -- Gini coef: 0.625
),
ranked_balances as (
    select date, balance, row_number() over (partition by date order by balance desc) as rank
    from balances
)
SELECT date, 
    -- (1 − 2B) https://en.wikipedia.org/wiki/Gini_coefficient
    1 - 2 * sum((balance * (rank - 1) + balance / 2)) / count(*) / sum(balance) AS gini
FROM ranked_balances
GROUP BY date
ORDER BY date ASC
-- verify here http://shlegeris.com/gini

Explanation is here https://medium.com/@medvedev1088/calculating-gini-coefficient-in-bigquery-3bc162c82168

Answer 2

I can't read the SQL code very easily, but if it helps, if I were going to calculate the Gini coefficient, this is what I would do (in plain English).

1. Figure out the $n$ of $x$ (ie. the number of people with rep on SO)
2. Sort $x$ from lowest to highest
3. Sum each $x$ multiplied by its order in the rank (ie. if there are 10 people, the rep for the person with the lowest rep gets multiplied by 1 and the rep of the person with the highest rep gets multiplied by 10)
4. Take that value and divide it by the product of $n$ and the sum of $x$ (ie. $n \times \sum $ rep) and then multiply that result by 2
5. Take that result and subtract the value of $1-(1/n)$ from it.
6. Voila!

I took those steps from the remarkably straight-forward code in the R function (in the ineq package) for calculating the Gini coefficient. For the record, here's that code:

> ineq::Gini
function (x) 
{
    n <- length(x)
    x <- sort(x)
    G <- sum(x * 1:n)
    G <- 2 * G/(n * sum(x))
    G - 1 - (1/n)
}
<environment: namespace:ineq>

It looks somewhat similar to your SQL code, but like I said, I can't really read that very easily!

Answer 3

There are, I believe, four equivalent formulations of the Gini index. To me, the most natural one is a U-statistic: $$ G = \frac 2{\mu n(n-1)}\sum_{i\neq j} |x_i - x_j| $$ where $\mu$ is the mean of $x$'s. You can double-check your computations with this formula. Obviously, the result must be non-negative. For what I know about Gini indices, the reputation distribution on CV should have the Gini index above 0.9; whether 0.98 makes a lot of sense or not, I can't say though.

Answer 4

Adding to @smillig answer, based on the provided equation:

SELECT something AS x into #t FROM sometable
SELECT *,ROW_NUMBER() OVER(ORDER BY x) AS i INTO #tt FROM #t
SELECT 2.0*SUM(x*i)/(COUNT(x)*SUM(x))-1.0-(1.0/COUNT(x)) AS gini FROM #tt

Gave me on my test set:

0.45503253636587840

Which is the same as R's ineq libraries Gini(x)