Awesome

dataframe-ec

A tabular data structure (aka a data frame) based on the Eclipse Collections framework. The underlying data frame structure is columnar, with focus on memory optimization achieved by using efficient Eclipse Collections data structures and APIs.

For more on Eclipse Collections see: https://www.eclipse.org/collections/.

Where to Get It

Get the latest release of dataframe-ec here:

<dependency>
  <groupId>io.github.vmzakharov</groupId>
  <artifactId>dataframe-ec</artifactId>
  <version>1.1.0</version>
</dependency>

Note that starting with the release 0.20.0, dataframe-ec is compatible with JDK 17+. The last release of dataframe-ec to support Java 8+ is 0.19.8 which can be found at these coordinates:

<dependency>
  <groupId>io.github.vmzakharov</groupId>
  <artifactId>dataframe-ec</artifactId>
  <version>0.19.8</version>
</dependency>

Code Kata

Learn dataframe-ec with a Kata! Check out dataframe-ec kata, an easy and fun way to learn the basics of data frame APIs and common usage patterns through hands-on exercises.

Data Frame Operations

• create a data frame programmatically or load from a csv file
• add a column to a data frame, columns can be
  • stored or computed
  • of type: string, long, int, double, float, date, date/time
• drop one or more columns
• select a subset of rows based on a criteria
• sort by one or more columns or by an expression
• select distinct rows based on all column values or a subset of columns
• union - concatenating data frames with the same schemas
• join with another data frame, based on the specified column values, inner and outer joins are supported
• join with complements, a single operation that returns three data frames - a complement of this data frame in another one, an inner join of the data frames, and a complement of the other data frame in this one
• lookup join - enrich a data frame by adding one or more columns based on value lookup in another data frame
• pivot - similar to pivot in a spreadsheet where the distinct values in a column become columns in the pivot table and the row values in the table are grouped and aggregated based on the aggregation functions provided
• find relative complements (set differences) of two data frames based on the specified column values
• aggregation - aggregating the entire data frame or grouping by the specified column values and aggregating within a group
• flag rows - individually or matching a criteria <br>...and more!

Examples

Creating a Data Frame

A data frame can be loaded from a CSV file. Let's say there is a file called "donut_orders.csv" with the following contents:

Customer,  Count,  Price,  Date
"Archibald", 5.0, 23.45, 2020-10-15
"Bridget", 10.0, 40.34, 2020-11-10
"Clyde", 4.0, 19.5, 2020-10-19

Then a data frame can be loaded from this file as shown below. The file schema can be inferred, like in this example, or specified explicitly.

DataFrame ordersFromFile  = new CsvDataSet("donut_orders.csv", "Donut Orders").loadAsDataFrame();

ordersFromFile

The loadAsDataFrame() method can take a numeric parameter, which specifies how many rows of data to load. For example:

DataFrame firstTwoOrders = new CsvDataSet("donut_orders.csv", "Donut Orders").loadAsDataFrame(2);

firstTwoOrders

CsvDataSet can also accept a schema object, which explicitly defines column types and also supports a number of options such as the value separator and the null marker in a source file. For example, let's consider a file "donut_orders_complicated.csv" that looks like

Customer|Count|Price|Date
"Archibald"|5|23.45|2020-10-15
"Bridget"|10|*null*|2020-11-10
"Clyde"|4|19.5|*null*

A data frame can be loaded from this file by providing a schema

CsvSchema donutSchema = new CsvSchema()
        .separator('|')
        .nullMarker("*null*");
donutSchema.addColumn("Customer", STRING);
donutSchema.addColumn("Count", LONG);
donutSchema.addColumn("Price", DOUBLE);
donutSchema.addColumn("Date", DATE);

CsvDataSet dataSet = new CsvDataSet("donut_orders_complicated.csv", "Donut Orders", donutSchema);
DataFrame schemingDonuts = dataSet.loadAsDataFrame();

schemingDonuts

A data frame can be created programmatically by providing values for individual rows or columns. Here is a sample constructing a data frame row by row:

DataFrame orders = new DataFrame("Donut Orders")
    .addStringColumn("Customer").addLongColumn("Count").addDoubleColumn("Price").addDateColumn("Date")
    .addRow("Alice",  5, 23.45, LocalDate.of(2020, 10, 15))
    .addRow("Bob",   10, 40.34, LocalDate.of(2020, 11, 10))
    .addRow("Alice",  4, 19.50, LocalDate.of(2020, 10, 19))
    .addRow("Carl",  11, 44.78, LocalDate.of(2020, 12, 25))
    .addRow("Doris",  1,  5.00, LocalDate.of(2020,  9,  1));

orders

This way of creating a data frame is more useful for contexts like unit tests, where readability matters. In your applications you probably want to load a data frame from a file or populate individual columns with strongly typed values as in the following example, which produces a data frame with the same exact contents as the one in the example above:

DataFrame ordersByCol = new DataFrame("Donut Orders")
        .addStringColumn("Customer", Lists.immutable.of("Alice", "Bob", "Alice", "Carl", "Doris"))
        .addLongColumn("Count", LongLists.immutable.of(5, 10, 4, 11, 1))
        .addDoubleColumn("Price", DoubleLists.immutable.of(23.45, 40.34, 19.50, 44.78, 5.00))
        .addDateColumn("Date", Lists.immutable.of(LocalDate.of(2020, 10, 15), LocalDate.of(2020, 11, 10),
                LocalDate.of(2020, 10, 19), LocalDate.of(2020, 12, 25), LocalDate.of(2020, 9, 1)));
ordersByCol.seal(); // finished constructing a data frame

ordersByCol

A data frame can also be created from a hierarchical data set via a projection operator supported by the DSL. Let's say we have two record types: Person and Address. Then we can use the projection operator to turn a list of Person objects into a data frame as follows.

var visitor = new InMemoryEvaluationVisitor();
visitor.getContext().addDataSet(
        new ObjectListDataSet("Person", Lists.immutable.of(
            new Person("Alice", 30, new Address("Brooklyn", "North Dakota")),
            new Person("Bob", 40, new Address("Lexington", "Nebraska")),
            new Person("Carl", 50, new Address("Northwood", "Idaho"))
        )));

var script = ExpressionParserHelper.DEFAULT.toScript("""
        project {
            Name : Person.name,
            ${Lucky Number} : Person.luckyNumber,
            State : Person.address.state
        }
        """);

DataFrame projectionValue = ((DataFrameValue) script.evaluate(visitor)).dataFrameValue();

projectionValue

Sum of Columns

DataFrame totalOrdered = orders.sum(Lists.immutable.of("Count", "Price"));

totalOrdered

Aggregation Functions

The following aggregation functions are supported

• sum
• min
• max
• avg - average, the return value is of the same type as the input data
• avg2d - average, the return value is of type double for any primitive input type
• count
• same - the result is null if the aggregated values are not the equal to each other, otherwise it equals to that value

DataFrame orderStats = orders.aggregate(Lists.immutable.of(max("Price", "MaxPrice"), min("Price", "MinPrice"), sum("Price", "Total")));

orderStats

Sum With Group By

DataFrame totalsByCustomer = orders.sumBy(Lists.immutable.of("Count", "Price"), Lists.immutable.of("Customer"));

totalsByCustomer

Add a Calculated Column

orders.addColumn("AvgDonutPrice", "Price / Count");

orders

Filter

Selection of a dataframe containing the rows matching the filter condition

DataFrame bigOrders = orders.selectBy("Count >= 10");

bigOrders

Selection of a dataframe without the rows matching the filter condition

DataFrame smallOrders = orders.rejectBy("Count >= 10");

smallOrders

Split the rows of a data frame into two subsets both matching and not matching the filter condition respectively

