11
$\begingroup$

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?

$\endgroup$
  • $\begingroup$ You're right, but I'm not sure I see why this should effect the calculation? $\endgroup$ – yossale Aug 25 '12 at 12:28
  • 3
    $\begingroup$ I'm guessing that your question is about the nature & calculation of the Gini index, & not about how to implement that in SQL (correct me if I'm wrong). If the latter, we should migrate this to SO. Continuing w/ my assumption, I have copied your code from the SE data site, but it might help if you can also rewrite it in pseudo-code for those who may not read SQL well. $\endgroup$ – gung Aug 25 '12 at 13:36
  • $\begingroup$ @gung thanks - I do ask about the calculation, not the SQL implementation. I'll re write it in pseudo code $\endgroup$ – yossale Aug 25 '12 at 14:56
1
$\begingroup$

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

$\endgroup$
12
$\begingroup$

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!

$\endgroup$
  • $\begingroup$ Thanks you very much! I missed the sorting part! that explains a lot... $\endgroup$ – yossale Aug 25 '12 at 15:21
  • $\begingroup$ Super. I'm interested in knowing what the value is so maybe leave a comment when you've made the calculation! $\endgroup$ – smillig Aug 25 '12 at 16:04
  • $\begingroup$ Well, When I aggregated the values (i.e if there are 10 people, with either 1,3, or 5 points, then i have just 3 ranks : 1:3,2:5,3:10) and multiplied the (how many with that score)*score*(rank of score) I got -0.98 , which would have made sense if not for the wrong sign. But I'm not sure how my little shortcut effects the gini scale $\endgroup$ – yossale Aug 25 '12 at 16:16
  • $\begingroup$ Would you not have to assign the average score? I.e. for 1:3 apply $3 \times 2$, for 2:5 apply $4 \times 3.5$ etc.? Or did you do that? $\endgroup$ – Björn May 7 '16 at 5:42
4
$\begingroup$

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.

$\endgroup$
1
$\begingroup$

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)

$\endgroup$
  • $\begingroup$ ;WITH t AS (SELECT CAST(income AS FLOAT) AS x FROM #data), tt AS (SELECT *,ROW_NUMBER() OVER(ORDER BY x) AS i FROM t) SELECT 2.0*SUM(x * i)/(COUNT(x) * SUM(x))-1.0-(1.0/COUNT(x)) AS gini FROM tt $\endgroup$ – Chris Sep 22 '16 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.