Java Code Examples for tech.tablesaw.api.Table#where()

public static void main(String[] args) throws IOException {

    Table wines = Table.read().csv("../data/test_wines.csv");

    Table champagne =
        wines.where(
            wines
                .stringColumn("wine type")
                .isEqualTo("Champagne & Sparkling")
                .and(wines.stringColumn("region").isEqualTo("California")));

    Figure figure =
        BubblePlot.create(
            "Average retail price for champagnes by year and rating",
            champagne, // table name
            "highest pro score", // x variable column name
            "year", // y variable column name
            "Mean Retail" // bubble size
            );

    Plot.show(figure);
  }
 

public static void main(String[] args) throws Exception {
  Table tornadoes = Table.read().csv("../data/tornadoes_1950-2014.csv");
  tornadoes = tornadoes.where(tornadoes.nCol("Start lat").isGreaterThan(20));
  NumericColumn<?> x = tornadoes.nCol("Start lon");
  NumericColumn<?> y = tornadoes.nCol("Start lat");
  Layout layout =
      Layout.builder()
          .title("tornado start points")
          .height(600)
          .width(800)
          .yAxis(Axis.builder().range(20, 60).build())
          .build();
  Trace trace =
      ScatterTrace.builder(x, y).marker(Marker.builder().size(1).build()).name("lat/lon").build();
  Plot.show(new Figure(layout, trace));
}
 

public static void main(String[] args) throws IOException {

    Table wines = Table.read().csv("../data/test_wines.csv");

    Table champagne =
        wines.where(
            wines
                .stringColumn("wine type")
                .isEqualTo("Champagne & Sparkling")
                .and(wines.stringColumn("region").isEqualTo("California")));

    Figure figure =
        BubblePlot.create(
            "Average retail price for champagnes by year and rating",
            champagne, // table name
            "highest pro score", // x variable column name
            "year", // y variable column name
            "Mean Retail" // bubble size
            );

    Plot.show(figure);
  }
 

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

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

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

public static void main(String[] args) throws Exception {
  Table baseball = Table.read().csv("../data/baseball.csv");
  Table nl = baseball.where(baseball.stringColumn("league").isEqualTo("NL"));
  Table al = baseball.where(baseball.stringColumn("league").isEqualTo("AL"));
  Plot.show(
      TukeyMeanDifferencePlot.create(
          "Wins NL vs AL",
          "wins",
          nl.intColumn("W").asDoubleArray(),
          al.intColumn("W").asDoubleArray()));

  // example with difference sized arrays;
  double[] first = new NormalDistribution().sample(100);
  double[] second = new NormalDistribution().sample(200);
  Plot.show(
      TukeyMeanDifferencePlot.create(
          "Test of different sized arrays", "random data", first, second));
}
 

private void filtering() {
  Table table = Table.create("table");
  try {
    // @@ filtering
    Table result =
        table.where(
            and(
                or(
                    t -> t.doubleColumn("nc1").isGreaterThan(4),
                    t -> t.doubleColumn("nc1").isNegative()
                    ),
                not(t -> t.doubleColumn("nc2").isLessThanOrEqualTo(5))));
    // @@ filtering
  } catch (Exception e) {
  }
}
 

private void filtering() {
  Table table = Table.create("table");
  try {
    // @@ filtering
    Table result =
        table.where(
            and(
                or(
                    t -> t.doubleColumn("nc1").isGreaterThan(4),
                    t -> t.doubleColumn("nc1").isNegative()
                    ),
                not(t -> t.doubleColumn("nc2").isLessThanOrEqualTo(5))));
    // @@ filtering
  } catch (Exception e) {
  }
}
 

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

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

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

@Test
public void testBefore() {
  Table t = Table.create("test");
  t.addColumns(column1);
  column1.appendCell("05:15:30");
  column1.appendCell("10:15:30");
  Table result = t.where(t.timeColumn("Game time").isBefore(LocalTime.of(7, 4, 2, 0)));
  assertEquals(1, result.rowCount());
  assertEquals(LocalTime.of(5, 15, 30), column1.get(0));
}
 

@Test
public void testBefore() {
  Table t = Table.create("test");
  t.addColumns(column1);
  column1.appendCell("05:15:30");
  column1.appendCell("10:15:30");
  Table result = t.where(t.timeColumn("Game time").isBefore(LocalTime.of(7, 4, 2, 0)));
  assertEquals(1, result.rowCount());
  assertEquals(LocalTime.of(5, 15, 30), column1.get(0));
}
 

public static void main(String[] args) throws Exception {
  Table table = Table.read().csv("../data/tornadoes_1950-2014.csv");
  table = table.where(table.numberColumn("Fatalities").isGreaterThan(3));
  Table t2 = table.summarize("fatalities", sum).by("State");

  t2 = t2.sortDescendingOn(t2.column(1).name());
  Layout layout = Layout.builder().title("Tornado Fatalities by State").build();
  BarTrace trace = BarTrace.builder(t2.categoricalColumn(0), t2.numberColumn(1)).build();
  Plot.show(new Figure(layout, trace));
}
 

@Test
public void testEqual() {
  Table t = Table.create("test");
  t.addColumns(column1);
  fillColumn();
  Table result = t.where(t.timeColumn("Game time").isEqualTo(LocalTime.of(7, 4, 2, 0)));
  assertEquals(result.rowCount(), 1);
  assertEquals(result.getUnformatted(0, 0), toShortTimeString(pack(LocalTime.of(7, 4, 2))));
}
 

@Test
void testExecution() throws Exception {
  Table table = Table.read().csv("../data/bush.csv");
  BooleanColumn b =
      BooleanColumn.create(
              "test", new BitmapBackedSelection().addRange(0, table.rowCount()), table.rowCount())
          .setName("b");
  assertTrue(b.get(0));
  table.addColumns(b);
  Table t = table.where(booleanColumn("b").isTrue());
  assertEquals(table.rowCount(), t.rowCount());

  t = table.where(stringColumn("who").isNotEqualTo("fox"));
  assertNotEquals("fox", t.stringColumn("who").get(10));

  t = table.where(num("approval").isLessThan(55));
  assertTrue(t.intColumn("approval").get(10) < 55);

  t = table.where(date("date").isInApril());
  assertEquals(4, t.dateColumn("date").get(10).getMonthValue());

  t = table.where(not(dateColumn("date").isInApril()));
  assertFalse(t.dateColumn("date").monthValue().contains(4));

  t = table.where(date("date").isInApril());
  assertEquals(4, t.dateColumn("date").get(10).getMonthValue());
}
 

public static void main(String[] args) throws Exception {
  Table table = Table.read().csv("../data/tornadoes_1950-2014.csv");
  table = table.where(table.numberColumn("Fatalities").isGreaterThan(3));
  Table t2 = table.summarize("fatalities", sum).by("State");

  t2 = t2.sortDescendingOn(t2.column(1).name());
  Layout layout = Layout.builder().title("Tornado Fatalities by State").build();
  BarTrace trace = BarTrace.builder(t2.categoricalColumn(0), t2.numberColumn(1)).build();
  Plot.show(new Figure(layout, trace));
}
 

@Test
public void testOnOrAfter() {
  Table t = Table.create("test");
  t.addColumns(column1);
  fillColumn();
  Table result = t.where(t.timeColumn("Game time").isOnOrAfter(LocalTime.of(7, 4, 2, 0)));
  assertEquals(2, result.rowCount());
}
 

@Test
public void testOnOrBefore() {
  Table t = Table.create("test");
  t.addColumns(column1);
  fillColumn();
  Table result = t.where(t.timeColumn("Game time").isOnOrBefore(LocalTime.of(7, 4, 2, 0)));
  assertEquals(1, result.rowCount());
  assertEquals(result.getUnformatted(0, 0), toShortTimeString(pack(LocalTime.of(7, 4, 2))));
}
 

public static void main(String[] args) throws Exception {

    Table tornadoes = Table.read().csv("../data/tornadoes_1950-2014.csv");
    out(tornadoes.structure());
    tornadoes.setName("tornadoes");

    // filter out a bad data point
    tornadoes = tornadoes.where(tornadoes.numberColumn("Start Lat").isGreaterThan(20f));

    IntColumn scale = tornadoes.intColumn("scale");
    scale.set(scale.isEqualTo(-9), IntColumnType.missingValueIndicator());

    Table fatalities1 = tornadoes.summarize("fatalities", sum).by("scale");

    Plot.show(
        HorizontalBarPlot.create(
            "Total fatalities by scale", fatalities1, "scale", "sum [fatalities]"));

    Plot.show(
        PiePlot.create("Total fatalities by scale", fatalities1, "scale", "sum [fatalities]"));

    Table fatalities2 = tornadoes.summarize("fatalities", sum).by("state");

    Plot.show(
        ParetoPlot.createVertical(
            "Total Tornado Fatalities by State", fatalities2, "state", "sum [fatalities]"));

    Table injuries1 = tornadoes.summarize("injuries", mean).by("scale");
    Plot.show(
        HorizontalBarPlot.create(
            "Tornado Injuries by Scale", injuries1, "scale", "mean [injuries]"));
    out(injuries1);
  }
 

