From 8bf556e856f4e77dee51af448e159b0df44fbc5a Mon Sep 17 00:00:00 2001
From: Yegor Kozlov <yegor@apache.org>
Date: Thu, 18 Oct 2018 13:46:04 +0000
Subject: Bug 62373: Support for FREQUENCY function

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1844238 13f79535-47bb-0310-9956-ffa450edef68
---
 .../apache/poi/ss/formula/eval/FunctionEval.java   |  3 +-
 .../apache/poi/ss/formula/functions/Frequency.java | 81 ++++++++++++++++++++++
 2 files changed, 83 insertions(+), 1 deletion(-)
 create mode 100644 src/java/org/apache/poi/ss/formula/functions/Frequency.java

(limited to 'src/java/org/apache/poi/ss')

diff --git a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
index 0c55327a4a..8442f5832f 100644
--- a/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
+++ b/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java
@@ -243,7 +243,8 @@ public final class FunctionEval {
 
         // 247: DB
         // 252: FEQUENCY
-        
+        retval[252] = Frequency.instance;
+
         retval[FunctionID.EXTERNAL_FUNC] = null; // ExternalFunction is a FreeRefFunction, nominally 255
 
         retval[261] = new Errortype();
diff --git a/src/java/org/apache/poi/ss/formula/functions/Frequency.java b/src/java/org/apache/poi/ss/formula/functions/Frequency.java
new file mode 100644
index 0000000000..9df832366d
--- /dev/null
+++ b/src/java/org/apache/poi/ss/formula/functions/Frequency.java
@@ -0,0 +1,81 @@
+/* ====================================================================
+   Licensed to the Apache Software Foundation (ASF) under one or more
+   contributor license agreements.  See the NOTICE file distributed with
+   this work for additional information regarding copyright ownership.
+   The ASF licenses this file to You under the Apache License, Version 2.0
+   (the "License"); you may not use this file except in compliance with
+   the License.  You may obtain a copy of the License at
+
+       http://www.apache.org/licenses/LICENSE-2.0
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.formula.functions;
+
+import org.apache.poi.ss.formula.CacheAreaEval;
+import org.apache.poi.ss.formula.eval.EvaluationException;
+import org.apache.poi.ss.formula.eval.NumberEval;
+import org.apache.poi.ss.formula.eval.ValueEval;
+
+import java.util.Arrays;
+
+/**
+ * Implementation of Excel 'Analysis ToolPak' function FREQUENCY()<br>
+ * Returns a frequency distribution as a vertical array<p>
+ * <p>
+ * <b>Syntax</b><br>
+ * <b>FREQUENCY</b>(<b>data_array</b>, <b>bins_array</b>)<p>
+ * <p>
+ * <b>data_array</b> Required. An array of or reference to a set of values for which you want to count frequencies.
+ * If data_array contains no values, FREQUENCY returns an array of zeros.<br>
+ * <b>bins_array</b> Required. An array of or reference to intervals into which you want to group the values in data_array.
+ * If bins_array contains no values, FREQUENCY returns the number of elements in data_array.<br>
+ *
+ * @author Yegor Kozlov
+ */
+public class Frequency extends Fixed2ArgFunction {
+    public static final Function instance = new Frequency();
+
+    private Frequency() {
+        // enforce singleton
+    }
+
+    public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0, ValueEval arg1) {
+        MatrixFunction.MutableValueCollector collector = new MatrixFunction.MutableValueCollector(false, false);
+
+        double[] values;
+        double[] bins;
+        try {
+            values = collector.collectValues(arg0);
+            bins = collector.collectValues(arg1);
+        } catch (EvaluationException e) {
+            return e.getErrorEval();
+        }
+
+        // can bins be not sorted?
+        //bins = Arrays.stream(bins).sorted().distinct().toArray();
+
+        int[] histogram = histogram(values, bins);
+        NumberEval[] result = Arrays.stream(histogram).boxed().map(NumberEval::new).toArray(NumberEval[]::new);
+        return new CacheAreaEval(srcRowIndex, srcColumnIndex,
+                srcRowIndex + result.length - 1, srcColumnIndex, result);
+    }
+
+    static int findBin(double value, double[] bins) {
+        int idx = Arrays.binarySearch(bins, value);
+        return idx >= 0 ? idx + 1 : -idx;
+    }
+
+    static int[] histogram(double[] values, double[] bins) {
+        int[] histogram = new int[bins.length + 1];
+        for (double val : values) {
+            histogram[findBin(val, bins) - 1]++;
+        }
+        return histogram;
+    }
+}
-- 
cgit v1.2.3