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

/**
 * Returns an object capable of summarizing the given columns in the given sourceTable, by
 * applying the given functions
 */
public Summarizer(
    Table sourceTable,
    Column<?> column1,
    Column<?> column2,
    Column<?> column3,
    Column<?> column4,
    AggregateFunction<?, ?>... functions) {
  Preconditions.checkArgument(!sourceTable.isEmpty(), "The table to summarize is empty.");
  Table tempTable = Table.create(sourceTable.name());
  tempTable.addColumns(column1);
  tempTable.addColumns(column2);
  tempTable.addColumns(column3);
  tempTable.addColumns(column4);
  this.temp = tempTable;
  this.original = sourceTable;
  summarizedColumns.add(column1.name());
  summarizedColumns.add(column2.name());
  summarizedColumns.add(column3.name());
  summarizedColumns.add(column4.name());
  this.reductions = functions;
}
 

@Test
public void lag() {
  IntColumn n1 = IntColumn.indexColumn("index", 4, 0);
  Table t = Table.create("tst");
  t.addColumns(n1, n1.lag(-2));
  assertEquals(
      "            tst            "
          + LINE_END
          + " index  |  index lag(-2)  |"
          + LINE_END
          + "---------------------------"
          + LINE_END
          + "     0  |              2  |"
          + LINE_END
          + "     1  |              3  |"
          + LINE_END
          + "     2  |                 |"
          + LINE_END
          + "     3  |                 |",
      t.print());
}
 

/**
 * Returns an object capable of summarizing the given column2 in the given sourceTable, by
 * applying the given functions
 */
public Summarizer(
    Table sourceTable,
    Column<?> column1,
    Column<?> column2,
    Column<?> column3,
    AggregateFunction<?, ?>... functions) {
  Table tempTable = Table.create(sourceTable.name());
  tempTable.addColumns(column1);
  tempTable.addColumns(column2);
  tempTable.addColumns(column3);
  this.temp = tempTable;
  this.original = sourceTable;
  summarizedColumns.add(column1.name());
  summarizedColumns.add(column2.name());
  summarizedColumns.add(column3.name());
  this.reductions = functions;
}
 

/**
 * Returns an object capable of summarizing the given column2 in the given sourceTable, by
 * applying the given functions
 */
public Summarizer(
    Table sourceTable,
    Column<?> column1,
    Column<?> column2,
    Column<?> column3,
    AggregateFunction<?, ?>... functions) {
  Table tempTable = Table.create(sourceTable.name());
  tempTable.addColumns(column1);
  tempTable.addColumns(column2);
  tempTable.addColumns(column3);
  this.temp = tempTable;
  this.original = sourceTable;
  summarizedColumns.add(column1.name());
  summarizedColumns.add(column2.name());
  summarizedColumns.add(column3.name());
  this.reductions = functions;
}
 

/** */
@Override
public Table countByCategory(String columnName) {
  Table t = Table.create("Column: " + columnName);
  StringColumn categories = StringColumn.create("Category");
  IntColumn counts = IntColumn.create("Count");
  // Now uses the keyToCount map
  for (Map.Entry<Integer, Integer> entry : keyToCount.int2IntEntrySet()) {
    categories.append(getValueForKey(entry.getKey()));
    counts.append(entry.getValue());
  }
  t.addColumns(categories);
  t.addColumns(counts);
  return t;
}
 

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

    Table data =
        Table.read()
            .csv(
                CsvReadOptions.builderFromFile("../data/urb_cpop1_1_Data.csv")
                    .missingValueIndicator(":"));
    Table filtered = data.dropWhere(data.column("Value").isMissing());
    filtered.addColumns(
        (filtered.stringColumn("Cities").join(":", filtered.column("INDIC_UR"))).setName("key"));
    Table cities = filtered.pivot("key", "time", "value", AggregateFunctions.mean);

    // Top 10 cities by pop in 2017:
    System.out.println(
        cities
            .where(
                cities
                    .stringColumn("key")
                    .containsString("January")
                    .and(cities.stringColumn("key").containsString("total"))
                    .and(cities.nCol("2017").isNotMissing()))
            .sortDescendingOn("2017")
            .first(10));

    // Highest growth cities:
    cities.addColumns(
        (cities.nCol("2016").divide(cities.nCol("2010").subtract(1)).multiply(100))
            .setName("growth"));
    System.out.println(
        cities
            .where(
                cities
                    .stringColumn("key")
                    .containsString("January")
                    .and(cities.stringColumn("key").containsString("total"))
                    .and(cities.nCol("growth").isNotMissing()))
            .sortDescendingOn("growth")
            .first(10));
  }
 

/**
 * 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 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());
}
 

/** */
@Override
public Table countByCategory(String columnName) {
  Table t = Table.create("Column: " + columnName);
  StringColumn categories = StringColumn.create("Category");
  IntColumn counts = IntColumn.create("Count");
  // Now uses the keyToCount map
  for (Map.Entry<Short, Integer> entry : keyToCount.short2IntEntrySet()) {
    categories.append(getValueForKey(entry.getKey()));
    counts.append(entry.getValue());
  }
  t.addColumns(categories);
  t.addColumns(counts);
  return t;
}
 

/**
 * Returns an object capable of summarizing the given column in the given sourceTable, by applying
 * the given functions
 */
public Summarizer(Table sourceTable, Column<?> column, AggregateFunction<?, ?>... functions) {
  Table tempTable = Table.create(sourceTable.name());
  tempTable.addColumns(column);
  this.temp = tempTable;
  this.original = sourceTable;
  summarizedColumns.add(column.name());
  this.reductions = functions;
}
 

private static Table addFillerColumn(Table table, int numberColumnsToAdd, String prefix) {
  int[] filler = new int[table.rowCount()];
  Arrays.fill(filler, 1);
  IntColumn col = IntColumn.create("temp", filler);
  for (int i = 0; i < numberColumnsToAdd; i++) {
    table.addColumns(col.copy().setName(prefix + "_appendColumn" + i));
  }
  return table;
}
 

public Table asTable() {
  Table t = Table.create(name);
  StringColumn measure = StringColumn.create("Measure");
  DoubleColumn value = DoubleColumn.create("Value");
  t.addColumns(measure);
  t.addColumns(value);

  measure.append("Count");
  value.append(n);

  measure.append("sum");
  value.append(sum());

  measure.append("Mean");
  value.append(mean());

  measure.append("Min");
  value.append(min());

  measure.append("Max");
  value.append(max());

  measure.append("Range");
  value.append(range());

  measure.append("Variance");
  value.append(variance());

  measure.append("Std. Dev");
  value.append(standardDeviation());

  return t;
}
 

/**
 * Returns an object capable of summarizing the given column in the given sourceTable, by applying
 * the given functions
 */
public Summarizer(Table sourceTable, Column<?> column, AggregateFunction<?, ?>... functions) {
  Table tempTable = Table.create(sourceTable.name());
  tempTable.addColumns(column);
  this.temp = tempTable;
  this.original = sourceTable;
  summarizedColumns.add(column.name());
  this.reductions = functions;
}
 

private void crossTab() throws IOException {
  Table table = Table.read().csv("../data/bush.csv");
  StringColumn month = table.dateColumn("date").month();
  month.setName("month");
  table.addColumns(month);

  // @@ crosstab
  Table percents = table.xTabTablePercents("month", "who");
  // make table print as percents with no decimals instead of the raw doubles it holds
  percents.columnsOfType(ColumnType.DOUBLE)
      .forEach(x -> ((DoubleColumn)x).setPrintFormatter(NumberColumnFormatter.percent(0)));
  System.out.println(percents);
  // @@ crosstab
  outputWriter.write(percents, "crosstab");
}
 

@Test
public void testColumnPercents() throws Exception {
  Table bush = Table.read().csv("../data/bush.csv");
  bush.addColumns(bush.dateColumn("date").year());
  Table xtab = CrossTab.columnPercents(bush, "who", "date year");
  assertEquals(6, xtab.columnCount());
  assertEquals(1.0, xtab.doubleColumn(1).getDouble(xtab.rowCount() - 1), 0.00001);
}
 

@Test
public void testRowPercents() throws Exception {
  Table bush = Table.read().csv("../data/bush.csv");
  bush.addColumns(bush.dateColumn("date").year());
  Table xtab = CrossTab.rowPercents(bush, "who", "date year");
  assertEquals(1.0, xtab.doubleColumn(xtab.columnCount() - 1).getDouble(0), 0.00001);
}
 

@Test
public void testGetMonthValue() {
  LocalDateTime date = LocalDate.of(2015, 1, 25).atStartOfDay();
  Month[] months = Month.values();

  DateTimeColumn dateTimeColumn = DateTimeColumn.create("test");
  for (int i = 0; i < months.length; i++) {
    dateTimeColumn.append(date);
    date = date.plusMonths(1);
  }

  assertTrue(dateTimeColumn.isInJanuary().contains(0));
  assertTrue(dateTimeColumn.isInFebruary().contains(1));
  assertTrue(dateTimeColumn.isInMarch().contains(2));
  assertTrue(dateTimeColumn.isInApril().contains(3));
  assertTrue(dateTimeColumn.isInMay().contains(4));
  assertTrue(dateTimeColumn.isInJune().contains(5));
  assertTrue(dateTimeColumn.isInJuly().contains(6));
  assertTrue(dateTimeColumn.isInAugust().contains(7));
  assertTrue(dateTimeColumn.isInSeptember().contains(8));
  assertTrue(dateTimeColumn.isInOctober().contains(9));
  assertTrue(dateTimeColumn.isInNovember().contains(10));
  assertTrue(dateTimeColumn.isInDecember().contains(11));

  assertTrue(dateTimeColumn.isInQ1().contains(2));
  assertTrue(dateTimeColumn.isInQ2().contains(4));
  assertTrue(dateTimeColumn.isInQ3().contains(8));
  assertTrue(dateTimeColumn.isInQ4().contains(11));

  Table t = Table.create("Test");
  t.addColumns(dateTimeColumn);
  IntColumn index = IntColumn.indexColumn("index", t.rowCount(), 0);
  t.addColumns(index);
}
 

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

    Table table =
        Table.read()
            .csv(
                CsvReadOptions.builder("../data/urb_cpop1_1_Data.csv")
                    .missingValueIndicator(":")
                    .build());

    Table filtered = table.dropWhere(table.intColumn("value").isMissing());

    StringColumn key = filtered.stringColumn("CITIES").join(":", filtered.stringColumn("INDIC_UR"));
    key.setName("key");
    filtered.addColumns(key);

    Table finalTable = filtered.pivot("key", "TIME", "Value", mean);
    // sortDescendingOn puts N/A values first unfortunately, so let's remove them before determining
    // and printing.
    Table existing2017 = finalTable.dropWhere(finalTable.column("2017").isMissing());

    System.out.println(
        existing2017
            .where(existing2017.stringColumn("key").endsWith("January, total"))
            .sortDescendingOn("2017")
            .print(20));

    // Add growth column
    DoubleColumn growthColumn =
        finalTable
            .doubleColumn("2016")
            .divide(finalTable.doubleColumn("2010"))
            .subtract(1)
            .multiply(100); // .subtract(1).multiply(100));

    growthColumn.setName("growth");
    finalTable.addColumns(growthColumn);

    Table temp = finalTable.dropWhere(finalTable.column("growth").isMissing());
    Table highestGrowthTable =
        temp.where(temp.stringColumn("key").endsWith("January, total")).sortDescendingOn("growth");

    System.out.println(highestGrowthTable.print(20));
  }
 

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

    Table bush = Table.read().csv("../data/bush.csv");

    NumericColumn<?> x = bush.nCol("approval");
    CategoricalColumn<?> y = bush.stringColumn("who");

    Layout layout = Layout.builder().title("Approval ratings by agency").build();

    ScatterTrace trace = ScatterTrace.builder(x, y).mode(ScatterTrace.Mode.MARKERS).build();
    Plot.show(new Figure(layout, trace));

    // A more complex example involving two traces
    IntColumn year = bush.dateColumn("date").year();
    year.setName("year");
    bush.addColumns(year);
    bush.dropWhere(bush.intColumn("year").isIn((Number) 2001, (Number) 2002));
    Table summary = bush.summarize("approval", AggregateFunctions.mean).by("who", "year");

    Layout layout2 =
        Layout.builder()
            .title("Mean approval ratings by agency and year for 2001 and 2002")
            .build();

    Table year1 = summary.where(summary.intColumn("year").isEqualTo(2001));
    Table year2 = summary.where(summary.intColumn("year").isEqualTo(2002));
    ScatterTrace trace2 =
        ScatterTrace.builder(year1.nCol("Mean [approval]"), year1.stringColumn("who"))
            .name("2001")
            .mode(ScatterTrace.Mode.MARKERS)
            .marker(Marker.builder().symbol(Symbol.DIAMOND).color("red").size(10).build())
            .build();

    ScatterTrace trace3 =
        ScatterTrace.builder(year2.nCol("Mean [approval]"), year2.stringColumn("who"))
            .name("2002")
            .mode(ScatterTrace.Mode.MARKERS)
            .marker(Marker.builder().symbol(Symbol.STAR).size(10).color("blue").build())
            .build();

    Plot.show(new Figure(layout2, trace2, trace3));
  }
 

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

    Table table = Table.read().csv("../data/bush.csv");
    StringColumn month = table.dateColumn("date").month();
    table.addColumns(month.setName("month"));

    // two variable counts
    Table counts = table.xTabCounts("month", "who");

    // make table print as integers with no decimals instead of the raw doubles it holds
    counts
        .columnsOfType(ColumnType.DOUBLE)
        .forEach(x -> ((NumberColumn<?, ?>) x).setPrintFormatter(NumberColumnFormatter.ints()));

    // single variable counts
    Table whoCounts = table.xTabCounts("who");
    whoCounts
        .columnsOfType(ColumnType.DOUBLE)
        .forEach(x -> ((NumberColumn<?, ?>) x).setPrintFormatter(NumberColumnFormatter.ints()));

    // single variable percents
    Table whoPercents = table.xTabPercents("who");
    whoPercents
        .columnsOfType(ColumnType.DOUBLE)
        .forEach(x -> ((NumberColumn<?, ?>) x).setPrintFormatter(NumberColumnFormatter.percent(0)));

    // table percents
    Table tablePercents = table.xTabTablePercents("month", "who");
    tablePercents
        .columnsOfType(ColumnType.DOUBLE)
        .forEach(x -> ((NumberColumn<?, ?>) x).setPrintFormatter(NumberColumnFormatter.percent(1)));

    // column percents
    Table columnPercents = table.xTabColumnPercents("month", "who");
    columnPercents
        .columnsOfType(ColumnType.DOUBLE)
        .forEach(x -> ((NumberColumn<?, ?>) x).setPrintFormatter(NumberColumnFormatter.percent(0)));

    // row percents
    Table rowPercents = table.xTabRowPercents("month", "who");
    rowPercents
        .columnsOfType(ColumnType.DOUBLE)
        .forEach(x -> ((NumberColumn<?, ?>) x).setPrintFormatter(NumberColumnFormatter.percent(0)));
  }