Joins and annotation¶

Combining two tables.

Join¶

join ¶

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

Join two Daf instances on their keyfields, as in SQL.

Both need a keyfield, and it must be a single column. A composite keyfield, a tuple of columns, raises KeyError. A row of this Daf is joined with the row of other_daf that has the same key.

The join is one to one. It is not a many to many join, as in SQL. Each row of this Daf finds at most one row of the other, through its key index. If a key repeats in the other Daf, every match gets its last row. If a key repeats in this Daf, each of those rows is in the result. Check the keys first if they may repeat. See keys().

The types of join are:

inner    only the keys that are in both.
left     all keys of this Daf.
right    all keys of the other Daf.
outer    all keys of both.

Columns that only one Daf has are in the result. A column that both have is given the name of its source as a suffix, such as name_daf1, unless it is in shared_fields. The names are daf1 and daf2 if the instances have no names. For other names use custom_translator_daf, which you can start from derive_join_translator().

When a key has no match, its cells from the other side are NULL, the empty string. Pass fill=None to get None there, as earlier versions did. The keyfield of the result is the keyfield of this Daf. The result is a new Daf.

A row that only the other Daf has, in a right or outer join, comes after the rows of this Daf. It takes its key, and the columns in shared_fields, from the other Daf, since this Daf has no row for it. This holds when the keyfields have different names too. The key column of the result has the name of this Daf's keyfield.

Parameters:

Name	Type	Description	Default
`other_daf`	`'Daf'`	The Daf to join with.	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 Daf, except shared ones, gets a suffix.	`False`
`custom_translator_daf`	`'Daf' \| None`	A translator that sets all the names.	`None`
`diagnose`	`bool`	If True, print progress messages.	`False`
`name`	`str`	The name of the result.	`''`
`fill`	`Any`	The value for cells that have no match.	`NULL`

Returns:

Type	Description
`'Daf'`	The joined Daf.

Raises:

Type	Description
`ValueError`	`how` is not one of the four.
`KeysDisabledError`	A Daf has no keyfield.
`KeyError`	A keyfield is a tuple.

Examples:

>>> a = Daf(lol=[[1, 'Alice'], [2, 'Bob']], cols=['id', 'name'], keyfield='id')
>>> b = Daf(lol=[[1, 50], [3, 70]], cols=['id', 'salary'], keyfield='id')
>>> a.join(b)

| id | name  | salary |
| -: | ----: | -----: |
|  1 | Alice |     50 |

%% daf rows=1; cols=3; keyfield='id'; name=''

>>> a.join(b, how='left')

| id | name  | salary |
| -: | ----: | -----: |
|  1 | Alice |     50 |
|  2 |   Bob |        |

%% daf rows=2; cols=3; keyfield='id'; name=''

>>> a.join(b, how='left', fill=None)

| id | name  | salary |
| -: | ----: | -----: |
|  1 | Alice |     50 |
|  2 |   Bob |   None |

%% daf rows=2; cols=3; keyfield='id'; name=''

join_records `staticmethod` ¶

join_records(
    records, translator_daf, join_names_ls=None, fill=NULL
)

Combine one record from each table into one record, using a translator.

This is a static method, and the step that join() repeats. A record may be None, which gives fill for the columns of that side. A column that a record does not have also gets fill.

Parameters:

Name	Type	Description	Default
`records`	`List[T_ma \| None]`	The two records, in the order of the source names.	required
`translator_daf`	`'Daf'`	The translator, as from `derive_join_translator()`.	required
`join_names_ls`	`T_ls \| None`	The two source names. Needed only if the translator names more than two sources.	`None`
`fill`	`Any`	The value for a column that has no record, or that the record lacks. NULL by default.	`NULL`

Returns:

Type	Description
`T_da`	The combined record, as a dict.

Raises:

Type	Description
`ValueError`	The translator names more than two sources and `join_names_ls` is not given.

Examples:

>>> tr = Daf.derive_join_translator_daf('id', 'id', ['id', 'v'], ['id', 'w'], 'L', 'R')
>>> Daf.join_records([{'id': 1, 'v': 'a'}, {'id': 1, 'w': 'b'}], tr)
{'id': 1, 'v': 'a', 'w': 'b'}
>>> Daf.join_records([{'id': 1, 'v': 'a'}, None], tr)
{'id': 1, 'v': 'a', 'w': ''}
>>> Daf.join_records([{'id': 1, 'v': 'a'}, None], tr, fill=0)
{'id': 1, 'v': 'a', 'w': 0}

derive_join_translator ¶

derive_join_translator(
    other_daf,
    shared_fields=None,
    omit_other_cols=None,
    tag_other=False,
)

Work out how the columns of two Daf instances are named in a join.

The translator is a Daf with a row for each column of the result. Its columns are resolved_colname, source_name, source_colname and is_keyfield. A column name that both Daf instances have is given a suffix with the name of its source, such as name_daf1, unless it is a shared field. The names of the instances are daf1 and daf2 if they have none. The keyfields are always shared.

