qframe: A Go repository from oflebbe

QFrame is an immutable data frame that support filtering, aggregation and data manipulation. Any operation on a QFrame results in a new QFrame, the original QFrame remains unchanged. This can be done fairly efficiently since much of the underlying data will be shared between the two frames.

The design of QFrame has mainly been driven by the requirements from qocache but it is in many aspects a general purpose data frame. Any suggestions for added/improved functionality to support a wider scope is always of interest as long as they don't conflict with the requirements from qocache! See Contribute.

Installation

go get github.com/tobgu/qframe

Usage

Below are some examples of common use cases. The list is not exhaustive in any way. For a complete description of all operations including more examples see the docs.

IO

QFrames can currently be read from and written to CSV, record oriented JSON, and any SQL database supported by the go database/sql driver.

CSV Data

Read CSV data:

input := `COL1,COL2
a,1.5
b,2.25
c,3.0`

f := qframe.ReadCSV(strings.NewReader(input))
fmt.Println(f)

Output:

COL1(s) COL2(f)
------- -------
      a     1.5
      b    2.25
      c       3

Dims = 2 x 3

SQL Data

QFrame supports reading and writing data from the standard library database/sql drivers. It has been tested with SQLite, Postgres, and MariaDB.

SQLite Example

Load data to and from an in-memory SQLite database. Note that this example requires you to have go-sqlite3 installed prior to running.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
	"github.com/tobgu/qframe"
	qsql "github.com/tobgu/qframe/config/sql"
)

func main() {
	// Create a new in-memory SQLite database.
	db, _ := sql.Open("sqlite3", ":memory:")
	// Add a new table.
	db.Exec(`
	CREATE TABLE test (
		COL1 INT,
		COL2 REAL,
		COL3 TEXT,
		COL4 BOOL
	);`)
	// Create a new QFrame to populate our table with.
	qf := qframe.New(map[string]interface{}{
		"COL1": []int{1, 2, 3},
		"COL2": []float64{1.1, 2.2, 3.3},
		"COL3": []string{"one", "two", "three"},
		"COL4": []bool{true, true, true},
	})
	fmt.Println(qf)
	// Start a new SQL Transaction.
	tx, _ := db.Begin()
	// Write the QFrame to the database.
	qf.ToSQL(tx,
		// Write only to the test table
		qsql.Table("test"),
		// Explicitly set SQLite compatibility.
		qsql.SQLite(),
	)
	// Create a new QFrame from SQL.
	newQf := qframe.ReadSQL(tx,
		// A query must return at least one column. In this 
		// case it will return all of the columns we created above.
		qsql.Query("SELECT * FROM test"),
		// SQLite stores boolean values as integers, so we
		// can coerce them back to bools with the CoercePair option.
		qsql.Coerce(qsql.CoercePair{Column: "COL4", Type: qsql.Int64ToBool}),
		qsql.SQLite(),
	)
	fmt.Println(newQf)
	fmt.Println(newQf.Equals(qf))
}

Output:

COL1(i) COL2(f) COL3(s) COL4(b)
------- ------- ------- -------
      1     1.1     one    true
      2     2.2     two    true
      3     3.3   three    true

Dims = 4 x 3
true

Filtering

Filtering can be done either by applying individual filters to the QFrame or by combining filters using AND and OR.

Filter with OR-clause:

f := qframe.New(map[string]interface{}{"COL1": []int{1, 2, 3}, "COL2": []string{"a", "b", "c"}})
newF := f.Filter(qframe.Or(
    qframe.Filter{Column: "COL1", Comparator: ">", Arg: 2},
    qframe.Filter{Column: "COL2", Comparator: "=", Arg: "a"}))
fmt.Println(newF)

Output:

COL1(i) COL2(s)
------- -------
      1       a
      3       c

Dims = 2 x 2

Grouping and aggregation

Grouping and aggregation is done in two distinct steps. The function used in the aggregation step takes a slice of elements and returns an element. For floats this function signature matches many of the statistical functions in Gonum, these can hence be applied directly.

intSum := func(xx []int) int {
    result := 0
    for _, x := range xx {
        result += x
    }
    return result
}

f := qframe.New(map[string]interface{}{"COL1": []int{1, 2, 2, 3, 3}, "COL2": []string{"a", "b", "c", "a", "b"}})
f = f.GroupBy(groupby.Columns("COL2")).Aggregate(qframe.Aggregation{Fn: intSum, Column: "COL1"})
fmt.Println(f.Sort(qframe.Order{Column: "COL2"}))

Output:

COL2(s) COL1(i)
------- -------
      a       4
      b       5
      c       2

Dims = 2 x 3

Data manipulation

There are two different functions by which data can be manipulated, Apply and Eval. Eval is slightly more high level and takes a more data driven approach but basically boils down to a bunch of Apply in the end.

Example using Apply to string concatenate two columns:

f := qframe.New(map[string]interface{}{"COL1": []int{1, 2, 3}, "COL2": []string{"a", "b", "c"}})
f = f.Apply(
    qframe.Instruction{Fn: function.StrI, DstCol: "COL1", SrcCol1: "COL1"},
    qframe.Instruction{Fn: function.ConcatS, DstCol: "COL3", SrcCol1: "COL1", SrcCol2: "COL2"})
fmt.Println(f.Select("COL3"))

Output:

COL3(s)
-------
     1a
     2b
     3c

Dims = 1 x 3

The same example using Eval instead:

f := qframe.New(map[string]interface{}{"COL1": []int{1, 2, 3}, "COL2": []string{"a", "b", "c"}})
f = f.Eval("COL3", qframe.Expr("+", qframe.Expr("str", types.ColumnName("COL1")), types.ColumnName("COL2")))
fmt.Println(f.Select("COL3"))

More usage examples

Examples of the most common operations are available in the docs.

Error handling

All operations that may result in errors will set the Err variable on the returned QFrame to indicate that an error occurred. The presence of an error on the QFrame will prevent any future operations from being executed on the frame (eg. it follows a monad-like pattern). This allows for smooth chaining of multiple operations without having to explicitly check errors between each operation.

Configuration parameters

API functions that require configuration parameters make use of functional options to allow more options to be easily added in the future in a backwards compatible way.

Design goals

Performance
- Speed should be on par with, or better than, Python Pandas for corresponding operations.
- No or very little memory overhead per data element.
- Performance impact of operations should be straight forward to reason about.
API
- Should be reasonably small and low ceremony.
- Should allow custom, user provided, functions to be used for data processing
- Should provide built in functions for most common operations

High level design

A QFrame is a collection of columns which can be of type int, float, string, bool or enum. For more information about the data types see the types docs.

In addition to the columns there is also an index which controls which rows in the columns that are part of the QFrame and the sort order of these columns. Many operations on QFrames only affect the index, the underlying data remains the same.

Many functions and methods in qframe take the empty interface as parameter, for functions to be applied or string references to internal functions for example. These always correspond to a union/sum type with a fixed set of valid types that are checked in runtime through type switches (there's hardly any reflection applied in QFrame for performance reasons). Which types are valid depends on the function called and the column type that is affected. Modelling this statically is hard/impossible in Go, hence the dynamic approach. If you plan to use QFrame with datasets with fixed layout and types it should be a small task to write tiny wrappers for the types you are using to regain static type safety.

Limitations

The API can still not be considered stable.
The maximum number of rows in a QFrame is 4294967296 (2^32).
The CSV parser only handles ASCII characters as separators.
Individual strings cannot be longer than 268 Mb (2^28 byte).
A string column cannot contain more than a total of 34 Gb (2^35 byte).
At the moment you cannot rely on any of the errors returned to fulfill anything else than the Error interface. In the future this will hopefully be improved to provide more help in identifying the root cause of errors.

Performance/benchmarks

There are a number of benchmarks in qbench comparing QFrame to Pandas and Gota where applicable.

Other data frames

The work on QFrame has been inspired by Python Pandas and Gota.

Contribute

Want to contribute? Great! Open an issue on Github and let the discussions begin! Below are some instructions for working with the QFrame repo.

Ideas for further work

Below are some ideas of areas where contributions would be welcome.

Support for more input and output formats.
Support for additional column formats.
Support for using the Arrow format for columns.
General CPU and memory optimizations.
Improve documentation.
More analytical functionality.
Dataset joins.
Improved interoperability with other libraries in the Go data science eco system.
Improve string representation of QFrames.

Install dependencies

make dev-deps

Tests

Please contribute tests together with any code. The tests should be written against the public API to avoid lockdown of the implementation and internal structure which would make it more difficult to change in the future.

Run tests: make test

This will also trigger code to be regenerated.

Code generation

The codebase contains some generated code to reduce the amount of duplication required for similar functionality across different column types. Generated code is recognized by file names ending with _gen.go. These files must never be edited directly.

To trigger code generation: make generate

oflebbe/qframe

Installation

Usage

IO

CSV Data

SQL Data

SQLite Example

Filtering

Grouping and aggregation

Data manipulation

More usage examples

Error handling

Configuration parameters

Design goals

High level design

Limitations

Performance/benchmarks

Other data frames

Contribute

Ideas for further work

Install dependencies

Tests

Code generation