1

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

  • Will there be only two columns to perform the group by? – Anant Goswami Apr 14 at 2:26
  • No, I could performe group by with any column. The problem is that I don't know for sure so the group by has to be generic – marks Apr 14 at 8:33
  • Provide the data structures you are working with, an example input and desired output (in code, not words), and the ouput of your given code for that input. – user1803551 Apr 14 at 9:23
  • For example Map<String, List<Object[]>> output = pivot(data, "sum", "Nation->Company") but the input and output could be anything (in my example the input data is a List<Object[]). Putting aside the custom aggregation function, I don't understand how to custom the last parameter because it can be chosen by the user so I have to create a structure that dinamically select the fields for the groupying by and put it in Collectors.groupingBy feature. – marks Apr 14 at 9:45
  • Very unclear. Edit the question with the info I asked for. Give a step by step explanation of your starting point, intermediate states, and final needed output. You gave some obscure pivot method without saying what the parameters are and where they are read from, explain all that in the edit. – user1803551 Apr 15 at 11:55
0
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.

  • Actually it shoul be more generic than that: I need to give as argument of my function which are the fields for groupbying. The problem is that I have to implement some generic group by statement because you can give to the function any columns to group by, so in the code we can't put groupByFun = i -> i.getNation() + "-"+ i.getCompany(); because we are being specific for that aggregation and because it is dynamic (one time I want to aggregate for one field, another for two field), so I have to implement something more abstract – marks Apr 14 at 8:44
  • Anyway, thank you, I could be a good alternative – marks Apr 14 at 8:45
0

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]}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.