Skip to content

Instantly share code, notes, and snippets.

@DrPaulBrewer
Last active May 4, 2020 04:19
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save DrPaulBrewer/453fe92c574df0fb745ec5711dcc3654 to your computer and use it in GitHub Desktop.
GINISS Gini Coefficient Function for Google Sheets. Open Tools->Script Editor in Sheets and Copy/Paste.
/*
* GINISS and THEILT Copyright 2018- Paul Brewer, Economic and Financial Technology Consulting LLC <drpaulbrewer@eaftc.com>
* This file may be used or copied under the terms of the MIT License as explained below or at https://opensource.org/licenses/MIT
* The MIT License
* Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated
* documentation files (the "Software"), to deal in the Software without restriction, including without limitation
* the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and
* to permit persons to whom the Software is furnished to do so, subject to the following conditions:
* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the
* Software.
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
* WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS
* OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT
* OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*/
/**
* internal function to extract a single row of data from selected region
* and eliminate or replace blanks
*/
function prepRow(xin, replaceBlank){
var i,l,x;
// return blank if xin is not an array
if (!Array.isArray(xin)) return null;
// return blank if xin is anything other than a single row
if (xin.length!==1) return null;
// remove the single row from the 2D input
x = xin[0];
l = x.length;
// if the single row does not have a length or has only 1 number, return blank
if ((!l) || (l<2)) return null;
if (typeof(replaceBlank)==="number"){
// convert blanks in the data to the number given in blank
for(i=0,l=x.length;i<l;++i){
if ((x[i]===undefined) || (x[i]==='') || (x[i]===' ') || (x[i]==='NA')){
x[i] = replaceBlank;
}
}
} else {
// remove blank data from input array
x = x.filter(function(v){ return (typeof(v)==="number"); });
l = x.length;
// if the single row does not have a length or has only 1 number, return blank
if ((!l) || (l<2)) return null;
}
return x;
}
/**
* Calculates a scaled Gini Coefficient of a row of incomes, or other positive numeric data.
* The Gini Coefficient has been scaled to be from 0.0 for perfect equality to 1.0 for perfect inequality.
* Perfect inequality will always yield 1.0, instead of (n-1)/n, where n is group size.
* Missing data is ignored and not counted in "n". Alternatively, replaceBlank can be used to set missing data to a value.
* See Wikipedia: Gini Coefficient
*
* @param {[[number]]} x Single row-shaped Array of incomes, wealth, or other numeric data
* @param {number} replaceBlank Optional. If present, sets a cell value, such as 0, to use in lieu of missing input data.
* @return {number} The small-sample scaled Gini Coefficient
* @customfunction
*/
function GINISS(xin, replaceBlank){
var sumdiff = 0;
var sum = 0;
var i,j,l;
var x = prepRow(xin, replaceBlank);
if (x===null) return null;
// calculate the sum and sumdiff for Gini coefficient using the method described in Wikipedia
for(i=0,l=x.length;i<l;++i){
sum += x[i];
for(j=0;j<i;++j)
sumdiff += Math.abs(x[i]-x[j]);
}
// in the edge case where all the inputs are zero, return zero
if ((sum===0) && (sumdiff===0)) return 0;
// calculate the adjusted Gini coefficient (scaled to always be 0 to 1.0)
return sumdiff/((l-1)*sum);
}
/**
* Calculates the Theil T function of a row of non-negative numeric data.
* @param {[[number]]} x Single row-shaped Array of incomes, wealth, or other numeric data
* @param {number} replaceBlank Optional. If present, sets a cell value, such as 0, to use in lieu of missing input data.
* @return {number} The Theil T Coefficient
* @customfunction
*/
function THEILT(xin,replaceBlank){
var i,l,z;
var sum = 0, mean=0, theil=0;
var x = prepRow(xin, replaceBlank);
if (x===null) return null;
for(i=0,l=x.length;i<l;++i){
if (x[i]<0) return null;
sum += x[i];
}
mean = sum/l;
if (mean === 0) return 0;
for(i=0,l=x.length;i<l;++i){
z = x[i]/mean;
if (z>0){
theil += (1.0/l)*z*Math.log(z)
}
}
return theil;
}
HOW TO ADD GINISS TO A GOOGLE SHEETS SPREADSHEET
1. Upload income/profit spreadsheet data or csv file to Google Drive.
2. Open the file and convert it to Google Sheets.
3. In Google Sheets, in the menu bar, select Tools, then Script Editor
4. In the Script Editing window, remove any example code provided by google.
5, In another web browser window, go to https://gist.github.com/DrPaulBrewer/453fe92c574df0fb745ec5711dcc3654
6. Select the text contents of GINISS from the link in 5 and COPY (Control-C)
7. Go to the Script editing window from step 4, and PASTE (Control-V)
8. The code of GINISS function should now be in the script editing window. Select SAVE (Disk Icon)
9. Go back to the Google Sheet, the GINISS function should work now.
To use GINISS or THEILT function after steps 1-9
Put income data in rows.
Add a blank column for the calculation of Gini coefficient.
In first cell of blank column, use "=GINISS(A1:N1)",
where A1:N1 should be replaced with the range of cells that contain the income or profit data
If a calculation is obtained, copy this cell to the other blank rows to calculate Gini Coefficient column
If you need blank or missing data to be ignored, use GINISS(A1:N1)
If you need blank or missing data to be counted as zero, use GINISS(A1:N1,0)
Google's Technical documentation on programming custom sheets functions:
https://developers.google.com/apps-script/guides/sheets/functions
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment