Implementing Java Pivot table using Streams
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(','));
}
}
Answer
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}}