Implementing pivot table in Java

Question

I need to implement a pivot table in Java and I know how to do with Java 8 Streams features. There are a lot of good solution over the web but I need something more and I don't understand how to do that: I need to create a more dynamic table where ideally you don't know for what columns you have to aggregate. For example, if I have the columns ("Nation", "Company", "Industry","Number of employes") I have to give as input:

• A custom aggregation function (ex. sum) for the measure
• A variable order of aggregation: ex., I want first aggregate for Nation and I gave as argument "Nation" or for Nation and Company and I gave as argument something like "Nation->Company". In other words, I don't know which are the fields for my aggregation and basically I need a way to implement a generic GROUP BY SQL clause, so something like:

// Given an the Arraylist ("Nation", "Company", "Industry","Number of employes") called data with some rows

Map<String, List<Object[]>> map = data.stream().collect(
                Collectors.groupingBy(row -> row[0].toString() + "-" + row[1].toString()));

for (Map.Entry<String, List<Object[]>> entry : map.entrySet()) {
            final double average = entry.getValue().stream()
                    .mapToInt(row -> (int) row[3]).average().getAsDouble();

It's not what I need because it is too explicit.

I need to:

• Split the input Arraylist in sublist by value given by the header name which I extract from my data (or more, it depends for how many column I have to group by)
• Aggregate each sublist
• Union the sublist

Could someone help or boost me? Thanks

Answer 1

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

class Input {
    private String nation, company, industry;
    private int employees;

    public Input(String nation, String company, String industry, int employees) {
        super();
        this.nation = nation;
        this.company = company;
        this.industry = industry;
        this.employees = employees;
    }

    public String getNation() {
        return nation;
    }

    public void setNation(String nation) {
        this.nation = nation;
    }

    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public String getIndustry() {
        return industry;
    }

    public void setIndustry(String industry) {
        this.industry = industry;
    }

    public int getEmployees() {
        return employees;
    }

    public void setEmployees(int employees) {
        this.employees = employees;
    }

    @Override
    public String toString() {

        return String.format(
                "Nation : %s, Company : %s, Industry : %s, Employees : %s",
                nation, company, industry, employees);
    }
}

public class CustomGroupBy {

    // Generic GroupBy
    static Map<String, List<Input>> groupBy(List<Input> input,
            Function<Input, String> classifier) {
        return input.stream().collect(Collectors.groupingBy(classifier));
    }

    public static void main(String[] args) {

        List<Input> input = Arrays.asList(new Input("India", "A", "IT", 12),
                new Input("USA", "B", "ELECTRICAL", 90), new Input("India",
                        "B", "MECHANICAL", 122), new Input("India", "B", "IT",
                        12), new Input("India", "C", "IT", 200));

        // You need to pass this in parameter
        Function<Input, String> groupByFun = i -> i.getNation() + "-"
                + i.getCompany();

        // Example-1
        Map<String, List<Input>> groupBy = groupBy(input, Input::getCompany);

        // Example-2
        Map<String, List<Input>> groupBy2 = groupBy(input, groupByFun);

        System.out.println(groupBy2);

        List<Double> averages = groupBy
                .entrySet()
                .stream()
                .map(entry -> entry.getValue().stream()
                        .mapToInt(row -> row.getEmployees()).average()
                        .getAsDouble()).collect(Collectors.toList());
        System.out.println(averages);
    }
}

You can make it generic by passing the functional interface. It is just for your reference.

Answer 2

I see two ways to make it generic. The first one is to use reflection to discover the method to call from the string representation of the field. The second one, is to create a generic get method that take a String in argument and return the value of the proper field. The second one is safer so I'll focus on that one. I'll start from the answer of @Anant Goswami which had done most of the work already.

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

class Scratch {
    // Input class from Anant Goswami in previous reply
    static class Input {
        private String nation, company, industry;
        private int employees;

        public Input(String nation, String company, String industry, int employees) {
            super();
            this.nation = nation;
            this.company = company;
            this.industry = industry;
            this.employees = employees;
        }

        public String getNation() {
            return nation;
        }

        public void setNation(String nation) {
            this.nation = nation;
        }

        public String getCompany() {
            return company;
        }

        public void setCompany(String company) {
            this.company = company;
        }

        public String getIndustry() {
            return industry;
        }

        public void setIndustry(String industry) {
            this.industry = industry;
        }

        public int getEmployees() {
            return employees;
        }

        public void setEmployees(int employees) {
            this.employees = employees;
        }

        @Override
        public String toString() {

            return String.format(
                    "Nation : %s, Company : %s, Industry : %s, Employees : %s",
                    nation, company, industry, employees);
        }

        public Object get(String field){
            switch (field.toLowerCase()){
                case "nation": return getNation();
                case "company": return getCompany();
                case "industry": return getIndustry();
                case "employees": return getEmployees();
                default: throw new UnsupportedOperationException();
            }
        }
    }

    private static Map<String, List<Input>> group(List<Input> inputs, String... fields){
        Function<Input, String> groupBy = i -> Arrays.stream(fields).map(f -> i.get(f).toString()).collect(Collectors.joining("-"));
        Map<String, List<Input>> result = inputs.stream().collect(Collectors.groupingBy(groupBy));
        System.out.println(result);
        return result;
    }

    public static void main(String[] args) {
        List<Input> input = Arrays.asList(new Input("India", "A", "IT", 12),
                new Input("USA", "B", "ELECTRICAL", 90), new Input("India",
                        "B", "MECHANICAL", 122), new Input("India", "B", "IT",
                        12), new Input("India", "C", "IT", 200));
        group(input, "company");
        group(input, "nation", "Company");
    }
}

Which give as output

{A=[Nation : India, Company : A, Industry : IT, Employees : 12], B=[Nation : USA, Company : B, Industry : ELECTRICAL, Employees : 90, Nation : India, Company : B, Industry : MECHANICAL, Employees : 122, Nation : India, Company : B, Industry : IT, Employees : 12], C=[Nation : India, Company : C, Industry : IT, Employees : 200]}
{India-B=[Nation : India, Company : B, Industry : MECHANICAL, Employees : 122, Nation : India, Company : B, Industry : IT, Employees : 12], India-C=[Nation : India, Company : C, Industry : IT, Employees : 200], India-A=[Nation : India, Company : A, Industry : IT, Employees : 12], USA-B=[Nation : USA, Company : B, Industry : ELECTRICAL, Employees : 90]}