join() calls this. Call it yourself to see the names, or to edit the translator and give it back as custom_translator_daf.

Parameters:

Name	Type	Description	Default
`other_daf`	`'Daf'`	The Daf to join with.	required
`shared_fields`	`T_ls \| None`	Columns that both have and that appear only once.	`None`
`omit_other_cols`	`T_ls \| None`	Columns of the other Daf to leave out.	`None`
`tag_other`	`bool`	If True, every column of the other Daf, except shared ones, gets a suffix.	`False`

Returns:

Type	Description
`'Daf'`	The translator Daf. Its keyfield is `resolved_colname`.

Examples:

>>> a = Daf(lol=[[1, 'x']], cols=['id', 'name'], keyfield='id')
>>> b = Daf(lol=[[1, 'y']], cols=['id', 'name'], keyfield='id')
>>> a.derive_join_translator(b).col('resolved_colname')
['id', 'name_daf1', 'name_daf2']

derive_join_translator_daf `classmethod` ¶

derive_join_translator_daf(
    self_keyfield,
    other_keyfield,
    self_cols,
    other_cols,
    self_name="",
    other_name="",
    shared_fields=None,
    omit_other_cols=None,
    tag_other=False,
)

Work out a join translator from column names, with no Daf instances.

This is the form that does not need two Daf instances, so SQL joins can use it. See derive_join_translator() for what the translator holds.

Parameters:

Name	Type	Description	Default
`self_keyfield`	`str`	The keyfield of the first table.	required
`other_keyfield`	`str`	The keyfield of the other table.	required
`self_cols`	`T_cs`	The column names of the first table.	required
`other_cols`	`T_cs`	The column names of the other table.	required
`self_name`	`str`	The name of the first table.	`''`
`other_name`	`str`	The name of the other table.	`''`
`shared_fields`	`T_cs \| None`	Columns that both have and that appear only once. The list is not changed.	`None`
`omit_other_cols`	`T_cs \| None`	Columns of the other table to leave out.	`None`
`tag_other`	`bool`	If True, every column of the other table, except shared ones, gets a suffix.	`False`

Returns:

Type	Description
`'Daf'`	The translator Daf. Its keyfield is `resolved_colname`.

Examples:

>>> tr = Daf.derive_join_translator_daf('id', 'id', ['id', 'v'], ['id', 'w'], 'L', 'R')
>>> tr.columns()
['resolved_colname', 'source_name', 'source_colname', 'is_keyfield']
>>> tr

| resolved_colname | source_name | source_colname | is_keyfield |
| ---------------: | ----------: | -------------: | ----------: |
|               id |           L |             id |        True |
|                v |           L |              v |       False |
|                w |           R |              w |       False |

%% daf rows=3; cols=4; keyfield='resolved_colname'; name=''

>>> tagged = Daf.derive_join_translator_daf('id', 'id', ['id', 'v'], ['id', 'w'], 'L', 'R', tag_other=True)
>>> tagged.col('resolved_colname')
['id', 'v', 'w_R']
>>> omitted = Daf.derive_join_translator_daf('id', 'id', ['id', 'v'], ['id', 'w', 'x'], 'L', 'R', omit_other_cols=['x'])
>>> omitted.col('resolved_colname')
['id', 'v', 'w']

Annotate¶

annotate_daf ¶

annotate_daf(other_daf, my_to_other_dict)

Copy fields from another Daf into this one, row by row, matching on the key.

Both Daf instances need a keyfield. For each row here, the row with the same key in other_daf is found, and each field of this row named in my_to_other_dict gets the value of the other field. A key that is missing in other_daf raises KeyError. A field that is not a column here is added as a new column at the right.

Parameters:

Name	Type	Description	Default
`other_daf`	`'Daf'`	The Daf to copy from.	required
`my_to_other_dict`	`T_ds`	Maps the field to set here to the field to read there.	required

Returns:

Type	Description
`'Daf'`	This Daf, which has been changed.

Raises:

Type	Description
`KeyError`	A keyfield is not set, or a key is not found in `other_daf`.

Examples:

>>> a = Daf(lol=[[1, 'x'], [2, 'y']], cols=['id', 'v'], keyfield='id')
>>> o = Daf(lol=[[1, 'P'], [2, 'Q']], cols=['id', 'w'], keyfield='id')
>>> a.annotate_daf(o, {'v': 'w'})

| id | v  |
| -: | -: |
|  1 |  P |
|  2 |  Q |

%% daf rows=2; cols=2; keyfield='id'; name=''

Joins and annotation¶

Join¶

join ¶

join_records staticmethod ¶

derive_join_translator ¶

derive_join_translator_daf classmethod ¶

Annotate¶

annotate_daf ¶

join_records `staticmethod` ¶

derive_join_translator_daf `classmethod` ¶