From commits-return-11728-archive-asf-public=cust-asf.ponee.io@poi.apache.org Thu Oct 18 15:46:08 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id 41D9D18064E for ; Thu, 18 Oct 2018 15:46:07 +0200 (CEST) Received: (qmail 29671 invoked by uid 500); 18 Oct 2018 13:46:06 -0000 Mailing-List: contact commits-help@poi.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@poi.apache.org Delivered-To: mailing list commits@poi.apache.org Received: (qmail 29662 invoked by uid 99); 18 Oct 2018 13:46:06 -0000 Received: from Unknown (HELO svn01-us-west.apache.org) (209.188.14.144) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 18 Oct 2018 13:46:06 +0000 Received: from svn01-us-west.apache.org (localhost [127.0.0.1]) by svn01-us-west.apache.org (ASF Mail Server at svn01-us-west.apache.org) with ESMTP id 95F753A0098 for ; Thu, 18 Oct 2018 13:46:05 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1844238 - in /poi/trunk/src: java/org/apache/poi/ss/formula/eval/FunctionEval.java java/org/apache/poi/ss/formula/functions/Frequency.java testcases/org/apache/poi/ss/formula/functions/TestFrequency.java Date: Thu, 18 Oct 2018 13:46:05 -0000 To: commits@poi.apache.org From: yegor@apache.org X-Mailer: svnmailer-1.0.9 Message-Id: <20181018134605.95F753A0098@svn01-us-west.apache.org> Author: yegor Date: Thu Oct 18 13:46:04 2018 New Revision: 1844238 URL: http://svn.apache.org/viewvc?rev=1844238&view=rev Log: Bug 62373: Support for FREQUENCY function Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Frequency.java (with props) poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFrequency.java (with props) Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Modified: poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java?rev=1844238&r1=1844237&r2=1844238&view=diff ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java (original) +++ poi/trunk/src/java/org/apache/poi/ss/formula/eval/FunctionEval.java Thu Oct 18 13:46:04 2018 @@ -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(); Added: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Frequency.java URL: http://svn.apache.org/viewvc/poi/trunk/src/java/org/apache/poi/ss/formula/functions/Frequency.java?rev=1844238&view=auto ============================================================================== --- poi/trunk/src/java/org/apache/poi/ss/formula/functions/Frequency.java (added) +++ poi/trunk/src/java/org/apache/poi/ss/formula/functions/Frequency.java Thu Oct 18 13:46:04 2018 @@ -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()
+ * Returns a frequency distribution as a vertical array

+ *

+ * Syntax
+ * FREQUENCY(data_array, bins_array)

+ *

+ * data_array 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.
+ * bins_array 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.
+ * + * @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; + } +} Propchange: poi/trunk/src/java/org/apache/poi/ss/formula/functions/Frequency.java ------------------------------------------------------------------------------ svn:eol-style = native Added: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFrequency.java URL: http://svn.apache.org/viewvc/poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFrequency.java?rev=1844238&view=auto ============================================================================== --- poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFrequency.java (added) +++ poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFrequency.java Thu Oct 18 13:46:04 2018 @@ -0,0 +1,91 @@ +/* ==================================================================== + 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.hssf.usermodel.HSSFWorkbook; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddress; +import org.junit.Test; + +import static org.apache.poi.ss.formula.functions.Frequency.histogram; +import static org.junit.Assert.assertArrayEquals; +import static org.junit.Assert.assertEquals; + +/** + * Testcase for the function FREQUENCY(data, bins) + * + * @author Yegor Kozlov + */ +public class TestFrequency { + + @Test + public void testHistogram() { + assertArrayEquals(new int[]{3, 2, 2, 0, 1, 1}, + histogram( + new double[]{11, 12, 13, 21, 29, 36, 40, 58, 69}, + new double[]{20, 30, 40, 50, 60}) + ); + + assertArrayEquals(new int[]{1, 1, 1, 1, 1, 0}, + histogram( + new double[]{20, 30, 40, 50, 60}, + new double[]{20, 30, 40, 50, 60}) + + ); + + assertArrayEquals(new int[]{2, 3}, + histogram( + new double[]{20, 30, 40, 50, 60}, + new double[]{30}) + + ); + } + + @Test + public void testEvaluate() { + Workbook wb = new HSSFWorkbook(); + FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); + + int[] data = {1, 1, 2, 3, 4, 4, 5, 7, 8, 9, 9, 11, 3, 5, 8}; + int[] bins = {3, 6, 9}; + Sheet sheet = wb.createSheet(); + Row dataRow = sheet.createRow(0); // A1:O1 + for (int i = 0; i < data.length; i++) { + dataRow.createCell(i).setCellValue(data[i]); + } + Row binsRow = sheet.createRow(1); + for (int i = 0; i < bins.length; i++) { // A2:C2 + binsRow.createCell(i).setCellValue(bins[i]); + } + Row fmlaRow = sheet.createRow(2); + CellRange arrayFmla = sheet.setArrayFormula("FREQUENCY(A1:O1,A2:C2)", CellRangeAddress.valueOf("A3:A6")); + Cell b3 = fmlaRow.createCell(1); // B3 + b3.setCellFormula("COUNT(FREQUENCY(A1:O1,A2:C2))"); // frequency returns a vertical array of bins+1 + + Cell c3 = fmlaRow.createCell(2); + c3.setCellFormula("SUM(FREQUENCY(A1:O1,A2:C2))"); // sum of the frequency bins should add up to the number of data values + + assertEquals(5, (int) evaluator.evaluate(arrayFmla.getFlattenedCells()[0]).getNumberValue()); + assertEquals(4, (int) evaluator.evaluate(arrayFmla.getFlattenedCells()[1]).getNumberValue()); + assertEquals(5, (int) evaluator.evaluate(arrayFmla.getFlattenedCells()[2]).getNumberValue()); + assertEquals(1, (int) evaluator.evaluate(arrayFmla.getFlattenedCells()[3]).getNumberValue()); + + assertEquals(4, (int) evaluator.evaluate(b3).getNumberValue()); + assertEquals(15, (int) evaluator.evaluate(c3).getNumberValue()); + + } +} Propchange: poi/trunk/src/testcases/org/apache/poi/ss/formula/functions/TestFrequency.java ------------------------------------------------------------------------------ svn:eol-style = native --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org For additional commands, e-mail: commits-help@poi.apache.org