Home create pivot table from csv file
Reply: 0

create pivot table from csv file

Gbenro 'Skenzy' Selere
1#
Gbenro 'Skenzy' Selere Published in 2018-02-14 04:52:30Z

I'm a c# guy and still learning as much, and I've been assigned on a Java REST Api project and I'm struggling with I assume should be a straightforward task.

I've been asked to create a method that takes a csv file from a post method and then returns a pivot table in json format back as the response

This is the sample table

ID,Quarter,Sales
1,1,400
2,1,450
3,2,500
4,2,550
5,3,600
6,3,650
7,4,700
8,4,750

And my sample output response should be:

ID, Quarter_1, Quarter_2, Quarter_3, Quarter_4
1, 850, 1050, 1250, 1450

The sample output should be the count of the sales for each quarter. So the pivot should be on Quarter and Sales Column. I was able to prepare the pivot in Excel, but its the translation of that approach to the program that is my challenge.

I have done research so far on java implementations of creating a pivot table but none seem to make any sense whatever. The closest working sample I found was from here: http://www.novixys.com/blog/java-pivot-table-using-streams/. I tried following it but the response was [].

I'm just seeking direction on how best to solve this problem.

I created this POJO class:

public class Report {

  private int Id;

  private int Quarter;

  private int Sales;

  public int getId() {
    return Id;
  }

  public void setId(int id) {
    Id = id;
  }

  public Report(int id, int quarter, int sales) {
    Id = id;
    Quarter = quarter;
    Sales = sales;
  }

  public Report() {
  }

  public int getQuarter() {
    return Quarter;
  }

  public void setQuarter(int quarter) {
    Quarter = quarter;
  }

  public int getSales() {
    return Sales;
  }

  public void setSales(int sales) {
    Sales = sales;
  }

  @Override
  public String toString() {
    return "{" +
      + Id + ","
      + Quarter + ","
      + Sales + "," +
      '}';
  }
}

and this is my POST method :

public String pivotCsv(@RequestBody String csv) throws IOException {

    Pattern pattern = Pattern.compile(",");

    String sReturn = null;

    try (BufferedReader buffer = new BufferedReader(new FileReader(csv));) {

      List<Report> quarterlyReports = buffer.lines().skip(1).map(line -> {
        String[] selectedData = pattern.split(line);
        return new Report(Integer.parseInt(selectedData[0]),
          Integer.parseInt(selectedData[1]),
          Integer.parseInt(selectedData[2]));
      }).collect(Collectors.toList());

      Map<List<String>, Integer> groupedReportsListMap = buffer.lines().skip(1).map(line -> {
        String[] selectedData = pattern.split(line);

        return new Report(Integer.parseInt(selectedData[0]),
          Integer.parseInt(selectedData[1]),
          Integer.parseInt(selectedData[2]));

      }) .collect(Collectors.groupingBy(x -> new ArrayList<String>
        (Arrays.asList(Integer.toString(x.getQuarter()))),
        Collectors.summingInt(Report::getSales)));


//
//      Table sample = new Table();
//      sample.headers = Arrays.asList("ID", "sales_q1", "sales_q2", "sales_q3", "sales_q4");
//      sample.data = new ArrayList<>();
//      for (int i = 0; i < quarterlyReports.size(); i++) {
//
//      }

//      buffer.close();

      return groupedReportsListMap.toString();

    } catch (IOException e) {
      throw e;
    }

  }
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.346333 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO