Tablesaw

You can create a table in code. Here we create a table and add two new columns to it:

More frequently, you will load a table from a CSV or other delimited text file.

Tablesaw does a pretty good job at guessing the column types for many data sets, but you can specify them if it guesses wrong, or to improve performance. Numerous other options are available, such as specifying whether or not there’s a header, using a non-standard delimiter, supplying a custom missing value indicator, and so on.

Because Tablesaw excels at manipulating tables, we use them whenever we can. When you ask tablesaw for the structure of a table, the answer comes back in the form of another table where one column contains the column names, etc. The methods ` structure(), `shape(), first(n), and last(n) can help you get to know a new data set. Here are some examples.

Table’s toString() method returns a String representation like those shown above. It returns a limited number of rows by default, but you can also use table.printAll(), or table.print(n) to get the output you want.

Of course, this is just the beginning of exploratory data analysis. You can also use numeric and visual tools to explore your data. These facilities are described in the documentation on statistics and Plotting

Often you’ll work with specific columns in a table. Here are some useful methods:

In tablesaw, column names are case-insensitive. You get the same column if you ask for any of these:

remembering column names is enough of a burden without having to remember exactly which characters are capitalized.

Getting specific column types from a table

Columns can be retrieved from tables by name or position. The simplest method column() returns a object of type Column. This may be good enough, but often you want to get a column of a specific type. For example, you would need to cast the value returned to a NumberColumn to use its values in a scatter plot.

When a variable type is "Column" it only provides methods that are available on all columns. You can’t perform math or do a string replace directly on a Column type. If you need a StringColumn you could cast the column, for example:

Table also supports methods that return columns of the desired type directly:

As with columns, many options exist for working with tables in row-wise fashion. Here are some useful ones:

You can also perform arbitrary operations on each row in the table. One way is to just iterate over the rows and work with each column individually.

Another approach lets you skip the iteration and just provide a Consumer for each row.

If you need to process more than one row at a time, there are several methods to help.

See [Rows](https://jtablesaw.github.io/tablesaw/userguide/rows) for more information and other options.

To sort a table, you can just use the sortOn() method and give it one or more column name:

See Sorting for more information and other options.

Query filters can be combined using the logical operations and, or, and not. These are implemented on the QuerySupport class.

Each method accepts a function with the following signature Function<Table, Selection>. Lambadas work nicely.

The usual way to calculate values is to use the summarize() method:

It’s important to recognize, that the column need not exist when summarize is invoked. Any map function can be used in the by() statement to group on calculated values. A common use case is in handling dates. You can summarize sales by day-of-week, as follows:

which says "return the mean and median sales by day of week."

See the documentation on Summarizing data, and the classes in the aggregate package for more detail.

If you’re only interested in how the frequently observations appear in different categories, you can use cross-tabulations. In the example below we show the table percents, but you can also get row and column percents and raw counts.

The formatted output is shown below.

See the section on #_crosstabs, and the JavaDocs for the CrossTab class.

Columns are usually created by importing a data file. They can also be instantiated by calling one of the static create() methods defined on the appropriate class. For example, you can create an empty DateColumn as follows:

The new column has the name "test", and a ColumnType of LOCAL_DATE. Names are important. We often ask a table for a column by name. All the columns within a given table must have unique names. You can always get the name of a column by calling name(), and its type by calling type().

To create a column with data, you can initialize the column with an array:

Once you have a column, you can add it to a table using the addColumns() method on Table.

You can add data to columns as shown below, but if your column is part of a table, you must take care to ensure that each column has the same number of elements.

To change the value of an element in a column you can use the set(index, value) method. This will replace the existing value at the given position with the new value.

Normally, you don’t remove data from a column in the normal sense. To remove elements from the middle of column would cause problems if the column is part of a table. However, if you do want to get rid of some elements you have two choices. The first is to set the value to missing as shown below.

Your other option is to create a new column without the offending data elements. This is done with filters as described below.

Columns do all the things you expect, here’s an incomplete list of standard operations:

These operations are available on nearly all column types. Each operates on an entire column.

To operate on the values of a column, you have two choices. You can work with individual values, or use column-wise operations to work with all the values in a column in the same way. To work with individual values, you can just iterate over the column:

Just about anything you can do with an individual LocalDate you can do with an entire DateColumn, using column-wise operations. For example, the above loop could be written as:

This is an example of a mapping function. You can find the date mapping functions in the interface DateMapFunctions. Many of the methods there deal with adding and subtracting units of time (days, weeks, months, etc), and calculating the column-wise differences between two date columns. Others provide access to elements of a date. The method month(), for example, returns a StringColumn containing the month for a given date. The methods year(), dayOfWeek(), dayOfMonth(), etc. function similarly.

Other columns have similar mapping functions.

You can filter two ways. The first is with the built-in predicates, like IsMonday(). See the end of this post for a full list of the built-in predicates for LocalDateColumn.

You can print data as individual values, columns or tables. The output format can be controlled by setting a type-specific formatter on a column. For example, to change how numbers are displayed you can call setPrintFormatter() on a NumberColumn, passing in a NumberColumnFormatter. Each formatter serves two functions, displaying true values and handling of missing ones. NumberColumnFormatter has several pre-configured options, including printing as currency or percents.

See the table section documentation for how to add and remove columns

Unary map functions operate on a single column, the method receiver. For example:

produces a new column that contains the values in columnA, but converted to upper case.

While unary mappers operate only on a single Column, they may have an additional parameter (or parameters) that are not Columns, for example:

or

Binary mappers operate on two columns. In the example below, the result produced is a new column that contains the row-wise sums of the values in the receiver (columnB) and the parameter (columnC):

N-ary mappers operate on an Array of columns:

The new Column is not added to the original columns table by default. To add it, use the addColumn() method defined on Table:

You can create functions that build new columns using doWithEach():

The method doWithEach(Consumer<T>) is defined on AbstractColumn. It takes a Consumer that accepts values of the same type as the column holds: LocalDates in the case of DateColumn. If the lambda syntax above is a bit too cryptic, here’s a version that does the same thing with the Consumer written out as an anonymous class:

The accept() method here calls plusDays(100) on each value in the column that receives the doWithEach method invocation and appends each result to the column d2. The results are added to the column dc2 declared just before the method is called.

In writing your own map functions it’s good practice to handle missing data as we do above.

You can also use a for loop to create new columns based on the values of existing ones. All columns in Tablesaw are iterable. For example:

While do() applies an operation to every row in the table individually, it is often useful to work with sets of contiguous rows: Calculating the difference in values between pairs of rows is a common example. These type of operations are called window operations. The size of the window refers to the number of rows it considers in evaluating the operation. Tablesaw supports two approaches, one for rolling windows, and one for stepping windows.

Rolling window operations move through the table one row at a time. For example, a rolling window with size of two, first looks at row 0 and row 1. Then it evaluates rows 1 and 2, and then rows 2 and 3. Any given row may be considered n times, where n is the size of the window.

A stepping window moves in steps. Each row is evaluated only once. A stepping window of size 2 first looks at rows 0 and 1, then rows 2 and 3, then rows 4 and 5.

Because working with Pairs of rows is common, it gets a bit of extra support that makes it a little easier to write these kind of operations.

Multi-row operations

To add a new ColumnType you implement the ColumnType interface. The simplest way is to subclass AbstractColumnType.

StringParsers are used by the CsvReader to load data from CSV files. StringParser is an abstract class.

To use automatic type detection for the column type, it must be accessible to CsvReader, you should probably update CsvReader for the new type detection.

To add a new column class you must implement the Column interface. One way is to subclass AbstractColumn.

Consider making your column implement CategoricalColumn.

If the type is numeric, on the other hand it would be preferable to subclass NumberColumn, which is a subclass of AbstractColumn.

TODO

TODO

TODO

Handling SQL Result Sets

Tablesaw has a huge number of methods for creating, querying, manipulating, displaying, and saving tables, so it makes sense that we use tables widely, and that many operations on tables return other tables. For example, when you ask a table to describe its structure, it returns a new table that contains the column names, types, and order.

You can create tables programmatically or by loading data from an external source.

The simplest way to display a table is to call "print()" on it, which return a formatted String representation.

The default implementation of print displays the first ten and last ten records. To specifically control the output, the methods first(n) and last(n) are available. These return a copy of the table that contains only the first n or last n rows respectively.

Table overides toString() to return print(). This makes for rather funky output in a debugger, but during analysis, you frequently want to look at the table data so frequently that the shortcut is worth the hassle it causes people programming Tablesaw.

There are a number of ways to get familiar with a new dataset. Here are some of the most useful.

table.name() returns its name, which defaults to the name of the file it was created from. You can change it if you like using setName(aString).

t.columnNames() returns an array of column-name strings.

t.structure() returns a list of columns with their position and types:

table.shape() returns the table’s size in rows and columns:

You can also get the rowCount() and columnCount() individually from a table.

You can add one or more columns to a Table using the addColumns() method:

You can also specify that the column be inserted at a particular place by providing an index:

As usual in java, column numbering begins at 0, rather than 1.

The column you add must either be empty or have the same number of elements as the other columns in the table.

To remove a column or columns:

Columns can also be removed by referencing them by name. Alternately, you can specify just the columns to retain:

Again you can specify the columns either directly, or by using their names.

While addColumns() and removeColumns() update the receiver in place, you can also create a new table with a subset of the columns in the receiver. This can be done by specifying the names of the columns that you want to retain, in the order you want them to appear.

You can also create a new table by specifying the columns in the current table that you don’t want, which might save some typing:

In this case the columns in the result table are in the same order as in the original.

Often you will want a reference to a column in the table. To get all the columns as a list:

Columns can also be selected by index or name:

Often you want just one column, which you can get using t.column(“columnName”).

Since Tablesaw columns are typed, you often need to cast the returned column to something more specific. For example:

as a convenience, tables have column accessors that are type specific: The do the casting for you.

Tables can be combined in one of several ways. The most basic is to append the rows of one table to another. This is only possible if the two tables have the same columns in the same order, but can be useful when, for example, you have the same data from two time periods.

You can concatenate two tables, adding the columns of one to the other by using the concat() method. The method returns the receiver rather than a new table. Two tables can be concatenated only if they have the same number of rows.

Tablesaw supports inner and outer joins between tables.

TODO

One of the most useful operations is filtering. Queries are created by forming expressions that produce a Selection, which effectively turns the query result into an object that can be used to filter by index. For example, the code below

This would produce a table containing every row in t where the value in the column named "Foo" contains a string that starts with "A".

Filters are covered in detail in the section on [Filtering](https://jtablesaw.github.io/tablesaw/userguide/filters).

There are numerous ways to summarize the data in a table.

The summarize() method and its variants let you specify the columns to summarize.

Summarize returns a Summarizer object.

The apply() method sent to summary above returns the result of applying the function to the table, and combining the results into a new table. It computes one summary for the original table.

Table can be sorted on any combination of columns, in any combination of ascending or descending order, or by supplying a comparator for complete flexibility. A simple example is shown below.

Sorting is covered in detail in the section on Sorting

There are no real rows in Tablesaw. Data is organized in columns. The closest you get to an actual row is a table with one line. However, rows are useful abstractions in tabular data, so we provide a kind of virtual row that may be useful for table operations.

Filters select a subset of the rows in a table. Given a filter, a table will (usually) return a table like itself, but having only the rows that pass the filter criteria.

The main methods for applying filters are:

which includes all rows for which the filter returns true. And

which excludes all rows for which the filter returns true.

As you can see, for any given selection dropWhere() returns the complement of the records returned by where().

There are several other methods like where to explore. Before digging into table filters, though, we should look Selections, and at the column filters table filters build on.

Key point: One way that where() in tablesaw differs from the where clause in SQL, is that Tablesaw always returns records in the order they appear in the original column or table. This can be used to good advantage when working with time-series data.

Both columns and tables are filtered using selections. A selection is a logical bitmap (like a boolean array) with an entry for each element in the column. For any given element, if the corresponding bitmap entry is "true", the element is included in the result.

Here’s what you need to know about selections.

Lets take a look at each of these.

To write a custom filter method for a column, you first create a predicate, and then pass it to an eval() method on your column. Here’s an example with NumberColumns.

Here’s an example. We write a filter that only selects prime numbers:

You can combine filters to query a table on the values in multiple columns.

You can select rows by specifying the index (zero-based):

You can also select by range:

You can also select a random sample of data. See the section on Sampling for more detail.

The line below returns a table containing 50 randomly sampled rows from table t.

Alternately, you can specify the sample size as a proportion of the table size using sampleX(aDouble):

You can also divide the table in two, assigning rows randomly to each, and return both sub-tables in an array. The code below puts ~ 1/3 of the rows in the results[0], and the other 2/3rds in results[1]. This is handy for separating data into a training and test subsets for machine learning applications.

You may want to exclude some of the columns in the original from the new table. To do this, you could simply execute the queries as above, and then eliminate columns from the new table as a separate step:

Alternately, you could specify the desired subset of columns as part of the query:

Given a list of columns as arguments, the select() statement returns a table containing only those columns. By chaning select() and where(), you get something that looks a lot like a sql statement that returns a subset of the data in the original table.

All the methods below return a Selection.

The simplest way to sort is just to provide the name of the sort column or columns as arguments to a method that specifies the order. For example, to sort from lowest to highest:

This sorts the table in ascending order, in the order the column names are listed. To see the result, we can use the first() method:

which produces:

The method sortDescending() provides the analogous operation:

sortAscending() and sortDescending() are limiting in that you must use the same order for every column. The advantage is that the sort logic is perfectly clear when reading the code.

If you need to sort such that some columns are order high-to-low, and others low-to-high, you can use sortOn().

sortOn() sorts in ascending order by default, so this code produces the same results as sortAscending(). To sort one of the columns in descending order, simply preface the column name with a minus sign ‘-‘, as in:

This sorts the table first on recipe in descending order, then on y and mix, in ascending order.

Finally, if you need complete control over sorting, you can create an Compator<VRow> and pass that to the sort() method. As Tablesaw is column-oriented, it does not have true rows. Materializing each row into some grouping of standard Java objects would be very inefficient. A VRow is a kind of virtual row that serves as a proxy for a single row in a table.

For example, a row comparator that sorts in ascending temperature order can be constructed and used as shown below:

It can be used to sort the cake table by:

Obviously, a custom comparator isn’t needed in this example, but the ability is there when you need it. With a Comparator<VRow>, sort logic can include transformations and combinations of the data when making ordering decisions.

Data: The cake dataset is from http://www.stats4stem.org/r-cake-data.html.

The column parameter is the column you want to summarize, and the functions parameter is an array of AggregateFunction, so you can calculate multiple statistics with a single method invocation as shown below.

In this example, the mean, max, and min functions are instances of AggregateFunction, which we access via a static import of AggregateFunctions. AggregateFunctions is a static utility class that defines many common (and uncommon) functions.

The return value from summarize is a SummaryFunction, to get the results in the form of a table, we use apply():

The results table is shown below. As you can see, there is a column for each of the functions. The default column name is `function-name [summarized-column-name] `

You can also summarize multiple columns in a single call, using one of the following variants:

Usually, you’ll be summarizing numeric columns, but some functions are available for other column types. BooleanColumn, for example, supports countTrue() and countFalse(). All column types support countMissing().

What if you want to apply countTrue() to a boolean column, while calculationg the standard deviation of a numeric column. You can achieve this with one of the options shown above:

The summary function will compute the results for every column type that supports the given function, so in this example, booleanColumn returns a value for countTrue, and numericColumn returns a value for standardDeviation.

In the above examples, we’ve been using columns as the first n arguments to summarize(). You can also refer to the column by its name.

t.summarize(columnName…​)

In this example, we’ll use a Tornado dataset from the NOAA’s Storm Prediction Center. It contains records of every recorded US tornado from 1950-2014. Once we’ve loaded the data, computing stats on subgroups is easy.

The argument to by() is a column or columns, which may be given by the column’s name. In the example below, we calculate the average number of injuries, subtotaling by the tornado’s scale attribute.

That’s all there is to it. Note that when we use by(), we no longer need to call apply() to get the table. We give the result table a more descriptive name, and then to see the result, we can use print():

In this dataset, missing scale values are indicated by a -9. A scale of 0 to 5 indicates the size of the storm, with 5 being the largest/most severe. As you can see, injuries increase dramatically with the most severe storms.

If we provide multiple columns to the by() method, the resulting table has a row for each unique combination. To total the number of fatalities by state and scale, we would write:

which produces:

etc.

Since the result returned is also a Table, you can easily perform other operations. For example, to see only results for storms in Texas and Oklahoma, you could do the following.

producing:

Data: The tornado dataset is from [NOAA’s Storm Prediction Center Severe Weather GIS](http://www.spc.noaa.gov/gis/svrgis/).

If you can group on calculated columns, why not summarize calculated columns? Lets say that you are analyzing text. Each row contains a single sentence, and we would like to characterize the length of the sentences in our dataset. You could create a column called "sentence length" and add it to the table, but might prefer to analyze the data directly.

In this example, a standard map function (length()) creates a NumberColumn containing the number of characters in each value of the StringColumn sentence. Various statistics (min, q1, etc.) are calculated on the resulting column.

Another useful facility is to summarize only that data that passes some test. For example, you could extract the common suffixes between two columns of String data, and then count the number that are longer than two characters. This can be done using the summarizeIf() method as follows:

To provide a slightly easier interface, we have provided a Row class. Row makes it easier to do things like sort using a comparator.

You can also use them for iteration with somewhat less boilerplate code:

You can get single variable counts using the xTabCounts() method that takes only one column name argument .

producing:

You may want to see the percent of polls conducted by each pollster, rather than raw counts. The xTabPercents() method is used for that.

Actually, percents is a misnomer. The results produced are the proportions in decimal format. To get percent-formatted output we use a different NumericColumnFormatter.

When you have two variables, you can display the percent that falls into each combination as shown below.

Because the percents are small, we updated the formatter to show a single fractional digit after the decimal point.

<br>

The output can best be understood by looking at an example. Of all the polls in the dataset, 1.9% were conducted by Fox in April, 3.1% by Gallup in April, 0.9% by Fox in August, etc.

As you can see, this also gives you the 'total' percents by month and pollster.

The final option is to show column percents or row percents. We’ll start with column percents. You calculate them as shown below.

Look at the column for "fox", the values you see are the percentages for fox alone: 9% of fox’s polls were conducted in April, 5% in August, etc.

Looking across the columns on the other hand is not very intuitive (or useful, probably) until you get to the total, which shows the average across all pollsters by month.

Row percents show the opposite viewpoint.

Here we see that, of all the polls conducted in April, fox conducted 26%, Gallup 43%, and The University of Pennsylvania conducted 0% with rounding.

And that’s all there is to Tablesaw CrossTabs.

With all these methods, Tablesaw looks at the data in each column in the file and takes a wild guess at the type. Actually, it looks at a sample of the data and applies some heuristics. Of course, it’s possible that the data set includes rare values that are missed in the type inference sample. If that happens, you can set the option sample(false) to consider all the data when performing type inference.

If nothing else seems to fit, the column is read as a StringColumn. Usually, Tablesaw gets it right, but sometimes it needs a little help.

All the examples above attempt to streamline the loading process when you have a CSV file stored on your file system. A more flexible way to load a CSV is using the Stream interface, which takes a java.io.InputStream as a parameter.

It can be used to read local files, but also files read across the net, in S3, etc. Here’s some examples of how it can be used.

By default, we assume a UTF-8 encoding for your files. If your files use another encoding, the loading process is slightly different. You need to open a reader on a FileInputStream that was constructed with the correct encoding. Here’s an example.

There are two ways to work with plotting. You can use the predefined "canned" plots in the api package, or you can roll-your-own custom visualizations.

See Tablesaw and the Tablesaw User Guide for more information.

All of the code used to produce the plots in this document can be found in the class BarPieAndParetoExample.java, which you can find here.

https://github.com/jtablesaw/tablesaw/blob/master/jsplot/src/test/java/tech/tablesaw/examples/BarPieAndParetoExample.java

We recommend you open that class to follow along.

First we load and clean the Tornado dataset. We use Table.read().csv() to open the file.

Next we clean the file. We want to work with the scale column. Tornadoes are assigned a scale from 0 to 5 according to their wind speed, but our dataset has missing values encoded as -9, which would throw off the graph. To fix this we set each value of -9 in the scale column to a missing-value indicator. Then we can create our plots.

We start with the ubiquitous bar chart. Bar charts generally display data that has been summarized into groups. To create a bar chart, you need two things:

We start by counting tornado-related fatalities according to the intensity of the tornadoes.

Then we plot the results:

Pie plots are both widely criticized and ubiquitous. As a general rule, bar plots are easier to interpret, and so, generally, are to be preferred. We’d be remiss, however to not provide Pie Chart support. This example shows a pie plot that displays the same data as the first bar plot above:

A simple variation on a bar plot is the Pareto Chart. In the plot below, fatality counts are summed by US state, and the results are sorted according to the totals in descending order. The example code has the details.

A one dimensional histogram of property prices is shown below.