Apply, reduce, group and join¶

Functions over rows, groups and counts, and joins.

Functions over rows¶

apply ¶

apply(func, by='row', keylist=None, **kwargs)

Apply a Python function to each row, and collect the results in a new table, as Daf.apply() does.

The function gets each row as a dict, with Python NULL for a missing value, and returns what to add to the new table, by the rules of Daf.apply():

A dict, or a KeyedList, adds one row.
A list of them adds a row for each, so one row can become several.
None, an empty dict or an empty list adds nothing, so the row is left out.

The new table takes its columns from the first row returned. A later row is placed by column name. A column it lacks gets Python NULL, and a name the first row did not have is dropped. So have the function return every column in every row. The new table has no keyfield and no dtypes, as the new Daf of Daf.apply() has none.

The rows are read 10,000 at a time, in order, and the results are written as they come, all in one transaction. So a table larger than memory can be converted, and memory does not grow. If the function raises, that error is raised, as itself, and nothing is kept. The new table is a TEMP table, as copy() makes. Keep it with save(name).

On 300,000 rows that each became 4, this took 23.1 s, most of it in the function. A registered SQL function cannot return a row, which is why the function runs in Python.

With by='table' the function gets this SqlDaf, and its result is returned as it is.

Parameters:

Name	Type	Description	Default
`func`	`Callable[..., Any]`	The function. It takes a row and the keyword arguments, or this SqlDaf for `by='table'`.	required
`by`	`str`	`row` to apply it to each row, or `table` to apply it to the whole SqlDaf.	`'row'`
`keylist`	`T_la \| None`	Not supported on a SqlDaf. Select the rows first.	`None`
`**kwargs`	`Any`	Keyword arguments passed on to the function.	`{}`

Returns:

Type	Description
`Any`	The new table, or the result of the function for `by='table'`.

Raises:

Type	Description
`NotImplementedError`	`keylist` is given, or `by` is not `row` or `table`.
`TypeError`	The function returns something other than a dict, a KeyedList, a list of them, or nothing.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> votes_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 'c0,c1'], ['b2', '']], cols=['ballot_id', 'contests']), name='votes')
>>> def split(row):
...     return [{'ballot_id': row['ballot_id'], 'contest': c} for c in row['contests'].split(',') if c]
>>> votes_sdaf.apply(split).to_daf().lol
[['b1', 'c0'], ['b1', 'c1']]

reduce ¶

reduce(
    func,
    by="row",
    cols=None,
    initial_da=None,
    indirect_col="",
    silent_error=False,
    **kwargs,
)

Combine all the rows into one result, using a function, as Daf.reduce() does.

The function is called in Python for each row, with the same arguments and the same result as in Daf.reduce(). A sum or a count is a reduction, as with Daf.sum_da. The rows are read 10,000 at a time, in order, so the table is never all in memory. Memory use still depends on the result the function builds.

With by='row', each row is a KeyedList of its values. Unlike in a Daf, writing to it does not change the table.
With by='sparse_row', the function gets the dict held in indirect_col, JSON or PYON text decoded as in a Daf, and the result starts as initial_da or an empty dict.
With by='table', the function gets this SqlDaf and cols.

For a plain total of columns, sum() is faster: SQLite adds them, and no row comes into Python. by='col' is not supported, since it needs each whole column in memory.

Parameters:

Name	Type	Description	Default
`func`	`Callable[..., Any]`	The reduction function, such as `Daf.sum_da`.	required
`by`	`str`	`row`, `sparse_row` or `table`.	`'row'`
`cols`	`T_ls \| None`	The columns included in the reduction. All columns if None.	`None`
`initial_da`	`T_da \| None`	The result to start from, instead of zeros.	`None`
`indirect_col`	`str`	A column that holds a dict. Required for `sparse_row`.	`''`
`silent_error`	`bool`	If True, a row for which the function raises is skipped.	`False`
`**kwargs`	`Any`	Keyword arguments passed on to the function.	`{}`

Returns:

Type	Description
`Any`	A dict for `row` and `sparse_row`. The function's result for `table`.

Raises:

Type	Description
`ValueError`	`sparse_row` is used with no `indirect_col`.
`NotImplementedError`	`by` is `col`, or not recognized.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> votes_sdaf = SqlDaf('votes', cols=['ballot_id', 'v', 'concise_votes'],
...                     lol=[['b1', 1, "{'c0b': 1, 'c0v': 1}"], ['b2', 2, '{"c0b": 1, "c1b": 1}']])
>>> votes_sdaf.reduce(Daf.sum_da, cols=['v'])
{'ballot_id': '', 'v': 3, 'concise_votes': ''}
>>> votes_sdaf.reduce(Daf.sum_da, by='sparse_row', indirect_col='concise_votes')
{'c0b': 2, 'c0v': 1, 'c1b': 1}

Groups¶

groupby ¶

groupby(
    colname="", colnames=None, omit_nulls=False, cols=None
)

Group the rows by the values of a column, as Daf.groupby() does.

The result is a KeyedList of selections, one for each value, in the order first seen. It works as a dict, as the result of Daf.groupby() does. One query finds the values. Each group is then select_by_dict() of its value, so Python NULL is a value like any other. Nothing is read until a group is read.

The first groupby on a column makes an index on it, in the parent table, unless an index already starts with it. Each group is then a lookup. Without the index, reading each group would need a full scan of the table. On 100,000 rows in 500 groups, summing every group took 2.3 s, against 14.8 s without it. The index stays with the table, and drop_cols() or rename_cols() of the column drops it.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column to group by.	`''`
`colnames`	`T_ls \| None`	Several columns to group by. See `groupby_cols()`.	`None`
`omit_nulls`	`bool`	If True, leave out the rows with Python NULL in the column.	`False`
`cols`	`T_ls \| None`	Not supported on a SqlDaf yet.	`None`

Returns:

Type	Description
`KeyedList`	A KeyedList that maps each value to a selection.

Raises:

Type	Description
`KeyError`	The column is not a column.
`NotImplementedError`	`cols` is given.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> bif_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 'P1'], ['b2', 'P2'], ['b3', 'P1']], cols=['ballot_id', 'precinct']), name='bif')
>>> groups = bif_sdaf.groupby('precinct')
>>> {key: group.to_daf().lol for key, group in groups.items()}
{'P1': [['b1', 'P1'], ['b3', 'P1']], 'P2': [['b2', 'P2']]}

groupby_cols ¶

groupby_cols(colnames, cols=None, omit_nulls=False)

Group the rows by the values of several columns, as Daf.groupby_cols() does.

Each key is a tuple of the values, even for one column. Each group is a selection, as in groupby().

Parameters:

Name	Type	Description	Default
`colnames`	`T_ls`	The columns to group by.	required
`cols`	`T_ls \| None`	Not supported on a SqlDaf yet.	`None`
`omit_nulls`	`bool`	If True, leave out the rows with Python NULL in any of the columns.	`False`

Returns:

Type	Description
`KeyedList`	A KeyedList that maps each tuple of values to a selection.

Raises:

Type	Description
`KeyError`	A name in `colnames` is not a column.
`NotImplementedError`	`cols` is given.

group_where ¶

group_where(where, *, indirect_col=None, cols=None)

Group the rows by the keys a Python function returns, as Daf.group_where() does.

The function gets each row as a KeyedList, as in select_where(), and returns a key, a list of keys, or None. A row goes into the group of each key, so a row can be in any number of groups, and None leaves it out. As in Daf.group_where(), any iterable other than a str is a list of keys, a tuple too. So to use a tuple as one key, return a list of tuples.

The function runs once for each row, in one pass, and each (key, row) pair is written to a TEMP table. Each group is then a selection of the rows listed under its key, in the order of this SqlDaf. Nothing more is read until a group is read. The result is a KeyedList of selections, in the order the keys were first returned.

Name the columns the function reads in cols, as in select_where().

Parameters:

Name	Type	Description	Default
`where`	`Callable[[Any], Any]`	A function of a row: a key, a list of keys, or None.	required
`indirect_col`	`str \| None`	Not supported on a SqlDaf yet.	`None`
`cols`	`T_ls \| None`	The columns the function reads. All if None.	`None`

Returns:

Type	Description
`KeyedList`	A KeyedList that maps each key to a selection.

Raises:

Type	Description
`KeyError`	A name in `cols` is not a column.
`NotImplementedError`	`indirect_col` is given.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> bif_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 'P1'], ['b2', 'P2'], ['b3', 'P1']], cols=['ballot_id', 'precinct']), name='bif')
>>> groups = bif_sdaf.group_where(lambda row: [row['precinct'], 'all'], cols=['precinct'])
>>> {key: len(group) for key, group in groups.items()}
{'P1': 2, 'all': 3, 'P2': 1}

groupsum_daf ¶

groupsum_daf(colname, by='row', reduce_cols=None)

Group by a column and add up the other columns of each group, as Daf.groupsum_daf() does.

One GROUP BY does it all in SQLite. The result is a Daf in memory, with one row for each value, in the order first seen, and colname as its keyfield. As in a Daf, only numbers are added: text, even text of digits, counts as 0, and a column with no numbers in a group totals 0. A column not in reduce_cols is Python NULL.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column to group by.	required
`by`	`str`	Only `row` is supported.	`'row'`
`reduce_cols`	`T_ls \| None`	The columns to add. All but `colname` if None.	`None`

Returns:

Type	Description
`Daf`	A Daf in memory, with one row for each group.

Raises:

Type	Description
`KeyError`	`colname` is not a column.
`NotImplementedError`	`by` is not `row`.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> nums_sdaf = SqlDaf.from_daf(Daf(lol=[['a', 1, 10], ['b', 2, 20], ['a', 3, 30]], cols=['g', 'x', 'y']), name='nums')
>>> nums_sdaf.groupsum_daf('g', reduce_cols=['y']).lol
[['a', '', 40], ['b', '', 20]]

sum ¶

sum(colnames_ls=None, numeric_only=False)

Total the columns, as Daf.sum() does, and return a dict of the totals.

Numbers are totalled by SQLite. A column that also holds text has its text cells read into Python and added as Daf.sum() adds them: text that is not a number raises ValueError, or counts as 0 with numeric_only. Python NULL is skipped. Each total starts as a float, and is converted to the dtype of its column if there is one.

Parameters:

Name	Type	Description	Default
`colnames_ls`	`T_ls \| None`	The columns to total. All if None.	`None`
`numeric_only`	`bool`	If True, total only the columns with an int or float dtype, and count text that is not a number as 0.	`False`

Returns:

Type	Description
`T_da`	A dict that maps each column name to its total.

Raises:

Type	Description
`ValueError`	A text cell cannot be converted to a number.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> SqlDaf.from_daf(Daf(lol=[[1, 10], [2, 20]], cols=['x', 'y']), name='nums').sum()
{'x': 3.0, 'y': 30.0}

Counts¶

valuecounts_for_colname ¶

valuecounts_for_colname(
    colname, sort=False, reverse=True, omit_nulls=False
)

Count how often each value occurs in one column, as Daf.valuecounts_for_colname() does.

One GROUP BY counts them in SQLite, so no column is read into Python. The values are in the order first seen, or by count with sort. Python NULL is counted as Python NULL, unless omit_nulls. A column that does not exist gives an empty dict.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column to count.	required
`sort`	`bool`	If True, order by count.	`False`
`reverse`	`bool`	With `sort`, True puts the most common first.	`True`
`omit_nulls`	`bool`	If True, leave out the count of Python NULL.	`False`

Returns:

Type	Description
`T_di`	A dict that maps each value to its count.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> bif_sdaf = SqlDaf.from_daf(Daf(lol=[['b1', 'P1'], ['b2', 'P2'], ['b3', 'P1']], cols=['ballot_id', 'precinct']), name='bif')
>>> bif_sdaf.valuecounts_for_colname('precinct', sort=True)
{'P1': 2, 'P2': 1}

valuecounts_for_colnames_ls ¶

valuecounts_for_colnames_ls(
    colnames_ls=None,
    sort=False,
    reverse=True,
    omit_nulls=False,
)

Count how often each value occurs, in each of several columns, as Daf.valuecounts_for_colnames_ls() does.

It is one GROUP BY for each column. See valuecounts_for_colname().

Parameters:

Name	Type	Description	Default
`colnames_ls`	`T_ls \| None`	The columns to count. All if None.	`None`
`sort`	`bool`	If True, order each count by size.	`False`
`reverse`	`bool`	With `sort`, True puts the most common first.	`True`
`omit_nulls`	`bool`	If True, leave out the count of Python NULL.	`False`

Returns:

Type	Description
`Dict[str, T_di]`	A dict that maps each column to a dict of value and count.

valuecounts_for_colname_selectedby_colname ¶

valuecounts_for_colname_selectedby_colname(
    colname,
    selectedby_colname,
    selectedby_colvalue,
    sort=False,
    reverse=True,
)

Count the values of a column, in the rows where another column has a value, as the Daf method does.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column to count.	required
`selectedby_colname`	`str`	The column to test.	required
`selectedby_colvalue`	`Any`	Only rows where that column has this value are counted. Python NULL matches Python NULL.	required
`sort`	`bool`	If True, order the counts by size.	`False`
`reverse`	`bool`	With `sort`, True puts the most common first.	`True`

Returns:

Type	Description
`T_di`	A dict that maps each value to its count. It is empty if a column does not exist.

valuecounts_for_colnames_ls_selectedby_colname ¶

valuecounts_for_colnames_ls_selectedby_colname(
    colnames_ls=None,
    selectedby_colname="",
    selectedby_colvalue="",
    sort=False,
    reverse=True,
)

Count the values of several columns, in the rows where another column has a value, as the Daf method does.

Parameters:

Name	Type	Description	Default
`colnames_ls`	`T_ls \| None`	The columns to count. All if None.	`None`
`selectedby_colname`	`str`	The column to test.	`''`
`selectedby_colvalue`	`Any`	Only rows where that column has this value are counted.	`''`
`sort`	`bool`	If True, order the counts by size.	`False`
`reverse`	`bool`	With `sort`, True puts the most common first.	`True`

Returns:

Type	Description
`Dict[str, T_di]`	A dict that maps each column to a dict of value and count.

valuecounts_for_colname1_groupedby_colname2 ¶

valuecounts_for_colname1_groupedby_colname2(
    colname1, groupedby_colname2, sort=False, reverse=True
)

Count the values of one column, for each value of another, as the Daf method does.

One GROUP BY of the two columns counts them all. The groups, and the values in each, are in the order first seen. Use it to see whether two columns relate one to one: each group should then hold a single value.

Parameters:

Name	Type	Description	Default
`colname1`	`str`	The column whose values are counted.	required
`groupedby_colname2`	`str`	The column whose values form the groups.	required
`sort`	`bool`	If True, order each count by size.	`False`
`reverse`	`bool`	With `sort`, True puts the most common first.	`True`

Returns:

Type	Description
`Dict[Any, T_di]`	A dict that maps each value of the second column to a dict of value and count.

value_counts_daf ¶

value_counts_daf(
    colname,
    sort=False,
    reverse=True,
    include_total=False,
    omit_nulls=False,
)

Count the values of a column into a Daf in memory, with one row per value, as Daf.value_counts_daf() does.

The counts come from valuecounts_for_colname(). The Daf has the columns colname and counts, and a row for each value.

Parameters:

Name	Type	Description	Default
`colname`	`str`	The column to count.	required
`sort`	`bool`	If True, order by count.	`False`
`reverse`	`bool`	With `sort`, True puts the most common first.	`True`
`include_total`	`bool`	If True, add a row with the total.	`False`
`omit_nulls`	`bool`	If True, leave out the count of Python NULL.	`False`

Returns:

Type	Description
`Daf`	A Daf in memory, with a row for each value.

daf_valuecount ¶

daf_valuecount(by='row', cols=None)

Count how often each value occurs, in each column, as Daf.daf_valuecount() does.

It gives the same result: a column that is not in cols gets Python NULL in place of its counts, and a table with no rows gives an empty dict. Each column is one GROUP BY.

Parameters:

Name	Type	Description	Default
`by`	`str`	Only `row` is supported.	`'row'`
`cols`	`T_ls \| None`	The columns to count. All if None.	`None`

Returns:

Type	Description
`T_da`	A dict that maps each column to a dict of value and count.

Raises:

Type	Description
`NotImplementedError`	`by` is not `row`.

Joins¶

join ¶

join(
    other_daf,
    how="inner",
    shared_fields=None,
    tag_other=False,
    custom_translator_daf=None,
    diagnose=False,
    name="",
    fill=NULL,
)

Join two SqlDaf tables on their keyfields, as Daf.join() does, in SQL.

The columns are named exactly as Daf.join() names them, by the same translator: a column that both have gets the name of its source as a suffix, such as name_audit, unless it is in shared_fields, and tag_other suffixes every column of the other. The names are those of the parent tables, or daf1 and daf2 if they are the same. The rows come as in Daf.join(): in this table's order, and for right and outer, the other table's unmatched rows after, in its order. A cell with no match is Python NULL. The keyfield of the result is this table's.

The keyfields must be unique, as in Daf.join(). A repeated key is not checked, and the result is then undefined. Check the keys first if they may repeat. A Python NULL key is undefined too: see SqlDaf.

A row found only in the other table, in a right or outer join, takes its key and its shared fields from the other table, as in Daf.join().

Nothing runs yet. The join is resolved the first time it is read, into a TEMP table of new rows, as Daf.join() makes new rows. A selection made from it resolves it first.

Parameters:

Name	Type	Description	Default
`other_daf`	`SqlDaf`	The SqlDaf to join with, in the same database file.	required
`how`	`str`	`inner`, `left`, `right` or `outer`.	`'inner'`
`shared_fields`	`T_ls \| None`	Columns that both have and that appear only once.	`None`
`tag_other`	`bool`	If True, every column of the other, except shared ones, gets a suffix.	`False`
`custom_translator_daf`	`Daf \| None`	Not supported on a SqlDaf yet.	`None`
`diagnose`	`bool`	Not used.	`False`
`name`	`str`	The name of the result table. Generated if empty.	`''`
`fill`	`Any`	The value for cells that have no match. Only Python NULL is supported.	`NULL`

Returns:

Type	Description
`SqlDaf`	A SqlDaf of the joined rows, resolved when first read.

Raises:

Type	Description
`ValueError`	`how` is not one of the four, or a table has no keyfield.
`NotImplementedError`	`custom_translator_daf` or a `fill` other than Python NULL, or a SqlDaf this connection cannot read.

Examples:

>>> import tempfile
>>> SqlDaf.db_path = tempfile.mkdtemp() + '/job.db'
>>> a_sdaf = SqlDaf.from_daf(Daf(lol=[[1, 'Alice'], [2, 'Bob']], cols=['id', 'name'], keyfield='id'), name='a')
>>> b_sdaf = SqlDaf.from_daf(Daf(lol=[[1, 50], [3, 70]], cols=['id', 'salary'], keyfield='id'), name='b')
>>> a_sdaf.join(b_sdaf, how='left').to_daf().lol
[[1, 'Alice', 50], [2, 'Bob', '']]