I have been struggling with this issue for a few days now. I am trying to create Pivot functionality using Java Streams. I only have to implement SUM, COUNT, MAX, MIN, and AVERAGE. For input I am given a Pivot Column Index, an array of pivot row indexes, and the value to calculate.
The catch is the data is in a List < List < Object>>, Where Object with either be String, Integer, or Double. but I won't know until runtime. And I have to return my results as List < List < Object>>.
I am having trouble with MAX/MIN (I am assuming that AVERAGE will be similar to MAX and MIN)
In order to pivot on multiple table values, I created a class to using my my second groupingBy
This will not compile, I am not sure what to Compare against, where to convert the object to int or if I even need to. I would like to do this all with one stream, but I am not sure it is possible. What am I doing wrong, or could I do it differently. Thanks in advance.
package pivot.test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Collectors;
public class PivotTest {
List<List<Object>> rows = new ArrayList<List<Object>>();
public PivotTest() throws Exception {
rows.add(Arrays.asList(new Object[]{ "East", "Boy", "Tee", 10, 12.00}));
rows.add(Arrays.asList(new Object[]{ "East", "Boy", "Golf", 15, 20.00}));
rows.add(Arrays.asList(new Object[]{ "East", "Girl", "Tee", 8, 14.00}));
rows.add(Arrays.asList(new Object[]{ "East", "Girl", "Golf", 20, 24.00}));
rows.add(Arrays.asList(new Object[]{ "West", "Boy", "Tee", 5, 12.00}));
rows.add(Arrays.asList(new Object[]{ "West", "Boy", "Golf", 12, 20.00}));
rows.add(Arrays.asList(new Object[]{ "West", "Girl", "Tee", 15, 14.00}));
rows.add(Arrays.asList(new Object[]{ "West", "Girl", "Golf", 10, 24.00}));
}
// Dynamic Max based upon Column, Value to sum, and an array of pivot rows
public void MaxTable(int colIdx, int valueIdx, int... rowIdx) {
Map<Object, Map<Object, Integer>> myList = newRows.stream().collect(
Collectors.groupingBy(r -> ((List<Object>) r).get(colIdx),
Collectors.groupingBy( r -> new PivotColumns(r, rowIdx),
Collectors.collectingAndThen( Collectors.maxBy(Comparator.comparingInt(???)),
r -> ((List<Object>) r).get(valueIdx)))));
System.out.println("Dynamic MAX PIVOT"); System.out.println(myList);
}
public static void main(String[] args) {
try {
PivotTest p = new PivotTest();
System.out.println("\n\nStreams PIVOT with index values inside a List\n");
p.MaxTable(0, 3, new int[] { 2 });
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
class PivotColumns {
ArrayList<Object> columns;
public PivotColumns(
List<Object> objs, int... pRows) {
columns = new ArrayList<Object>();
for (int i = 0; i < pRows.length; i++) {
columns.add(objs.get(pRows[i]));
}
}
public void addObject(Object obj) {
columns.add(obj);
}
@Override
public int hashCode() {
final int prime = 31;
int result = 1;
result = prime * result + ((columns == null) ? 0 : columns.hashCode());
return result;
}
@Override
public boolean equals(Object obj) {
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
PivotColumns other = (PivotColumns) obj;
if (columns == null) {
if (other.columns != null)
return false;
} else if (!columns.equals(other.columns))
return false;
return true;
}
public String toString() {
String s = "";
for (Object obj : columns) {
s += obj + ",";
}
return s.substring(0, s.lastIndexOf(','));
}
}
Use the Data Model to create a new PivotTable For more information, see Import multiple tables from other data sources. To make all this unrelated data work together, you'll need to add each table to the Data Model, and then create relationships between the tables using matching field values.
Pivot allows developers to easily construct visually-engaging, cross-platform, connected applications in Java or any other JVM language, such as JavaScript, Groovy, or Scala.
A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for: Querying large amounts of data in many user-friendly ways.
As all possible values (String
, Integer
, Double
) are known to be Comparable
, you may perform an unchecked cast to the Comparable
interface. Also don't forget to unpack the optional. Finally if I understand correctly, the result should be Map<Object, Map<Object, Object>> myList
, not Map<Object, Map<Object, Integer>> myList
, as your column may have non-integer values:
public void MaxTable(int colIdx, int valueIdx, int... rowIdx) {
Map<Object, Map<Object, Object>> myList = newRows.stream().collect(
Collectors.groupingBy(r -> r.get(colIdx),
Collectors.groupingBy( r -> new PivotColumns(r, rowIdx),
Collectors.collectingAndThen( Collectors.maxBy(
Comparator.comparing(r -> (Comparable<Object>)(((List<Object>) r).get(valueIdx)))),
r -> r.get().get(valueIdx)))));
System.out.println("Dynamic MAX PIVOT"); System.out.println(myList);
}
Results:
> p.MaxTable(0, 3, new int[] { 1 });
{West={Girl=15, Boy=12}, East={Girl=20, Boy=15}}
> p.MaxTable(0, 4, new int[] { 1 });
{West={Girl=24.0, Boy=20.0}, East={Girl=24.0, Boy=20.0}}
As you can see, you can handle both Integer
and Double
column. Even String
can be handled (lexicographically max value will be selected).
For the averaging you may assume that your column values are numbers (Number
class, either Integer
or Double
) and collect to Double
(average of integers can be non-integer as well):
public void AverageTable(int colIdx, int valueIdx, int... rowIdx) {
Map<Object, Map<Object, Double>> myList = newRows.stream().collect(
Collectors.groupingBy(r -> r.get(colIdx), Collectors
.groupingBy(r -> new PivotColumns(r, rowIdx),
Collectors.averagingDouble(r -> ((Number) (r
.get(valueIdx))).doubleValue()))));
System.out.println("Dynamic AVG PIVOT"); System.out.println(myList);
}
Output:
> p.AverageTable(0, 3, new int[] { 1 });
{West={Girl=12.5, Boy=8.5}, East={Girl=14.0, Boy=12.5}}
> p.AverageTable(0, 4, new int[] { 1 });
{West={Girl=19.0, Boy=16.0}, East={Girl=19.0, Boy=16.0}}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With