ENH: Support DataFrame.searchsorted

[ChrisJar]

Is your feature request related to a problem?

Currently searchsorted is supported for Series and Index objects, however I wish to be able to use it with DataFrame objects as well.

Describe the solution you'd like

I'd like to be able to call DataFrame.searchsorted(value, side, sorter) like in the case of Series.searchsorted, but in this case the value parameter would be a DataFrame with the same number of columns as self.

Additional context

For example:

import pandas as pd

df = pd.DataFrame({'a': [1, 3, 5, 7], 'b': [10, 12, 14, 16]})
value_df = pd.DataFrame({'a': [0, 2], 'b': [10, 11]})
df.searchsorted(value_df)

would return

array([0, 1])

[KiranHipparagi]

@ChrisJar @mroeschke I want to work on this feature .
Can I contribute to this issue ?

[KiranHipparagi]

@ChrisJar what would be the answer of below code??

import pandas as pd

df = pd.DataFrame({'a': [1, 3, 5, 7], 'b': [10, 12, 14, 16]})
value_df = pd.DataFrame({'a': [0, 2], 'b': [10, 13]})
df.searchsorted(value_df)

why can't we try like this...

array([0, 1]),array([0, 2])

[mroeschke]

As the Needs Discussion label states, this feature needs more buy-in from the core team before proceeding.

[charlesbluca]

Bumping this issue with a little more context from #43907; this functionality is currently desired as it (along with multi-column quantiles, see #43881) would unblock performant multi-column sort_values in Dask, using an algorithm roughly similar to that of dask-cudf's sort_values.

Just in case it wasn't clear, this searchsorted would be returning indices to maintain order in a dataframe that is lexicographically sorted by all columns; for example, in cuDF:

>>> df = cudf.DataFrame({"a" :[1, 1, 1, 2, 2, 2], "b": list(range(6))})
>>> values = cudf.DataFrame({"a": [1, 1, 2, 2], "b": [1, 2, 4, 6]})
>>> df.searchsorted(values, side="right")
array([2, 3, 5, 6], dtype=int32)

One way to achieve this now would be to convert the respective Pandas dataframes into series, and then use Series.searchsorted:

In [8]: import pandas as pd
   ...: 
   ...: df = pd.DataFrame({"a" :[1, 1, 1, 2, 2, 2], "b": list(range(6))})
   ...: values = pd.DataFrame({"a": [1, 1, 2, 2], "b": [1, 2, 4, 6]})
   ...: 
   ...: ser = pd.Series(
   ...:     list(df.itertuples(index=False, name=None)),
   ...:     index=df.index,
   ...:     name=tuple(df.columns)
   ...: )
   ...: values = pd.Series(
   ...:     list(values.itertuples(index=False, name=None)),
   ...:     index=values.index,
   ...:     name=tuple(values.columns)
   ...: )
   ...: 
   ...: ser.searchsorted(values, side="right")
Out[8]: array([2, 3, 5, 6])

However, there is probably a more performant way to do this.