You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "judahrand (via GitHub)" <gi...@apache.org> on 2023/02/17 12:27:39 UTC
[GitHub] [arrow] judahrand opened a new pull request, #34234: [Python] Add `join_asof` binding
judahrand opened a new pull request, #34234:
URL: https://github.com/apache/arrow/pull/34234
<!--
Thanks for opening a pull request!
If this is your first pull request you can find detailed information on how
to contribute here:
* [New Contributor's Guide](https://arrow.apache.org/docs/dev/developers/guide/step_by_step/pr_lifecycle.html#reviews-and-merge-of-the-pull-request)
* [Contributing Overview](https://arrow.apache.org/docs/dev/developers/overview.html)
If this is not a [minor PR](https://github.com/apache/arrow/blob/master/CONTRIBUTING.md#Minor-Fixes). Could you open an issue for this pull request on GitHub? https://github.com/apache/arrow/issues/new/choose
Opening GitHub issues ahead of time contributes to the [Openness](http://theapacheway.com/open/#:~:text=Openness%20allows%20new%20users%20the,must%20happen%20in%20the%20open.) of the Apache Arrow project.
Then could you also rename the pull request title in the following format?
GH-${GITHUB_ISSUE_ID}: [${COMPONENT}] ${SUMMARY}
or
MINOR: [${COMPONENT}] ${SUMMARY}
In the case of PARQUET issues on JIRA the title also supports:
PARQUET-${JIRA_ISSUE_ID}: [${COMPONENT}] ${SUMMARY}
-->
### Rationale for this change
<!--
Why are you proposing this change? If this is already explained clearly in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand your changes and offer better suggestions for fixes.
-->
### What changes are included in this PR?
<!--
There is no need to duplicate the description in the issue here but it is sometimes worth providing a summary of the individual changes in this PR.
-->
### Are these changes tested?
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example, are they covered by existing tests)?
-->
### Are there any user-facing changes?
<!--
If there are user-facing changes then we may require documentation to be updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please uncomment the line below and explain which changes are breaking.
-->
<!-- **This PR includes breaking changes to public APIs.** -->
<!--
Please uncomment the line below (and provide explanation) if the changes fix either (a) a security vulnerability, (b) a bug that caused incorrect or invalid data to be produced, or (c) a bug that causes a crash (even when the API contract is upheld). We use this to highlight fixes to issues that may affect users without their knowledge. For this reason, fixing bugs that cause errors don't count, since those are usually obvious.
-->
<!-- **This PR contains a "Critical Fix".** -->
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1446093696
@AlenkaF Do you need me to do anything else on this PR?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1510947458
##########
python/pyarrow/table.pxi:
##########
@@ -4859,6 +4859,91 @@ cdef class Table(_Tabular):
output_type=Table
)
+ def join_asof(self, right_table, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this table and another one.
+
+ Result of the join will be a new Table, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_table : Table
+ The table to join to the current one, acting as the right table
+ in the join operation.
+ on : str
+ The column from current table that should be used as the on key
+ of the join operation left side.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ by : str or list[str]
+ The columns from current table that should be used as the keys
+ of the join operation left side. The join operation is then done
+ only for the matches in these columns.
+ tolerance : int
+ The tolerance for inexact "on" key matching. A right row is considered
+ a match with the left row ``right.on - left.on <= tolerance``. The
+ ``tolerance`` may be:
+
+ - negative, in which case a past-as-of-join occurs;
+ - or positive, in which case a future-as-of-join occurs;
+ - or zero, in which case an exact-as-of-join occurs.
+
+ The tolerance is interpreted in the same units as the "on" key.
+ right_on : str or list[str], default None
+ The columns from the right_table that should be used as the on key
+ on the join operation right side.
+ When ``None`` use the same key name as the left table.
+ right_by : str or list[str], default None
+ The columns from the right_table that should be used as keys
+ on the join operation right side.
+ When ``None`` use the same key names as the left table.
+
+ Returns
+ -------
+ Table
+
+ Example
+ --------
+ >>> import pandas as pd
+ >>> import pyarrow as pa
+ >>> df1 = pd.DataFrame({'id': [1, 2, 3],
+ ... 'year': [2020, 2022, 2019]})
+ >>> df2 = pd.DataFrame({'id': [3, 4],
+ ... 'year': [2020, 2021],
+ ... 'n_legs': [5, 100],
+ ... 'animal': ["Brittle stars", "Centipede"]})
+ >>> t1 = pa.Table.from_pandas(df1).sort_by('year')
+ >>> t2 = pa.Table.from_pandas(df2).sort_by('year')
+
+ >>> t1.join_asof(
+ ... t2, on='year', by='id', tolerance=1
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/0553f1dcb1c556729839e36b59e7fc84de2c5faa
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1438129090
The Appveyor failure looks like a timeout?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1878851750
@jorisvandenbossche ping?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1510908728
##########
python/pyarrow/tests/test_exec_plan.py:
##########
@@ -323,6 +323,83 @@ def test_join_extension_array_column():
assert result["colB"] == pa.chunked_array(ext_array)
+@pytest.mark.parametrize("tolerance,expected", [
+ (
+ 1,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, None, None],
+ },
+ ),
+ (
+ 3,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, 3., None],
+ },
+ ),
+ (
+ -5,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [None, None, 1., None, None],
+ },
+ ),
+])
+@pytest.mark.parametrize("use_datasets", [False, True])
+def test_join_asof(tolerance, expected, use_datasets):
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/5fcce5f689d4ff42af13baec2816f8e49345e075
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] github-actions[bot] commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1434576475
* Closes: #34235
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1118630021
##########
python/pyarrow/_dataset.pyx:
##########
@@ -514,6 +514,53 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
+ tolerance : int
Review Comment:
If the "on" key is a timestamp column, what value can be used here? (not an int?)
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
Those `left_columns`/`right_columns` is not really used, except for checking the column collisions?
What happens if we don't check for this here in cython and there actually is a column collision? Does the C++ implementation give an error for that as well?
##########
python/pyarrow/table.pxi:
##########
@@ -4993,6 +4993,78 @@ cdef class Table(_PandasConvertible):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=Table)
+ def join_asof(self, right_table, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this table and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_table : Table
+ The table to join to the current one, acting as the right table
+ in the join operation.
+ on : str
+ The column from current table that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current table that should be used as the by keys
+ of the join operation left side.
+ tolerance : int
+ The tolerance for inexact "on" key matching. A right row is considered
+ a match with the left row ``right.on - left.on <= tolerance``. The
+ ``tolerance`` may be:
+ - negative, in which case a past-as-of-join occurs;
+ - or positive, in which case a future-as-of-join occurs;
+ - or zero, in which case an exact-as-of-join occurs.
+
+ The tolerance is interpreted in the same units as the "on" key.
+ right_on : str or list[str], default None
+ The columns from the right_table that should be used as the on key
+ on the join operation right side.
+ When ``None`` use the same key name as the left table.
+ right_by : str or list[str], default None
+ The columns from the right_table that should be used as by keys
+ on the join operation right side.
+ When ``None`` use the same key names as the left table.
+
+ Returns
+ -------
+ Table
+
+ Example
+ --------
+ >>> import pandas as pd
+ >>> import pyarrow as pa
+ >>> df1 = pd.DataFrame({'id': [1, 2, 3],
+ ... 'year': [2020, 2022, 2019]})
+ >>> df2 = pd.DataFrame({'id': [3, 4],
+ ... 'year': [2020, 2021],
+ ... 'n_legs': [5, 100],
+ ... 'animal': ["Brittle stars", "Centipede"]})
+ >>> t1 = pa.Table.from_pandas(df1).sort_by('year')
+ >>> t2 = pa.Table.from_pandas(df2).sort_by('year')
+
+ >>> t1.join_asof(t2, 'year', 'id', 1).combine_chunks().sort_by('year')
Review Comment:
Can you use explicit keyword names (except for the first table argument)? I think that will make it easier to understand what those arguments are.
##########
python/pyarrow/tests/test_dataset.py:
##########
@@ -4839,6 +4839,57 @@ def test_dataset_join_collisions(tempdir):
], names=["colA", "colB", "colVals", "colB_r", "colVals_r"])
+@pytest.mark.dataset
+def test_dataset_join_asof(tempdir):
+ t1 = pa.Table.from_pydict({
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"]
+ })
+ ds.write_dataset(t1, tempdir / "t1", format="ipc")
+ ds1 = ds.dataset(tempdir / "t1", format="ipc")
+
+ t2 = pa.Table.from_pydict({
+ "colB": [2, 9, 15],
+ "col3": ["a", "b", "g"],
+ "colC": [1., 3., 5.]
+ })
+ ds.write_dataset(t2, tempdir / "t2", format="ipc")
+ ds2 = ds.dataset(tempdir / "t2", format="ipc")
+
+ result = ds1.join_asof(ds2, "colA", "col2", 1, "colB", "col3")
Review Comment:
Also here, can you use keyword names instead of positional arguments (except for the first argument) to make it a bit more readable?
##########
python/pyarrow/tests/test_table.py:
##########
@@ -2297,6 +2297,49 @@ def test_table_join_many_columns():
})
+@pytest.mark.dataset
+def test_table_join_asof():
+ t1 = pa.Table.from_pydict({
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"]
+ })
+
+ t2 = pa.Table.from_pydict({
+ "colB": [2, 9, 15],
+ "col3": ["a", "b", "g"],
+ "colC": [1., 3., 5.]
+ })
+
+ r = t1.join_asof(t2, "colA", "col2", 1, "colB", "col3")
Review Comment:
Some additional test case ideas to ensure good coverage:
- A test where the left/right column names are the same, so you can rely on not having to specify right_on/by
- A test where the `by` keys is a list of columns instead of a single one
##########
python/pyarrow/_dataset.pyx:
##########
@@ -514,6 +514,53 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
+ tolerance : int
+ The tolerance for inexact "on" key matching. A right row is considered
+ a match with the left row `right.on - left.on <= tolerance`. The
+ `tolerance` may be:
+ - negative, in which case a past-as-of-join occurs;
+ - or positive, in which case a future-as-of-join occurs;
+ - or zero, in which case an exact-as-of-join occurs.
Review Comment:
```suggestion
- negative, in which case a past-as-of-join occurs;
- or positive, in which case a future-as-of-join occurs;
- or zero, in which case an exact-as-of-join occurs.
```
Formatting nitpick: restructuredtext is quite strict about this, and so a list shouldn't use indentation but requires a blank line before and after
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1711783475
@jorisvandenbossche Sorry this took me so long to revisit! I think I've dealt with all of your comments?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "AlenkaF (via GitHub)" <gi...@apache.org>.
AlenkaF commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1776630532
Thanks!
AppVeyor failure is not connected - seems to be failing elsewhere also. Opened https://github.com/apache/arrow/issues/38431
I have re-run `C GLib & Ruby` job, don't think the failure is connected though.
Otherwise this looks good to me 👍 @jorisvandenbossche would you like to have a look before we merge?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche merged PR #34234:
URL: https://github.com/apache/arrow/pull/34234
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] AlenkaF commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "AlenkaF (via GitHub)" <gi...@apache.org>.
AlenkaF commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1436340748
> I'm struggling to run the tests locally as I can't get Arrow to build on an M1 Mac.
Can you try adding `-DGTest_SOURCE="BUNDLED"` CMake flag? See https://github.com/apache/arrow/issues/14917
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1453485713
@judahrand I merged https://github.com/apache/arrow/pull/34102, so you should be able to add a `AsofJoinNodeOptions` subclass, and then update `_perform_join_asof` to use that options class together with Declaration (similar as I am doing in https://github.com/apache/arrow/pull/3440)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1118639807
##########
python/pyarrow/tests/test_table.py:
##########
@@ -2297,6 +2297,49 @@ def test_table_join_many_columns():
})
+@pytest.mark.dataset
+def test_table_join_asof():
+ t1 = pa.Table.from_pydict({
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"]
+ })
+
+ t2 = pa.Table.from_pydict({
+ "colB": [2, 9, 15],
+ "col3": ["a", "b", "g"],
+ "colC": [1., 3., 5.]
+ })
+
+ r = t1.join_asof(t2, "colA", "col2", 1, "colB", "col3")
Review Comment:
Some additional test case ideas to ensure good coverage:
- A test where the left/right column names are the same, so you can rely on not having to specify right_on/by
- A test where the `by` keys is a list of columns instead of a single one (and what happens if passing an empty list?)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1510922315
##########
python/pyarrow/tests/test_exec_plan.py:
##########
@@ -323,6 +323,83 @@ def test_join_extension_array_column():
assert result["colB"] == pa.chunked_array(ext_array)
+@pytest.mark.parametrize("tolerance,expected", [
+ (
+ 1,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, None, None],
+ },
+ ),
+ (
+ 3,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, 3., None],
+ },
+ ),
+ (
+ -5,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [None, None, 1., None, None],
+ },
+ ),
+])
+@pytest.mark.parametrize("use_datasets", [False, True])
+def test_join_asof(tolerance, expected, use_datasets):
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/5fcce5f689d4ff42af13baec2816f8e49345e075
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] AlenkaF commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "AlenkaF (via GitHub)" <gi...@apache.org>.
AlenkaF commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1437186111
Hi @judahrand , thank you for your contribution! ⭐
The code LGTM 👍 I would maybe add tests to `test_table.py` also, as in https://github.com/apache/arrow/pull/12452/files#diff-72bd29ba764c85f18fbf9e74898b72d47ed8a1b04be879c1a5b2a59382e2eaef
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1436768922
@AlenkaF I believe that this is ready for a round of review when you have time.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1319785287
##########
python/pyarrow/_acero.pyx:
##########
@@ -340,6 +340,78 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+ left_by: str, Expression or list[str]
+ The left keys on which the join operation should be performed.
+ Each key can be a string column name or a field expression,
+ or a list of such field references.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/6f8db25fec78546e19c91e405a3f1c14a0ddcd64
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "conbench-apache-arrow[bot] (via GitHub)" <gi...@apache.org>.
conbench-apache-arrow[bot] commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-2002328737
After merging your PR, Conbench analyzed the 7 benchmarking runs that have been run so far on merge-commit 681be03cfc63fbdda1e3a445d38f20b0434cb1c2.
There were 9 benchmark results indicating a performance regression:
- Commit Run on `ursa-i9-9960x` at [2024-03-16 07:11:19Z](https://conbench.ursa.dev/compare/runs/9c7530e82b6643a3b7cf19b9b1563b4f...aedc0c12b56049fe98fffa397ed2d5e2/)
- [`file-read` (R) with compression=uncompressed, dataset=fanniemae_2016Q4, file_type=feather, language=R, output_type=dataframe](https://conbench.ursa.dev/compare/benchmarks/065f522220567ef880008f0a09085737...065f542f44db700c8000fb12c690c1d4)
- [`file-read` (R) with compression=lz4, dataset=nyctaxi_2010-01, file_type=feather, language=R, output_type=table](https://conbench.ursa.dev/compare/benchmarks/065f5229a2c5793780009ad17928c815...065f5438d0a775478000232ee9632889)
- and 7 more (see the report linked below)
The [full Conbench report](https://github.com/apache/arrow/runs/22749599554) has more details. It also includes information about 1 possible false positive for unstable benchmarks that are known to sometimes produce them.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1510947458
##########
python/pyarrow/table.pxi:
##########
@@ -4859,6 +4859,91 @@ cdef class Table(_Tabular):
output_type=Table
)
+ def join_asof(self, right_table, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this table and another one.
+
+ Result of the join will be a new Table, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_table : Table
+ The table to join to the current one, acting as the right table
+ in the join operation.
+ on : str
+ The column from current table that should be used as the on key
+ of the join operation left side.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ by : str or list[str]
+ The columns from current table that should be used as the keys
+ of the join operation left side. The join operation is then done
+ only for the matches in these columns.
+ tolerance : int
+ The tolerance for inexact "on" key matching. A right row is considered
+ a match with the left row ``right.on - left.on <= tolerance``. The
+ ``tolerance`` may be:
+
+ - negative, in which case a past-as-of-join occurs;
+ - or positive, in which case a future-as-of-join occurs;
+ - or zero, in which case an exact-as-of-join occurs.
+
+ The tolerance is interpreted in the same units as the "on" key.
+ right_on : str or list[str], default None
+ The columns from the right_table that should be used as the on key
+ on the join operation right side.
+ When ``None`` use the same key name as the left table.
+ right_by : str or list[str], default None
+ The columns from the right_table that should be used as keys
+ on the join operation right side.
+ When ``None`` use the same key names as the left table.
+
+ Returns
+ -------
+ Table
+
+ Example
+ --------
+ >>> import pandas as pd
+ >>> import pyarrow as pa
+ >>> df1 = pd.DataFrame({'id': [1, 2, 3],
+ ... 'year': [2020, 2022, 2019]})
+ >>> df2 = pd.DataFrame({'id': [3, 4],
+ ... 'year': [2020, 2021],
+ ... 'n_legs': [5, 100],
+ ... 'animal': ["Brittle stars", "Centipede"]})
+ >>> t1 = pa.Table.from_pandas(df1).sort_by('year')
+ >>> t2 = pa.Table.from_pandas(df2).sort_by('year')
+
+ >>> t1.join_asof(
+ ... t2, on='year', by='id', tolerance=1
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/905bb54307da77541085e694c04d10ce92eee56b
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1507683277
##########
python/pyarrow/_acero.pyx:
##########
@@ -392,6 +392,84 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
Review Comment:
```suggestion
The input dataset must be sorted by the "on" key. Must be a single
```
##########
python/pyarrow/_acero.pyx:
##########
@@ -392,6 +392,84 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ left_by: str, Expression or list[str]
+ The left keys on which the join operation should be performed.
Review Comment:
```suggestion
The left keys on which the join operation should be performed.
Exact equality is used for each field of the "by" keys.
```
##########
python/pyarrow/_acero.pyx:
##########
@@ -392,6 +392,84 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ left_by: str, Expression or list[str]
Review Comment:
```suggestion
left_by: str, Expression or list
```
(the list can also be a list of Expressions)
##########
python/pyarrow/_acero.pyx:
##########
@@ -392,6 +392,84 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ left_by: str, Expression or list[str]
+ The left keys on which the join operation should be performed.
+ Each key can be a string column name or a field expression,
+ or a list of such field references.
+ right_on : str, Expression
+ The right key on which the join operation should be performed.
+ See `left_on` for details.
+ right_by: str, Expression or list[str]
Review Comment:
```suggestion
right_by: str, Expression or list
```
##########
python/pyarrow/tests/test_exec_plan.py:
##########
@@ -323,6 +323,83 @@ def test_join_extension_array_column():
assert result["colB"] == pa.chunked_array(ext_array)
+@pytest.mark.parametrize("tolerance,expected", [
+ (
+ 1,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, None, None],
+ },
+ ),
+ (
+ 3,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, 3., None],
+ },
+ ),
+ (
+ -5,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [None, None, 1., None, None],
+ },
+ ),
+])
+@pytest.mark.parametrize("use_datasets", [False, True])
+def test_join_asof(tolerance, expected, use_datasets):
Review Comment:
This file of `test_exec_plan.py` exist more for historical reasons, from before we had the Acero bindings. Do the test you added here add anything in addition to what is either tested in `test_acero.py` or `test_table.py`/`test_dataset.py`? If so, I think you can leave out those tests, or if it does test something else, add that to one of the other files.
##########
python/pyarrow/table.pxi:
##########
@@ -4859,6 +4859,91 @@ cdef class Table(_Tabular):
output_type=Table
)
+ def join_asof(self, right_table, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this table and another one.
+
Review Comment:
I would still like to see a bit more expanded explanation (apart from the individual keyword explanations) about _what_ and asof join exactly is.
Something indicating it does 1) an inexact join, 2) on a sorted dataset, 3) potentially first joining on other attributes, and 4) typically useful for time series data that are not perfectly aligned. Are that the most relevant characteristics?
##########
python/pyarrow/_acero.pyx:
##########
@@ -392,6 +392,84 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
Review Comment:
```suggestion
Currently, the "on" key must be an integer, date, or timestamp type.
```
##########
python/pyarrow/_acero.pyx:
##########
@@ -392,6 +392,84 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
Review Comment:
```suggestion
An inexact match is used on the "on" key, i.e. a row is considered a
match if and only if left_on - tolerance <= right_on <= left_on.
```
##########
python/pyarrow/table.pxi:
##########
@@ -4859,6 +4859,91 @@ cdef class Table(_Tabular):
output_type=Table
)
+ def join_asof(self, right_table, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this table and another one.
+
+ Result of the join will be a new Table, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_table : Table
+ The table to join to the current one, acting as the right table
+ in the join operation.
+ on : str
+ The column from current table that should be used as the on key
+ of the join operation left side.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input dataset must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ by : str or list[str]
+ The columns from current table that should be used as the keys
+ of the join operation left side. The join operation is then done
+ only for the matches in these columns.
+ tolerance : int
+ The tolerance for inexact "on" key matching. A right row is considered
+ a match with the left row ``right.on - left.on <= tolerance``. The
+ ``tolerance`` may be:
+
+ - negative, in which case a past-as-of-join occurs;
+ - or positive, in which case a future-as-of-join occurs;
+ - or zero, in which case an exact-as-of-join occurs.
+
+ The tolerance is interpreted in the same units as the "on" key.
+ right_on : str or list[str], default None
+ The columns from the right_table that should be used as the on key
+ on the join operation right side.
+ When ``None`` use the same key name as the left table.
+ right_by : str or list[str], default None
+ The columns from the right_table that should be used as keys
+ on the join operation right side.
+ When ``None`` use the same key names as the left table.
+
+ Returns
+ -------
+ Table
+
+ Example
+ --------
+ >>> import pandas as pd
+ >>> import pyarrow as pa
+ >>> df1 = pd.DataFrame({'id': [1, 2, 3],
+ ... 'year': [2020, 2022, 2019]})
+ >>> df2 = pd.DataFrame({'id': [3, 4],
+ ... 'year': [2020, 2021],
+ ... 'n_legs': [5, 100],
+ ... 'animal': ["Brittle stars", "Centipede"]})
+ >>> t1 = pa.Table.from_pandas(df1).sort_by('year')
+ >>> t2 = pa.Table.from_pandas(df2).sort_by('year')
+
+ >>> t1.join_asof(
+ ... t2, on='year', by='id', tolerance=1
Review Comment:
The fact that there is no repetition of the vaues in the by "id" key in the example data makes that it is difficult to see what exactly happens with the "on" key?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125727946
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
The `left_columns/right_columns` variables were also used to filter out the 'special' Dataset columns which we get back if the operands are datasets. This isn't currently an issue due to the temporary conversion to Tables due to the lack of ScanNodeOptions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] AlenkaF commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "AlenkaF (via GitHub)" <gi...@apache.org>.
AlenkaF commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1446106386
Just one more thing: could you rebase to latest master to get AppVeyor working? (related issue was closed: https://github.com/apache/arrow/issues/34296)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1369276501
##########
python/pyarrow/acero.py:
##########
@@ -253,6 +254,93 @@ def _perform_join(join_type, left_operand, left_keys,
raise TypeError("Unsupported output type")
+def _perform_join_asof(left_operand, left_on, left_by,
+ right_operand, right_on, right_by,
+ tolerance, use_threads=True,
+ output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ if not isinstance(left_operand, (Table, ds.Dataset)):
+ raise TypeError(f"Expected Table or Dataset, got {type(left_operand)}")
+ if not isinstance(right_operand, (Table, ds.Dataset)):
+ raise TypeError(f"Expected Table or Dataset, got {type(right_operand)}")
+
+ if not isinstance(left_by, (tuple, list)):
+ left_by = [left_by]
+ if not isinstance(right_by, (tuple, list)):
+ right_by = [right_by]
+
+ # AsofJoin does not return on or by columns for right_operand.
+ right_columns = [
+ col for col in right_operand.schema.names
+ if col not in [right_on] + right_by
+ ]
+ columns_collisions = set(left_operand.schema.names) & set(right_columns)
+ if columns_collisions:
+ raise ValueError(
+ "Columns {} present in both tables. AsofJoin does not support "
+ "column collisions.".format(columns_collisions),
+ )
+
+ # Add the join node to the execplan
+ if isinstance(left_operand, ds.Dataset):
+ left_source = _dataset_to_decl(left_operand, use_threads=use_threads)
+ else:
+ left_source = Declaration(
+ "table_source", TableSourceNodeOptions(left_operand),
+ )
+ if isinstance(right_operand, ds.Dataset):
+ right_source = _dataset_to_decl(right_operand, use_threads=use_threads)
+ else:
+ right_source = Declaration(
+ "table_source", TableSourceNodeOptions(right_operand)
+ )
+
+ join_opts = AsofJoinNodeOptions(
+ left_on, left_by, right_on, right_by, tolerance
+ )
+ decl = Declaration(
+ "asofjoin", options=join_opts, inputs=[left_source, right_source]
+ )
+
+ # Last time I checked the asof join node does not support using threads.
+ result_table = decl.to_table(use_threads=False)
Review Comment:
`use_threads=True` does seem to work! It didn't when I originally opened this PR way back when but now all seems well!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1369185194
##########
python/pyarrow/_dataset.pyx:
##########
@@ -874,6 +874,63 @@ cdef class Dataset(_Weakrefable):
output_type=InMemoryDataset
)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new Dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input table must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ by : str or list[str]
+ The columns from current dataset that should be used as the keys
+ of the join operation left side.
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/acb082e8da4647c13a60484f654d7ca1425f7f6e
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1510932106
##########
python/pyarrow/table.pxi:
##########
@@ -4859,6 +4859,91 @@ cdef class Table(_Tabular):
output_type=Table
)
+ def join_asof(self, right_table, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this table and another one.
+
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/e6da4df5cae7464439ae51c92ca1f43012a331a1
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1135210477
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
OK, I see. I added ScanNodeOptions, so you should be able to update this now.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1490687130
@judahrand to be clear, there is still interest in this!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] amol- commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "amol- (via GitHub)" <gi...@apache.org>.
amol- commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1490665829
Closing because it has been untouched for a while, in case it's still relevant feel free to reopen and move it forward 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] amol- closed pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "amol- (via GitHub)" <gi...@apache.org>.
amol- closed pull request #34234: GH-34235: [Python] Add `join_asof` binding
URL: https://github.com/apache/arrow/pull/34234
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] AlenkaF commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "AlenkaF (via GitHub)" <gi...@apache.org>.
AlenkaF commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1438144552
Yeah, I see the Appveyor build is failing on other PRs also.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "AlenkaF (via GitHub)" <gi...@apache.org>.
AlenkaF commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1345483594
##########
cpp/src/arrow/acero/asof_join_node.cc:
##########
@@ -1545,7 +1545,7 @@ class AsofJoinNode : public ExecNode {
if (by_key_type[k] == NULLPTR) {
by_key_type[k] = by_field[k]->type().get();
} else if (*by_key_type[k] != *by_field[k]->type()) {
- return Status::Invalid("Expected on-key type ", *by_key_type[k], " but got ",
+ return Status::Invalid("Expected by-key type ", *by_key_type[k], " but got ",
Review Comment:
Good catch 👍
##########
python/pyarrow/acero.py:
##########
@@ -253,6 +254,93 @@ def _perform_join(join_type, left_operand, left_keys,
raise TypeError("Unsupported output type")
+def _perform_join_asof(left_operand, left_on, left_by,
+ right_operand, right_on, right_by,
+ tolerance, use_threads=True,
+ output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ if not isinstance(left_operand, (Table, ds.Dataset)):
+ raise TypeError(f"Expected Table or Dataset, got {type(left_operand)}")
+ if not isinstance(right_operand, (Table, ds.Dataset)):
+ raise TypeError(f"Expected Table or Dataset, got {type(right_operand)}")
+
+ if not isinstance(left_by, (tuple, list)):
+ left_by = [left_by]
+ if not isinstance(right_by, (tuple, list)):
+ right_by = [right_by]
+
+ # AsofJoin does not return on or by columns for right_operand.
+ right_columns = [
+ col for col in right_operand.schema.names
+ if col not in [right_on] + right_by
+ ]
+ columns_collisions = set(left_operand.schema.names) & set(right_columns)
+ if columns_collisions:
+ raise ValueError(
+ "Columns {} present in both tables. AsofJoin does not support "
+ "column collisions.".format(columns_collisions),
+ )
+
+ # Add the join node to the execplan
+ if isinstance(left_operand, ds.Dataset):
+ left_source = _dataset_to_decl(left_operand, use_threads=use_threads)
+ else:
+ left_source = Declaration(
+ "table_source", TableSourceNodeOptions(left_operand),
+ )
+ if isinstance(right_operand, ds.Dataset):
+ right_source = _dataset_to_decl(right_operand, use_threads=use_threads)
+ else:
+ right_source = Declaration(
+ "table_source", TableSourceNodeOptions(right_operand)
+ )
+
+ join_opts = AsofJoinNodeOptions(
+ left_on, left_by, right_on, right_by, tolerance
+ )
+ decl = Declaration(
+ "asofjoin", options=join_opts, inputs=[left_source, right_source]
+ )
+
+ # Last time I checked the asof join node does not support using threads.
+ result_table = decl.to_table(use_threads=False)
Review Comment:
Yeah, _I think_ it uses threads by default.
https://github.com/apache/arrow/blob/57e9386d4a3a18dfa3c5269c49efaee93aabea2b/cpp/src/arrow/acero/CMakeLists.txt#L177-L182
But it is a bit unclear to me how the `use_threads` in the `DeclarationToTable` corresponds to the `use_threads` in the specific join.
https://github.com/apache/arrow/blob/57e9386d4a3a18dfa3c5269c49efaee93aabea2b/python/pyarrow/_acero.pyx#L495-L501
https://github.com/apache/arrow/blob/57e9386d4a3a18dfa3c5269c49efaee93aabea2b/cpp/src/arrow/acero/exec_plan.cc#L779-L790
I would think the threading here is separate from the threading in the specific join - but I am not very familiar with the acero codebase. Have you tried setting it to `True`/default? What happens in that case?
##########
cpp/src/arrow/acero/asof_join_node.cc:
##########
@@ -1545,7 +1545,7 @@ class AsofJoinNode : public ExecNode {
if (by_key_type[k] == NULLPTR) {
by_key_type[k] = by_field[k]->type().get();
} else if (*by_key_type[k] != *by_field[k]->type()) {
- return Status::Invalid("Expected on-key type ", *by_key_type[k], " but got ",
+ return Status::Invalid("Expected by-key type ", *by_key_type[k], " but got ",
Review Comment:
Good catch 👍
##########
python/pyarrow/_dataset.pyx:
##########
@@ -874,6 +874,63 @@ cdef class Dataset(_Weakrefable):
output_type=InMemoryDataset
)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new Dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+
+ An inexact match is used on the “on” key.i.e., a row is considered a
+ match iff left_on - tolerance <= right_on <= left_on.
+
+ The input table must be sorted by the “on” key. Must be a single
+ field of a common type.
+
+ Currently, the “on” key must be an integer, date, or timestamp type.
+ by : str or list[str]
+ The columns from current dataset that should be used as the keys
+ of the join operation left side.
Review Comment:
```suggestion
The columns from current dataset that should be used as the keys
of the join operation left side. The join operation is then done
only for the matches in these columns.
```
This would help me to understand the `by` parameter a bit better (same for the table), though I was struggling with how to phrase it and am not sure it is the clearest. Feel free to change/ignore.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] github-actions[bot] commented on pull request #34234: [Python] Add `join_asof` binding
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1434573106
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
Thanks for opening a pull request!
If this is not a [minor PR](https://github.com/apache/arrow/blob/master/CONTRIBUTING.md#Minor-Fixes). Could you open an issue for this pull request on GitHub? https://github.com/apache/arrow/issues/new/choose
Opening GitHub issues ahead of time contributes to the [Openness](http://theapacheway.com/open/#:~:text=Openness%20allows%20new%20users%20the,must%20happen%20in%20the%20open.) of the Apache Arrow project.
Then could you also rename the pull request title in the following format?
GH-${GITHUB_ISSUE_ID}: [${COMPONENT}] ${SUMMARY}
or
MINOR: [${COMPONENT}] ${SUMMARY}
In the case of PARQUET issues on JIRA the title also supports:
PARQUET-${JIRA_ISSUE_ID}: [${COMPONENT}] ${SUMMARY}
See also:
* [Other pull requests](https://github.com/apache/arrow/pulls/)
* [Contribution Guidelines - How to contribute patches](https://arrow.apache.org/docs/developers/contributing.html#how-to-contribute-patches)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] github-actions[bot] commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1434576517
:warning: GitHub issue #34235 **has been automatically assigned in GitHub** to PR creator.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125748731
##########
python/pyarrow/_dataset.pyx:
##########
@@ -514,6 +514,53 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
+ tolerance : int
Review Comment:
This is a good question actually... I'm not 100% sure how this is intended to work. The C++ implementation exclusively accepts an `int64_t` for the tolerance. It simply states that it will use the same units as the `on` column... it is unclear what that means. I'd assumed it mean the resolution of the timestamp in a timestamp case.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1498165547
I'll try and find the time to push this through 🙏
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1807987455
@jorisvandenbossche Do you think you might be able to take another look at this? I know I've been quite slow at pushing this PR forward but I think it potentially ready to be merged now.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1976446763
@jorisvandenbossche I believe I've dealt will all the feedback 😄
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125727946
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
The `left_columns/right_columns` variables are also used to filter out the 'special' Dataset columns which we get back if the operands are datasets.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125748731
##########
python/pyarrow/_dataset.pyx:
##########
@@ -514,6 +514,53 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
+ tolerance : int
Review Comment:
This is a good question actually... I'm not 100% sure how this is intended to work. The C++ implementation exclusively accepts an `int64_t` for the tolerance.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125727946
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
The `left_columns/right_columns` variables were also used to filter out the 'special' Dataset columns which we get back if the operands are datasets.
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
I believe the reason I added this in is that it was cause the C++ implementation to segfault.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125754138
##########
python/pyarrow/tests/test_table.py:
##########
@@ -2297,6 +2297,49 @@ def test_table_join_many_columns():
})
+@pytest.mark.dataset
+def test_table_join_asof():
+ t1 = pa.Table.from_pydict({
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"]
+ })
+
+ t2 = pa.Table.from_pydict({
+ "colB": [2, 9, 15],
+ "col3": ["a", "b", "g"],
+ "colC": [1., 3., 5.]
+ })
+
+ r = t1.join_asof(t2, "colA", "col2", 1, "colB", "col3")
Review Comment:
> A test where the left/right column names are the same, so you can rely on not having to specify right_on/by
This is now tested.
> A test where the by keys is a list of columns instead of a single one
This is now tested.
> and what happens if passing an empty list?
It seems like it just doesn't perform the join over any partitions - this is also now tested.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1446181964
@judahrand thanks for working on this!
Before diving into the details, I have two general comments:
- We are in the middle of refactoring how we expose the Acero / ExecPlan features, and I have a PR that exposes the Declaration object and ExecNodeOptions subclasses in Python (https://github.com/apache/arrow/pull/34102). Once that is merged, it should be the goal that also the asof join could be exposed by adding an `AsofJoinNodeOptions` class in pyarrow.
Ideally, I would prefer that we can do this directly, but I know that the mentioned PR isn't merged yet (I hope it can be merged in one of the coming days, though)
- I think we should try to do a better job of explaining what the "asof" exactly is and does in the docstring (I also noted that AsOfJoin is missing in the C++ user guide (will open an issue about that), although it has some reference docs), since I think this is generally not a very well known join type: what is the difference with a normal join? What is the difference between the "on" and "by" join keys?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] jorisvandenbossche commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1135226479
##########
python/pyarrow/_dataset.pyx:
##########
@@ -857,6 +857,54 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
Review Comment:
We will need to explain here what's the difference between "on" and "by" keys.
##########
python/pyarrow/tests/test_exec_plan.py:
##########
@@ -321,3 +321,77 @@ def test_join_extension_array_column():
result = ep._perform_join(
"left outer", t1, ["colB"], t3, ["colC"])
assert result["colB"] == pa.chunked_array(ext_array)
+
+
+@pytest.mark.parametrize("tolerance,expected", [
+ (
+ 1,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, None, None],
+ },
+ ),
+ (
+ 3,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, 3., None],
+ },
+ ),
+ (
+ -5,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [None, None, 1., None, None],
+ },
+ ),
+])
+@pytest.mark.parametrize("use_datasets", [False, True])
+def test_join_asof(tolerance, expected, use_datasets):
+ # Allocate table here instead of using parametrize
+ # this prevents having arrow allocated memory forever around.
+ expected = pa.table(expected)
+
+ t1 = pa.Table.from_pydict({
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"]
+ })
+
+ t2 = pa.Table.from_pydict({
+ "colB": [2, 9, 15],
+ "col3": ["a", "b", "g"],
+ "colC": [1., 3., 5.]
+ })
+
+ if use_datasets:
+ t1 = ds.dataset([t1])
+ t2 = ds.dataset([t2])
+
+ r = ep._perform_join_asof(t1, "colA", "col2", t2, "colB", "col3", tolerance)
+ r = r.combine_chunks()
+ r = r.sort_by("colA")
+ assert r == expected
+
+
+def test_table_join_asof_collisions():
+ t1 = pa.table({
+ "colA": [1, 2, 6],
+ "colB": [10, 20, 60],
+ "on": [1, 2, 3],
+ "colVals": ["a", "b", "f"]
+ })
+
+ t2 = pa.table({
+ "colB": [99, 20, 10],
+ "colVals": ["Z", "B", "A"],
+ "colUniq": [100, 200, 300],
+ "colA": [99, 2, 1],
+ "on": [2, 3, 4],
+ })
+
+ msg = "colVals present in both tables. AsofJoin does not support column collisions."
+ with pytest.raises(ValueError, match=msg):
+ ep._perform_join_asof(t1, "on", ["colA", "colB"], t2, "on", ["colA", "colB"], 1)
Review Comment:
Another failure case to test: what happens if you pass by keys of different length? (or specifying columns where the type doesn't match)
##########
python/pyarrow/_acero.pyx:
##########
@@ -340,6 +340,78 @@ class HashJoinNodeOptions(_HashJoinNodeOptions):
)
+cdef class _AsofJoinNodeOptions(ExecNodeOptions):
+
+ def _set_options(self, left_on, left_by, right_on, right_by, tolerance):
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if not isinstance(left_by, (list, tuple)):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(_ensure_field_ref(key))
+
+ c_left_keys.on_key = _ensure_field_ref(left_on)
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ if not isinstance(right_by, (list, tuple)):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(_ensure_field_ref(key))
+
+ c_right_keys.on_key = _ensure_field_ref(right_on)
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ self.wrapped.reset(
+ new CAsofJoinNodeOptions(
+ c_input_keys,
+ tolerance,
+ )
+ )
+
+
+class AsofJoinNodeOptions(_AsofJoinNodeOptions):
+ """
+ Make a node which implements 'as of join' operation.
+
+ This is the option class for the "asofjoin" node factory.
+
+ Parameters
+ ----------
+ left_on : str, Expression
+ The left key on which the join operation should be performed.
+ Can be a string column name or a field expression.
+ left_by: str, Expression or list[str]
+ The left keys on which the join operation should be performed.
+ Each key can be a string column name or a field expression,
+ or a list of such field references.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
Review Comment:
This can be left out here in the options docstring (that's not a parameter)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1727170071
> @jorisvandenbossche Sorry this took me so long to revisit! I think I've dealt with all of your comments?
Merged main in which should fix the appveyor test failure.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125748731
##########
python/pyarrow/_dataset.pyx:
##########
@@ -514,6 +514,53 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
+ tolerance : int
Review Comment:
This is a good question actually... I'm not 100% sure how this is intended to work. The C++ implementation exclusively accepts an `int64_t` for the tolerance. It simply states that it will use the same units as the `on` column... it is unclear what that means. I'd assumed it meant the resolution of the timestamp in a timestamp case.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125726158
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
I believe the reason I added this in is that it was causing the C++ implementation to segfault.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1125726158
##########
python/pyarrow/_exec_plan.pyx:
##########
@@ -390,6 +390,120 @@ def _perform_join(join_type, left_operand not None, left_keys,
return result_table
+def _perform_join_asof(left_operand not None, left_on, left_by,
+ right_operand not None, right_on, right_by,
+ tolerance, output_type=Table):
+ """
+ Perform asof join of two tables or datasets.
+
+ The result will be an output table with the result of the join operation
+
+ Parameters
+ ----------
+ left_operand : Table or Dataset
+ The left operand for the join operation.
+ left_on : str
+ The left key (or keys) on which the join operation should be performed.
+ left_by: str or list[str]
+ The left key (or keys) on which the join operation should be performed.
+ right_operand : Table or Dataset
+ The right operand for the join operation.
+ right_on : str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ right_by: str or list[str]
+ The right key (or keys) on which the join operation should be performed.
+ tolerance : int
+ The tolerance to use for the asof join. The tolerance is interpreted in
+ the same units as the "on" key.
+ output_type: Table or InMemoryDataset
+ The output type for the exec plan result.
+
+ Returns
+ -------
+ result_table : Table or InMemoryDataset
+ """
+ cdef:
+ vector[CFieldRef] c_left_by
+ vector[CFieldRef] c_right_by
+ CAsofJoinKeys c_left_keys
+ CAsofJoinKeys c_right_keys
+ vector[CAsofJoinKeys] c_input_keys
+ vector[CDeclaration] c_decl_plan
+
+ # Prepare left AsofJoinNodeOption::Keys
+ if isinstance(left_by, str):
+ left_by = [left_by]
+ for key in left_by:
+ c_left_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_left_keys.on_key = CFieldRef(<c_string>tobytes(left_on))
+ c_left_keys.by_key = c_left_by
+
+ c_input_keys.push_back(c_left_keys)
+
+ # Prepare right AsofJoinNodeOption::Keys
+ right_by_order = {}
+ if isinstance(right_by, str):
+ right_by = [right_by]
+ for key in right_by:
+ c_right_by.push_back(CFieldRef(<c_string>tobytes(key)))
+
+ c_right_keys.on_key = CFieldRef(<c_string>tobytes(right_on))
+ c_right_keys.by_key = c_right_by
+
+ c_input_keys.push_back(c_right_keys)
+
+ # By default expose all columns on both left and right table
+ if isinstance(left_operand, Table):
+ left_columns = left_operand.column_names
+ elif isinstance(left_operand, Dataset):
+ left_columns = left_operand.schema.names
+ else:
+ raise TypeError("Unsupported left join member type")
Review Comment:
I believe the reason I adding this in is that it was cause the C++ implementation to segfault.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1434614898
I'm struggling to run the tests locally as I can't get Arrow to build on an M1 Mac.
```
Undefined symbols for architecture arm64:
"testing::Matcher<std::__1::basic_string_view<char, std::__1::char_traits<char> > const&>::Matcher(char const*)", referenced from:
testing::Matcher<std::__1::basic_string_view<char, std::__1::char_traits<char> > const&> testing::internal::MatcherCastImpl<std::__1::basic_string_view<char, std::__1::char_traits<char> > const&, char const*>::CastImpl<true>(char const* const&, std::__1::integral_constant<bool, true>, std::__1::integral_constant<bool, true>) in array_test.cc.o
testing::Matcher<std::__1::basic_string_view<char, std::__1::char_traits<char> > const&> testing::internal::MatcherCastImpl<std::__1::basic_string_view<char, std::__1::char_traits<char> > const&, char const*>::CastImpl<true>(char const* const&, std::__1::integral_constant<bool, true>, std::__1::integral_constant<bool, true>) in array_binary_test.cc.o
ld: symbol(s) not found for architecture arm64
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make[2]: *** [src/arrow/CMakeFiles/arrow-array-test.dir/build.make:208: build/debug/arrow-array-test] Error 1
make[1]: *** [CMakeFiles/Makefile2:1714: src/arrow/CMakeFiles/arrow-array-test.dir/all] Error 2
make[1]: *** Waiting for unfinished jobs....
[ 54%] Built target arrow-public-api-test
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1437263752
> Hi @judahrand , thank you for your contribution! ⭐
>
> The code LGTM 👍 I would maybe add tests to `test_table.py` also, as in https://github.com/apache/arrow/pull/12452/files#diff-72bd29ba764c85f18fbf9e74898b72d47ed8a1b04be879c1a5b2a59382e2eaef
Done
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] danepitkin commented on pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "danepitkin (via GitHub)" <gi...@apache.org>.
danepitkin commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1579006589
Hey @judahrand! would you consider moving this PR to draft mode to signify changes are still needed?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1319961690
##########
python/pyarrow/_dataset.pyx:
##########
@@ -857,6 +857,54 @@ cdef class Dataset(_Weakrefable):
use_threads=use_threads, coalesce_keys=coalesce_keys,
output_type=InMemoryDataset)
+ def join_asof(self, right_dataset, on, by, tolerance, right_on=None, right_by=None):
+ """
+ Perform an asof join between this dataset and another one.
+
+ Result of the join will be a new dataset, where further
+ operations can be applied.
+
+ Parameters
+ ----------
+ right_dataset : dataset
+ The dataset to join to the current one, acting as the right dataset
+ in the join operation.
+ on : str
+ The column from current dataset that should be used as the on key
+ of the join operation left side.
+ by : str or list[str]
+ The columns from current dataset that should be used as the by keys
+ of the join operation left side.
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/d66a2e8089d422cfdf9bd605c628cda45e24142f
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [arrow] judahrand commented on a diff in pull request #34234: GH-34235: [Python] Add `join_asof` binding
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on code in PR #34234:
URL: https://github.com/apache/arrow/pull/34234#discussion_r1319949676
##########
python/pyarrow/tests/test_exec_plan.py:
##########
@@ -321,3 +321,77 @@ def test_join_extension_array_column():
result = ep._perform_join(
"left outer", t1, ["colB"], t3, ["colC"])
assert result["colB"] == pa.chunked_array(ext_array)
+
+
+@pytest.mark.parametrize("tolerance,expected", [
+ (
+ 1,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, None, None],
+ },
+ ),
+ (
+ 3,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [1., None, None, 3., None],
+ },
+ ),
+ (
+ -5,
+ {
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"],
+ "colC": [None, None, 1., None, None],
+ },
+ ),
+])
+@pytest.mark.parametrize("use_datasets", [False, True])
+def test_join_asof(tolerance, expected, use_datasets):
+ # Allocate table here instead of using parametrize
+ # this prevents having arrow allocated memory forever around.
+ expected = pa.table(expected)
+
+ t1 = pa.Table.from_pydict({
+ "colA": [1, 1, 5, 6, 7],
+ "col2": ["a", "b", "a", "b", "f"]
+ })
+
+ t2 = pa.Table.from_pydict({
+ "colB": [2, 9, 15],
+ "col3": ["a", "b", "g"],
+ "colC": [1., 3., 5.]
+ })
+
+ if use_datasets:
+ t1 = ds.dataset([t1])
+ t2 = ds.dataset([t2])
+
+ r = ep._perform_join_asof(t1, "colA", "col2", t2, "colB", "col3", tolerance)
+ r = r.combine_chunks()
+ r = r.sort_by("colA")
+ assert r == expected
+
+
+def test_table_join_asof_collisions():
+ t1 = pa.table({
+ "colA": [1, 2, 6],
+ "colB": [10, 20, 60],
+ "on": [1, 2, 3],
+ "colVals": ["a", "b", "f"]
+ })
+
+ t2 = pa.table({
+ "colB": [99, 20, 10],
+ "colVals": ["Z", "B", "A"],
+ "colUniq": [100, 200, 300],
+ "colA": [99, 2, 1],
+ "on": [2, 3, 4],
+ })
+
+ msg = "colVals present in both tables. AsofJoin does not support column collisions."
+ with pytest.raises(ValueError, match=msg):
+ ep._perform_join_asof(t1, "on", ["colA", "colB"], t2, "on", ["colA", "colB"], 1)
Review Comment:
https://github.com/apache/arrow/pull/34234/commits/102f965f83ba702997b74dd72626b78970cfb91a
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] GH-34235: [Python] Add `join_asof` binding [arrow]
Posted by "judahrand (via GitHub)" <gi...@apache.org>.
judahrand commented on PR #34234:
URL: https://github.com/apache/arrow/pull/34234#issuecomment-1969348630
@jorisvandenbossche @AlenkaF ping?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org