Best data structure to "group by" and aggregate values in Java?

Question

I created an ArrayList of Array type like below,

ArrayList<Object[]> csvArray = new ArrayList<Object[]>();

As you can see, each element of the ArrayList is an array like {Country, City, Name, Age}.

Now I'm wanting to do a "group by" on Country and City (combined), followed by taking the average Age of the people for each Country+City.

May I know what is the easiest way to achieve this? Or you guys have suggestions to use data structures better than ArrayList for this "group by" and aggregation requirements?

Your answers are much appreciated.

Why don't you use SQL or other relational algebra languages? — amit, May 04 '15 at 10:41
With java 8 you could use the various collectors. But as amit said, if the data is coming from a database, implementing the SQL query would be more efficient. — Alexis C., May 04 '15 at 10:42
See if this helps - http://stackoverflow.com/questions/21678430/group-a-list-of-objects-by-an-attribute-java — LittlePanda, May 04 '15 at 10:43
@SaurabhJhunjhunwala Reading CSV files line by line, then split each line to retrieve value from requested columns and store them in an Array. Each array is added to the ArrayList. — isaacniu, May 04 '15 at 10:48
its always suggestible to group while populating the list, as it saves an extra iteration. — Saurabh Jhunjhunwala, May 04 '15 at 10:49
@amit Hi Amit, thanks for your response. This however is purely a Java programming problem. I'm not supposed to use database/SQL. — isaacniu, May 04 '15 at 10:56

score 7 · Answer 1 · answered May 04 '15 at 10:59

You will get lot of options in Java 8.

Example

 Stream<Person> people = Stream.of(new Person("Paul", 24), new Person("Mark",30), new Person("Will", 28));
 Map<Integer, List<String>> peopleByAge = people
.collect(groupingBy(p -> p.age, mapping((Person p) -> p.name, toList())));
 System.out.println(peopleByAge);

If you can use Java 8 and no specific reason for using a data structure, you can go through below tutorial

http://java.dzone.com/articles/java-8-group-collections

score 1 · Answer 2 · answered May 04 '15 at 10:59

You could use Java 8 streams for this and Collectors.groupingBy. For example:

final List<Object[]> data = new ArrayList<>();
data.add(new Object[]{"NL", "Rotterdam", "Kees", 38});
data.add(new Object[]{"NL", "Rotterdam", "Peter", 54});
data.add(new Object[]{"NL", "Amsterdam", "Suzanne", 51});
data.add(new Object[]{"NL", "Rotterdam", "Tom", 17});

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

for (final Map.Entry<String, List<Object[]>> entry : map.entrySet()) {
    final double average = entry.getValue().stream()
                                .mapToInt(row -> (int) row[3]).average().getAsDouble();
    System.out.println("Average age for " + entry.getKey() + " is " + average);
}

score 1 · Answer 3 · answered May 04 '15 at 11:09

You can check the collections recommended by @duffy356. I can give you an standard solution related with java.utils

I'd use a common Map<Key,Value> and being specific a HashMap.
For the keys, as I can see, you'll need and extra plain object which relates country and city. The point is create a working equals(Object) : boolean method. I'd use the Eclipse-auto generator; for me it gives me the following:

class CountryCityKey {
 // package visibility
 String country;
 String city;

@Override
public int hashCode() {
  final int prime = 31;
  int result = 1;
  result = prime * result + ((country == null) ? 0 : country.hashCode());
  result = prime * result + ((region == null) ? 0 : region.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;
  CountryCityKey other = (CountryCityKey) obj;
  if (country == null) {
    if (other.country != null)
      return false;
  } else if (!country.equals(other.country))
    return false;
  if (region == null) {
    if (other.region != null)
      return false;
  } else if (!region.equals(other.region))
    return false;
  return true;
}

}

Now we can group or objects in a HashMap<CountryCityKey, MySuperObject>

The code for that could be:

Map<CountryCityKey, List<MySuperObject>> group(List<MySu0perObject> list) {
  Map<CountryCityKey, MySuperObject> response = new HashMap<>(list.size());  
  for (MySuperObject o : list) {
     CountryCityKey key = o.getKey(); // I consider this done, so simply
     List<MySuperObject> l;
     if (response.containsKey(key)) {
        l = response.get(key);
     } else {
        l = new ArrayList<MySuperObject>();
     }
     l.add(o);
     response.put(key, l);
  }
  return response;
}

And you have it :)

score 0 · Answer 4 · answered May 04 '15 at 10:49

0

you could use the brownies-collections library of magicwerk.org (http://www.magicwerk.org/page-collections-overview.html)

they offer keylists, which fit your requirements.(http://www.magicwerk.org/page-collections-examples.html)

answered May 04 '15 at 10:49

duffy356

3,678
3
32
47

Hi duffy thanks for your response. May I know if you've got any clue to achieve the requirements while NOT using any external jar files? – isaacniu May 04 '15 at 10:55

swinkler · Answer 5 · 2015-05-04T11:06:31.633

I would recommend an additional step. You gather your data from CSV in Object[]. If you wrap your data into a class containing these data java8 collections will easily help you. (also without but it is more readable and understandable)

Here is an example - it introduces a class Information which contains your given data (country, city,name, age). The class has a constructor initializing these fields by a given Object[] array which might help you to do so - BUT: the fields have to be fixed (which is usual for CSV):

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class CSVExample {

  public static void main(String[] args) {
    ArrayList<Information> csvArray = new ArrayList<>();

    csvArray.add(new Information(new Object[] {"France", "Paris", "Pierre", 34}));
    csvArray.add(new Information(new Object[] {"France", "Paris", "Madeleine", 26}));
    csvArray.add(new Information(new Object[] {"France", "Toulouse", "Sam", 34}));
    csvArray.add(new Information(new Object[] {"Italy", "Rom", "Paul", 44}));

// combining country and city with whitespace delimiter to use it as the map key
    Map<String, List<Information>> collect = csvArray.stream().collect(Collectors.groupingBy(s -> (s.getCountry() + " " + s.getCity())));
//for each key (country and city) print the key and the average age
    collect.forEach((k, v) -> System.out.println(k + " " + v.stream().collect(Collectors.averagingInt(Information::getAge))));
  }
}

class Information {
  private String country;
  private String city;
  private String name;
  private int age;

  public Information(Object[] information) {
    this.country = (String) information[0];
    this.city = (String) information[1];
    this.name = (String) information[2];
    this.age = (Integer) information[3];

  }

  public Information(String country, String city, String name, int age) {
    super();
    this.country = country;
    this.city = city;
    this.name = name;
    this.age = age;
  }

  public String getCountry() {
    return country;
  }

  public String getCity() {
    return city;
  }

  public String getName() {
    return name;
  }

  public int getAge() {
    return age;
  }

  @Override
  public String toString() {
    return "Information [country=" + country + ", city=" + city + ", name=" + name + ", age=" + age + "]";
  }

}

The main shows a simple output for your question.

MChaker · Answer 6 · 2015-05-04T15:11:49.890

In java 8 the idea of grouping objects in a collection based on the values of one or more of their properties is simplified by using a Collector.

First, I suggest you add a new class as follow

class Info {

    private String country;
    private String city;
    private String name;
    private int age;

    public Info(String country,String city,String name,int age){
        this.country=country;
        this.city=city;
        this.name=name;
        this.age=age;
    }

    public String toString() {
         return "("+country+","+city+","+name+","+age+")";
    }

   // getters and setters       

}

Setting up infos

   ArrayList<Info> infos  =new  ArrayList();


   infos.add(new Info("USA", "Florida", "John", 26));
   infos.add(new Info("USA", "Florida", "James", 18));
   infos.add(new Info("USA", "California", "Alan", 30));

Group by Country+City:

  Map<String, Map<String, List<Info>>> 
           groupByCountryAndCity = infos.
             stream().
               collect(
                    Collectors.
                        groupingBy(
                            Info::getCountry,
                            Collectors.
                                groupingBy(
                                     Info::getCity     
                                          )
                                   )
                     );


    System.out.println(groupByCountryAndCity.get("USA").get("California"));

Output

[(USA,California,James,18), (USA,California,Alan,30)]

The average Age of the people for each Country+City:

    Map<String, Map<String, Double>> 
    averageAgeByCountryAndCity = infos.
         stream().
           collect(
             Collectors.
                 groupingBy(
                    Info::getCountry,
                     Collectors.
                         groupingBy(
                             Info::getCity,
                             Collectors.averagingDouble(Info::getAge)
                                   )
                            )
              );

     System.out.println(averageAgeByCountryAndCity.get("USA").get("Florida"));

Output:

22.0

@user381753 Any comments? Has it work for you or not? – MChaker May 04 '15 at 15:14 — MChaker, May 04 '15 at 15:14

score 0 · Answer 7 · answered May 01 '18 at 14:20

/* category , list of cars*/

Please use the below code : I have pasted it from my sample app !Happy Coding .

                            Map<String, List<JmCarDistance>> map = new HashMap<String, List<JmCarDistance>>();

                            for (JmCarDistance jmCarDistance : carDistanceArrayList) {
                                String key  = jmCarDistance.cartype;
                                if(map.containsKey(key)){
                                    List<JmCarDistance> list = map.get(key);
                                    list.add(jmCarDistance);

                                }else{
                                    List<JmCarDistance> list = new ArrayList<JmCarDistance>();
                                    list.add(jmCarDistance);
                                    map.put(key, list);
                                }

                            }

Phong Phuong · Answer 8 · 2020-08-05T20:38:06.807

0

Best data structure is a Map<Tuple, List>.

Tuple is the key, i.e. your group by columns. List is used to store the row data.

Once you have your data in this structure, you can iterate through each key, and perform the aggregation on the subset of data.

edited Aug 05 '20 at 20:38

answered Aug 02 '20 at 15:29

Phong Phuong

369
4
8

Best data structure to "group by" and aggregate values in Java?

8 Answers8

Group by Country+City:

The average Age of the people for each Country+City: