compareFunctions = [ { method: "mean", language: "JavaScript", function: "d3.mean", link: "https://github.com/d3/d3-array/blob/v3.2.2/README.md#mean", example: '`d3.mean(waterUsage.map(d => d.waterGallons))`' }, { method: "mean", language: "Python", function: "pandas mean", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.mean.html", example: "`waterUsage[\"waterGallons\"].mean()`" }, { method: "mean", language: "SQL", function: "AVG", link: "https://www.w3schools.com/sql/sql_where.asp", example: "`SELECT AVG(waterGallons) AS avgGallons FROM waterUsage`" }, {method: "mean", language: "R", function: "mean", link: "https://stat.ethz.ch/R-manual/R-devel/library/stats/html/median.html", example: "`mean(waterUsage$waterGallons)` " }, { method: "mean", language: 'Excel', function: 'AVERAGE', link: "https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6", example: '`=AVERAGE(E:E)` _(if waterGallons is in column E)_' }, { method: "standard_deviation", language: "JavaScript", function: "d3.deviation", link: "https://github.com/d3/d3-array/blob/v3.2.2/README.md#deviation", example: '`d3.deviation(waterUsage.map(d => d.waterGallons))`' }, { method: "standard_deviation", language: "Python", function: "pandas std", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.std.html", example: "`waterUsage[\"waterGallons\"].std()`" }, { method: "standard_deviation", language: "SQL", function: "STDDEV_SAMP", link: "https://duckdb.org/docs/sql/aggregates.html", example: "`SELECT STDDEV_SAMP(waterGallons) AS sdGallons FROM waterUsage`" }, {method: "standard_deviation", language: "R", function: "sd", link: "https://stat.ethz.ch/R-manual/R-devel/library/stats/html/median.html", example: "`sd(waterUsage$waterGallons)` " }, { method: "standard_deviation", language: 'Excel', function: 'STDEV', link: "https://support.microsoft.com/en-us/office/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0", example: '`=STDEV(E:E)` _(if waterGallons is in column E)_' }, { method: "median", language: "JavaScript", function: "d3.median", link: "https://github.com/d3/d3-array/blob/v3.2.2/README.md#median", example: "`d3.median(waterUsage.map(d => d.waterGallons))`" }, { method: "median", language: "Python", function: "pandas median", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.median.html", example: "`waterUsage[\"waterGallons\"].median()`" }, { method: "median", language: "SQL", function: "MEDIAN", link: "https://duckdb.org/docs/sql/aggregates.html#statistical-aggregates", example: "`SELECT MEDIAN(waterGallons) AS medWater FROM waterUsage`" }, { method: "median", language: "R", function: "median", link: "https://stat.ethz.ch/R-manual/R-devel/library/stats/html/median.html", example: "`median(waterUsage$waterGallons)`" }, { method: "median", language: 'Excel', function: 'MEDIAN', link: 'https://support.microsoft.com/en-us/office/median-function-d0916313-4753-414c-8537-ce85bdd967d2', example: '`=MEDIAN(E:E)` _(if waterGallons is in column E)_' }, { method: "range", language: "JavaScript", function: "d3.min, d3.max", link: "https://observablehq.com/@d3/d3-extent", example: "`d3.min(waterUsage.map(d => d.waterGallons))`" }, { method: "range", language: "Python", function: "pandas min, max", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.median.html", example: "`waterUsage[\"waterGallons\"].min()`" }, { method: "range", language: "SQL", function: "MIN, MAX", link: "https://www.w3schools.com/sql/sql_min_max.asp", example: "`SELECT MIN(waterGallons) AS minWater FROM waterUsage`" }, { method: "range", language: "R", function: "min, max", link: "https://stat.ethz.ch/R-manual/R-devel/library/base/html/Extremes.html", example: "`min(waterUsage$waterGallons)`" }, { method: "range", language: 'Excel', function: 'MIN, MAX', link: 'https://support.microsoft.com/en-us/office/calculate-the-smallest-or-largest-number-in-a-range-45fe249f-96c3-443b-8e9f-87f16c48462c', example: '`=MIN(E:E)` _(if waterGallons is in column E)_' }, { method: "count", language: "JavaScript", function: "length property", link: "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/length", example: "`waterUsage.length`" }, { method: "count", language: "Python", function: "pandas shape", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.shape.html", example: '`waterUsage.shape[0]` _(use [1] to return number of columns)_' }, { method: "count", language: "SQL", function: "COUNT", link: "https://www.w3schools.com/sql/sql_count_avg_sum.asp", example: "`SELECT COUNT(*) AS countRows FROM waterUsage`" }, { method: "count", language: "R", function: "nrow", link: "https://stat.ethz.ch/R-manual/R-devel/library/base/html/nrow.html", example: "`nrow(waterUsage)`" }, { method: "count", language: 'Excel', function: 'ROWS', link: 'https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597', example: '`=ROWS(A2:E237)` _(if data are in columns A through E)_' }, { method: "grouped_mean", language: "JavaScript", function: "d3.rollup, d3.mean", link: "https://github.com/d3/d3-array/blob/v3.2.2/README.md#rollup", example: "`d3.rollup(waterUsage, v => d3.mean(v, d => d.waterGallons), d => d.propertyId)`" }, { method: "grouped_mean", language: "Python", function: "pandas groupby", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html", example: "`waterUsage.groupby(\'propertyId\')[\'waterGallons\'].mean()`" }, { method: "grouped_mean", language: "SQL", function: 'GROUP BY', link: 'https://www.w3schools.com/sql/sql_groupby.asp', example: '`SELECT propertyId, AVG(waterGallons) AS mean FROM waterUsage GROUP BY propertyId`' }, { method: "grouped_mean", language: "R", function: "group_by, summarize", link: "https://dplyr.tidyverse.org/reference/summarise.html", example: "`waterUsage` |`>` `group_by(propertyId)` |`>` `summarize(mean = mean(waterGallons))`" }, { method: "grouped_mean", language: 'Excel', function: 'pivot table', link: 'https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576', example: '_(Insert > PivotTable)_' }, { method: "grouped_count", language: "JavaScript", function: "d3.rollup, d3.count", link: "https://github.com/d3/d3-array/blob/v3.2.2/README.md#rollup", example: "`d3.rollup(waterUsage, v => d3.count(v, d => d.waterGallons), d => d.propertyId)`" }, { method: "grouped_count", language: "Python", function: "pandas groupby", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html", example: "`waterUsage.groupby(\'propertyId\')[\'waterGallons\'].count()`" }, { method: "grouped_count", language: "SQL", function: 'GROUP BY, COUNT', link: 'https://www.w3schools.com/sql/sql_groupby.asp', example: '`SELECT propertyId, COUNT(waterGallons) AS counts FROM waterUsage GROUP BY propertyId`' }, { method: "grouped_count", language: "R", function: "dplyr count", link: "https://dplyr.tidyverse.org/reference/count.html", example: "`waterUsage` |`>` `count(propertyId)`" }, { method: "grouped_count", language: 'Excel', function: 'pivot table', link: 'https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576', example: '_(Insert > PivotTable)_' }, { method: "conditional_count", language: "JavaScript", function: "Array filter, length", link: "https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/filter", example: "`waterUsage.filter(d => d.waterGallons > 90 && d.propertyId == \"B002\").length`" }, { method: "conditional_count", language: "Python", function: "pandas query, shape", link: "TODO", example: "`waterUsage.query('waterGallons > 90 & propertyId == \"B002\"').shape[0]`" }, { method: "conditional_count", language: "SQL", function: "GROUP BY, WHERE", link: "https://www.w3schools.com/sql/sql_groupby.asp", example: "`SELECT COUNT(*) AS exceedDays FROM waterUsage WHERE waterGallons > 90 AND propertyId = 'B002'`" }, { method: "conditional_count", language: "R", function: "dplyr::filter, dplyr::count", link: "TODO", example: "`waterUsageR` |`>` `filter(waterGallons > 90, propertyId == \"B002\")` |`>` `count()`" }, { method: "conditional_count", language: 'Excel', function: 'COUNTIFS', link: 'https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842', example: '`=COUNTIFS(B2:B237, "=B002", E2:E237,">90")` _(if propertyId is in column B, and waterGallons is in column E)_' }, { method: "percentile", language: "JavaScript", function: "d3.quantile", link: "https://github.com/d3/d3-array#quantile", example: "`d3.quantile(waterUsage.map(d => d.waterGallons), 0.9)`" }, { method: "percentile", language: "Python", function: "pandas quantile", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.quantile.html", example: "`waterUsage['waterGallons'].quantile(0.9)`" }, { method: "percentile", language: "SQL", function: "quantile_cont", link: "https://duckdb.org/docs/sql/aggregates.html#statistical-aggregates", example: "`SELECT quantile_cont(waterGallons, 0.9) AS quant90 FROM waterUsage`" }, { method: "percentile", language: "R", function: "quantile", link: "https://stat.ethz.ch/R-manual/R-devel/library/stats/html/quantile.html", example: "`quantile(waterUsage$waterGallons, probs = 0.9)`" }, { method: "percentile", language: 'Excel', function: 'PERCENTILE', link: 'https://support.microsoft.com/en-us/office/percentile-function-91b43a53-543c-4708-93de-d626debdddca', example: '`=PERCENTILE(E2:E237, 0.9)`' }, { method: "ranks", language: "JavaScript", function: "d3.rank", link: "https://github.com/d3/d3-array/blob/main/README.md#rank", example: '`waterUsage.map((d, i) => ({...d, rank: d3.rank(waterUsage.map(d => d.waterGallons), d3.descending)[i] + 1}))` _(1 added so that ranks start at 1 instead of 0)_' }, { method: "ranks", language: "Python", function: "pandas rank", link: "https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.rank.html", example: "`waterUsage['rank'] = waterUsage['waterGallons'].rank(ascending=False, method = \"min\")`" }, { method: "ranks", language: "SQL", function: "RANK", link: "https://duckdb.org/docs/sql/window_functions.html#general-purpose-window-functions", example: "`SELECT *, RANK() OVER (ORDER BY waterGallons DESC) AS ranks FROM waterUsage`" }, { method: "ranks", language: "R", function: "rank", link: "https://stat.ethz.ch/R-manual/R-devel/library/base/html/rank.html", example: "`waterUsage` |`>` `mutate(rank = rank(-waterGallons, ties.method = \"min\"))`" }, { method: "ranks", language: 'Excel', function: 'RANK', link: 'https://support.microsoft.com/en-us/office/rank-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723', example: '`=RANK(E2, $E$2:$E$237)`' } ]