Results 1 to 5 of 5
  1. #1
    jdepp is offline Member
    Join Date
    Mar 2017
    Posts
    4
    Rep Power
    0

    Default aggregating totals and create tuple of totals by 3 values

    I have an input file that consists of multiple rows like the following:

    Category, Country, Date, Product, Amount
    Fruit, brazil, 12-mar-2016, mango, 2000.00
    Condiment, argentina, 15-dec-2016, pepper sauce, 4300.00
    Bakery, Florentina, 14-dec-2016, bread, 15000.00


    The goal is to retrieve the input from the csv file and calculate the totals and group by the 3 values. Example get totals and create an output file with a single line of totals by tuple of category, country, month(date).

    I know how to read in the file but how and where do I store the data efficiently. Would I need 3 separate arraylists or lists and how do I actually calculate the totals? Could someone please help me get started?

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: aggregating totals and create tuple of totals by 3 values

    It's easier than you think. You can create a Map<String, Double> where the key is the concatenation of category-country-month. Then calculate the value while adding it to the map. In Java 8 you have these awesome methods that do the work for you.

    Example:
    Java Code:
    import java.util.HashMap;
    import java.util.Map;
    
    public class GroupBy {
        private static Map<String, Double> values = new HashMap<>();
    
        public static void main(String[] args) {
            group("fruit-brazil-mar", 2000);
            group("condiment-holland-mar", 1000);
            group("meat-brazil-mar", 500);
            group("meat-brazil-mar", 100);
            group("meat-brazil-apr", 1500);
            group("meat-brazil-apr", 150);
            group("fruit-brazil-mar", 10);
            group("fruit-brazil-mar", 100);
    
            System.out.println("values = " + values);
        }
    
        private static void group(String key, double value) {
            values.compute(key, (k, v) -> v == null ? value : v + value);
        }
    }
    Output:
    Java Code:
    values = {fruit-brazil-mar=2110.0, 
                   condiment-holland-mar=1000.0, 
                   meat-brazil-mar=600.0, 
                   meat-brazil-apr=1650.0
    }
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  3. #3
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: aggregating totals and create tuple of totals by 3 values

    Edit: it's better to use a Key object as group object. I updated the code so it uses a Key object as key in the map. This has the advantage that you can use the original values when iterating over the totals.

    If you decide to use a Key object, make sure the hashcode() and equals() are correct, since that is how the Map finds the keys.
    Java Code:
    import java.util.*;
    
    public class GroupBy {
        private static Map<Key, Double> values = new HashMap<>();
    
        public static void main(String[] args) {
            group(new Key("fruit", "brazil", "mar"), 2000);
            group(new Key("condiment", "holland", "mar"), 1000);
            group(new Key("meat", "brazil", "mar"), 500);
            group(new Key("meat", "brazil", "mar"), 100);
            group(new Key("meat", "brazil", "apr"), 1500);
            group(new Key("meat", "brazil", "apr"), 150);
            group(new Key("fruit", "brazil", "mar"), 10);
            group(new Key("fruit", "brazil", "mar"), 100);
    
            for (Map.Entry<Key, Double> entry: values.entrySet()) {
                Key key = entry.getKey();
                Double value = entry.getValue();
                System.out.println( String.format("%s = %.2f", key.toString(), value) );
    
            }
        }
    
        private static void group(Key key, double value) {
            values.compute(key, (k, v) -> v == null ? value : v + value);
        }
    
        static class Key {
            String category, country, month;
    
            public Key(String category, String country, String month) {
                this.category = category;
                this.country = country;
                this.month = month;
            }
    
            @Override
            public String toString() {
                return String.format("%s - %s - %s", category, country, month);
            }
    
            @Override
            public boolean equals(Object o) {
                if (this == o) {
                    return true;
                }
                if (o == null || getClass() != o.getClass()) {
                    return false;
                }
                Key key = (Key) o;
                return Objects.equals(category, key.category) &&
                       Objects.equals(country, key.country) &&
                       Objects.equals(month, key.month);
            }
    
            @Override
            public int hashCode() {
                return Objects.hash(category, country, month);
            }
        }
    }
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  4. #4
    jdepp is offline Member
    Join Date
    Mar 2017
    Posts
    4
    Rep Power
    0

    Default Re: aggregating totals and create tuple of totals by 3 values

    I appreciate your help with this issue; I apologize for the late response. Completely forgot about this thread and just got reprimanded by burk...

  5. #5
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: aggregating totals and create tuple of totals by 3 values

    Quote Originally Posted by jdepp View Post
    I appreciate your help with this issue; I apologize for the late response. Completely forgot about this thread and just got reprimanded by burk...
    Don't worry about it. I hope I gave you some inspiration on how to work with this.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

Similar Threads

  1. Need help calculating totals from values in a 2D array
    By scottdaking in forum New To Java
    Replies: 4
    Last Post: 03-02-2013, 08:22 PM
  2. Array help! - Grand totals
    By Gaebril in forum New To Java
    Replies: 23
    Last Post: 04-18-2011, 12:02 AM
  3. Finding totals - HELP!!
    By mikeg in forum New To Java
    Replies: 20
    Last Post: 11-19-2010, 05:37 PM
  4. Running totals in java. HELP!
    By mikeg in forum New To Java
    Replies: 8
    Last Post: 11-19-2010, 09:29 AM
  5. Java Loops - accumulating totals
    By iv3java in forum New To Java
    Replies: 6
    Last Post: 12-15-2009, 05:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •