DFLib Documentation

Series is the simplest of the two data structures. You can think of it as a wrapper around an array of values. You can use Series to model sequences of data such as timestamps in a time series. Series object is parameterized for the type of data that it holds. So there can be Series<String> or a Series<LocalDate>, etc. There is also an important category of "primitive" Series (IntSeries, LongSeries, DoubleSeries, BooleanSeries) that are optimized for memory use and arithmetic operations. Of course, each primitive Series can also pose as Series of a corresponding wrapper object type (e.g. IntSeries is also a Series<Integer>).

Series object is an important building block of DataFrame, but it defines a number of useful data manipulation and transformation operations on its own. Those will be covered in the following chapters. Here we’ll discuss how to create Series.

DataFrame is an in-memory table made of an Index header and a number of named columns. Each column is a Series, and has an associated name in the Index. DataFrame can contain columns of different kinds, so it is not parameterized for any single type.

"Rows" is a purely virtual concept as the data is organized by column, yet there is a number of APIs that appear to operate on rows for user convenience.

There are a few ways to create a DataFrame. Here we’ll show how to convert various in-memory objects to DataFrames (arrays, Streams, Collections, Series).

First example, adding data row by row:

The resulting DataFrame looks like this:

A more general example - creating a DataFrame from a list of objects by "extracting" column data from object properties:

The resulting DataFrame looks like this:

Another example - a single-dimensional array can be "folded" into a DataFrame row-by-row:

Same, but folding column-by-column:

You can also create DataFrames from collections or Streams (folded either by row or by column). Here is an example of how to use a Stream of primitive ints, creating a DataFrame made of memory-efficient IntSeries columns:

Finally, a DataFrame can be created from an array of Series, each Series representing a column:

This is the most efficient way, as an array of Series is how each DataFrame is structured internally.

Index is somewhat similar to Series. It serves as a DataFrame "header" and internally allows to quickly resolve column String labels to their numeric positions. You’d work with Index instances outside a DataFrame only occasionally, but it is still good to know its capabilities. E.g., here is how to get all the column labels:

$str(..) and $decimal(..) expressions in the example above are the column lookup expressions. They return a DataFrame column with a given name or at a given (zero-based) position without applying any transformations to the data.

Factory methods for column expressions are easy to spot in the Exp interface - they all start with a dollar sign:

Return type is implied from the method name ($str(..) produces a Series<String>, $decimal(..) - Series<BigDecimal>, $int(..) - Series<Integer>, $date(..) - Series<LocalDate> and so on).

To avoid overhead, column expressions do not perform any type conversions of the column data (with a notable exception of $bool that does). So you need to pick the right type in your code based on your knowledge of data to avoid ClassCastExceptions downstream, or use a generic $col(..) expression. If you do want to convert data from one type to another, use one of the castAs methods explicitly:

castAs will do its best to provide a meaningful conversion to the target type. But sometimes it is simply not possible, and it will throw an exception (e.g. a String like "abcd" can’t be converted to a number without some contextual knowledge). In some other cases, no default conversion exists, but a custom conversion is possible and desired. You can do it via Exp.mapVal(..).

If we want to generate a Series with the same repeating value, we can use the $val(..) expression:

A useful case for $val(..) is to create a separator for String concatenation:

$int(..), $long(..), double(..) and $decimal(..) columns expressions mentioned above are numeric (i.e. instances of NumExp expression subclass), and as such, they provide arithmetics, comparison operations, and numeric aggregating operations.

We will look at comparisons later in the "Conditions" chapter. Here is an example of arithmetics operation:

Here is an example of aggregating operations:

TODO

Expressions can be expanded by calling methods on the Exp object (and its type-specific subclasses). As we’ve already seen above, expressions can be composed of other expressions by invoking static methods of Exp. E.g.:

TODO

Sorter is a special object, that allows to sort DFLib data structures. Internally a Sorter is using an expression to retrieve values that comprise sorting criteria and index them in the specified order. Sorter can be created from any expression by calling its asc() or desc() methods:

Columns are picked either by condition, by name, by position or implicitly. Let’s take a look at each style…​

We saw how we can select a subset of columns. While doing that, we can also assign new names to all or some of the columns via selectAs(..).

Rename all column set columns:

Rename a subset of columns. Specifying names of all columns at once may not be practical for "wide" column sets. Instead, you can pass a map of old to new names to selectAs(..) to rename just some columns. Names not present in the map will remain unchanged:

Rename with a function, applied to all column names in turn. This is useful e.g. to convert all names to lower or upper case:

In the above examples we performed DataFrame vertical slicing, columns reordering and renaming. In this chapter we’ll show how to transform column data.

Generating columns with expressions:

Generating columns row-by-row with RowMapper:

There’s also a way to generating columns row-by-row with per-column array of RowToValueMapper. It is not very different from what we’ve seen so far, so we are leaving this as an exercise for the reader.

Values can be split using an expression into iterable or array elements, and new columns can be generated from those elements, thus "expanding" each row. Here is an example of handling Lists of values (List is, of course, an Iterable) :

If you don’t know the exact number of phones, but would like to capture them all in separate columns, do not specify any explicit names (as mentioned in the implicit column selection chapter). As many columns as needed to fit the longest array of phone numbers will be generated on the fly, and the names will be assigned dynamically:

If the phone numbers were provided as a comma-separated String instead of a List, you can split the String into an array of numbers using the split(..) expression, and use selectExpandArray(..) to generate the columns:

The ColumnSet operations demonstrated so far resulted in discarding the source DataFrame, and returning only the columns defined within the set. But often we would like to recombine transformed ColumnSet with the original DataFrame. Any in-place data cleanup, data set enrichment, etc. fall in this category.

For this ColumnSet provides a number of "merging" methods. All the ColumnsSet methods that do not start with select…​ are performing a merge with the source DataFrame. In fact, all the renaming and data transformation operations that we just discussed can also be executed as "merges".

Here is an example of cleaning up a data set and adding a new column:

Now let’s check the result. The first two columns in the ColumnSet ("last", "first") were already present in the DataFrame, so they got replaced with the cleaned up versions. The last column ("full") was new, so it was appended to the right side of the DataFrame:

General merge rules:

What if we want to ensure that all of the ColumnSet columns are appended, and no replacement of the original columns occurs? For this you can manually specify ColumnSet labels that are not found in the source DataFrame, or you can use DataFrame.colsAppend(..), and DFLib itself will ensure that the names are unique, appending _ to each label until it doesn’t overlap with anything already in the DataFrame:

This transformation converts columns to primitives. The values in the columns to be converted can be Numbers, Booleans or Strings (or objects with toString() that can be parsed to a primitive type):

As a part of a dataset cleanup effort, you might want to replace null values with something meaningful. For this you may have to guess or maybe extrapolate the existing values based on some internal knowledge of the dataset. To help with this task, ColumnSet provides a few methods discussed here.

Filling nulls with a constant value:

Filling nulls with values adjacent to the cells with nulls:

There’s also a way to fill nulls from another Series object used as a "mask" :

Finally, we can use an Exp to calculate values for nulls:

To get rid of certain columns in a DataFrame, you can either select the columns you would want to drop and call drop() or select the columns you’d want to remain and call select():

In both cases, the result is the same:

Rows are picked either by condition, by positions or as a range. Let’s take a look at each style…​

Just like ColumnSet, RowSet defines a number of column- and row-based transformations. And just like with ColumnSet, each transformation can be invoked as a "select" or a "merge", returning either the RowSet rows or all rows from the original DataFrame. Here we will demonstrate selecting operations, and will talk about merging next.

Transforming with column expressions:

Transforming row-by-row with RowMapper:

There’s also a way to transforming row-by-row with per-column array of RowToValueMapper. It is not very different from what we’ve seen so far, so we are leaving this as an exercise for the reader.

If we want to merge the result of a RowSet transformation to the original DataFrame, we’ll need to use methods like merge(..) (i.e. those that do not start with select):

Merging rows is done similar to columns, except rows have no name labels, so it is done by position. General merge rules:

Splitting rows is somewhat simpler than splitting columns. The API takes a single column (no expression is allowed), and splits any expandable values into new rows:

To deduplicate DataFrames, there’s an API to select fully or partially-unique rows. Uniqueness is checked either on all columns of each row, or a preselected subset:

To get rid of certain rows in a DataFrame, you can either select the rows you would want to drop, and call drop() or select the rows you’d want to remain and call select():

In both cases, the result is the same:

TODO

TODO

TODO

Series provides a sort method to sort its data using a sorter built from an expression:

The result is a new Series with sorted data:

Alternatively the same can be achieved using a Comparator:

The next example shows how to sort Series in the "natural" order (alphabetically for Strings):

Series of primitives have methods to sort values in the natural order without an explicit sorter or comparator:

Additionally, IntSeries has an optimized method to sort ints with a custom IntComparator.

Next we’ll check how to sort a DataFrame.

Rows in a DataFrame can be sorted with one or more sorters.

Alternatively sorting can be done by a single column name or an array of columns. The assumption is the same as above - values in columns used for sorting must be either Java primitive or implement java.lang.Comparable.

If you have a Series object and want to concatenate it with one or more other Series, you’d use Series.concat(..) method:

The result is as expected contains values of all Series put together:

If you have a collection or an array of Series and want to "glue" them together, you can use a static SeriesConcat.concat(..):

The result is the same as in the previous example. Also you can concatenate Series with itself:

DataFrame offers two options for concatenation - vertical (stacking DataFrames on top of each other) and horizontal (putting them next to each other). Let’s see some examples..

So far all our concatenation examples consisted of DataFrames that had matching dimensions (same number and names of columns for vConcat and same number of rows for hConcat). But what if concatenated DataFrames are shaped or named differently?

Concat methods may take an extra "how" parameter to define concatenation semantics. The type of the "how" parameter is JoinType, and can be one of inner (default), left, right, full.

Let’s look how this works with vConcat:

As you can see in the result, the behavior of inner join is to only keep columns that are present in both df1 and df2. Columns are joined by name, regardless of their order (though the order of columns is preserved in the result, following the order in each DataFame being joined, left to right).

Changing semantics from inner to left gives us all the columns of the leftmost DataFrame, but those columns that are missing from the concatenated DataFrame are filled with nulls in the corresponding positions:

Leaving it as an exercise for the reader to try right and full joins.

hConcat works similarly, however concatenation is done by row position, as there are no row names:

Notice how DFLib treated the id column in the join result above. Since it was present in both left and right DataFrames, it was included in the result twice, but the second instance was renamed to id_ to avoid a naming collision. The same renaming would occur for any other columns from the right DataFrame that have conflicting names with the columns from the left. A quick way to get rid of duplicate columns is to exclude them from the result using the name pattern:

For better clarity, you can assign names to the left and/or the right DataFrames just before the join, and the column names in the result will be prefixed with the corresponding DataFrame name, thus avoiding the trailing underscore:

This makes it easier to identify the origin of each column.

When building the join result, you can apply expressions to alter the existing columns or create new columns all together:

Now let’s demonstrate outer joins. Here is a leftJoin(..) example using the same left and right DataFrames:

It has all the rows from the left DataFrame, and only the matching rows from the right DataFrame. For left rows with no matching right rows, the right columns are filled with null.

Join API allows to explicitly identify which rows had a match, and which didn’t by adding a special "indicator" column. Let’s show it on a fullJoin(..) example:

The join_type column will contain a special enum of org.dflib.join.JoinIndicator type that allows to categorize rows in the produced DataFrame.

The joins we’ve seen so far were all based on comparing left and right column values. They are knows as "hash joins", and are usually as fast as joins can get. However, they can not express every possible condition, and sometimes we have to resort to another kind of joins - "nested loop", that would compare every row from the left DataFrame to every row from the right. For instance, let’s join a DataFrame of salaries with itself to produce pairs of names - a person with higher salary on the left vs. everyone with lower salary on the right:

Once the imports are setup, you’d use Jdbc class to obtain an instance of JdbcConnector that can be later used for all DB operations. You may already have a preconfigured javax.sql.DataSource, so the simplest way to create a connector is to pass that DataSource to Jdbc factory method:

Alternatively you can build everything from scratch:

Once you have the connector, you can start reading and persisting DataFrame data. Many DataFrames map directly to individual tables (or views) defined in the database. For those DFLib provides a set of rather straightforward operations that do not require the user to write SQL (all needed SQL is auto-generated by the framework):

Table loader provides a way to customize the load operation. It allows to select specific columns, set the maximum number of rows to read, sample rows, and even specify fetch condition as another DataFrame. Some examples:

What it doesn’t require (unlike CSV loader) is explicit column types, as the proper value types are inferred from the database metadata.

Table saver allows to save DataFrame to a table. Column names in the DataFrame should match column names in the DB table:

In this scenario table saver executes insert for each DataFrame row. But what if there is already an existing data in the table? There are a few options the user has to overwrite or merge the data:

Delete before insert example:

Merge by PK example (PK columns are detected from DB metadata) :

TODO

The simplest API for reading a DataFrame from a CSV is this:

The result is a DataFrame that matches CSV structure:

DFLib makes a few assumptions about the data in f1.csv, namely that the first row represents column names, that all columns are Strings, and that all of them need to be included. These assumptions are not necessarily true with many data sets. So there is a longer form of this API that allows to configure column types, skip rows and columns, or even sample the data without loading the entire CSV in memory. Some examples:

Writing to a CSV is equally easy:

Just like with the loader, CSV saver provides its own set of options, if the defaults are not sufficient:

In most cases you don’t need to know anything about Avro schemas. DFLib automatically generates a schema for a DataFrame before it is saved. Alternatively you can create a custom Schema object based on Avro specification and pass it to AvroSaver.

TODO…​

To control truncation behavior, you can use static methods on DFLibJupyter:

TODO

TODO

TODO

TODO

TODO