How Should I read CSV By Grouping Column of Same Value

Question

I have a task to read a CSV file then do some logic and then create a JSON for that.

I am kind of stuck with required logic before creating JSON, I need to set Max PR VALUE against the SK as PR for all the same SK's.

My Requirement:

CSV:

SK,VR,ST,PR
1000,1000-Q1,10,187
1000,1000-Q2,20,925  // MAX PR against SK
1001,1001-Q1,10,112
1001,1001-Q2,30,120  // MAX PR against SK

Note: Max PR against SK will always be in the last row of its SK.

I have to read CSV here and need to write JSON data as below :

[
   {
      "SK": "1000",
      "VR": "1000-Q1",
      "ST": "10",
      "PR": "925"
   },
   {
      "SK": "1000",
      "VR": "1000-Q2",
      "ST": "20",
      "PR": "925"
   },
   {
      "SK": "1001",
      "VR": "1001-Q1",
      "ST": "10",
      "PR": "120"
   },
   {
      "SK": "1001",
      "VR": "1001-Q2",
      "ST": "30",
      "PR": "120"
   }
]

Edit:

Code

       File input = new File("input.csv");
       File output = new File("output.json");
       CsvSchema csvSchema = CsvSchema.builder().setUseHeader(true).build();
       CsvMapper csvMapper = new CsvMapper();

       // Read data from CSV file
       List<Object> readAll = csvMapper.readerFor(Map.class).with(csvSchema).readValues(input).readAll();

       ObjectMapper mapper = new ObjectMapper();

       // Write JSON formated data to output.json file
       mapper.writerWithDefaultPrettyPrinter().writeValue(output, readAll);

       // Write JSON formated data to stdout
       System.out.println(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(readAll));

Answer 1

One Approach would be to first group your CSV Records per SK

        String[] HEADERS = { "SK","VR","ST","PR"};

        Reader in = new FileReader("mycsvfile.csv");
        Iterable<CSVRecord> records = CSVFormat.DEFAULT
          .withHeader(HEADERS)
          .withFirstRecordAsHeader()
          .parse(in);

        // Group the records by  SK
     Map<String, List<CSVRecord>> recordListBySK =   StreamSupport
            .stream(records.spliterator(), false).
            collect(Collectors.groupingBy(record -> record.get("SK")));

Then you need to add another a Mapping this time, which keeps the MAX-PR per each Sk

 Map<String, Integer> skMaxMap =  recordListBySK
    .entrySet()
    .stream()
    .collect(Collectors
                .toMap( e -> e.getKey(),
                        e -> e.getValue()
                              .stream()
                              .mapToInt(v -> Integer.parseInt(v.get("PR")))
                              .max()
                              .getAsInt() 
                      )
            );

Now you simply build you json Sk list like this:

 // Building the new sk (JSON ) objects
 List<NewSk> newSkList = new ArrayList<>();
 recordListBySK
    .values()
    .stream()
    .flatMap(v -> v.stream())
    .forEach(csvRecord -> {
         NewSk newSk = new NewSk(csvRecord.get("SK"),
                                csvRecord.get("VR"),
                                csvRecord.get("ST"),
                                skMaxMap.get(csvRecord.get("SK"))
                                );
         newSkList.add(newSk);
    });

if you try to print them out:

newSkList.forEach(sk -> {
         System.out.print(" "+sk.getSk());
         System.out.print(" "+sk.getVr());
         System.out.print(" "+sk.getSt());
         System.out.print(" "+sk.getPr());
         System.out.println(" ");
     });

you'll get this

 1001 1001-Q1 10 120 
 1001 1001-Q2 30 120 
 1000 1000-Q1 10 925 
 1000 1000-Q2 20 925

No you can write your List to your JSON file using your JSON Object Mapper. Hope it helps

EDIT:

public class NewSk {

    private String sk;
    private String vr;
    private String st;
    private String pr;

    public NewSk(String sk, String vr, String st, String pr) {
        this.sk = sk;
        this.vr = vr;
        this.st = st;
        this.pr = pr;
    }

    public String getSk() {
        return sk;
    }

    public void setSk(String sk) {
        this.sk = sk;
    }

    public String getVr() {
        return vr;
    }

    public void setVr(String vr) {
        this.vr = vr;
    }

    public String getSt() {
        return st;
    }

    public void setSt(String st) {
        this.st = st;
    }

    public String getPr() {
        return pr;
    }

    public void setPr(String pr) {
        this.pr = pr;
    }

}