tech.tablesaw.api.Table Java Examples

@Test
public void innerJoinAnimalPeopleTreeOnAgeHome() {
  Table table1 = createANIMALHOMES();
  Table table2 = createDOUBLEINDEXEDPEOPLENameHomeAgeMoveInDate();
  Table table3 = createTREE();
  Table table4 = createFLOWER();
  Table joined = table1.joinOn("Age", "Home").inner(true, table2, table3, table4);
  assert (joined
      .columnNames()
      .containsAll(
          Arrays.asList(
              "Animal",
              "Name",
              "Home",
              "Age",
              "MoveInDate",
              "ID",
              "T2.Name",
              "T2.MoveInDate",
              "T3.Name",
              "T4.Name",
              "Color")));
  assertEquals(11, joined.columnCount());
  assertEquals(2, joined.rowCount());
}
 

@Test
void transposeWithMissingData() {
  Table testTable =
      Table.create(
          TABLE_NAME,
          DoubleColumn.create("value1", 1.0, DoubleColumnType.missingValueIndicator(), 1.2),
          DoubleColumn.create("value2", 2.0, 2.1, 2.2));
  Table result = testTable.transpose();

  assertTableEquals(
      Table.create(
          TABLE_NAME,
          DoubleColumn.create("0", 1.0, 2.0),
          DoubleColumn.create("1", DoubleColumnType.missingValueIndicator(), 2.1),
          DoubleColumn.create("2", 1.2, 2.2)),
      result);
}
 

@SuppressWarnings({"rawtypes", "unchecked"})
private void withMissingLeftJoin(
    Table destination, Table table1, Selection table1Rows, Set<Integer> ignoreColumns) {
  for (int c = 0; c < destination.columnCount(); c++) {
    if (ignoreColumns.contains(c)) {
      continue;
    }
    if (c < table1.columnCount()) {
      Column t1Col = table1.column(c);
      for (int index : table1Rows) {
        destination.column(c).append(t1Col, index);
      }
    } else {
      for (int r1 = 0; r1 < table1Rows.size(); r1++) {
        destination.column(c).appendMissing();
      }
    }
  }
}
 

private static Table createBOAT10() {
  return Table.read()
      .csv(
          Joiner.on(System.lineSeparator())
              .join(
                  "Type,Bedrooms,SoldDate,Owner",
                  "Yacht,2,1970-02-03,Jones",
                  "Dinghy,0,1988-12-12,White",
                  "HouseBoat,3,1981-04-21,Smith",
                  "Contemporary,5,1980-05-17,White",
                  "Paddleboat,3,1981-04-21,Smith",
                  "Rowboat,5,1980-05-17,White",
                  "Sailboat,4,1980-05-17,Black",
                  "Cruise,200,1989-01-23,Brown"),
          "Boat10");
}
 

private static Table createSTUDENT() {
  return Table.read()
      .csv(
          Joiner.on(System.lineSeparator())
              .join(
                  "ID,FirstName,LastName,City,State,Age,USID,GradYear",
                  "1,Bob,Barney,Burke,VA,20,11122,2019",
                  "2,Chris,Cabello,Canyonville,OR,20,22224,2019",
                  "3,Dan,Dirble,Denver,CO,21,33335,2020",
                  "4,Edward,Earhardt,Easterly,WA,21,44339,2021",
                  "5,Frank,Farnsworth,Fredriksburg,VA,22,55338,2019",
                  "6,George,Gabral,Garrisburg,MD,22,66337,2020",
                  "7,Michael,Marbury,Milton,NH,23,77330,2020",
                  "8,Robert,Riley,Roseburg,OR,23,88836,2020",
                  "9,Bob,Earhardt,Milton,NH,24,93333,2019",
                  "10,Dan,Gabral,Easterly,WA,24,13333,2020"),
          "Student");
}
 

@BeforeEach
public void setUp() throws Exception {

  table =
      Table.read()
          .csv(
              CsvReadOptions.builder("../data/bus_stop_test.csv")
                  // explicitly set column type, due to CsvReader#detectType returns
                  // ColumnType.FLOAT
                  // for 'stop_lat' and 'stop_lon' columns
                  .columnTypes(
                      new ColumnType[] {
                        ColumnType.DOUBLE,
                        ColumnType.STRING,
                        ColumnType.STRING,
                        ColumnType.DOUBLE,
                        ColumnType.DOUBLE
                      }));
  index = new DoubleIndex(table.doubleColumn("stop_lat"));
}
 

public static Table build(
    List<String> columnNames, List<String[]> dataRows, ReadOptions options) {
  Table table = Table.create(options.tableName());

  if (dataRows.isEmpty()) {
    return table;
  }

  ColumnTypeDetector detector = new ColumnTypeDetector(options.columnTypesToDetect());
  Iterator<String[]> iterator = dataRows.iterator();
  ColumnType[] types = detector.detectColumnTypes(iterator, options);
  for (int i = 0; i < columnNames.size(); i++) {
    table.addColumns(types[i].create(columnNames.get(i)));
  }

  for (int i = 0; i < dataRows.size(); i++) {
    for (int j = 0; j < table.columnCount(); j++) {
      table.column(j).appendCell(dataRows.get(i)[j]);
    }
  }

  return table;
}
 

@Test
void transposeCanBeFullyReversible() {
  Table testTable =
      Table.create(
          TABLE_NAME,
          StringColumn.create("label", "row1", "row2", "row3"),
          DoubleColumn.create("value1", 1.0, 1.1, 1.2),
          DoubleColumn.create("value2", 2.0, 2.1, 2.2));
  Table result = testTable.transpose(true, true);

  assertTableEquals(
      Table.create(
          TABLE_NAME,
          StringColumn.create("label", "value1", "value2"),
          DoubleColumn.create("row1", 1.0, 2.0),
          DoubleColumn.create("row2", 1.1, 2.1),
          DoubleColumn.create("row3", 1.2, 2.2)),
      result);

  assertEquals(
      testTable.print(), result.transpose(true, true).print(), "Transpose is reversible");
}
 

private static Table createCLASS() {
  return Table.read()
      .csv(
          Joiner.on(System.lineSeparator())
              .join(
                  "ID,ClassType,Name,Level,Description,StartDate,EndDate,Completed,Age",
                  "1001,Math,Calculus,101,Newton math,2018-09-20,2018-12-17,false,16",
                  "1002,Math,Calculus,102,Newton math,2019-01-06,2019-03-06,false,17",
                  "1003,Math,Calculus,103,Newton math,2019-03-10,2019-06-17,false,18",
                  "1004,Writing,Composition,101,Writing papers,2018-09-20,2018-12-17,false,19",
                  "1005,Writing,Composition,102,Writing papers,2019-01-06,2019-03-07,false,20",
                  "1006,Software,Programming,101,Programming basics,2018-09-22,2018-12-15,false,21",
                  "1007,Software,Programming,102,Programming basics,2019-01-05,2019-03-07,false,22",
                  "1008,Economics,Microeconomics,101,Basic micro economics,2018-09-20,2018-12-17,false,23",
                  "1009,Economics,Microeconomics,102,Basic micro economics,2018-01-05,2019-03-07,false,24",
                  "1010,Literature,Shakespeare,101,Understanding Shakespeare,2018-09-20,2018-12-17,false,25"),
          "Class");
}
 

@Test
public void testMissingValues() {
  String destinationColumnName = "dest";
  Table table =
      Table.create(
          "table",
          DoubleColumn.create(
              "col1", new double[] {1, 1, 1, Double.NaN, Double.NaN, Double.NaN, 1, 1, 1}));

  AnalyticQuery query =
      AnalyticQuery.quickQuery()
          .from(table)
          .rowsBetween()
          .preceding(1)
          .andCurrentRow()
          .sum("col1")
          .as(destinationColumnName)
          .build();

  AnalyticQueryEngine queryEngine = AnalyticQueryEngine.create(query);
  Table result = queryEngine.execute();

  double[] expected = new double[] {1, 2, 2, 1, Double.NaN, Double.NaN, 1, 2, 2};
  double[] actual = result.doubleColumn(destinationColumnName).asDoubleArray();
  assertArrayEquals(expected, actual);
}
 

@Test
public void testReadFailure2() throws IOException {
  Table table1 =
      Table.read()
          .csv(CsvReadOptions.builder("../data/read_failure_test2.csv").minimizeColumnSizes());
  table1.structure(); // just make sure the import completed
  ShortColumn test = table1.shortColumn("Test");

  // TODO(lwhite): Better tests
  assertNotNull(test.summary());
}
 

private List<Table> readN(String name, int expectedCount) {
  try {
    String fileName = name + ".xlsx";
    List<Table> tables =
        new XlsxReader().readMultiple(XlsxReadOptions.builder("../data/" + fileName).build());
    assertNotNull(tables, "No tables read from " + fileName);
    assertEquals(expectedCount, tables.size(), "Wrong number of tables in " + fileName);
    return tables;
  } catch (final IOException e) {
    fail(e.getMessage());
  }
  return null;
}
 

@Test
void test2ColumnGroupMean() {
  StringColumn byColumn1 = table.stringColumn("who");
  DateColumn byColumn2 = table.dateColumn("date");
  Table result = table.summarize("approval", mean, sum).by(byColumn1, byColumn2);
  assertEquals(4, result.columnCount());
  assertEquals("who", result.column(0).name());
  assertEquals(323, result.rowCount());
  assertEquals(
      "46.0",
      result
          .where(
              and(str("who").isEqualTo("fox"), date("date").isEqualTo(LocalDate.of(2001, 1, 24))))
          .getUnformatted(0, 2));
}
 

private static Table createBEDANDBREAKFAST() {
  return Table.read()
      .csv(
          Joiner.on(System.lineSeparator())
              .join(
                  "Design,Bedrooms,SoldDate,Owner",
                  "Colonial,5,1980-05-17,Smith",
                  "Gambrel,4,1982-11-18,Jones",
                  "Contemporary,5,1980-03-24,White",
                  "Split,2,1970-09-30,Brown"),
          "BedAndBreakfast");
}
 

public static Figure create(
    String title,
    Table table,
    String xCol,
    String yCol,
    String zCol,
    String sizeColumn,
    String groupCol) {

  TableSliceGroup tables = table.splitOn(table.categoricalColumn(groupCol));

  Layout layout = standardLayout(title, xCol, yCol, zCol, false);

  Scatter3DTrace[] traces = new Scatter3DTrace[tables.size()];
  for (int i = 0; i < tables.size(); i++) {

    List<Table> tableList = tables.asTableList();
    Marker marker =
        Marker.builder()
            .size(tableList.get(i).numberColumn(sizeColumn))
            // .opacity(.75)
            .build();

    traces[i] =
        Scatter3DTrace.builder(
                tableList.get(i).numberColumn(xCol),
                tableList.get(i).numberColumn(yCol),
                tableList.get(i).numberColumn(zCol))
            .marker(marker)
            .showLegend(true)
            .name(tableList.get(i).name())
            .build();
  }
  return new Figure(layout, traces);
}
 

@Test
void testWithInstant() throws IOException {

  Table dateTable = Table.read().csv("../data/dateTimeTestFile.csv");
  dateTable.addColumns(dateTable.dateTimeColumn(0).asInstantColumn().setName("Instant"));
  Figure figure =
      TimeSeriesPlot.create(
          "Value over time",
          "time",
          dateTable.instantColumn("Instant"),
          "values",
          dateTable.numberColumn("Value"));
  assertNotNull(figure);
}
 

@Test
public void innerJoinWithDoubleBirdsCatsFishDouble() {
  Table joined =
      DOUBLE_INDEXED_BIRDS.joinOn("ID").inner(DOUBLE_INDEXED_CATS, DOUBLE_INDEXED_FISH);
  assertEquals(4, joined.columnCount());
  assertEquals(1, joined.rowCount());
}
 

@Test
public void summaryMixedTypes() {
  Table testTable =
      Table.create(
          "Data",
          StringColumn.create("label", "yellow", "yellow", "green"),
          DoubleColumn.create("value1", 1.0, 1.1, 1.2),
          BooleanColumn.create("truthy", true, false, true),
          DateColumn.create(
              "dates",
              new LocalDate[] {
                LocalDate.of(2001, 1, 1), LocalDate.of(2002, 1, 1), LocalDate.of(2001, 1, 1)
              }));
  Table result = testTable.summary();
  assertEquals(
      "                                   Data                                    \n"
          + "  Summary   |  label   |         value1         |  truthy  |    dates     |\n"
          + "---------------------------------------------------------------------------\n"
          + "     Count  |       3  |                     3  |          |           3  |\n"
          + "    Unique  |       2  |                        |          |              |\n"
          + "       Top  |  yellow  |                        |          |              |\n"
          + " Top Freq.  |       2  |                        |          |              |\n"
          + "       sum  |          |                   3.3  |          |              |\n"
          + "      Mean  |          |                   1.1  |          |              |\n"
          + "       Min  |          |                     1  |          |              |\n"
          + "       Max  |          |                   1.2  |          |              |\n"
          + "     Range  |          |   0.19999999999999996  |          |              |\n"
          + "  Variance  |          |  0.009999999999999995  |          |              |\n"
          + "  Std. Dev  |          |   0.09999999999999998  |          |              |\n"
          + "     false  |          |                        |       1  |              |\n"
          + "      true  |          |                        |       2  |              |\n"
          + "   Missing  |          |                        |          |           0  |\n"
          + "  Earliest  |          |                        |          |  2001-01-01  |\n"
          + "    Latest  |          |                        |          |  2002-01-01  |",
      result.print());
}
 

public static void main(String[] args) throws Exception {
  Table baseball = Table.read().csv("../data/baseball.csv");
  NumericColumn<?> x = baseball.nCol("BA");
  NumericColumn<?> y = baseball.nCol("W");
  NumericColumn<?> y2 = baseball.nCol("SLG");

  Layout layout =
      Layout.builder()
          .title("Wins vs BA and SLG")
          .xAxis(Axis.builder().title("Batting Average").build())
          .yAxis(Axis.builder().title("Wins").build())
          .yAxis2(
              Axis.builder()
                  .title("SLG")
                  .side(Axis.Side.right)
                  .overlaying(ScatterTrace.YAxis.Y)
                  .build())
          .build();

  Trace trace =
      ScatterTrace.builder(x, y)
          .name("Batting avg.")
          .marker(Marker.builder().opacity(.7).color("#01FF70").build())
          .build();

  Trace trace2 =
      ScatterTrace.builder(x, y2)
          .yAxis(ScatterTrace.YAxis.Y2)
          .name("Slugging pct.")
          .marker(Marker.builder().opacity(.7).color("rgb(17, 157, 255)").build())
          .build();

  Figure figure = new Figure(layout, trace2, trace);
  Plot.show(figure);
}
 

/**
 * Full outer join to the given tables assuming that they have a column of the name we're joining
 * on
 *
 * @param allowDuplicateColumnNames if {@code false} the join will fail if any columns other than
 *     the join column have the same name if {@code true} the join will succeed and duplicate
 *     columns are renamed*
 * @param tables The tables to join with
 * @return The resulting table
 */
public Table fullOuter(boolean allowDuplicateColumnNames, Table... tables) {
  Table joined = table;

  for (Table currT : tables) {
    joined =
        joinInternal(
            joined, currT, JoinType.FULL_OUTER, allowDuplicateColumnNames, joinColumnNames);
  }
  return joined;
}
 

/** Read from a file input stream while performing column type inference */
@Test
public void testLoadFromFileStream() throws IOException {
  String location = "../data/bush.csv";
  Table table;
  File file = Paths.get(location).toFile();
  try (InputStream input = new FileInputStream(file)) {
    table = Table.read().csv(CsvReadOptions.builder(input).tableName("Bush approval ratings"));
  }
  assertNotNull(table);
  assertEquals(3, table.columnCount());
}
 

/** Create a reverse index for a given column. */
private Index indexFor(Table table, int colIndex) {
  ColumnType type = table.column(colIndex).type();
  if (type instanceof DateColumnType) {
    return new IntIndex(table.dateColumn(colIndex));
  } else if (type instanceof DateTimeColumnType) {
    return new LongIndex(table.dateTimeColumn(colIndex));
  } else if (type instanceof InstantColumnType) {
    return new LongIndex(table.instantColumn(colIndex));
  } else if (type instanceof TimeColumnType) {
    return new IntIndex(table.timeColumn(colIndex));
  } else if (type instanceof StringColumnType || type instanceof TextColumnType) {
    return new StringIndex(table.stringColumn(colIndex));
  } else if (type instanceof IntColumnType) {
    return new IntIndex(table.intColumn(colIndex));
  } else if (type instanceof LongColumnType) {
    return new LongIndex(table.longColumn(colIndex));
  } else if (type instanceof ShortColumnType) {
    return new ShortIndex(table.shortColumn(colIndex));
  } else if (type instanceof BooleanColumnType) {
    return new ByteIndex(table.booleanColumn(colIndex));
  } else if (type instanceof DoubleColumnType) {
    return new DoubleIndex(table.doubleColumn(colIndex));
  } else if (type instanceof FloatColumnType) {
    return new FloatIndex(table.floatColumn(colIndex));
  }
  throw new IllegalArgumentException("Joining attempted on unsupported column type " + type);
}
 

/**
 * Associates the columns to be summarized with the functions that match their type. All valid
 * combinations are used
 *
 * @param group A table slice group
 * @return A table containing a row of summarized data for each group in the table slice group
 */
private Table summarize(TableSliceGroup group) {
  List<Table> results = new ArrayList<>();

  ArrayListMultimap<String, AggregateFunction<?, ?>> reductionMultimap =
      getAggregateFunctionMultimap();

  for (String name : reductionMultimap.keys()) {
    List<AggregateFunction<?, ?>> reductions = reductionMultimap.get(name);
    results.add(group.aggregate(name, reductions.toArray(new AggregateFunction<?, ?>[0])));
  }
  return combineTables(results);
}
 

public static Figure create(
    String title, Table table, String groupColName, String numberColName) {

  Layout layout = Layout.builder(title).build();

  PieTrace trace =
      PieTrace.builder(table.column(groupColName), table.numberColumn(numberColName))
          .showLegend(true)
          .build();
  return new Figure(layout, trace);
}
 

private static Table createFOOTBALLSCHEDULE() {
  return Table.read()
      .csv(
          Joiner.on(System.lineSeparator())
              .join(
                  "TeamName,PlayDate,PlayTime,Location,HomeGame",
                  "Lancers,2018-09-10,15:30,Springfield,true",
                  "Tigers,2018-09-12,15:00,Detroit,false",
                  "Patriots,2018-09-14,14:30,Boston,true",
                  "Ravens,2018-09-10,12:30,Baltimore,true"),
          "FootballSchedule");
}
 

@Test
public void rightJoinHouseBoatOnBedroomsOwner() {
  Table table1 = createHOUSE();
  Table table2 = createBOAT();
  Table joined =
      table1.joinOn("Bedrooms", "Owner").rightOuter(table2, new String[] {"Bedrooms", "Owner"});
  assertEquals(6, joined.columnCount());
  assertEquals(5, joined.rowCount());
}
 

public void write(Table table, HtmlWriteOptions options) throws IOException {
  ElementCreator elements = options.elementCreator();
  Element html = elements.create("table");
  html.appendChild(header(table.columns(), elements));

  Element tbody = elements.create("tbody");
  html.appendChild(tbody);
  for (int row = 0; row < table.rowCount(); row++) {
    tbody.appendChild(row(row, table, elements, options));
  }

  try (Writer writer = options.destination().createWriter()) {
    writer.write(html.toString());
  }
}
 

@Test
public void testShortRow() {
  assertThrows(
      AddCellToColumnException.class,
      () -> {
        Table.read().csv("../data/short_row.csv");
      });
}
 

/**
 * Returns the result of applying to the functions to all the values in the appropriate column
 * TODO add a test that uses a non numeric return type with apply
 */
@SuppressWarnings({"unchecked", "rawtypes"})
public Table apply() {

  if (groupColumnNames.length > 0) {
    TableSliceGroup group = StandardTableSliceGroup.create(temp, groupColumnNames);
    return summarize(group);
  } else {
    List<Table> results = new ArrayList<>();
    ArrayListMultimap<String, AggregateFunction<?, ?>> reductionMultimap =
        getAggregateFunctionMultimap();

    for (String name : reductionMultimap.keys()) {
      List<AggregateFunction<?, ?>> reductions = reductionMultimap.get(name);
      Table table = TableSliceGroup.summaryTableName(temp);
      for (AggregateFunction function : reductions) {
        Column column = temp.column(name);
        Object result = function.summarize(column);
        ColumnType type = function.returnType();
        Column newColumn =
            type.create(TableSliceGroup.aggregateColumnName(name, function.functionName()));
        if (result instanceof Number) {
          Number number = (Number) result;
          newColumn.append(number.doubleValue());
        } else {
          newColumn.append(result);
        }
        table.addColumns(newColumn);
      }
      results.add(table);
    }
    return (combineTables(results));
  }
}
 

@Test
public void asIntMatrixColArgs() {
  double[] array1 = {0, 1, 1};
  double[] array2 = {0, 1, 2};
  double[] array3 = {0, 1, 3};

  DoubleColumn c1 = DoubleColumn.create("1", array1);
  DoubleColumn c2 = DoubleColumn.create("2", array2);
  DoubleColumn c3 = DoubleColumn.create("3", array3);
  Table table = Table.create("test", c1, c2, c3);

  int[][] expected = {{0, 0}, {1, 1}, {1, 3}};
  int[][] results = table.as().intMatrix("1", "3");
  assertTrue(Arrays.deepEquals(expected, results));
}