Repository: jtablesaw/tablesaw
Pull request #869 (2021-01-24 16:15:14)

Beta implementation of "Tidy" Melt and Cast operations
Thanks for contributing.

- [x] Tick to sign-off your agreement to the [Developer Certificate of Origin (DCO) 1.1](https://developercertificate.org)

## Description

What was changed

Added two operations to Table
Added getInternal() methods to Row for Boolean and Instant column types. 

## Testing

Did you add a unit test?
yes


sonarcloud[bot]: SonarCloud Quality Gate failed.

[<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/bug.png' alt='Bug' width='16' height='16' />](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=BUG) [<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/RatingBadge/A.png' alt='A' width='16' height='16' />](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=BUG) [0 Bugs](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=BUG)  
[<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/vulnerability.png' alt='Vulnerability' width='16' height='16' />](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=VULNERABILITY) [<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/RatingBadge/A.png' alt='A' width='16' height='16' />](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=VULNERABILITY) [0 Vulnerabilities](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=VULNERABILITY)  
[<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/security_hotspot.png' alt='Security Hotspot' width='16' height='16' />](https://sonarcloud.io/project/security_hotspots?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=SECURITY_HOTSPOT) [<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/RatingBadge/A.png' alt='A' width='16' height='16' />](https://sonarcloud.io/project/security_hotspots?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=SECURITY_HOTSPOT) [0 Security Hotspots](https://sonarcloud.io/project/security_hotspots?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=SECURITY_HOTSPOT)  
[<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/common/code_smell.png' alt='Code Smell' width='16' height='16' />](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=CODE_SMELL) [<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/RatingBadge/A.png' alt='A' width='16' height='16' />](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=CODE_SMELL) [5 Code Smells](https://sonarcloud.io/project/issues?id=jtablesaw_tablesaw&pullRequest=869&resolved=false&types=CODE_SMELL)

[<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/CoverageChart/60.png' alt='68.4%' width='16' height='16' />](https://sonarcloud.io/component_measures?id=jtablesaw_tablesaw&pullRequest=869&metric=new_coverage&view=list) [68.4% Coverage](https://sonarcloud.io/component_measures?id=jtablesaw_tablesaw&pullRequest=869&metric=new_coverage&view=list)  
[<img src='https://sonarsource.github.io/sonarcloud-github-static-resources/v2/checks/Duplications/3.png' alt='0.0%' width='16' height='16' />](https://sonarcloud.io/component_measures?id=jtablesaw_tablesaw&pullRequest=869&metric=new_duplicated_lines_density&view=list) [0.0% Duplication](https://sonarcloud.io/component_measures?id=jtablesaw_tablesaw&pullRequest=869&metric=new_duplicated_lines_density&view=list)



Patch:
diff --git a/core/src/main/java/tech/tablesaw/api/Row.java b/core/src/main/java/tech/tablesaw/api/Row.java
index 1ae75ff9..a5887e42 100644
--- a/core/src/main/java/tech/tablesaw/api/Row.java
+++ b/core/src/main/java/tech/tablesaw/api/Row.java
@@ -171,6 +171,16 @@ public class Row implements Iterator<Row> {
     return getBoolean(columnNames[columnIndex]);
   }
 
+  /** Returns an element from a Boolean column in its internal byte form, avoiding boxing */
+  public byte getBooleanAsByte(int columnIndex) {
+    return getBooleanAsByte(columnNames[columnIndex]);
+  }
+
+  /** Returns an element from a Boolean column in its internal byte form, avoiding boxing */
+  public byte getBooleanAsByte(String columnName) {
+    return booleanColumnMap.get(columnName).getByte(getIndex(rowNumber));
+  }
+
   public Boolean getBoolean(String columnName) {
     return booleanColumnMap.get(columnName).get(getIndex(rowNumber));
   }
@@ -247,6 +257,14 @@ public class Row implements Iterator<Row> {
     return dateColumnMap.get(columnNames[columnIndex]).getIntInternal(getIndex(rowNumber));
   }
 
+  public long getPackedInstant(int columnIndex) {
+    return instantColumnMap.get(columnNames[columnIndex]).getLongInternal(getIndex(rowNumber));
+  }
+
+  public long getPackedInstant(String columnName) {
+    return instantColumnMap.get(columnName).getLongInternal(getIndex(rowNumber));
+  }
+
   public long getPackedDateTime(String columnName) {
     return dateTimeColumnMap.get(columnName).getLongInternal(getIndex(rowNumber));
   }
diff --git a/core/src/main/java/tech/tablesaw/api/Table.java b/core/src/main/java/tech/tablesaw/api/Table.java
index cdcc668b..8dca191e 100644
--- a/core/src/main/java/tech/tablesaw/api/Table.java
+++ b/core/src/main/java/tech/tablesaw/api/Table.java
@@ -19,6 +19,7 @@ import static tech.tablesaw.aggregate.AggregateFunctions.countMissing;
 import static tech.tablesaw.api.QuerySupport.not;
 import static tech.tablesaw.selection.Selection.selectNRowsAtRandom;
 
+import com.google.common.annotations.Beta;
 import com.google.common.base.Preconditions;
 import com.google.common.collect.Streams;
 import com.google.common.primitives.Ints;
@@ -56,10 +57,7 @@ import tech.tablesaw.selection.Selection;
 import tech.tablesaw.sorting.Sort;
 import tech.tablesaw.sorting.SortUtils;
 import tech.tablesaw.sorting.comparators.IntComparatorChain;
-import tech.tablesaw.table.Relation;
-import tech.tablesaw.table.Rows;
-import tech.tablesaw.table.StandardTableSliceGroup;
-import tech.tablesaw.table.TableSliceGroup;
+import tech.tablesaw.table.*;
 
 /**
  * A table of data, consisting of some number of columns, each of which has the same number of rows.
@@ -82,6 +80,10 @@ public class Table extends Relation implements Iterable<Row> {
   /** The name of the table */
   private String name;
 
+  // standard column names for melt and cast operations
+  public static final String MELT_VARIABLE_COLUMN_NAME = "variable";
+  public static final String MELT_VALUE_COLUMN_NAME = "value";
+
   /** Returns a new table */
   private Table() {}
 
@@ -1310,6 +1312,221 @@ public class Table extends Relation implements Iterable<Row> {
     return transposed;
   }
 
+  /**
+   * Melt implements the 'tidy' melt operation as described in these papers by Hadley Wickham.
+   *
+   * <p>Tidy concepts: {@see https://www.jstatsoft.org/article/view/v059i10}
+   *
+   * <p>Cast function details: {@see https://www.jstatsoft.org/article/view/v021i12}
+   *
+   * <p>In short, melt turns columns into rows, but in a particular way. Used with the cast method,
+   * it can help make data tidy. In a tidy dataset, every variable is a column and every observation
+   * a row.
+   *
+   * <p>This method returns a table that contains all the data in this table, but organized such
+   * that there is a set of identifier variables (columns) and a single measured variable (column).
+   * For example, given a table with columns:
+   *
+   * <p>patient_id, gender, age, weight, temperature,
+   *
+   * <p>it returns a table with the columns:
+   *
+   * <p>patient_id, variable, value
+   *
+   * <p>In the new format, the strings age, weight, and temperature have become cells in the
+   * measurement table, such that a single row in the source table might look like this in the
+   * result table:
+   *
+   * <p>1234, gender, male 1234, age, 42 1234, weight, 186 1234, temperature, 97.4
+   *
+   * <p>This kind of structure often makes for a good intermediate format for performing subsequent
+   * transformations. It is especially useful when combined with the {@link #cast()} operation
+   *
+   * @param idVariables A list of column names intended to be used as identifiers. In he example,
+   *     only patient_id would be an identifier
+   * @param measuredVariables A list of columns intended to be used as measured variables. All
+   *     columns must have the same type
+   * @param dropMissing drop any row where the value is missing
+   */
+  @Beta
+  public Table melt(
+      List<String> idVariables, List<NumericColumn<?>> measuredVariables, Boolean dropMissing) {
+
+    Table result = Table.create(name);
+    for (String idColName : idVariables) {
+      result.addColumns(column(idColName).type().create(idColName));
+    }
+    result.addColumns(
+        StringColumn.create(MELT_VARIABLE_COLUMN_NAME),
+        DoubleColumn.create(MELT_VALUE_COLUMN_NAME));
+
+    List<String> measureColumnNames =
+        measuredVariables.stream().map(Column::name).collect(Collectors.toList());
+
+    TableSliceGroup slices = splitOn(idVariables.toArray(new String[0]));
+    for (TableSlice slice : slices) {
+      // TODO: This is inefficient, but it looks like there is a bug in row iteration on slices
+      Table temp = slice.asTable();
+
+      for (Row row : temp) {
+        for (String colName : measureColumnNames) {
+          if (!dropMissing || !row.isMissing(colName)) {
+            writeIdVariables(idVariables, result, row);
+            result.stringColumn(MELT_VARIABLE_COLUMN_NAME).append(colName);
+            result.doubleColumn(MELT_VALUE_COLUMN_NAME).append(row.getNumber(colName));
+          }
+        }
+      }
+    }
+
+    return result;
+  }
+
+  private void writeIdVariables(List<String> idVariables, Table result, Row row) {
+    for (String id : idVariables) {
+      Column<?> resultColumn = result.column(id);
+      final ColumnType columnType = resultColumn.type();
+      if (columnType.equals(ColumnType.STRING)) {
+        StringColumn sc = (StringColumn) resultColumn;
+        sc.append(row.getString(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.TEXT)) {
+        TextColumn sc = (TextColumn) resultColumn;
+        sc.append(row.getString(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.INTEGER)) {
+        IntColumn ic = (IntColumn) resultColumn;
+        ic.append(row.getInt(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.LONG)) {
+        LongColumn ic = (LongColumn) resultColumn;
+        ic.append(row.getLong(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.SHORT)) {
+        ShortColumn ic = (ShortColumn) resultColumn;
+        ic.append(row.getShort(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.LOCAL_DATE)) {
+        DateColumn ic = (DateColumn) resultColumn;
+        ic.appendInternal(row.getPackedDate(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.LOCAL_DATE_TIME)) {
+        DateTimeColumn ic = (DateTimeColumn) resultColumn;
+        ic.appendInternal(row.getPackedDateTime(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.LOCAL_TIME)) {
+        TimeColumn ic = (TimeColumn) resultColumn;
+        ic.appendInternal(row.getPackedTime(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.INSTANT)) {
+        InstantColumn ic = (InstantColumn) resultColumn;
+        ic.appendInternal(row.getPackedInstant(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.BOOLEAN)) {
+        BooleanColumn ic = (BooleanColumn) resultColumn;
+        ic.append(row.getBooleanAsByte(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.DOUBLE)) {
+        DoubleColumn ic = (DoubleColumn) resultColumn;
+        ic.append(row.getDouble(resultColumn.name()));
+      } else if (columnType.equals(ColumnType.FLOAT)) {
+        FloatColumn ic = (FloatColumn) resultColumn;
+        ic.append(row.getFloat(resultColumn.name()));
+      } else {
+        throw new IllegalArgumentException("melt() does not support column type " + columnType);
+      }
+    }
+  }
+
+  /**
+   * Cast implements the 'tidy' cast operation as described in these papers by Hadley Wickham:
+   *
+   * <p>Cast takes a table in 'molten' format, such as is produced by the {@link #melt(List, List,
+   * Boolean)} t} method, and returns a version in standard tidy format.
+   *
+   * <p>The molten table should have a StringColumn called "variable" and a column called "value"
+   * Every unique variable name will become a column in the output table.
+   *
+   * <p>All other columns in this table are considered identifier variable. Each combination of
+   * identifier variables specifies an observation, so there will be one row for each, with the
+   * other variables added.
+   *
+   * <p>Variable columns are returned in an arbitrary order. Use {@link #reorderColumns(String...)}
+   * if column order is important.
+   *
+   * <p>Tidy concepts: {@see https://www.jstatsoft.org/article/view/v059i10}
+   *
+   * <p>Cast function details: {@see https://www.jstatsoft.org/article/view/v021i12}
+   */
+  @Beta
+  public Table cast() {
+    StringColumn variableNames = stringColumn(MELT_VARIABLE_COLUMN_NAME);
+    List<Column<?>> idColumns =
+        columnList.stream()
+            .filter(
+                column ->
+                    !column.name().equals(MELT_VARIABLE_COLUMN_NAME)
+                        && !column.name().equals(MELT_VALUE_COLUMN_NAME))
+            .collect(toList());
+    Table result = Table.create(name);
+    for (Column<?> idColumn : idColumns) {
+      result.addColumns(idColumn.type().create(idColumn.name()));
+    }
+    StringColumn uniqueVariableNames = variableNames.unique();
+    for (String varName : uniqueVariableNames) {
+      result.addColumns(DoubleColumn.create(varName));
+    }
+    TableSliceGroup slices = splitOn(idColumns.stream().map(Column::name).toArray(String[]::new));
+    for (TableSlice slice : slices) {
+      Table sliceTable = slice.asTable();
+      for (Column<?> idColumn : idColumns) {
+        final ColumnType columnType = idColumn.type();
+        if (columnType.equals(ColumnType.STRING)) {
+          StringColumn source = (StringColumn) sliceTable.column(idColumn.name());
+          StringColumn dest = (StringColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.TEXT)) {
+          TextColumn source = (TextColumn) sliceTable.column(idColumn.name());
+          TextColumn dest = (TextColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.INTEGER)) {
+          IntColumn source = (IntColumn) sliceTable.column(idColumn.name());
+          IntColumn dest = (IntColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.LONG)) {
+          LongColumn source = (LongColumn) sliceTable.column(idColumn.name());
+          LongColumn dest = (LongColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.SHORT)) {
+          ShortColumn source = (ShortColumn) sliceTable.column(idColumn.name());
+          ShortColumn dest = (ShortColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.BOOLEAN)) {
+          BooleanColumn source = (BooleanColumn) sliceTable.column(idColumn.name());
+          BooleanColumn dest = (BooleanColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.LOCAL_DATE)) {
+          DateColumn source = (DateColumn) sliceTable.column(idColumn.name());
+          DateColumn dest = (DateColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.LOCAL_DATE_TIME)) {
+          DateTimeColumn source = (DateTimeColumn) sliceTable.column(idColumn.name());
+          DateTimeColumn dest = (DateTimeColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.INSTANT)) {
+          InstantColumn source = (InstantColumn) sliceTable.column(idColumn.name());
+          InstantColumn dest = (InstantColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        } else if (columnType.equals(ColumnType.LOCAL_TIME)) {
+          TimeColumn source = (TimeColumn) sliceTable.column(idColumn.name());
+          TimeColumn dest = (TimeColumn) result.column(idColumn.name());
+          dest.append(source.get(0));
+        }
+      }
+      for (String varName : uniqueVariableNames) {
+        DoubleColumn dest = (DoubleColumn) result.column(varName);
+        Table sliceRow =
+            sliceTable.where(sliceTable.stringColumn(MELT_VARIABLE_COLUMN_NAME).isEqualTo(varName));
+        if (!sliceRow.isEmpty()) {
+          dest.append(sliceRow.doubleColumn(MELT_VALUE_COLUMN_NAME).get(0));
+        } else {
+          dest.appendMissing();
+        }
+      }
+    }
+    return result;
+  }
+
   /**
    * Applies the operation in {@code doable} to every row in the table
    *
diff --git a/data/molten_smiths.csv b/data/molten_smiths.csv
new file mode 100644
index 00000000..94642449
--- /dev/null
+++ b/data/molten_smiths.csv
@@ -0,0 +1,7 @@
+subject,time,variable,value
+John Smith,1,age,33.0
+John Smith,1,weight,90.0
+John Smith,1,height,1.87
+Mary Smith,1,age,
+Mary Smith,1,weight,
+Mary Smith,1,height,1.54
diff --git a/data/molten_smiths_drop_missing.csv b/data/molten_smiths_drop_missing.csv
new file mode 100644
index 00000000..db909bf8
--- /dev/null
+++ b/data/molten_smiths_drop_missing.csv
@@ -0,0 +1,5 @@
+subject,time,variable,value
+John Smith,1,age,33.0
+John Smith,1,weight,90.0
+John Smith,1,height,1.87
+Mary Smith,1,height,1.54


Test patch:
diff --git a/core/src/test/java/tech/tablesaw/api/TableTest.java b/core/src/test/java/tech/tablesaw/api/TableTest.java
index 5981110b..5148129c 100644
--- a/core/src/test/java/tech/tablesaw/api/TableTest.java
+++ b/core/src/test/java/tech/tablesaw/api/TableTest.java
@@ -25,8 +25,11 @@ import static org.junit.jupiter.api.Assertions.fail;
 import static tech.tablesaw.aggregate.AggregateFunctions.mean;
 import static tech.tablesaw.aggregate.AggregateFunctions.stdDev;
 
+import com.google.common.collect.ImmutableList;
 import com.google.common.collect.Lists;
 import java.io.IOException;
+import java.io.StringReader;
+import java.io.StringWriter;
 import java.time.LocalDate;
 import java.util.ArrayList;
 import java.util.Comparator;
@@ -351,6 +354,87 @@ public class TableTest {
     assertEquals(sum1, rowConsumer.getSum());
   }
 
+  @Test
+  void melt() throws Exception {
+    boolean dropMissing = false;
+    String df =
+        "subject, time, age, weight, height\n"
+            + "John Smith,    1,  33,     90,   1.87\n"
+            + "Mary Smith,    1,  NA,     NA,   1.54";
+    StringReader reader = new StringReader(df);
+    Table t = Table.read().csv(reader);
+    t.columnNames();
+    List<String> ids = ImmutableList.of("subject", "time");
+    List<NumericColumn<?>> measures = t.numericColumns("age", "weight", "height");
+
+    Table melted = t.melt(ids, measures, dropMissing);
+    assertEquals(
+        "                                              \n"
+            + "  subject    |  time  |  variable  |  value  |\n"
+            + "----------------------------------------------\n"
+            + " John Smith  |     1  |       age  |     33  |\n"
+            + " John Smith  |     1  |    weight  |     90  |\n"
+            + " John Smith  |     1  |    height  |   1.87  |\n"
+            + " Mary Smith  |     1  |       age  |         |\n"
+            + " Mary Smith  |     1  |    weight  |         |\n"
+            + " Mary Smith  |     1  |    height  |   1.54  |",
+        melted.toString());
+  }
+
+  @Test
+  void meltAndDropMissing() throws Exception {
+    boolean dropMissing = true;
+    String df =
+        "subject, time, age, weight, height\n"
+            + "John Smith,    1,  33,     90,   1.87\n"
+            + "Mary Smith,    1,  NA,     NA,   1.54";
+    StringReader reader = new StringReader(df);
+    Table t = Table.read().csv(reader);
+    t.columnNames();
+    List<String> ids = ImmutableList.of("subject", "time");
+    List<NumericColumn<?>> measures = t.numericColumns("age", "weight", "height");
+
+    Table melted = t.melt(ids, measures, dropMissing);
+    melted.write().csv("../data/molten_smiths_drop_missing.csv");
+    assertEquals(
+        "                                              \n"
+            + "  subject    |  time  |  variable  |  value  |\n"
+            + "----------------------------------------------\n"
+            + " John Smith  |     1  |       age  |     33  |\n"
+            + " John Smith  |     1  |    weight  |     90  |\n"
+            + " John Smith  |     1  |    height  |   1.87  |\n"
+            + " Mary Smith  |     1  |    height  |   1.54  |",
+        melted.toString());
+  }
+
+  @Test
+  void cast() throws IOException {
+    Table molten = Table.read().csv("../data/molten_smiths.csv");
+    Table cast = molten.cast();
+    StringWriter writer = new StringWriter();
+    cast.write().csv(writer);
+    String writeString = writer.toString();
+    assertEquals(
+        "subject,time,weight,age,height\n"
+            + "John Smith,1,90.0,33.0,1.87\n"
+            + "Mary Smith,1,,,1.54\n",
+        writeString);
+  }
+
+  @Test
+  void castWithDropMissing() throws IOException {
+    Table molten = Table.read().csv("../data/molten_smiths_drop_missing.csv");
+    Table cast = molten.cast();
+    StringWriter writer = new StringWriter();
+    cast.write().csv(writer);
+    String writeString = writer.toString();
+    assertEquals(
+        "subject,time,weight,age,height\n"
+            + "John Smith,1,90.0,33.0,1.87\n"
+            + "Mary Smith,1,,,1.54\n",
+        writeString);
+  }
+
   private static class RowConsumer implements Consumer<Row[]> {
 
     private int sum = 0;