public static void main(String[] args) throws Exception {

    out("");
    out("Some Examples: ");

    // Read the CSV file
    ColumnType[] types = {
      ColumnType.DOUBLE, ColumnType.STRING, ColumnType.STRING, ColumnType.DOUBLE, ColumnType.DOUBLE
    };
    Table table =
        Table.read().csv(CsvReadOptions.builder("../data/bus_stop_test.csv").columnTypes(types));

    // Look at the column names
    out(table.columnNames());

    // Peak at the data
    out(table.first(5));

    // Remove the description column
    table.removeColumns("stop_desc");

    // Check the column names to see that it's gone
    out(table.columnNames());

    // Take a look at some data
    out("In 'examples. Printing first(5)");
    out(table.first(5));

    // Lets take a look at the latitude and longitude columns
    // out(table.realColumn("stop_lat").rowSummary().out());
    out(table.numberColumn("stop_lat").summary());

    // Now lets fill a column based on data in the existing columns

    // Apply the map function and fill the resulting column to the original table

    // Lets filtering out some of the rows. We're only interested in records with IDs between
    // 524-624

    Table filtered = table.where(table.numberColumn("stop_id").isBetweenInclusive(524, 624));
    out(filtered.first(5));

    // Write out the new CSV file
    filtered.write().csv("../data/filtered_bus_stops.csv");
  }
 

public static void main(String[] args) throws Exception {

    Table wines = Table.read().csv("../data/test_wines.csv");
    out(wines);
    out(wines.structure().printAll());
    out(wines.column("varietal").unique().print());
    out(wines.column("region").unique().print());
    out(wines.column("wine type").unique().print());

    wines.column("year").setName("vintage");

    Table champagne =
        wines.where(
            wines
                .stringColumn("wine type")
                .isEqualTo("Champagne & Sparkling")
                .and(wines.stringColumn("region").isEqualTo("California")));

    Plot.show(
        ScatterPlot.create("Champagne prices by vintage", champagne, "mean retail", "vintage"));

    Plot.show(
        BubblePlot.create(
            "Average retail price for champagnes by vintage and rating",
            champagne,
            "highest pro score",
            "vintage",
            "Mean Retail"));

    Plot.show(
        BubblePlot.create(
            "Average retail price for champagnes by vintage and rating",
            champagne,
            "highest pro score",
            "vintage",
            "Mean Retail",
            "appellation"));

    Plot.show(
        Scatter3DPlot.create(
            "Average retail price for champagnes by vintage and rating",
            champagne,
            "highest pro score",
            "vintage",
            "Mean Retail",
            "appellation"));

    Plot.show(
        Scatter3DPlot.create(
            "Highest & lowest retail price for champagnes by vintage and rating",
            champagne,
            "vintage",
            "highest retail",
            "lowest retail",
            "highest pro score",
            "appellation"));

    Plot.show(
        Scatter3DPlot.create(
            "Average retail price for champagnes by vintage and rating",
            champagne, // table
            "highest pro score", // x
            "vintage", // y
            "Mean Retail")); // z

    Plot.show(
        ScatterPlot.create(
            "Wine prices and ratings", wines, "Mean Retail", "highest pro score", "wine type"));

    Plot.show(
        Scatter3DPlot.create(
            "Champagne (prices, ratings, vintage, appellation) ",
            champagne,
            "vintage",
            "highest pro score",
            "mean retail",
            "appellation"));
  }
 

public static void main(String[] args) throws Exception {
  Table bush = Table.read().csv("../data/bush.csv");
  Table foxOnly = bush.where(bush.stringColumn("who").equalsIgnoreCase("fox"));
  Figure foxPlot =
      TimeSeriesPlot.create("George W. Bush approval ratings", foxOnly, "date", "approval");
  Plot.show(foxPlot);

  Plot.show(
      TimeSeriesPlot.create("George W. Bush approval ratings", bush, "date", "approval", "who"));

  Table robberies = Table.read().csv("../data/boston-robberies.csv");
  Plot.show(
      LinePlot.create(
          "Boston Robberies by month: Jan 1966-Oct 1975", robberies, "Record", "Robberies"));

  Layout layout =
      Layout.builder("Boston Robberies by month: Jan 1966-Oct 1975", "year", "robberies").build();

  ScatterTrace trace =
      ScatterTrace.builder(robberies.numberColumn("Record"), robberies.numberColumn("Robberies"))
          .mode(ScatterTrace.Mode.LINE)
          .marker(Marker.builder().color("#3D9970").build())
          .fill(ScatterTrace.Fill.TO_NEXT_Y)
          .build();
  Plot.show(new Figure(layout, trace));

  Table priceTable = Table.read().csv("../data/ohlcdata.csv");
  priceTable.numberColumn("Volume").setPrintFormatter(NumberColumnFormatter.intsWithGrouping());
  Plot.show(OHLCPlot.create("Prices", priceTable, "date", "open", "high", "low", "close"));
  Plot.show(CandlestickPlot.create("Prices", priceTable, "date", "open", "high", "low", "close"));

  // using a datetime column
  Table dateTable = Table.read().csv("../data/dateTimeTestFile.csv");
  Plot.show(
      TimeSeriesPlot.create(
          "Value over time",
          "time",
          dateTable.dateTimeColumn("Time"),
          "values",
          dateTable.numberColumn("Value")));

  // using a datetime column2
  Plot.show(TimeSeriesPlot.createDateTimeSeries("Value over time", dateTable, "Time", "Value"));
}