Twin<DataFrame> highAndLow = orders.partition("Count >= 10");

Result - a pair of data frames:

highAndLow.getOne()

highAndLow.getTwo()

Drop Column

orders.dropColumn("AvgDonutPrice");

orders

Sort

Sort by the order date:

orders.sortBy(Lists.immutable.of("Date"));

orders

Sort by Customer ignoring the first letter of their name

orders.sortByExpression("substr(Customer, 1)");

orders

Union

The union operation concatenates two data frames with the same schema. Note that it does not remove duplicate rows.

DataFrame otherOrders = new DataFrame("Other Donut Orders")
    .addStringColumn("Customer").addLongColumn("Count").addDoubleColumn("Price").addDateColumn("Date")
    .addRow("Eve",  2, 9.80, LocalDate.of(2020, 12, 5));

DataFrame combinedOrders = orders.union(otherOrders);

combinedOrders

Distinct

Say we want to get a list of all clients who placed orders, which are listed in the orders data frame above. We can use the distinct() method for that:

DataFrame distinctCustomers = orders.distinct(Lists.immutable.of("Customer"));

distinctCustomers

Join

DataFrame joining1 = new DataFrame("df1")
        .addStringColumn("Foo").addStringColumn("Bar").addStringColumn("Letter").addLongColumn("Baz")
        .addRow("Pinky", "pink", "B", 8)
        .addRow("Inky", "cyan", "C", 9)
        .addRow("Clyde", "orange", "D", 10);

DataFrame joining2 = new DataFrame("df2")
        .addStringColumn("Name").addStringColumn("Color").addStringColumn("Code").addLongColumn("Number")
        .addRow("Grapefruit", "pink", "B", 2)
        .addRow("Orange", "orange", "D", 4)
        .addRow("Apple", "red", "A", 1);

DataFrame joined = joining1.outerJoin(joining2, Lists.immutable.of("Bar", "Letter"), Lists.immutable.of("Color", "Code"));

joining1

joining2

joined

Join With Complements

DataFrame sideA = new DataFrame("Side A")
        .addStringColumn("Key").addLongColumn("Value")
        .addRow("A", 1)
        .addRow("B", 2)
        .addRow("X", 3)
        ;

DataFrame sideB = new DataFrame("Side B")
        .addStringColumn("Id").addLongColumn("Count")
        .addRow("X", 30)
        .addRow("B", 10)
        .addRow("C", 20)
        ;

Triplet<DataFrame> result = sideA.joinWithComplements(sideB, Lists.immutable.of("Key"), Lists.immutable.of("Id"));

sideA

sideB

Complement of B in A:

result.getOne()

Intersection (inner join) of A and B based on the key columns:

result.getTwo()

Complement of A in B:

result.getThree()

Lookup Join

DataFrame pets = new DataFrame("Pets")
        .addStringColumn("Name").addLongColumn("Pet Kind Code")
        .addRow("Sweet Pea", 1)
        .addRow("Mittens",   2)
        .addRow("Spot",      1)
        .addRow("Eagly",     5)
        .addRow("Grzgxxch", 99);

DataFrame codes = new DataFrame("Pet Kinds")
        .addLongColumn("Code").addStringColumn("Description")
        .addRow(1, "Dog")
        .addRow(2, "Cat")
        .addRow(5, "Eagle")
        .addRow(7, "Snake");

pets.lookup(DfJoin.to(codes)
        .match("Pet Kind Code", "Code")
        .select("Description")
        .ifAbsent("Unclear"));

pets

There is another option to do lookup using a more fluent API directly on the DataFrame class:

pets.lookupIn(codes)
    .match("Pet Kind Code", "Code")
    .select("Description")
    .ifAbsent("Unclear")
    .resolveLookup();

Note that if using this approach, once the all the lookup parameters are specified you need to call the resolveLookup() methof to actually execute the lookup.

Pivot

Say we have a data frame of individual donut sales like this:

  DataFrame donutSales = new DataFrame("Donut Shop Purchases")
          .addStringColumn("Customer").addStringColumn("Month").addStringColumn("Donut Type")
          .addLongColumn("Qty").addDoubleColumn("Amount")
          .addRow("Alice", "Jan", "Blueberry", 10, 10.00)
          .addRow("Alice", "Feb", "Glazed", 10, 12.00)
          .addRow("Alice", "Feb", "Old Fashioned", 10, 8.00)
          .addRow("Alice", "Jan", "Blueberry", 10, 10.00)
          .addRow("Bob", "Jan", "Blueberry", 5, 5.00)
          .addRow("Bob", "Jan", "Pumpkin Spice", 5, 10.00)
          .addRow("Bob", "Jan", "Apple Cider", 4, 4.40)
          .addRow("Bob", "Mar", "Apple Cider", 8, 8.80)
          .addRow("Dave", "Jan", "Blueberry", 10, 10.00)
          .addRow("Dave", "Jan", "Old Fashioned", 20, 16.00)
          .addRow("Carol", "Jan", "Blueberry", 6, 6.00)
          .addRow("Carol", "Feb", "Old Fashioned", 12, 9.60)
          .addRow("Carol", "Mar", "Jelly", 10, 15.00)
          .addRow("Carol", "Jan", "Apple Cider", 12, 13.20)
          ;

donutSales

Now we want to see total number of donuts purchased by each customer in each month. So the month when the sale happen becomes a column in the pivot table, the customer is the grouping criteria and the aggregate value is the number of donuts sold to this customer in this month.

DataFrame qtyByCustomerAndMonth = donutSales.pivot(
        Lists.immutable.of("Customer"),
        "Month",
        Lists.immutable.of(sum("Qty")));

qtyByCustomerAndMonth

It is possible to aggregate more than one value in a pivot table, then the pivot table will contain as many columns per each pivot column value as there are aggregations (in this case for each month there will be two columns):

DataFrame qtyAndAmountByCustomerAndMonth = donutSales.pivot(
        Lists.immutable.of("Customer"),
        "Month",
        Lists.immutable.of(sum("Qty"), sum("Amount")));

qtyAndAmountByCustomerAndMonth

Domain Specific Language

The framework supports a simple Domain Specific Language (DSL) for computed column expression and operations on data frames such as filtering.

Script

A DSL script is a sequence of one or more statements (see below for the kinds of statements supported). The result of executing a script is the value of the last statement (or expression) that was executed in the script.

Value Types

The language supports variable and literal values of the following types:

• string
• int - 32-bit integer values (up to 2^31 – 1)
• long - 64-bit integer values (up to 2^63 - 1)
• float - 32-bit floating point values
• double - 64-bit floating point values
• decimal - arbitrary precision (for all practical reasons) numbers
• date
• date-time
• vector
• boolean

There is no implicit type conversion of values and variables to avoid inadvertent errors, to minimize surprising results, and to fail early.

Literals

The following are examples of literals

Variables

The variables in the DSL are immutable - once assigned, the value of the variable cannot change. This helps avoid errors arising from reusing variables.

A variable type is inferred at runtime and doesn't need to be declared.

Examples:

x = 123
123 in (x - 1, x, x + 1) ? 'in' : 'out'
a = "Hello, "
b = "there"
substr(a + b, 3)

Expressions

Statements

The following statements are available:

• assignment
• conditional
• a free-standing expression

Functions

There are two types of functions - intrinsic (built-in) and explicitly declared using the DSL function declaration.

Recursion (direct or indirect) is not supported.

Built-in functions

User Declared Function Example 1

function abs(x)
{
  if x > 0 then
    x
  else
    -x
  endif
}
abs(-123)

User Declared Function Example 2

function hello()
{
  'Hello'
}

hello() + ' world!'