Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1"""
2SQL-style merge routines
3"""
5from __future__ import annotations
7from collections.abc import (
8 Hashable,
9 Sequence,
10)
11import datetime
12from functools import partial
13import types
14from typing import (
15 TYPE_CHECKING,
16 Literal,
17 cast,
18 final,
19)
20import uuid
21import warnings
23import numpy as np
25from pandas._libs import (
26 Timedelta,
27 hashtable as libhashtable,
28 join as libjoin,
29 lib,
30)
31from pandas._libs.lib import is_range_indexer
32from pandas._typing import (
33 AnyArrayLike,
34 ArrayLike,
35 IndexLabel,
36 JoinHow,
37 MergeHow,
38 Shape,
39 Suffixes,
40 npt,
41)
42from pandas.errors import MergeError
43from pandas.util._decorators import (
44 cache_readonly,
45 set_module,
46)
47from pandas.util._exceptions import find_stack_level
49from pandas.core.dtypes.base import ExtensionDtype
50from pandas.core.dtypes.cast import find_common_type
51from pandas.core.dtypes.common import (
52 ensure_int64,
53 ensure_object,
54 is_bool,
55 is_bool_dtype,
56 is_float_dtype,
57 is_integer,
58 is_integer_dtype,
59 is_list_like,
60 is_number,
61 is_numeric_dtype,
62 is_object_dtype,
63 is_string_dtype,
64 needs_i8_conversion,
65)
66from pandas.core.dtypes.dtypes import (
67 CategoricalDtype,
68 DatetimeTZDtype,
69)
70from pandas.core.dtypes.generic import (
71 ABCDataFrame,
72 ABCSeries,
73)
74from pandas.core.dtypes.missing import (
75 isna,
76 na_value_for_dtype,
77)
79from pandas import (
80 ArrowDtype,
81 Categorical,
82 Index,
83 MultiIndex,
84 Series,
85)
86import pandas.core.algorithms as algos
87from pandas.core.arrays import (
88 ArrowExtensionArray,
89 BaseMaskedArray,
90 ExtensionArray,
91)
92from pandas.core.arrays.string_ import StringDtype
93import pandas.core.common as com
94from pandas.core.construction import (
95 ensure_wrapped_if_datetimelike,
96 extract_array,
97)
98from pandas.core.indexes.api import default_index
99from pandas.core.sorting import (
100 get_group_index,
101 is_int64_overflow_possible,
102)
104if TYPE_CHECKING:
105 from pandas import DataFrame
106 from pandas.core import groupby
107 from pandas.core.arrays import DatetimeArray
108 from pandas.core.indexes.frozen import FrozenList
110_factorizers = {
111 np.int64: libhashtable.Int64Factorizer,
112 np.longlong: libhashtable.Int64Factorizer,
113 np.int32: libhashtable.Int32Factorizer,
114 np.int16: libhashtable.Int16Factorizer,
115 np.int8: libhashtable.Int8Factorizer,
116 np.uint64: libhashtable.UInt64Factorizer,
117 np.uint32: libhashtable.UInt32Factorizer,
118 np.uint16: libhashtable.UInt16Factorizer,
119 np.uint8: libhashtable.UInt8Factorizer,
120 np.bool_: libhashtable.UInt8Factorizer,
121 np.float64: libhashtable.Float64Factorizer,
122 np.float32: libhashtable.Float32Factorizer,
123 np.complex64: libhashtable.Complex64Factorizer,
124 np.complex128: libhashtable.Complex128Factorizer,
125 np.object_: libhashtable.ObjectFactorizer,
126}
128# See https://github.com/pandas-dev/pandas/issues/52451
129if np.intc is not np.int32:
130 if np.dtype(np.intc).itemsize == 4:
131 _factorizers[np.intc] = libhashtable.Int32Factorizer
132 else:
133 _factorizers[np.intc] = libhashtable.Int64Factorizer
135if np.uintc is not np.uint32:
136 if np.dtype(np.uintc).itemsize == 4:
137 _factorizers[np.uintc] = libhashtable.UInt32Factorizer
138 else:
139 _factorizers[np.uintc] = libhashtable.UInt64Factorizer
142_known = (np.ndarray, ExtensionArray, Index, ABCSeries)
145@set_module("pandas")
146def merge(
147 left: DataFrame | Series,
148 right: DataFrame | Series,
149 how: MergeHow = "inner",
150 on: IndexLabel | AnyArrayLike | None = None,
151 left_on: IndexLabel | AnyArrayLike | None = None,
152 right_on: IndexLabel | AnyArrayLike | None = None,
153 left_index: bool = False,
154 right_index: bool = False,
155 sort: bool = False,
156 suffixes: Suffixes = ("_x", "_y"),
157 copy: bool | lib.NoDefault = lib.no_default,
158 indicator: str | bool = False,
159 validate: str | None = None,
160) -> DataFrame:
161 """
162 Merge DataFrame or named Series objects with a database-style join.
164 A named Series object is treated as a DataFrame with a single named column.
166 The join is done on columns or indexes. If joining columns on
167 columns, the DataFrame indexes *will be ignored*. Otherwise if joining indexes
168 on indexes or indexes on a column or columns, the index will be passed on.
169 When performing a cross merge, no column specifications to merge on are
170 allowed.
172 .. warning::
174 If both key columns contain rows where the key is a null value, those
175 rows will be matched against each other. This is different from usual SQL
176 join behaviour and can lead to unexpected results.
178 Parameters
179 ----------
180 left : DataFrame or named Series
181 First pandas object to merge.
182 right : DataFrame or named Series
183 Second pandas object to merge.
184 how : {'left', 'right', 'outer', 'inner', 'cross', 'left_anti', 'right_anti},
185 default 'inner'
186 Type of merge to be performed.
188 * left: use only keys from left frame, similar to a SQL left outer join;
189 preserve key order.
190 * right: use only keys from right frame, similar to a SQL right outer join;
191 preserve key order.
192 * outer: use union of keys from both frames, similar to a SQL full outer
193 join; sort keys lexicographically.
194 * inner: use intersection of keys from both frames, similar to a SQL inner
195 join; preserve the order of the left keys.
196 * cross: creates the cartesian product from both frames, preserves the order
197 of the left keys.
198 * left_anti: use only keys from left frame that are not in right frame, similar
199 to SQL left anti join; preserve key order.
200 * right_anti: use only keys from right frame that are not in left frame, similar
201 to SQL right anti join; preserve key order.
202 on : Hashable or a sequence of the previous
203 Column or index level names to join on. These must be found in both
204 DataFrames. If `on` is None and not merging on indexes then this defaults
205 to the intersection of the columns in both DataFrames.
206 left_on : Hashable or a sequence of the previous, or array-like
207 Column or index level names to join on in the left DataFrame. Can also
208 be an array or list of arrays of the length of the left DataFrame.
209 These arrays are treated as if they are columns.
210 right_on : Hashable or a sequence of the previous, or array-like
211 Column or index level names to join on in the right DataFrame. Can also
212 be an array or list of arrays of the length of the right DataFrame.
213 These arrays are treated as if they are columns.
214 left_index : bool, default False
215 Use the index from the left DataFrame as the join key(s). If it is a
216 MultiIndex, the number of keys in the other DataFrame (either the index
217 or a number of columns) must match the number of levels.
218 right_index : bool, default False
219 Use the index from the right DataFrame as the join key. Same caveats as
220 left_index.
221 sort : bool, default False
222 Sort the join keys lexicographically in the result DataFrame. If False,
223 the order of the join keys depends on the join type (how keyword).
224 suffixes : list-like, default is ("_x", "_y")
225 A length-2 sequence where each element is optionally a string
226 indicating the suffix to add to overlapping column names in
227 `left` and `right` respectively. Pass a value of `None` instead
228 of a string to indicate that the column name from `left` or
229 `right` should be left as-is, with no suffix. At least one of the
230 values must not be None.
231 copy : bool, default False
232 This keyword is now ignored; changing its value will have no
233 impact on the method.
235 .. deprecated:: 3.0.0
237 This keyword is ignored and will be removed in pandas 4.0. Since
238 pandas 3.0, this method always returns a new object using a lazy
239 copy mechanism that defers copies until necessary
240 (Copy-on-Write). See the `user guide on Copy-on-Write
241 <https://pandas.pydata.org/docs/dev/user_guide/copy_on_write.html>`__
242 for more details.
244 indicator : bool or str, default False
245 If True, adds a column to the output DataFrame called "_merge" with
246 information on the source of each row. The column can be given a different
247 name by providing a string argument. The column will have a Categorical
248 type with the value of "left_only" for observations whose merge key only
249 appears in the left DataFrame, "right_only" for observations
250 whose merge key only appears in the right DataFrame, and "both"
251 if the observation's merge key is found in both DataFrames.
253 validate : str, optional
254 If specified, checks if merge is of specified type.
256 * "one_to_one" or "1:1": check if merge keys are unique in both
257 left and right datasets.
258 * "one_to_many" or "1:m": check if merge keys are unique in left
259 dataset.
260 * "many_to_one" or "m:1": check if merge keys are unique in right
261 dataset.
262 * "many_to_many" or "m:m": allowed, but does not result in checks.
264 Returns
265 -------
266 DataFrame
267 A DataFrame of the two merged objects.
269 See Also
270 --------
271 merge_ordered : Merge with optional filling/interpolation.
272 merge_asof : Merge on nearest keys.
273 DataFrame.join : Similar method using indices.
275 Examples
276 --------
277 >>> df1 = pd.DataFrame(
278 ... {"lkey": ["foo", "bar", "baz", "foo"], "value": [1, 2, 3, 5]}
279 ... )
280 >>> df2 = pd.DataFrame(
281 ... {"rkey": ["foo", "bar", "baz", "foo"], "value": [5, 6, 7, 8]}
282 ... )
283 >>> df1
284 lkey value
285 0 foo 1
286 1 bar 2
287 2 baz 3
288 3 foo 5
289 >>> df2
290 rkey value
291 0 foo 5
292 1 bar 6
293 2 baz 7
294 3 foo 8
296 Merge df1 and df2 on the lkey and rkey columns. The value columns have
297 the default suffixes, _x and _y, appended.
299 >>> df1.merge(df2, left_on="lkey", right_on="rkey")
300 lkey value_x rkey value_y
301 0 foo 1 foo 5
302 1 foo 1 foo 8
303 2 bar 2 bar 6
304 3 baz 3 baz 7
305 4 foo 5 foo 5
306 5 foo 5 foo 8
308 Merge DataFrames df1 and df2 with specified left and right suffixes
309 appended to any overlapping columns.
311 >>> df1.merge(df2, left_on="lkey", right_on="rkey", suffixes=("_left", "_right"))
312 lkey value_left rkey value_right
313 0 foo 1 foo 5
314 1 foo 1 foo 8
315 2 bar 2 bar 6
316 3 baz 3 baz 7
317 4 foo 5 foo 5
318 5 foo 5 foo 8
320 Merge DataFrames df1 and df2, but raise an exception if the DataFrames have
321 any overlapping columns.
323 >>> df1.merge(df2, left_on="lkey", right_on="rkey", suffixes=(False, False))
324 Traceback (most recent call last):
325 ...
326 ValueError: columns overlap but no suffix specified:
327 Index(['value'], dtype='str')
329 >>> df1 = pd.DataFrame({"a": ["foo", "bar"], "b": [1, 2]})
330 >>> df2 = pd.DataFrame({"a": ["foo", "baz"], "c": [3, 4]})
331 >>> df1
332 a b
333 0 foo 1
334 1 bar 2
335 >>> df2
336 a c
337 0 foo 3
338 1 baz 4
340 >>> df1.merge(df2, how="inner", on="a")
341 a b c
342 0 foo 1 3
344 >>> df1.merge(df2, how="left", on="a")
345 a b c
346 0 foo 1 3.0
347 1 bar 2 NaN
349 >>> df1 = pd.DataFrame({"left": ["foo", "bar"]})
350 >>> df2 = pd.DataFrame({"right": [7, 8]})
351 >>> df1
352 left
353 0 foo
354 1 bar
355 >>> df2
356 right
357 0 7
358 1 8
360 >>> df1.merge(df2, how="cross")
361 left right
362 0 foo 7
363 1 foo 8
364 2 bar 7
365 3 bar 8
366 """
367 left_df = _validate_operand(left)
368 left._check_copy_deprecation(copy)
369 right_df = _validate_operand(right)
370 if how == "cross":
371 return _cross_merge(
372 left_df,
373 right_df,
374 on=on,
375 left_on=left_on,
376 right_on=right_on,
377 left_index=left_index,
378 right_index=right_index,
379 sort=sort,
380 suffixes=suffixes,
381 indicator=indicator,
382 validate=validate,
383 )
384 else:
385 op = _MergeOperation(
386 left_df,
387 right_df,
388 how=how,
389 on=on,
390 left_on=left_on,
391 right_on=right_on,
392 left_index=left_index,
393 right_index=right_index,
394 sort=sort,
395 suffixes=suffixes,
396 indicator=indicator,
397 validate=validate,
398 )
399 return op.get_result()
402def _cross_merge(
403 left: DataFrame,
404 right: DataFrame,
405 on: IndexLabel | AnyArrayLike | None = None,
406 left_on: IndexLabel | AnyArrayLike | None = None,
407 right_on: IndexLabel | AnyArrayLike | None = None,
408 left_index: bool = False,
409 right_index: bool = False,
410 sort: bool = False,
411 suffixes: Suffixes = ("_x", "_y"),
412 indicator: str | bool = False,
413 validate: str | None = None,
414) -> DataFrame:
415 """
416 See merge.__doc__ with how='cross'
417 """
419 if (
420 left_index
421 or right_index
422 or right_on is not None
423 or left_on is not None
424 or on is not None
425 ):
426 raise MergeError(
427 "Can not pass on, right_on, left_on or set right_index=True or "
428 "left_index=True"
429 )
431 cross_col = f"_cross_{uuid.uuid4()}"
432 left = left.assign(**{cross_col: 1})
433 right = right.assign(**{cross_col: 1})
435 left_on = right_on = [cross_col]
437 res = merge(
438 left,
439 right,
440 how="inner",
441 on=on,
442 left_on=left_on,
443 right_on=right_on,
444 left_index=left_index,
445 right_index=right_index,
446 sort=sort,
447 suffixes=suffixes,
448 indicator=indicator,
449 validate=validate,
450 )
451 del res[cross_col]
452 return res
455def _groupby_and_merge(
456 by, left: DataFrame | Series, right: DataFrame | Series, merge_pieces
457):
458 """
459 groupby & merge; we are always performing a left-by type operation
461 Parameters
462 ----------
463 by: field to group
464 left: DataFrame
465 right: DataFrame
466 merge_pieces: function for merging
467 """
468 pieces = []
469 if not isinstance(by, (list, tuple)):
470 by = [by]
472 lby = left.groupby(by, sort=False)
473 rby: groupby.DataFrameGroupBy | groupby.SeriesGroupBy | None = None
475 # if we can groupby the rhs
476 # then we can get vastly better perf
477 if all(item in right.columns for item in by):
478 rby = right.groupby(by, sort=False)
480 for key, lhs in lby._grouper.get_iterator(lby._selected_obj):
481 if rby is None:
482 rhs = right
483 else:
484 try:
485 rhs = right.take(rby.indices[key])
486 except KeyError:
487 # key doesn't exist in left
488 lcols = lhs.columns.tolist()
489 cols = lcols + [r for r in right.columns if r not in set(lcols)]
490 merged = lhs.reindex(columns=cols)
491 merged.index = range(len(merged))
492 pieces.append(merged)
493 continue
495 merged = merge_pieces(lhs, rhs)
497 # make sure join keys are in the merged
498 # TODO, should merge_pieces do this?
499 merged[by] = key
501 pieces.append(merged)
503 # preserve the original order
504 # if we have a missing piece this can be reset
505 from pandas.core.reshape.concat import concat
507 result = concat(pieces, ignore_index=True)
508 result = result.reindex(columns=pieces[0].columns)
509 return result, lby
512@set_module("pandas")
513def merge_ordered(
514 left: DataFrame | Series,
515 right: DataFrame | Series,
516 on: IndexLabel | None = None,
517 left_on: IndexLabel | None = None,
518 right_on: IndexLabel | None = None,
519 left_by=None,
520 right_by=None,
521 fill_method: str | None = None,
522 suffixes: Suffixes = ("_x", "_y"),
523 how: JoinHow = "outer",
524) -> DataFrame:
525 """
526 Perform a merge for ordered data with optional filling/interpolation.
528 Designed for ordered data like time series data. Optionally
529 perform group-wise merge (see examples).
531 Parameters
532 ----------
533 left : DataFrame or named Series
534 First pandas object to merge.
535 right : DataFrame or named Series
536 Second pandas object to merge.
537 on : Hashable or a sequence of the previous
538 Field names to join on. Must be found in both DataFrames.
539 left_on : Hashable or a sequence of the previous, or array-like
540 Field names to join on in left DataFrame. Can be a vector or list of
541 vectors of the length of the DataFrame to use a particular vector as
542 the join key instead of columns.
543 right_on : Hashable or a sequence of the previous, or array-like
544 Field names to join on in right DataFrame or vector/list of vectors per
545 left_on docs.
546 left_by : column name or list of column names
547 Group left DataFrame by group columns and merge piece by piece with
548 right DataFrame. Must be None if either left or right are a Series.
549 right_by : column name or list of column names
550 Group right DataFrame by group columns and merge piece by piece with
551 left DataFrame. Must be None if either left or right are a Series.
552 fill_method : {'ffill', None}, default None
553 Interpolation method for data.
554 suffixes : list-like, default is ("_x", "_y")
555 A length-2 sequence where each element is optionally a string
556 indicating the suffix to add to overlapping column names in
557 `left` and `right` respectively. Pass a value of `None` instead
558 of a string to indicate that the column name from `left` or
559 `right` should be left as-is, with no suffix. At least one of the
560 values must not be None.
562 how : {'left', 'right', 'outer', 'inner'}, default 'outer'
563 * left: use only keys from left frame (SQL: left outer join)
564 * right: use only keys from right frame (SQL: right outer join)
565 * outer: use union of keys from both frames (SQL: full outer join)
566 * inner: use intersection of keys from both frames (SQL: inner join).
568 Returns
569 -------
570 DataFrame
571 The merged DataFrame output type will be the same as
572 'left', if it is a subclass of DataFrame.
574 See Also
575 --------
576 merge : Merge with a database-style join.
577 merge_asof : Merge on nearest keys.
579 Examples
580 --------
581 >>> from pandas import merge_ordered
582 >>> df1 = pd.DataFrame(
583 ... {
584 ... "key": ["a", "c", "e", "a", "c", "e"],
585 ... "lvalue": [1, 2, 3, 1, 2, 3],
586 ... "group": ["a", "a", "a", "b", "b", "b"],
587 ... }
588 ... )
589 >>> df1
590 key lvalue group
591 0 a 1 a
592 1 c 2 a
593 2 e 3 a
594 3 a 1 b
595 4 c 2 b
596 5 e 3 b
598 >>> df2 = pd.DataFrame({"key": ["b", "c", "d"], "rvalue": [1, 2, 3]})
599 >>> df2
600 key rvalue
601 0 b 1
602 1 c 2
603 2 d 3
605 >>> merge_ordered(df1, df2, fill_method="ffill", left_by="group")
606 key lvalue group rvalue
607 0 a 1 a NaN
608 1 b 1 a 1.0
609 2 c 2 a 2.0
610 3 d 2 a 3.0
611 4 e 3 a 3.0
612 5 a 1 b NaN
613 6 b 1 b 1.0
614 7 c 2 b 2.0
615 8 d 2 b 3.0
616 9 e 3 b 3.0
617 """
619 def _merger(x, y) -> DataFrame:
620 # perform the ordered merge operation
621 op = _OrderedMerge(
622 x,
623 y,
624 on=on,
625 left_on=left_on,
626 right_on=right_on,
627 suffixes=suffixes,
628 fill_method=fill_method,
629 how=how,
630 )
631 return op.get_result()
633 if left_by is not None and right_by is not None:
634 raise ValueError("Can only group either left or right frames")
635 if left_by is not None:
636 if isinstance(left_by, str):
637 left_by = [left_by]
638 check = set(left_by).difference(left.columns)
639 if len(check) != 0:
640 raise KeyError(f"{check} not found in left columns")
641 result, _ = _groupby_and_merge(left_by, left, right, lambda x, y: _merger(x, y))
642 elif right_by is not None:
643 if isinstance(right_by, str):
644 right_by = [right_by]
645 check = set(right_by).difference(right.columns)
646 if len(check) != 0:
647 raise KeyError(f"{check} not found in right columns")
648 result, _ = _groupby_and_merge(
649 right_by, right, left, lambda x, y: _merger(y, x)
650 )
651 else:
652 result = _merger(left, right)
653 return result
656@set_module("pandas")
657def merge_asof(
658 left: DataFrame | Series,
659 right: DataFrame | Series,
660 on: IndexLabel | None = None,
661 left_on: IndexLabel | None = None,
662 right_on: IndexLabel | None = None,
663 left_index: bool = False,
664 right_index: bool = False,
665 by=None,
666 left_by=None,
667 right_by=None,
668 suffixes: Suffixes = ("_x", "_y"),
669 tolerance: int | datetime.timedelta | None = None,
670 allow_exact_matches: bool = True,
671 direction: str = "backward",
672) -> DataFrame:
673 """
674 Perform a merge by key distance.
676 This is similar to a left-join except that we match on nearest
677 key rather than equal keys. Both DataFrames must be first sorted by
678 the merge key in ascending order before calling this function.
679 Sorting by any additional 'by' grouping columns is not required.
681 For each row in the left DataFrame:
683 - A "backward" search selects the last row in the right DataFrame whose
684 'on' key is less than or equal to the left's key.
686 - A "forward" search selects the first row in the right DataFrame whose
687 'on' key is greater than or equal to the left's key.
689 - A "nearest" search selects the row in the right DataFrame whose 'on'
690 key is closest in absolute distance to the left's key.
692 Optionally match on equivalent keys with 'by' before searching with 'on'.
694 Parameters
695 ----------
696 left : DataFrame or named Series
697 First pandas object to merge.
698 right : DataFrame or named Series
699 Second pandas object to merge.
700 on : label
701 Field name to join on. Must be found in both DataFrames.
702 The data MUST be in ascending order. Furthermore this must be
703 a numeric column, such as datetimelike, integer, or float. ``on``
704 or ``left_on`` / ``right_on`` must be given.
705 left_on : label
706 Field name to join on in left DataFrame. If specified, sort the left
707 DataFrame by this column in ascending order before merging.
708 right_on : label
709 Field name to join on in right DataFrame. If specified, sort the right
710 DataFrame by this column in ascending order before merging.
711 left_index : bool
712 Use the index of the left DataFrame as the join key.
713 right_index : bool
714 Use the index of the right DataFrame as the join key.
715 by : column name or list of column names
716 Match on these columns before performing merge operation. It is not required
717 to sort by these columns.
718 left_by : column name
719 Field names to match on in the left DataFrame.
720 right_by : column name
721 Field names to match on in the right DataFrame.
722 suffixes : 2-length sequence (tuple, list, ...)
723 Suffix to apply to overlapping column names in the left and right
724 side, respectively.
725 tolerance : int or timedelta, optional, default None
726 Select asof tolerance within this range; must be compatible
727 with the merge index.
728 allow_exact_matches : bool, default True
730 - If True, allow matching with the same 'on' value
731 (i.e. less-than-or-equal-to / greater-than-or-equal-to)
732 - If False, don't match the same 'on' value
733 (i.e., strictly less-than / strictly greater-than).
735 direction : 'backward' (default), 'forward', or 'nearest'
736 Whether to search for prior, subsequent, or closest matches.
738 Returns
739 -------
740 DataFrame
741 A DataFrame of the two merged objects, containing all rows from the
742 left DataFrame and the nearest matches from the right DataFrame.
744 See Also
745 --------
746 merge : Merge with a database-style join.
747 merge_ordered : Merge with optional filling/interpolation.
749 Examples
750 --------
751 >>> left = pd.DataFrame({"a": [1, 5, 10], "left_val": ["a", "b", "c"]})
752 >>> left
753 a left_val
754 0 1 a
755 1 5 b
756 2 10 c
758 >>> right = pd.DataFrame({"a": [1, 2, 3, 6, 7], "right_val": [1, 2, 3, 6, 7]})
759 >>> right
760 a right_val
761 0 1 1
762 1 2 2
763 2 3 3
764 3 6 6
765 4 7 7
767 >>> pd.merge_asof(left, right, on="a")
768 a left_val right_val
769 0 1 a 1
770 1 5 b 3
771 2 10 c 7
773 >>> pd.merge_asof(left, right, on="a", allow_exact_matches=False)
774 a left_val right_val
775 0 1 a NaN
776 1 5 b 3.0
777 2 10 c 7.0
779 >>> pd.merge_asof(left, right, on="a", direction="forward")
780 a left_val right_val
781 0 1 a 1.0
782 1 5 b 6.0
783 2 10 c NaN
785 >>> pd.merge_asof(left, right, on="a", direction="nearest")
786 a left_val right_val
787 0 1 a 1
788 1 5 b 6
789 2 10 c 7
791 We can use indexed DataFrames as well.
793 >>> left = pd.DataFrame({"left_val": ["a", "b", "c"]}, index=[1, 5, 10])
794 >>> left
795 left_val
796 1 a
797 5 b
798 10 c
800 >>> right = pd.DataFrame({"right_val": [1, 2, 3, 6, 7]}, index=[1, 2, 3, 6, 7])
801 >>> right
802 right_val
803 1 1
804 2 2
805 3 3
806 6 6
807 7 7
809 >>> pd.merge_asof(left, right, left_index=True, right_index=True)
810 left_val right_val
811 1 a 1
812 5 b 3
813 10 c 7
815 Here is a real-world times-series example
817 >>> quotes = pd.DataFrame(
818 ... {
819 ... "time": [
820 ... pd.Timestamp("2016-05-25 13:30:00.023"),
821 ... pd.Timestamp("2016-05-25 13:30:00.023"),
822 ... pd.Timestamp("2016-05-25 13:30:00.030"),
823 ... pd.Timestamp("2016-05-25 13:30:00.041"),
824 ... pd.Timestamp("2016-05-25 13:30:00.048"),
825 ... pd.Timestamp("2016-05-25 13:30:00.049"),
826 ... pd.Timestamp("2016-05-25 13:30:00.072"),
827 ... pd.Timestamp("2016-05-25 13:30:00.075"),
828 ... ],
829 ... "ticker": [
830 ... "GOOG",
831 ... "MSFT",
832 ... "MSFT",
833 ... "MSFT",
834 ... "GOOG",
835 ... "AAPL",
836 ... "GOOG",
837 ... "MSFT",
838 ... ],
839 ... "bid": [720.50, 51.95, 51.97, 51.99, 720.50, 97.99, 720.50, 52.01],
840 ... "ask": [720.93, 51.96, 51.98, 52.00, 720.93, 98.01, 720.88, 52.03],
841 ... }
842 ... )
843 >>> quotes
844 time ticker bid ask
845 0 2016-05-25 13:30:00.023 GOOG 720.50 720.93
846 1 2016-05-25 13:30:00.023 MSFT 51.95 51.96
847 2 2016-05-25 13:30:00.030 MSFT 51.97 51.98
848 3 2016-05-25 13:30:00.041 MSFT 51.99 52.00
849 4 2016-05-25 13:30:00.048 GOOG 720.50 720.93
850 5 2016-05-25 13:30:00.049 AAPL 97.99 98.01
851 6 2016-05-25 13:30:00.072 GOOG 720.50 720.88
852 7 2016-05-25 13:30:00.075 MSFT 52.01 52.03
854 >>> trades = pd.DataFrame(
855 ... {
856 ... "time": [
857 ... pd.Timestamp("2016-05-25 13:30:00.023"),
858 ... pd.Timestamp("2016-05-25 13:30:00.038"),
859 ... pd.Timestamp("2016-05-25 13:30:00.048"),
860 ... pd.Timestamp("2016-05-25 13:30:00.048"),
861 ... pd.Timestamp("2016-05-25 13:30:00.048"),
862 ... ],
863 ... "ticker": ["MSFT", "MSFT", "GOOG", "GOOG", "AAPL"],
864 ... "price": [51.95, 51.95, 720.77, 720.92, 98.0],
865 ... "quantity": [75, 155, 100, 100, 100],
866 ... }
867 ... )
868 >>> trades
869 time ticker price quantity
870 0 2016-05-25 13:30:00.023 MSFT 51.95 75
871 1 2016-05-25 13:30:00.038 MSFT 51.95 155
872 2 2016-05-25 13:30:00.048 GOOG 720.77 100
873 3 2016-05-25 13:30:00.048 GOOG 720.92 100
874 4 2016-05-25 13:30:00.048 AAPL 98.00 100
876 By default we are taking the asof of the quotes
878 >>> pd.merge_asof(trades, quotes, on="time", by="ticker")
879 time ticker price quantity bid ask
880 0 2016-05-25 13:30:00.023 MSFT 51.95 75 51.95 51.96
881 1 2016-05-25 13:30:00.038 MSFT 51.95 155 51.97 51.98
882 2 2016-05-25 13:30:00.048 GOOG 720.77 100 720.50 720.93
883 3 2016-05-25 13:30:00.048 GOOG 720.92 100 720.50 720.93
884 4 2016-05-25 13:30:00.048 AAPL 98.00 100 NaN NaN
886 We only asof within 2ms between the quote time and the trade time
888 >>> pd.merge_asof(
889 ... trades, quotes, on="time", by="ticker", tolerance=pd.Timedelta("2ms")
890 ... )
891 time ticker price quantity bid ask
892 0 2016-05-25 13:30:00.023 MSFT 51.95 75 51.95 51.96
893 1 2016-05-25 13:30:00.038 MSFT 51.95 155 NaN NaN
894 2 2016-05-25 13:30:00.048 GOOG 720.77 100 720.50 720.93
895 3 2016-05-25 13:30:00.048 GOOG 720.92 100 720.50 720.93
896 4 2016-05-25 13:30:00.048 AAPL 98.00 100 NaN NaN
898 We only asof within 10ms between the quote time and the trade time
899 and we exclude exact matches on time. However *prior* data will
900 propagate forward
902 >>> pd.merge_asof(
903 ... trades,
904 ... quotes,
905 ... on="time",
906 ... by="ticker",
907 ... tolerance=pd.Timedelta("10ms"),
908 ... allow_exact_matches=False,
909 ... )
910 time ticker price quantity bid ask
911 0 2016-05-25 13:30:00.023 MSFT 51.95 75 NaN NaN
912 1 2016-05-25 13:30:00.038 MSFT 51.95 155 51.97 51.98
913 2 2016-05-25 13:30:00.048 GOOG 720.77 100 NaN NaN
914 3 2016-05-25 13:30:00.048 GOOG 720.92 100 NaN NaN
915 4 2016-05-25 13:30:00.048 AAPL 98.00 100 NaN NaN
916 """
917 op = _AsOfMerge(
918 left,
919 right,
920 on=on,
921 left_on=left_on,
922 right_on=right_on,
923 left_index=left_index,
924 right_index=right_index,
925 by=by,
926 left_by=left_by,
927 right_by=right_by,
928 suffixes=suffixes,
929 how="asof",
930 tolerance=tolerance,
931 allow_exact_matches=allow_exact_matches,
932 direction=direction,
933 )
934 return op.get_result()
937# TODO: transformations??
938class _MergeOperation:
939 """
940 Perform a database (SQL) merge operation between two DataFrame or Series
941 objects using either columns as keys or their row indexes
942 """
944 _merge_type = "merge"
945 how: JoinHow | Literal["asof"]
946 on: IndexLabel | None
947 # left_on/right_on may be None when passed, but in validate_specification
948 # get replaced with non-None.
949 left_on: Sequence[Hashable | AnyArrayLike]
950 right_on: Sequence[Hashable | AnyArrayLike]
951 left_index: bool
952 right_index: bool
953 sort: bool
954 suffixes: Suffixes
955 indicator: str | bool
956 validate: str | None
957 join_names: list[Hashable]
958 right_join_keys: list[ArrayLike]
959 left_join_keys: list[ArrayLike]
961 def __init__(
962 self,
963 left: DataFrame | Series,
964 right: DataFrame | Series,
965 how: JoinHow | Literal["left_anti", "right_anti", "asof"] = "inner",
966 on: IndexLabel | AnyArrayLike | None = None,
967 left_on: IndexLabel | AnyArrayLike | None = None,
968 right_on: IndexLabel | AnyArrayLike | None = None,
969 left_index: bool = False,
970 right_index: bool = False,
971 sort: bool = True,
972 suffixes: Suffixes = ("_x", "_y"),
973 indicator: str | bool = False,
974 validate: str | None = None,
975 ) -> None:
976 _left = _validate_operand(left)
977 _right = _validate_operand(right)
978 self.left = self.orig_left = _left
979 self.right = self.orig_right = _right
980 self.how, self.anti_join = self._validate_how(how)
982 self.on = com.maybe_make_list(on)
984 self.suffixes = suffixes
985 self.sort = sort or how == "outer"
987 self.left_index = left_index
988 self.right_index = right_index
990 self.indicator = indicator
992 if not is_bool(left_index):
993 raise ValueError(
994 f"left_index parameter must be of type bool, not {type(left_index)}"
995 )
996 if not is_bool(right_index):
997 raise ValueError(
998 f"right_index parameter must be of type bool, not {type(right_index)}"
999 )
1001 # GH 40993: raise when merging between different levels; enforced in 2.0
1002 if _left.columns.nlevels != _right.columns.nlevels:
1003 msg = (
1004 "Not allowed to merge between different levels. "
1005 f"({_left.columns.nlevels} levels on the left, "
1006 f"{_right.columns.nlevels} on the right)"
1007 )
1008 raise MergeError(msg)
1010 self.left_on, self.right_on = self._validate_left_right_on(left_on, right_on)
1012 (
1013 self.left_join_keys,
1014 self.right_join_keys,
1015 self.join_names,
1016 left_drop,
1017 right_drop,
1018 ) = self._get_merge_keys()
1020 if left_drop:
1021 self.left = self.left._drop_labels_or_levels(left_drop)
1023 if right_drop:
1024 self.right = self.right._drop_labels_or_levels(right_drop)
1026 self._maybe_require_matching_dtypes(self.left_join_keys, self.right_join_keys)
1027 self._validate_tolerance(self.left_join_keys)
1029 # validate the merge keys dtypes. We may need to coerce
1030 # to avoid incompatible dtypes
1031 self._maybe_coerce_merge_keys()
1033 # If argument passed to validate,
1034 # check if columns specified as unique
1035 # are in fact unique.
1036 if validate is not None:
1037 self._validate_validate_kwd(validate)
1039 @final
1040 def _validate_how(
1041 self, how: JoinHow | Literal["left_anti", "right_anti", "asof"]
1042 ) -> tuple[JoinHow | Literal["asof"], bool]:
1043 """
1044 Validate the 'how' parameter and return the actual join type and whether
1045 this is an anti join.
1046 """
1047 # GH 59435: raise when "how" is not a valid Merge type
1048 merge_type = {
1049 "left",
1050 "right",
1051 "inner",
1052 "outer",
1053 "left_anti",
1054 "right_anti",
1055 "cross",
1056 "asof",
1057 }
1058 if how not in merge_type:
1059 raise ValueError(
1060 f"'{how}' is not a valid Merge type: "
1061 f"left, right, inner, outer, left_anti, right_anti, cross, asof"
1062 )
1063 anti_join = False
1064 if how in {"left_anti", "right_anti"}:
1065 how = how.split("_")[0] # type: ignore[assignment]
1066 anti_join = True
1067 how = cast(JoinHow | Literal["asof"], how)
1068 return how, anti_join
1070 def _maybe_require_matching_dtypes(
1071 self, left_join_keys: list[ArrayLike], right_join_keys: list[ArrayLike]
1072 ) -> None:
1073 # Overridden by AsOfMerge
1074 pass
1076 def _validate_tolerance(self, left_join_keys: list[ArrayLike]) -> None:
1077 # Overridden by AsOfMerge
1078 pass
1080 @final
1081 def _reindex_and_concat(
1082 self,
1083 join_index: Index,
1084 left_indexer: npt.NDArray[np.intp] | None,
1085 right_indexer: npt.NDArray[np.intp] | None,
1086 ) -> DataFrame:
1087 """
1088 reindex along index and concat along columns.
1089 """
1090 # Take views so we do not alter the originals
1091 left = self.left[:]
1092 right = self.right[:]
1094 llabels, rlabels = _items_overlap_with_suffix(
1095 self.left._info_axis, self.right._info_axis, self.suffixes
1096 )
1098 if left_indexer is not None and not is_range_indexer(left_indexer, len(left)):
1099 # Pinning the index here (and in the right code just below) is not
1100 # necessary, but makes the `.take` more performant if we have e.g.
1101 # a MultiIndex for left.index.
1102 lmgr = left._mgr.reindex_indexer(
1103 join_index,
1104 left_indexer,
1105 axis=1,
1106 only_slice=True,
1107 allow_dups=True,
1108 use_na_proxy=True,
1109 )
1110 left = left._constructor_from_mgr(lmgr, axes=lmgr.axes)
1111 left.index = join_index
1113 if right_indexer is not None and not is_range_indexer(
1114 right_indexer, len(right)
1115 ):
1116 rmgr = right._mgr.reindex_indexer(
1117 join_index,
1118 right_indexer,
1119 axis=1,
1120 only_slice=True,
1121 allow_dups=True,
1122 use_na_proxy=True,
1123 )
1124 right = right._constructor_from_mgr(rmgr, axes=rmgr.axes)
1125 right.index = join_index
1127 from pandas import concat
1129 left.columns = llabels
1130 right.columns = rlabels
1131 result = concat([left, right], axis=1)
1132 return result
1134 def get_result(self) -> DataFrame:
1135 """
1136 Execute the merge.
1137 """
1138 if self.indicator:
1139 self.left, self.right = self._indicator_pre_merge(self.left, self.right)
1141 join_index, left_indexer, right_indexer = self._get_join_info()
1143 result = self._reindex_and_concat(join_index, left_indexer, right_indexer)
1145 if self.indicator:
1146 result = self._indicator_post_merge(result)
1148 self._maybe_add_join_keys(result, left_indexer, right_indexer)
1150 self._maybe_restore_index_levels(result)
1152 return result.__finalize__(
1153 types.SimpleNamespace(
1154 input_objs=[self.left, self.right], left=self.left, right=self.right
1155 ),
1156 method="merge",
1157 )
1159 @final
1160 @cache_readonly
1161 def _indicator_name(self) -> str | None:
1162 if isinstance(self.indicator, str):
1163 return self.indicator
1164 elif isinstance(self.indicator, bool):
1165 return "_merge" if self.indicator else None
1166 else:
1167 raise ValueError(
1168 "indicator option can only accept boolean or string arguments"
1169 )
1171 @final
1172 def _indicator_pre_merge(
1173 self, left: DataFrame, right: DataFrame
1174 ) -> tuple[DataFrame, DataFrame]:
1175 """
1176 Add one indicator column to each of the left and right inputs.
1178 These columns are used to produce another column in the output of the
1179 merge, indicating for each row of the output whether it was produced
1180 using the left, right or both inputs.
1181 """
1182 columns = left.columns.union(right.columns)
1184 for i in ["_left_indicator", "_right_indicator"]:
1185 if i in columns:
1186 raise ValueError(
1187 "Cannot use `indicator=True` option when "
1188 f"data contains a column named {i}"
1189 )
1190 if self._indicator_name in columns:
1191 raise ValueError(
1192 "Cannot use name of an existing column for indicator column"
1193 )
1195 left = left.copy(deep=False)
1196 right = right.copy(deep=False)
1198 left["_left_indicator"] = 1
1199 left["_left_indicator"] = left["_left_indicator"].astype("int8")
1201 right["_right_indicator"] = 2
1202 right["_right_indicator"] = right["_right_indicator"].astype("int8")
1204 return left, right
1206 @final
1207 def _indicator_post_merge(self, result: DataFrame) -> DataFrame:
1208 """
1209 Add an indicator column to the merge result.
1211 This column indicates for each row of the output whether it was produced using
1212 the left, right or both inputs.
1213 """
1214 result["_left_indicator"] = result["_left_indicator"].fillna(0)
1215 result["_right_indicator"] = result["_right_indicator"].fillna(0)
1217 result[self._indicator_name] = Categorical(
1218 (result["_left_indicator"] + result["_right_indicator"]),
1219 categories=[1, 2, 3],
1220 )
1221 result[self._indicator_name] = result[
1222 self._indicator_name
1223 ].cat.rename_categories(["left_only", "right_only", "both"])
1225 result = result.drop(labels=["_left_indicator", "_right_indicator"], axis=1)
1226 return result
1228 @final
1229 def _maybe_restore_index_levels(self, result: DataFrame) -> None:
1230 """
1231 Restore index levels specified as `on` parameters
1233 Here we check for cases where `self.left_on` and `self.right_on` pairs
1234 each reference an index level in their respective DataFrames. The
1235 joined columns corresponding to these pairs are then restored to the
1236 index of `result`.
1238 **Note:** This method has side effects. It modifies `result` in-place
1240 Parameters
1241 ----------
1242 result: DataFrame
1243 merge result
1245 Returns
1246 -------
1247 None
1248 """
1249 names_to_restore = []
1250 for name, left_key, right_key in zip(
1251 self.join_names, self.left_on, self.right_on, strict=True
1252 ):
1253 if (
1254 # Argument 1 to "_is_level_reference" of "NDFrame" has incompatible
1255 # type "Union[Hashable, ExtensionArray, Index, Series]"; expected
1256 # "Hashable"
1257 self.orig_left._is_level_reference(left_key) # type: ignore[arg-type]
1258 # Argument 1 to "_is_level_reference" of "NDFrame" has incompatible
1259 # type "Union[Hashable, ExtensionArray, Index, Series]"; expected
1260 # "Hashable"
1261 and self.orig_right._is_level_reference(
1262 right_key # type: ignore[arg-type]
1263 )
1264 and left_key == right_key
1265 and name not in result.index.names
1266 ):
1267 names_to_restore.append(name)
1269 if names_to_restore:
1270 result.set_index(names_to_restore, inplace=True)
1272 @final
1273 def _maybe_add_join_keys(
1274 self,
1275 result: DataFrame,
1276 left_indexer: npt.NDArray[np.intp] | None,
1277 right_indexer: npt.NDArray[np.intp] | None,
1278 ) -> None:
1279 left_has_missing = None
1280 right_has_missing = None
1282 assert all(isinstance(x, _known) for x in self.left_join_keys)
1284 keys = zip(self.join_names, self.left_on, self.right_on, strict=True)
1285 for i, (name, lname, rname) in enumerate(keys):
1286 if not _should_fill(lname, rname):
1287 continue
1289 take_left, take_right = None, None
1291 if name in result:
1292 if left_indexer is not None or right_indexer is not None:
1293 if name in self.left:
1294 if left_has_missing is None:
1295 left_has_missing = (
1296 False
1297 if left_indexer is None
1298 else (left_indexer == -1).any()
1299 )
1301 if left_has_missing:
1302 take_right = self.right_join_keys[i]
1304 if result[name].dtype != self.left[name].dtype:
1305 take_left = self.left[name]._values
1307 elif name in self.right:
1308 if right_has_missing is None:
1309 right_has_missing = (
1310 False
1311 if right_indexer is None
1312 else (right_indexer == -1).any()
1313 )
1315 if right_has_missing:
1316 take_left = self.left_join_keys[i]
1318 if result[name].dtype != self.right[name].dtype:
1319 take_right = self.right[name]._values
1321 else:
1322 take_left = self.left_join_keys[i]
1323 take_right = self.right_join_keys[i]
1325 if take_left is not None or take_right is not None:
1326 if take_left is None:
1327 lvals = result[name]._values
1328 elif left_indexer is None:
1329 lvals = take_left
1330 else:
1331 # TODO: can we pin down take_left's type earlier?
1332 take_left = extract_array(take_left, extract_numpy=True)
1333 lfill = na_value_for_dtype(take_left.dtype)
1334 lvals = algos.take_nd(take_left, left_indexer, fill_value=lfill)
1336 if take_right is None:
1337 rvals = result[name]._values
1338 elif right_indexer is None:
1339 rvals = take_right
1340 else:
1341 # TODO: can we pin down take_right's type earlier?
1342 taker = extract_array(take_right, extract_numpy=True)
1343 rfill = na_value_for_dtype(taker.dtype)
1344 rvals = algos.take_nd(taker, right_indexer, fill_value=rfill)
1346 # if we have an all missing left_indexer
1347 # make sure to just use the right values or vice-versa
1348 if left_indexer is not None and (left_indexer == -1).all():
1349 key_col = Index(rvals, dtype=rvals.dtype, copy=False)
1350 result_dtype = rvals.dtype
1351 elif right_indexer is not None and (right_indexer == -1).all():
1352 key_col = Index(lvals, dtype=lvals.dtype, copy=False)
1353 result_dtype = lvals.dtype
1354 else:
1355 key_col = Index(lvals, dtype=lvals.dtype, copy=False)
1356 if left_indexer is not None:
1357 mask_left = left_indexer == -1
1358 key_col = key_col.where(~mask_left, rvals)
1359 result_dtype = find_common_type([lvals.dtype, rvals.dtype])
1360 if (
1361 lvals.dtype.kind == "M"
1362 and rvals.dtype.kind == "M"
1363 and result_dtype.kind == "O"
1364 ):
1365 # TODO(non-nano) Workaround for common_type not dealing
1366 # with different resolutions
1367 result_dtype = key_col.dtype
1369 if result._is_label_reference(name):
1370 result[name] = result._constructor_sliced(
1371 key_col, dtype=result_dtype, index=result.index
1372 )
1373 elif result._is_level_reference(name):
1374 if isinstance(result.index, MultiIndex):
1375 key_col.name = name
1376 idx_list = [
1377 (
1378 result.index.get_level_values(level_name)
1379 if level_name != name
1380 else key_col
1381 )
1382 for level_name in result.index.names
1383 ]
1385 result.set_index(idx_list, inplace=True)
1386 else:
1387 result.index = Index(key_col, name=name)
1388 else:
1389 result.insert(i, name or f"key_{i}", key_col)
1391 def _get_join_indexers(
1392 self,
1393 ) -> tuple[npt.NDArray[np.intp] | None, npt.NDArray[np.intp] | None]:
1394 """return the join indexers"""
1395 # make mypy happy
1396 assert self.how != "asof"
1397 return get_join_indexers(
1398 self.left_join_keys, self.right_join_keys, sort=self.sort, how=self.how
1399 )
1401 @final
1402 def _get_join_info(
1403 self,
1404 ) -> tuple[Index, npt.NDArray[np.intp] | None, npt.NDArray[np.intp] | None]:
1405 left_ax = self.left.index
1406 right_ax = self.right.index
1408 if self.left_index and self.right_index and self.how != "asof":
1409 join_index, left_indexer, right_indexer = left_ax.join(
1410 right_ax, how=self.how, return_indexers=True, sort=self.sort
1411 )
1413 elif self.right_index and self.how == "left":
1414 join_index, left_indexer, right_indexer = _left_join_on_index(
1415 left_ax, right_ax, self.left_join_keys, sort=self.sort
1416 )
1418 elif self.left_index and self.how == "right":
1419 join_index, right_indexer, left_indexer = _left_join_on_index(
1420 right_ax, left_ax, self.right_join_keys, sort=self.sort
1421 )
1422 else:
1423 (left_indexer, right_indexer) = self._get_join_indexers()
1425 if self.right_index:
1426 if len(self.left) > 0:
1427 join_index = self._create_join_index(
1428 left_ax,
1429 right_ax,
1430 left_indexer,
1431 how="right",
1432 )
1433 elif right_indexer is None:
1434 join_index = right_ax.copy()
1435 else:
1436 join_index = right_ax.take(right_indexer)
1437 elif self.left_index:
1438 if self.how == "asof":
1439 # GH#33463 asof should always behave like a left merge
1440 join_index = self._create_join_index(
1441 left_ax,
1442 right_ax,
1443 left_indexer,
1444 how="left",
1445 )
1447 elif len(self.right) > 0:
1448 join_index = self._create_join_index(
1449 right_ax,
1450 left_ax,
1451 right_indexer,
1452 how="left",
1453 )
1454 elif left_indexer is None:
1455 join_index = left_ax.copy()
1456 else:
1457 join_index = left_ax.take(left_indexer)
1458 else:
1459 n = len(left_ax) if left_indexer is None else len(left_indexer)
1460 join_index = default_index(n)
1462 if self.anti_join:
1463 join_index, left_indexer, right_indexer = self._handle_anti_join(
1464 join_index, left_indexer, right_indexer
1465 )
1467 return join_index, left_indexer, right_indexer
1469 @final
1470 def _create_join_index(
1471 self,
1472 index: Index,
1473 other_index: Index,
1474 indexer: npt.NDArray[np.intp] | None,
1475 how: JoinHow = "left",
1476 ) -> Index:
1477 """
1478 Create a join index by rearranging one index to match another
1480 Parameters
1481 ----------
1482 index : Index
1483 index being rearranged
1484 other_index : Index
1485 used to supply values not found in index
1486 indexer : np.ndarray[np.intp] or None
1487 how to rearrange index
1488 how : str
1489 Replacement is only necessary if indexer based on other_index.
1491 Returns
1492 -------
1493 Index
1494 """
1495 if self.how in (how, "outer") and not isinstance(other_index, MultiIndex):
1496 # if final index requires values in other_index but not target
1497 # index, indexer may hold missing (-1) values, causing Index.take
1498 # to take the final value in target index. So, we set the last
1499 # element to be the desired fill value. We do not use allow_fill
1500 # and fill_value because it throws a ValueError on integer indices
1501 mask = indexer == -1
1502 if np.any(mask):
1503 fill_value = na_value_for_dtype(index.dtype, compat=False)
1504 if not index._can_hold_na:
1505 new_index = Index([fill_value])
1506 else:
1507 new_index = Index([fill_value], dtype=index.dtype)
1508 index = index.append(new_index)
1509 if indexer is None:
1510 return index.copy()
1511 return index.take(indexer)
1513 @final
1514 def _handle_anti_join(
1515 self,
1516 join_index: Index,
1517 left_indexer: npt.NDArray[np.intp] | None,
1518 right_indexer: npt.NDArray[np.intp] | None,
1519 ) -> tuple[Index, npt.NDArray[np.intp] | None, npt.NDArray[np.intp] | None]:
1520 """
1521 Handle anti join by returning the correct join index and indexers
1523 Parameters
1524 ----------
1525 join_index : Index
1526 join index
1527 left_indexer : np.ndarray[np.intp] or None
1528 left indexer
1529 right_indexer : np.ndarray[np.intp] or None
1530 right indexer
1532 Returns
1533 -------
1534 Index, np.ndarray[np.intp] or None, np.ndarray[np.intp] or None
1535 """
1536 # Make sure indexers are not None
1537 if left_indexer is None:
1538 left_indexer = np.arange(len(self.left))
1539 if right_indexer is None:
1540 right_indexer = np.arange(len(self.right))
1542 assert self.how in {"left", "right"}
1543 if self.how == "left":
1544 # Filter to rows where left keys are not in right keys
1545 filt = right_indexer == -1
1546 else:
1547 # Filter to rows where right keys are not in left keys
1548 filt = left_indexer == -1
1549 join_index = join_index[filt]
1550 left_indexer = left_indexer[filt]
1551 right_indexer = right_indexer[filt]
1553 return join_index, left_indexer, right_indexer
1555 @final
1556 def _get_merge_keys(
1557 self,
1558 ) -> tuple[
1559 list[ArrayLike],
1560 list[ArrayLike],
1561 list[Hashable],
1562 list[Hashable],
1563 list[Hashable],
1564 ]:
1565 """
1566 Returns
1567 -------
1568 left_keys, right_keys, join_names, left_drop, right_drop
1569 """
1570 left_keys: list[ArrayLike] = []
1571 right_keys: list[ArrayLike] = []
1572 join_names: list[Hashable] = []
1573 right_drop: list[Hashable] = []
1574 left_drop: list[Hashable] = []
1576 left, right = self.left, self.right
1578 is_lkey = lambda x: isinstance(x, _known) and len(x) == len(left)
1579 is_rkey = lambda x: isinstance(x, _known) and len(x) == len(right)
1581 # Note that pd.merge_asof() has separate 'on' and 'by' parameters. A
1582 # user could, for example, request 'left_index' and 'left_by'. In a
1583 # regular pd.merge(), users cannot specify both 'left_index' and
1584 # 'left_on'. (Instead, users have a MultiIndex). That means the
1585 # self.left_on in this function is always empty in a pd.merge(), but
1586 # a pd.merge_asof(left_index=True, left_by=...) will result in a
1587 # self.left_on array with a None in the middle of it. This requires
1588 # a work-around as designated in the code below.
1589 # See _validate_left_right_on() for where this happens.
1591 # ugh, spaghetti re #733
1592 if _any(self.left_on) and _any(self.right_on):
1593 for lk, rk in zip(self.left_on, self.right_on, strict=True):
1594 lk = extract_array(lk, extract_numpy=True)
1595 rk = extract_array(rk, extract_numpy=True)
1596 if is_lkey(lk):
1597 lk = cast(ArrayLike, lk)
1598 left_keys.append(lk)
1599 if is_rkey(rk):
1600 rk = cast(ArrayLike, rk)
1601 right_keys.append(rk)
1602 join_names.append(None) # what to do?
1603 else:
1604 # Then we're either Hashable or a wrong-length arraylike,
1605 # the latter of which will raise
1606 rk = cast(Hashable, rk)
1607 if rk is not None:
1608 right_keys.append(right._get_label_or_level_values(rk))
1609 join_names.append(rk)
1610 else:
1611 # work-around for merge_asof(right_index=True)
1612 right_keys.append(right.index._values)
1613 join_names.append(right.index.name)
1614 else:
1615 if not is_rkey(rk):
1616 # Then we're either Hashable or a wrong-length arraylike,
1617 # the latter of which will raise
1618 rk = cast(Hashable, rk)
1619 if rk is not None:
1620 right_keys.append(right._get_label_or_level_values(rk))
1621 else:
1622 # work-around for merge_asof(right_index=True)
1623 right_keys.append(right.index._values)
1624 if lk is not None and lk == rk: # FIXME: what about other NAs?
1625 right_drop.append(rk)
1626 else:
1627 rk = cast(ArrayLike, rk)
1628 right_keys.append(rk)
1629 if lk is not None:
1630 # Then we're either Hashable or a wrong-length arraylike,
1631 # the latter of which will raise
1632 lk = cast(Hashable, lk)
1633 left_keys.append(left._get_label_or_level_values(lk))
1634 join_names.append(lk)
1635 else:
1636 # work-around for merge_asof(left_index=True)
1637 left_keys.append(left.index._values)
1638 join_names.append(left.index.name)
1639 elif _any(self.left_on):
1640 for k in self.left_on:
1641 if is_lkey(k):
1642 k = extract_array(k, extract_numpy=True)
1643 k = cast(ArrayLike, k)
1644 left_keys.append(k)
1645 join_names.append(None)
1646 else:
1647 # Then we're either Hashable or a wrong-length arraylike,
1648 # the latter of which will raise
1649 k = cast(Hashable, k)
1650 left_keys.append(left._get_label_or_level_values(k))
1651 join_names.append(k)
1652 if isinstance(self.right.index, MultiIndex):
1653 right_keys = [
1654 lev._values.take(lev_codes)
1655 for lev, lev_codes in zip(
1656 self.right.index.levels, self.right.index.codes, strict=True
1657 )
1658 ]
1659 else:
1660 right_keys = [self.right.index._values]
1661 elif _any(self.right_on):
1662 for k in self.right_on:
1663 k = extract_array(k, extract_numpy=True)
1664 if is_rkey(k):
1665 k = cast(ArrayLike, k)
1666 right_keys.append(k)
1667 join_names.append(None)
1668 else:
1669 # Then we're either Hashable or a wrong-length arraylike,
1670 # the latter of which will raise
1671 k = cast(Hashable, k)
1672 right_keys.append(right._get_label_or_level_values(k))
1673 join_names.append(k)
1674 if isinstance(self.left.index, MultiIndex):
1675 left_keys = [
1676 lev._values.take(lev_codes)
1677 for lev, lev_codes in zip(
1678 self.left.index.levels, self.left.index.codes, strict=True
1679 )
1680 ]
1681 else:
1682 left_keys = [self.left.index._values]
1684 return left_keys, right_keys, join_names, left_drop, right_drop
1686 @final
1687 def _maybe_coerce_merge_keys(self) -> None:
1688 # we have valid merges but we may have to further
1689 # coerce these if they are originally incompatible types
1690 #
1691 # for example if these are categorical, but are not dtype_equal
1692 # or if we have object and integer dtypes
1694 for lk, rk, name in zip(
1695 self.left_join_keys, self.right_join_keys, self.join_names, strict=True
1696 ):
1697 if (len(lk) and not len(rk)) or (not len(lk) and len(rk)):
1698 continue
1700 lk = extract_array(lk, extract_numpy=True)
1701 rk = extract_array(rk, extract_numpy=True)
1703 lk_is_cat = isinstance(lk.dtype, CategoricalDtype)
1704 rk_is_cat = isinstance(rk.dtype, CategoricalDtype)
1705 lk_is_object_or_string = is_object_dtype(lk.dtype) or is_string_dtype(
1706 lk.dtype
1707 )
1708 rk_is_object_or_string = is_object_dtype(rk.dtype) or is_string_dtype(
1709 rk.dtype
1710 )
1712 # if either left or right is a categorical
1713 # then the must match exactly in categories & ordered
1714 if lk_is_cat and rk_is_cat:
1715 lk = cast(Categorical, lk)
1716 rk = cast(Categorical, rk)
1717 if lk._categories_match_up_to_permutation(rk):
1718 continue
1720 elif lk_is_cat or rk_is_cat:
1721 pass
1723 elif lk.dtype == rk.dtype:
1724 continue
1726 msg = (
1727 f"You are trying to merge on {lk.dtype} and {rk.dtype} columns "
1728 f"for key '{name}'. If you wish to proceed you should use pd.concat"
1729 )
1731 # if we are numeric, then allow differing
1732 # kinds to proceed, eg. int64 and int8, int and float
1733 # further if we are object, but we infer to
1734 # the same, then proceed
1735 if is_numeric_dtype(lk.dtype) and is_numeric_dtype(rk.dtype):
1736 if lk.dtype.kind == rk.dtype.kind:
1737 continue
1739 if isinstance(lk.dtype, ExtensionDtype) and not isinstance(
1740 rk.dtype, ExtensionDtype
1741 ):
1742 ct = find_common_type([lk.dtype, rk.dtype])
1743 if isinstance(ct, ExtensionDtype):
1744 com_cls = ct.construct_array_type()
1745 rk = com_cls._from_sequence(rk, dtype=ct, copy=False)
1746 else:
1747 rk = rk.astype(ct)
1748 elif isinstance(rk.dtype, ExtensionDtype):
1749 ct = find_common_type([lk.dtype, rk.dtype])
1750 if isinstance(ct, ExtensionDtype):
1751 com_cls = ct.construct_array_type()
1752 lk = com_cls._from_sequence(lk, dtype=ct, copy=False)
1753 else:
1754 lk = lk.astype(ct)
1756 # check whether ints and floats
1757 if is_integer_dtype(rk.dtype) and is_float_dtype(lk.dtype):
1758 # GH 47391 numpy > 1.24 will raise a RuntimeError for nan -> int
1759 with np.errstate(invalid="ignore"):
1760 # error: Argument 1 to "astype" of "ndarray" has incompatible
1761 # type "Union[ExtensionDtype, Any, dtype[Any]]"; expected
1762 # "Union[dtype[Any], Type[Any], _SupportsDType[dtype[Any]]]"
1763 casted = lk.astype(rk.dtype) # type: ignore[arg-type]
1765 mask = ~np.isnan(lk)
1766 match = lk == casted
1767 # error: Item "ExtensionArray" of
1768 # "ExtensionArray | Any" has no attribute "all"
1769 if not match[mask].all(): # type: ignore[union-attr]
1770 warnings.warn(
1771 "You are merging on int and float "
1772 "columns where the float values "
1773 "are not equal to their int representation.",
1774 UserWarning,
1775 stacklevel=find_stack_level(),
1776 )
1777 continue
1779 if is_float_dtype(rk.dtype) and is_integer_dtype(lk.dtype):
1780 # GH 47391 numpy > 1.24 will raise a RuntimeError for nan -> int
1781 with np.errstate(invalid="ignore"):
1782 # error: Argument 1 to "astype" of "ndarray" has incompatible
1783 # type "Union[ExtensionDtype, Any, dtype[Any]]"; expected
1784 # "Union[dtype[Any], Type[Any], _SupportsDType[dtype[Any]]]"
1785 casted = rk.astype(lk.dtype) # type: ignore[arg-type]
1787 mask = ~np.isnan(rk)
1788 match = rk == casted
1789 # error: Item "ExtensionArray" of
1790 # "ExtensionArray | Any" has no attribute "all"
1791 if not match[mask].all(): # type: ignore[union-attr]
1792 warnings.warn(
1793 "You are merging on int and float "
1794 "columns where the float values "
1795 "are not equal to their int representation.",
1796 UserWarning,
1797 stacklevel=find_stack_level(),
1798 )
1799 continue
1801 # let's infer and see if we are ok
1802 if lib.infer_dtype(lk, skipna=False) == lib.infer_dtype(
1803 rk, skipna=False
1804 ):
1805 continue
1807 # Check if we are trying to merge on obviously
1808 # incompatible dtypes GH 9780, GH 15800
1810 # bool values are coerced to object
1811 elif (lk_is_object_or_string and is_bool_dtype(rk.dtype)) or (
1812 is_bool_dtype(lk.dtype) and rk_is_object_or_string
1813 ):
1814 pass
1816 # object values are allowed to be merged
1817 elif (lk_is_object_or_string and is_numeric_dtype(rk.dtype)) or (
1818 is_numeric_dtype(lk.dtype) and rk_is_object_or_string
1819 ):
1820 inferred_left = lib.infer_dtype(lk, skipna=False)
1821 inferred_right = lib.infer_dtype(rk, skipna=False)
1822 bool_types = ["integer", "mixed-integer", "boolean", "empty"]
1823 string_types = ["string", "unicode", "mixed", "bytes", "empty"]
1825 # inferred bool
1826 if inferred_left in bool_types and inferred_right in bool_types:
1827 pass
1829 # unless we are merging non-string-like with string-like
1830 elif (
1831 inferred_left in string_types and inferred_right not in string_types
1832 ) or (
1833 inferred_right in string_types and inferred_left not in string_types
1834 ):
1835 raise ValueError(msg)
1837 # datetimelikes must match exactly
1838 elif needs_i8_conversion(lk.dtype) and not needs_i8_conversion(rk.dtype):
1839 raise ValueError(msg)
1840 elif not needs_i8_conversion(lk.dtype) and needs_i8_conversion(rk.dtype):
1841 raise ValueError(msg)
1842 elif isinstance(lk.dtype, DatetimeTZDtype) and not isinstance(
1843 rk.dtype, DatetimeTZDtype
1844 ):
1845 raise ValueError(msg)
1846 elif not isinstance(lk.dtype, DatetimeTZDtype) and isinstance(
1847 rk.dtype, DatetimeTZDtype
1848 ):
1849 raise ValueError(msg)
1850 elif (
1851 isinstance(lk.dtype, DatetimeTZDtype)
1852 and isinstance(rk.dtype, DatetimeTZDtype)
1853 ) or (lk.dtype.kind == "M" and rk.dtype.kind == "M"):
1854 # allows datetime with different resolutions
1855 continue
1856 # datetime and timedelta not allowed
1857 elif lk.dtype.kind == "M" and rk.dtype.kind == "m":
1858 raise ValueError(msg)
1859 elif lk.dtype.kind == "m" and rk.dtype.kind == "M":
1860 raise ValueError(msg)
1862 elif is_object_dtype(lk.dtype) and is_object_dtype(rk.dtype):
1863 continue
1865 # Houston, we have a problem!
1866 # let's coerce to object if the dtypes aren't
1867 # categorical, otherwise coerce to the category
1868 # dtype. If we coerced categories to object,
1869 # then we would lose type information on some
1870 # columns, and end up trying to merge
1871 # incompatible dtypes. See GH 16900.
1872 if name in self.left.columns:
1873 typ = cast(Categorical, lk).categories.dtype if lk_is_cat else object
1874 self.left = self.left.copy(deep=False)
1875 self.left[name] = self.left[name].astype(typ)
1876 if name in self.right.columns:
1877 typ = cast(Categorical, rk).categories.dtype if rk_is_cat else object
1878 self.right = self.right.copy(deep=False)
1879 self.right[name] = self.right[name].astype(typ)
1881 def _validate_left_right_on(self, left_on, right_on):
1882 left_on = com.maybe_make_list(left_on)
1883 right_on = com.maybe_make_list(right_on)
1885 # Hm, any way to make this logic less complicated??
1886 if self.on is None and left_on is None and right_on is None:
1887 if self.left_index and self.right_index:
1888 left_on, right_on = (), ()
1889 elif self.left_index:
1890 raise MergeError("Must pass right_on or right_index=True")
1891 elif self.right_index:
1892 raise MergeError("Must pass left_on or left_index=True")
1893 else:
1894 # use the common columns
1895 left_cols = self.left.columns
1896 right_cols = self.right.columns
1897 common_cols = left_cols.intersection(right_cols)
1898 if len(common_cols) == 0:
1899 raise MergeError(
1900 "No common columns to perform merge on. "
1901 f"Merge options: left_on={left_on}, "
1902 f"right_on={right_on}, "
1903 f"left_index={self.left_index}, "
1904 f"right_index={self.right_index}"
1905 )
1906 if (
1907 not left_cols.join(common_cols, how="inner").is_unique
1908 or not right_cols.join(common_cols, how="inner").is_unique
1909 ):
1910 raise MergeError(f"Data columns not unique: {common_cols!r}")
1911 left_on = right_on = common_cols
1912 elif self.on is not None:
1913 if left_on is not None or right_on is not None:
1914 raise MergeError(
1915 'Can only pass argument "on" OR "left_on" '
1916 'and "right_on", not a combination of both.'
1917 )
1918 if self.left_index or self.right_index:
1919 raise MergeError(
1920 'Can only pass argument "on" OR "left_index" '
1921 'and "right_index", not a combination of both.'
1922 )
1923 left_on = right_on = self.on
1924 elif left_on is not None:
1925 if self.left_index:
1926 raise MergeError(
1927 'Can only pass argument "left_on" OR "left_index" not both.'
1928 )
1929 if not self.right_index and right_on is None:
1930 raise MergeError('Must pass "right_on" OR "right_index".')
1931 if self.right_index and right_on is not None:
1932 raise MergeError(
1933 'Can only pass argument "right_on" OR "right_index" not both.'
1934 )
1935 n = len(left_on)
1936 if self.right_index:
1937 if len(left_on) != self.right.index.nlevels:
1938 raise ValueError(
1939 "len(left_on) must equal the number "
1940 'of levels in the index of "right"'
1941 )
1942 right_on = [None] * n
1943 elif right_on is not None:
1944 if self.right_index:
1945 raise MergeError(
1946 'Can only pass argument "right_on" OR "right_index" not both.'
1947 )
1948 if not self.left_index and left_on is None:
1949 raise MergeError('Must pass "left_on" OR "left_index".')
1950 n = len(right_on)
1951 if self.left_index:
1952 if len(right_on) != self.left.index.nlevels:
1953 raise ValueError(
1954 "len(right_on) must equal the number "
1955 'of levels in the index of "left"'
1956 )
1957 left_on = [None] * n
1958 if len(right_on) != len(left_on):
1959 raise ValueError("len(right_on) must equal len(left_on)")
1961 return left_on, right_on
1963 @final
1964 def _validate_validate_kwd(self, validate: str) -> None:
1965 # Check uniqueness of each
1966 if self.left_index:
1967 left_join_index = self.orig_left.index
1968 left_unique = left_join_index.is_unique
1969 else:
1970 left_join_index = MultiIndex.from_arrays(self.left_join_keys)
1971 left_unique = left_join_index.is_unique
1973 if self.right_index:
1974 right_join_index = self.orig_right.index
1975 right_unique = self.orig_right.index.is_unique
1976 else:
1977 right_join_index = MultiIndex.from_arrays(self.right_join_keys)
1978 right_unique = right_join_index.is_unique
1980 def left_error_msg(x: Index) -> str:
1981 name = self.left_on if not self.left_index else lib.no_default
1982 msg = x[x.duplicated()][:5].to_frame(name=name).to_string(index=False)
1983 return f"\nDuplicates in left:\n {msg} ..."
1985 def right_error_msg(x: Index) -> str:
1986 name = self.right_on if not self.right_index else lib.no_default
1987 msg = x[x.duplicated()][:5].to_frame(name=name).to_string(index=False)
1988 return f"\nDuplicates in right:\n {msg} ..."
1990 # Check data integrity
1991 if validate in ["one_to_one", "1:1"]:
1992 if not left_unique and not right_unique:
1993 raise MergeError(
1994 "Merge keys are not unique in either left "
1995 "or right dataset; not a one-to-one merge."
1996 f"{left_error_msg(left_join_index)}"
1997 f"{right_error_msg(right_join_index)}"
1998 )
1999 if not left_unique:
2000 raise MergeError(
2001 "Merge keys are not unique in left dataset; not a one-to-one merge"
2002 f"{left_error_msg(left_join_index)}"
2003 )
2004 if not right_unique:
2005 raise MergeError(
2006 "Merge keys are not unique in right dataset; not a one-to-one merge"
2007 f"{right_error_msg(right_join_index)}"
2008 )
2010 elif validate in ["one_to_many", "1:m"]:
2011 if not left_unique:
2012 raise MergeError(
2013 "Merge keys are not unique in left dataset; not a one-to-many merge"
2014 f"{left_error_msg(left_join_index)}"
2015 )
2017 elif validate in ["many_to_one", "m:1"]:
2018 if not right_unique:
2019 raise MergeError(
2020 "Merge keys are not unique in right dataset; "
2021 "not a many-to-one merge\n"
2022 f"{right_error_msg(right_join_index)}"
2023 )
2025 elif validate in ["many_to_many", "m:m"]:
2026 pass
2028 else:
2029 raise ValueError(
2030 f'"{validate}" is not a valid argument. '
2031 "Valid arguments are:\n"
2032 '- "1:1"\n'
2033 '- "1:m"\n'
2034 '- "m:1"\n'
2035 '- "m:m"\n'
2036 '- "one_to_one"\n'
2037 '- "one_to_many"\n'
2038 '- "many_to_one"\n'
2039 '- "many_to_many"'
2040 )
2043def get_join_indexers(
2044 left_keys: list[ArrayLike],
2045 right_keys: list[ArrayLike],
2046 sort: bool = False,
2047 how: JoinHow = "inner",
2048) -> tuple[npt.NDArray[np.intp] | None, npt.NDArray[np.intp] | None]:
2049 """
2051 Parameters
2052 ----------
2053 left_keys : list[ndarray, ExtensionArray, Index, Series]
2054 right_keys : list[ndarray, ExtensionArray, Index, Series]
2055 sort : bool, default False
2056 how : {'inner', 'outer', 'left', 'right'}, default 'inner'
2058 Returns
2059 -------
2060 np.ndarray[np.intp] or None
2061 Indexer into the left_keys.
2062 np.ndarray[np.intp] or None
2063 Indexer into the right_keys.
2064 """
2065 assert len(left_keys) == len(right_keys), (
2066 "left_keys and right_keys must be the same length"
2067 )
2069 # fast-path for empty left/right
2070 left_n = len(left_keys[0])
2071 right_n = len(right_keys[0])
2072 if left_n == 0:
2073 if how in ["left", "inner"]:
2074 return _get_empty_indexer()
2075 elif not sort and how in ["right", "outer"]:
2076 return _get_no_sort_one_missing_indexer(right_n, True)
2077 elif right_n == 0:
2078 if how in ["right", "inner"]:
2079 return _get_empty_indexer()
2080 elif not sort and how in ["left", "outer"]:
2081 return _get_no_sort_one_missing_indexer(left_n, False)
2083 lkey: ArrayLike
2084 rkey: ArrayLike
2085 if len(left_keys) > 1:
2086 # get left & right join labels and num. of levels at each location
2087 mapped = (
2088 _factorize_keys(left_keys[n], right_keys[n], sort=sort)
2089 for n in range(len(left_keys))
2090 )
2091 zipped = zip(*mapped, strict=True)
2092 llab, rlab, shape = (list(x) for x in zipped)
2094 # get flat i8 keys from label lists
2095 lkey, rkey = _get_join_keys(llab, rlab, tuple(shape), sort)
2096 else:
2097 lkey = left_keys[0]
2098 rkey = right_keys[0]
2100 left = Index(lkey, copy=False)
2101 right = Index(rkey, copy=False)
2103 if (
2104 left.is_monotonic_increasing
2105 and right.is_monotonic_increasing
2106 and (left.is_unique or right.is_unique)
2107 ):
2108 _, lidx, ridx = left.join(right, how=how, return_indexers=True, sort=sort)
2109 else:
2110 lidx, ridx = get_join_indexers_non_unique(
2111 left._values, right._values, sort, how
2112 )
2114 if lidx is not None and is_range_indexer(lidx, len(left)):
2115 lidx = None
2116 if ridx is not None and is_range_indexer(ridx, len(right)):
2117 ridx = None
2118 return lidx, ridx
2121def get_join_indexers_non_unique(
2122 left: ArrayLike,
2123 right: ArrayLike,
2124 sort: bool = False,
2125 how: JoinHow = "inner",
2126) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
2127 """
2128 Get join indexers for left and right.
2130 Parameters
2131 ----------
2132 left : ArrayLike
2133 right : ArrayLike
2134 sort : bool, default False
2135 how : {'inner', 'outer', 'left', 'right'}, default 'inner'
2137 Returns
2138 -------
2139 np.ndarray[np.intp]
2140 Indexer into left.
2141 np.ndarray[np.intp]
2142 Indexer into right.
2143 """
2144 lkey, rkey, count = _factorize_keys(left, right, sort=sort, how=how)
2145 if count == -1:
2146 # hash join
2147 return lkey, rkey
2148 if how == "left":
2149 lidx, ridx = libjoin.left_outer_join(lkey, rkey, count, sort=sort)
2150 elif how == "right":
2151 ridx, lidx = libjoin.left_outer_join(rkey, lkey, count, sort=sort)
2152 elif how == "inner":
2153 lidx, ridx = libjoin.inner_join(lkey, rkey, count, sort=sort)
2154 elif how == "outer":
2155 lidx, ridx = libjoin.full_outer_join(lkey, rkey, count)
2156 return lidx, ridx
2159def restore_dropped_levels_multijoin(
2160 left: MultiIndex,
2161 right: MultiIndex,
2162 dropped_level_names,
2163 join_index: Index,
2164 lindexer: npt.NDArray[np.intp],
2165 rindexer: npt.NDArray[np.intp],
2166) -> tuple[FrozenList, FrozenList, FrozenList]:
2167 """
2168 *this is an internal non-public method*
2170 Returns the levels, labels and names of a multi-index to multi-index join.
2171 Depending on the type of join, this method restores the appropriate
2172 dropped levels of the joined multi-index.
2173 The method relies on lindexer, rindexer which hold the index positions of
2174 left and right, where a join was feasible
2176 Parameters
2177 ----------
2178 left : MultiIndex
2179 left index
2180 right : MultiIndex
2181 right index
2182 dropped_level_names : str array
2183 list of non-common level names
2184 join_index : Index
2185 the index of the join between the
2186 common levels of left and right
2187 lindexer : np.ndarray[np.intp]
2188 left indexer
2189 rindexer : np.ndarray[np.intp]
2190 right indexer
2192 Returns
2193 -------
2194 levels : list of Index
2195 levels of combined multiindexes
2196 labels : np.ndarray[np.intp]
2197 labels of combined multiindexes
2198 names : List[Hashable]
2199 names of combined multiindex levels
2201 """
2203 def _convert_to_multiindex(index: Index) -> MultiIndex:
2204 if isinstance(index, MultiIndex):
2205 return index
2206 else:
2207 return MultiIndex.from_arrays([index._values], names=[index.name])
2209 # For multi-multi joins with one overlapping level,
2210 # the returned index if of type Index
2211 # Assure that join_index is of type MultiIndex
2212 # so that dropped levels can be appended
2213 join_index = _convert_to_multiindex(join_index)
2215 join_levels = join_index.levels
2216 join_codes = join_index.codes
2217 join_names = join_index.names
2219 # Iterate through the levels that must be restored
2220 for dropped_level_name in dropped_level_names:
2221 if dropped_level_name in left.names:
2222 idx = left
2223 indexer = lindexer
2224 else:
2225 idx = right
2226 indexer = rindexer
2228 # The index of the level name to be restored
2229 name_idx = idx.names.index(dropped_level_name)
2231 restore_levels = idx.levels[name_idx]
2232 # Inject -1 in the codes list where a join was not possible
2233 # IOW indexer[i]=-1
2234 codes = idx.codes[name_idx]
2235 if indexer is None:
2236 restore_codes = codes
2237 else:
2238 restore_codes = algos.take_nd(codes, indexer, fill_value=-1)
2240 # Use + operator: FrozenList.__add__ returns FrozenList, unpacking returns list
2241 join_levels = join_levels + [restore_levels] # noqa: RUF005
2242 join_codes = join_codes + [restore_codes] # noqa: RUF005
2243 join_names = join_names + [dropped_level_name] # noqa: RUF005
2245 return join_levels, join_codes, join_names
2248class _OrderedMerge(_MergeOperation):
2249 _merge_type = "ordered_merge"
2251 def __init__(
2252 self,
2253 left: DataFrame | Series,
2254 right: DataFrame | Series,
2255 on: IndexLabel | None = None,
2256 left_on: IndexLabel | None = None,
2257 right_on: IndexLabel | None = None,
2258 left_index: bool = False,
2259 right_index: bool = False,
2260 suffixes: Suffixes = ("_x", "_y"),
2261 fill_method: str | None = None,
2262 how: JoinHow | Literal["asof"] = "outer",
2263 ) -> None:
2264 self.fill_method = fill_method
2265 _MergeOperation.__init__(
2266 self,
2267 left,
2268 right,
2269 on=on,
2270 left_on=left_on,
2271 left_index=left_index,
2272 right_index=right_index,
2273 right_on=right_on,
2274 how=how,
2275 suffixes=suffixes,
2276 sort=True, # factorize sorts
2277 )
2279 def get_result(self) -> DataFrame:
2280 join_index, left_indexer, right_indexer = self._get_join_info()
2282 left_join_indexer: npt.NDArray[np.intp] | None
2283 right_join_indexer: npt.NDArray[np.intp] | None
2285 if self.fill_method == "ffill":
2286 if left_indexer is None:
2287 left_join_indexer = None
2288 else:
2289 left_join_indexer = libjoin.ffill_indexer(left_indexer)
2290 if right_indexer is None:
2291 right_join_indexer = None
2292 else:
2293 right_join_indexer = libjoin.ffill_indexer(right_indexer)
2294 elif self.fill_method is None:
2295 left_join_indexer = left_indexer
2296 right_join_indexer = right_indexer
2297 else:
2298 raise ValueError("fill_method must be 'ffill' or None")
2300 result = self._reindex_and_concat(
2301 join_index, left_join_indexer, right_join_indexer
2302 )
2303 self._maybe_add_join_keys(result, left_indexer, right_indexer)
2305 return result
2308def _asof_by_function(direction: str):
2309 name = f"asof_join_{direction}_on_X_by_Y"
2310 return getattr(libjoin, name, None)
2313class _AsOfMerge(_OrderedMerge):
2314 _merge_type = "asof_merge"
2316 def __init__(
2317 self,
2318 left: DataFrame | Series,
2319 right: DataFrame | Series,
2320 on: IndexLabel | None = None,
2321 left_on: IndexLabel | None = None,
2322 right_on: IndexLabel | None = None,
2323 left_index: bool = False,
2324 right_index: bool = False,
2325 by=None,
2326 left_by=None,
2327 right_by=None,
2328 suffixes: Suffixes = ("_x", "_y"),
2329 how: Literal["asof"] = "asof",
2330 tolerance=None,
2331 allow_exact_matches: bool = True,
2332 direction: str = "backward",
2333 ) -> None:
2334 self.by = by
2335 self.left_by = left_by
2336 self.right_by = right_by
2337 self.tolerance = tolerance
2338 self.allow_exact_matches = allow_exact_matches
2339 self.direction = direction
2341 # check 'direction' is valid
2342 if self.direction not in ["backward", "forward", "nearest"]:
2343 raise MergeError(f"direction invalid: {self.direction}")
2345 # validate allow_exact_matches
2346 if not is_bool(self.allow_exact_matches):
2347 msg = (
2348 "allow_exact_matches must be boolean, "
2349 f"passed {self.allow_exact_matches}"
2350 )
2351 raise MergeError(msg)
2353 _OrderedMerge.__init__(
2354 self,
2355 left,
2356 right,
2357 on=on,
2358 left_on=left_on,
2359 right_on=right_on,
2360 left_index=left_index,
2361 right_index=right_index,
2362 how=how,
2363 suffixes=suffixes,
2364 fill_method=None,
2365 )
2367 def _validate_left_right_on(self, left_on, right_on):
2368 left_on, right_on = super()._validate_left_right_on(left_on, right_on)
2370 # we only allow on to be a single item for on
2371 if len(left_on) != 1 and not self.left_index:
2372 raise MergeError("can only asof on a key for left")
2374 if len(right_on) != 1 and not self.right_index:
2375 raise MergeError("can only asof on a key for right")
2377 if self.left_index and isinstance(self.left.index, MultiIndex):
2378 raise MergeError("left can only have one index")
2380 if self.right_index and isinstance(self.right.index, MultiIndex):
2381 raise MergeError("right can only have one index")
2383 # set 'by' columns
2384 if self.by is not None:
2385 if self.left_by is not None or self.right_by is not None:
2386 raise MergeError("Can only pass by OR left_by and right_by")
2387 self.left_by = self.right_by = self.by
2388 if self.left_by is None and self.right_by is not None:
2389 raise MergeError("missing left_by")
2390 if self.left_by is not None and self.right_by is None:
2391 raise MergeError("missing right_by")
2393 # GH#29130 Check that merge keys do not have dtype object
2394 if not self.left_index:
2395 left_on_0 = left_on[0]
2396 if isinstance(left_on_0, _known):
2397 lo_dtype = left_on_0.dtype
2398 else:
2399 lo_dtype = (
2400 self.left._get_label_or_level_values(left_on_0).dtype
2401 if left_on_0 in self.left.columns
2402 else self.left.index.get_level_values(left_on_0)
2403 )
2404 else:
2405 lo_dtype = self.left.index.dtype
2407 if not self.right_index:
2408 right_on_0 = right_on[0]
2409 if isinstance(right_on_0, _known):
2410 ro_dtype = right_on_0.dtype
2411 else:
2412 ro_dtype = (
2413 self.right._get_label_or_level_values(right_on_0).dtype
2414 if right_on_0 in self.right.columns
2415 else self.right.index.get_level_values(right_on_0)
2416 )
2417 else:
2418 ro_dtype = self.right.index.dtype
2420 if (
2421 is_object_dtype(lo_dtype)
2422 or is_object_dtype(ro_dtype)
2423 or is_string_dtype(lo_dtype)
2424 or is_string_dtype(ro_dtype)
2425 ):
2426 raise MergeError(
2427 f"Incompatible merge dtype, {lo_dtype!r} and "
2428 f"{ro_dtype!r}, both sides must have numeric dtype"
2429 )
2431 # add 'by' to our key-list so we can have it in the
2432 # output as a key
2433 if self.left_by is not None:
2434 if not is_list_like(self.left_by):
2435 self.left_by = [self.left_by]
2436 if not is_list_like(self.right_by):
2437 self.right_by = [self.right_by]
2439 if len(self.left_by) != len(self.right_by):
2440 raise MergeError("left_by and right_by must be the same length")
2442 left_on = self.left_by + list(left_on)
2443 right_on = self.right_by + list(right_on)
2445 return left_on, right_on
2447 def _maybe_require_matching_dtypes(
2448 self, left_join_keys: list[ArrayLike], right_join_keys: list[ArrayLike]
2449 ) -> None:
2450 # TODO: why do we do this for AsOfMerge but not the others?
2452 def _check_dtype_match(left: ArrayLike, right: ArrayLike, i: int) -> None:
2453 if left.dtype != right.dtype:
2454 if isinstance(left.dtype, CategoricalDtype) and isinstance(
2455 right.dtype, CategoricalDtype
2456 ):
2457 # The generic error message is confusing for categoricals.
2458 #
2459 # In this function, the join keys include both the original
2460 # ones of the merge_asof() call, and also the keys passed
2461 # to its by= argument. Unordered but equal categories
2462 # are not supported for the former, but will fail
2463 # later with a ValueError, so we don't *need* to check
2464 # for them here.
2465 msg = (
2466 f"incompatible merge keys [{i}] {left.dtype!r} and "
2467 f"{right.dtype!r}, both sides category, but not equal ones"
2468 )
2469 else:
2470 msg = (
2471 f"incompatible merge keys [{i}] {left.dtype!r} and "
2472 f"{right.dtype!r}, must be the same type"
2473 )
2474 raise MergeError(msg)
2476 # validate index types are the same
2477 for i, (lk, rk) in enumerate(zip(left_join_keys, right_join_keys, strict=True)):
2478 _check_dtype_match(lk, rk, i)
2480 if self.left_index:
2481 lt = self.left.index._values
2482 else:
2483 lt = left_join_keys[-1]
2485 if self.right_index:
2486 rt = self.right.index._values
2487 else:
2488 rt = right_join_keys[-1]
2490 _check_dtype_match(lt, rt, 0)
2492 def _validate_tolerance(self, left_join_keys: list[ArrayLike]) -> None:
2493 # validate tolerance; datetime.timedelta or Timedelta if we have a DTI
2494 if self.tolerance is not None:
2495 if self.left_index:
2496 lt = self.left.index._values
2497 else:
2498 lt = left_join_keys[-1]
2500 msg = (
2501 f"incompatible tolerance {self.tolerance}, must be compat "
2502 f"with type {lt.dtype!r}"
2503 )
2505 if needs_i8_conversion(lt.dtype) or (
2506 isinstance(lt, ArrowExtensionArray) and lt.dtype.kind in "mM"
2507 ):
2508 if not isinstance(self.tolerance, datetime.timedelta):
2509 raise MergeError(msg)
2510 if self.tolerance < Timedelta(0):
2511 raise MergeError("tolerance must be positive")
2513 elif is_integer_dtype(lt.dtype):
2514 if not is_integer(self.tolerance):
2515 raise MergeError(msg)
2516 if self.tolerance < 0:
2517 raise MergeError("tolerance must be positive")
2519 elif is_float_dtype(lt.dtype):
2520 if not is_number(self.tolerance):
2521 raise MergeError(msg)
2522 # error: Unsupported operand types for > ("int" and "Number")
2523 if self.tolerance < 0: # type: ignore[operator]
2524 raise MergeError("tolerance must be positive")
2526 else:
2527 raise MergeError("key must be integer, timestamp or float")
2529 def _convert_values_for_libjoin(
2530 self, values: AnyArrayLike, side: str
2531 ) -> np.ndarray:
2532 # we require sortedness and non-null values in the join keys
2533 if not Index(values, copy=False).is_monotonic_increasing:
2534 if isna(values).any():
2535 raise ValueError(f"Merge keys contain null values on {side} side")
2536 raise ValueError(f"{side} keys must be sorted")
2538 if isinstance(values, ArrowExtensionArray):
2539 values = values._maybe_convert_datelike_array()
2541 if needs_i8_conversion(values.dtype):
2542 values = values.view("i8")
2544 elif isinstance(values, BaseMaskedArray):
2545 # we've verified above that no nulls exist
2546 values = values._data
2547 elif isinstance(values, ExtensionArray):
2548 values = values.to_numpy()
2550 # error: Incompatible return value type (got "Union[ExtensionArray,
2551 # Any, ndarray[Any, Any], ndarray[Any, dtype[Any]], Index, Series]",
2552 # expected "ndarray[Any, Any]")
2553 return values # type: ignore[return-value]
2555 def _get_join_indexers(self) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
2556 """return the join indexers"""
2558 # values to compare
2559 left_values = (
2560 self.left.index._values if self.left_index else self.left_join_keys[-1]
2561 )
2562 right_values = (
2563 self.right.index._values if self.right_index else self.right_join_keys[-1]
2564 )
2566 # _maybe_require_matching_dtypes already checked for dtype matching
2567 assert left_values.dtype == right_values.dtype
2569 tolerance = self.tolerance
2570 if tolerance is not None:
2571 # TODO: can we reuse a tolerance-conversion function from
2572 # e.g. TimedeltaIndex?
2573 if needs_i8_conversion(left_values.dtype) or (
2574 isinstance(left_values, ArrowExtensionArray)
2575 and left_values.dtype.kind in "mM"
2576 ):
2577 tolerance = Timedelta(tolerance)
2578 # TODO: we have no test cases with PeriodDtype here; probably
2579 # need to adjust tolerance for that case.
2580 if left_values.dtype.kind in "mM":
2581 # Make sure the i8 representation for tolerance
2582 # matches that for left_values/right_values.
2583 if isinstance(left_values, ArrowExtensionArray):
2584 unit = left_values.dtype.pyarrow_dtype.unit
2585 else:
2586 unit = ensure_wrapped_if_datetimelike(left_values).unit
2587 tolerance = tolerance.as_unit(unit)
2589 tolerance = tolerance._value
2591 # initial type conversion as needed
2592 left_values = self._convert_values_for_libjoin(left_values, "left")
2593 right_values = self._convert_values_for_libjoin(right_values, "right")
2595 # a "by" parameter requires special handling
2596 if self.left_by is not None:
2597 # remove 'on' parameter from values if one existed
2598 if self.left_index and self.right_index:
2599 left_join_keys = self.left_join_keys
2600 right_join_keys = self.right_join_keys
2601 else:
2602 left_join_keys = self.left_join_keys[0:-1]
2603 right_join_keys = self.right_join_keys[0:-1]
2605 mapped = [
2606 _factorize_keys(
2607 left_join_keys[n],
2608 right_join_keys[n],
2609 sort=False,
2610 )
2611 for n in range(len(left_join_keys))
2612 ]
2614 if len(left_join_keys) == 1:
2615 left_by_values = mapped[0][0]
2616 right_by_values = mapped[0][1]
2617 else:
2618 arrs = [np.concatenate(m[:2]) for m in mapped]
2619 shape = tuple(m[2] for m in mapped)
2620 group_index = get_group_index(
2621 arrs, shape=shape, sort=False, xnull=False
2622 )
2623 left_len = len(left_join_keys[0])
2624 left_by_values = group_index[:left_len]
2625 right_by_values = group_index[left_len:]
2627 left_by_values = ensure_int64(left_by_values)
2628 right_by_values = ensure_int64(right_by_values)
2630 # choose appropriate function by type
2631 func = _asof_by_function(self.direction)
2632 return func(
2633 left_values,
2634 right_values,
2635 left_by_values,
2636 right_by_values,
2637 self.allow_exact_matches,
2638 tolerance,
2639 )
2640 else:
2641 # choose appropriate function by type
2642 func = _asof_by_function(self.direction)
2643 return func(
2644 left_values,
2645 right_values,
2646 None,
2647 None,
2648 self.allow_exact_matches,
2649 tolerance,
2650 False,
2651 )
2654def _get_multiindex_indexer(
2655 join_keys: list[ArrayLike], index: MultiIndex, sort: bool
2656) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
2657 # left & right join labels and num. of levels at each location
2658 mapped = (
2659 _factorize_keys(index.levels[n]._values, join_keys[n], sort=sort)
2660 for n in range(index.nlevels)
2661 )
2662 zipped = zip(*mapped, strict=True)
2663 rcodes, lcodes, shape = (list(x) for x in zipped)
2664 if sort:
2665 rcodes = list(map(np.take, rcodes, index.codes))
2666 else:
2667 i8copy = lambda a: a.astype("i8", subok=False)
2668 rcodes = list(map(i8copy, index.codes))
2670 # fix right labels if there were any nulls
2671 for i, join_key in enumerate(join_keys):
2672 mask = index.codes[i] == -1
2673 if mask.any():
2674 # check if there already was any nulls at this location
2675 # if there was, it is factorized to `shape[i] - 1`
2676 a = join_key[lcodes[i] == shape[i] - 1]
2677 if a.size == 0 or not a[0] != a[0]:
2678 shape[i] += 1
2680 rcodes[i][mask] = shape[i] - 1
2682 # get flat i8 join keys
2683 lkey, rkey = _get_join_keys(lcodes, rcodes, tuple(shape), sort)
2684 return lkey, rkey
2687def _get_empty_indexer() -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
2688 """Return empty join indexers."""
2689 return (
2690 np.array([], dtype=np.intp),
2691 np.array([], dtype=np.intp),
2692 )
2695def _get_no_sort_one_missing_indexer(
2696 n: int, left_missing: bool
2697) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
2698 """
2699 Return join indexers where all of one side is selected without sorting
2700 and none of the other side is selected.
2702 Parameters
2703 ----------
2704 n : int
2705 Length of indexers to create.
2706 left_missing : bool
2707 If True, the left indexer will contain only -1's.
2708 If False, the right indexer will contain only -1's.
2710 Returns
2711 -------
2712 np.ndarray[np.intp]
2713 Left indexer
2714 np.ndarray[np.intp]
2715 Right indexer
2716 """
2717 idx = np.arange(n, dtype=np.intp)
2718 idx_missing = np.full(shape=n, fill_value=-1, dtype=np.intp)
2719 if left_missing:
2720 return idx_missing, idx
2721 return idx, idx_missing
2724def _left_join_on_index(
2725 left_ax: Index, right_ax: Index, join_keys: list[ArrayLike], sort: bool = False
2726) -> tuple[Index, npt.NDArray[np.intp] | None, npt.NDArray[np.intp]]:
2727 if isinstance(right_ax, MultiIndex):
2728 lkey, rkey = _get_multiindex_indexer(join_keys, right_ax, sort=sort)
2729 else:
2730 # error: Incompatible types in assignment (expression has type
2731 # "Union[Union[ExtensionArray, ndarray[Any, Any]], Index, Series]",
2732 # variable has type "ndarray[Any, dtype[signedinteger[Any]]]")
2733 lkey = join_keys[0] # type: ignore[assignment]
2734 # error: Incompatible types in assignment (expression has type "Index",
2735 # variable has type "ndarray[Any, dtype[signedinteger[Any]]]")
2736 rkey = right_ax._values # type: ignore[assignment]
2738 left_key, right_key, count = _factorize_keys(lkey, rkey, sort=sort)
2739 left_indexer, right_indexer = libjoin.left_outer_join(
2740 left_key, right_key, count, sort=sort
2741 )
2743 if sort or len(left_ax) != len(left_indexer):
2744 # if asked to sort or there are 1-to-many matches
2745 join_index = left_ax.take(left_indexer)
2746 return join_index, left_indexer, right_indexer
2748 # left frame preserves order & length of its index
2749 return left_ax, None, right_indexer
2752def _factorize_keys(
2753 lk: ArrayLike,
2754 rk: ArrayLike,
2755 sort: bool = True,
2756 how: str | None = None,
2757) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp], int]:
2758 """
2759 Encode left and right keys as enumerated types.
2761 This is used to get the join indexers to be used when merging DataFrames.
2763 Parameters
2764 ----------
2765 lk : ndarray, ExtensionArray
2766 Left key.
2767 rk : ndarray, ExtensionArray
2768 Right key.
2769 sort : bool, defaults to True
2770 If True, the encoding is done such that the unique elements in the
2771 keys are sorted.
2772 how: str, optional
2773 Used to determine if we can use hash-join. If not given, then just factorize
2774 keys.
2776 Returns
2777 -------
2778 np.ndarray[np.intp]
2779 Left (resp. right if called with `key='right'`) labels, as enumerated type.
2780 np.ndarray[np.intp]
2781 Right (resp. left if called with `key='right'`) labels, as enumerated type.
2782 int
2783 Number of unique elements in union of left and right labels. -1 if we used
2784 a hash-join.
2786 See Also
2787 --------
2788 merge : Merge DataFrame or named Series objects
2789 with a database-style join.
2790 algorithms.factorize : Encode the object as an enumerated type
2791 or categorical variable.
2793 Examples
2794 --------
2795 >>> lk = np.array(["a", "c", "b"])
2796 >>> rk = np.array(["a", "c"])
2798 Here, the unique values are `'a', 'b', 'c'`. With the default
2799 `sort=True`, the encoding will be `{0: 'a', 1: 'b', 2: 'c'}`:
2801 >>> pd.core.reshape.merge._factorize_keys(lk, rk)
2802 (array([0, 2, 1]), array([0, 2]), 3)
2804 With the `sort=False`, the encoding will correspond to the order
2805 in which the unique elements first appear: `{0: 'a', 1: 'c', 2: 'b'}`:
2807 >>> pd.core.reshape.merge._factorize_keys(lk, rk, sort=False)
2808 (array([0, 1, 2]), array([0, 1]), 3)
2809 """
2810 # TODO: if either is a RangeIndex, we can likely factorize more efficiently?
2812 if (
2813 isinstance(lk.dtype, DatetimeTZDtype) and isinstance(rk.dtype, DatetimeTZDtype)
2814 ) or (lib.is_np_dtype(lk.dtype, "M") and lib.is_np_dtype(rk.dtype, "M")):
2815 # Extract the ndarray (UTC-localized) values
2816 # Note: we dont need the dtypes to match, as these can still be compared
2817 lk, rk = cast("DatetimeArray", lk)._ensure_matching_resos(rk)
2818 lk = cast("DatetimeArray", lk)._ndarray
2819 rk = cast("DatetimeArray", rk)._ndarray
2821 elif (
2822 isinstance(lk.dtype, CategoricalDtype)
2823 and isinstance(rk.dtype, CategoricalDtype)
2824 and lk.dtype == rk.dtype
2825 ):
2826 assert isinstance(lk, Categorical)
2827 assert isinstance(rk, Categorical)
2828 # Cast rk to encoding so we can compare codes with lk
2830 rk = lk._encode_with_my_categories(rk)
2832 lk = ensure_int64(lk.codes)
2833 rk = ensure_int64(rk.codes)
2835 elif isinstance(lk, ExtensionArray) and lk.dtype == rk.dtype:
2836 if isinstance(lk.dtype, ArrowDtype) or (
2837 isinstance(lk.dtype, StringDtype) and lk.dtype.storage == "pyarrow"
2838 ):
2839 import pyarrow as pa
2841 from pandas.compat.pyarrow import _safe_fill_null
2843 len_lk = len(lk)
2844 lk = lk._pa_array # type: ignore[attr-defined]
2845 rk = rk._pa_array # type: ignore[union-attr]
2846 dc = (
2847 pa.chunked_array(lk.chunks + rk.chunks) # type: ignore[union-attr]
2848 .combine_chunks()
2849 .dictionary_encode()
2850 )
2852 llab, rlab, count = (
2853 _safe_fill_null(dc.indices[slice(len_lk)], -1)
2854 .to_numpy()
2855 .astype(np.intp, copy=False),
2856 _safe_fill_null(dc.indices[slice(len_lk, None)], -1)
2857 .to_numpy()
2858 .astype(np.intp, copy=False),
2859 len(dc.dictionary),
2860 )
2862 if sort:
2863 uniques = dc.dictionary.to_numpy(zero_copy_only=False)
2864 llab, rlab = _sort_labels(uniques, llab, rlab)
2866 if dc.null_count > 0:
2867 lmask = llab == -1
2868 lany = lmask.any()
2869 rmask = rlab == -1
2870 rany = rmask.any()
2871 if lany:
2872 np.putmask(llab, lmask, count)
2873 if rany:
2874 np.putmask(rlab, rmask, count)
2875 count += 1
2876 return llab, rlab, count
2878 if not isinstance(lk, BaseMaskedArray) and not (
2879 # exclude arrow dtypes that would get cast to object
2880 isinstance(lk.dtype, ArrowDtype)
2881 and (
2882 is_numeric_dtype(lk.dtype.numpy_dtype)
2883 or (is_string_dtype(lk.dtype) and not sort)
2884 )
2885 ):
2886 lk, _ = lk._values_for_factorize()
2888 # error: Item "ndarray" of "Union[Any, ndarray]" has no attribute
2889 # "_values_for_factorize"
2890 rk, _ = rk._values_for_factorize() # type: ignore[union-attr]
2892 if needs_i8_conversion(lk.dtype) and lk.dtype == rk.dtype:
2893 # GH#23917 TODO: Needs tests for non-matching dtypes
2894 # GH#23917 TODO: needs tests for case where lk is integer-dtype
2895 # and rk is datetime-dtype
2896 lk = np.asarray(lk, dtype=np.int64)
2897 rk = np.asarray(rk, dtype=np.int64)
2899 klass, lk, rk = _convert_arrays_and_get_rizer_klass(lk, rk)
2901 rizer = klass(
2902 max(len(lk), len(rk)),
2903 uses_mask=isinstance(rk, (BaseMaskedArray, ArrowExtensionArray)),
2904 )
2906 if isinstance(lk, BaseMaskedArray):
2907 assert isinstance(rk, BaseMaskedArray)
2908 lk_data, lk_mask = lk._data, lk._mask
2909 rk_data, rk_mask = rk._data, rk._mask
2910 elif isinstance(lk, ArrowExtensionArray):
2911 assert isinstance(rk, ArrowExtensionArray)
2912 # we can only get here with numeric dtypes
2913 # TODO: Remove when we have a Factorizer for Arrow
2914 lk_data = lk.to_numpy(na_value=1, dtype=lk.dtype.numpy_dtype)
2915 rk_data = rk.to_numpy(na_value=1, dtype=lk.dtype.numpy_dtype)
2916 lk_mask, rk_mask = lk.isna(), rk.isna()
2917 else:
2918 # Argument 1 to "factorize" of "ObjectFactorizer" has incompatible type
2919 # "Union[ndarray[Any, dtype[signedinteger[_64Bit]]],
2920 # ndarray[Any, dtype[object_]]]"; expected "ndarray[Any, dtype[object_]]"
2921 lk_data, rk_data = lk, rk # type: ignore[assignment]
2922 lk_mask, rk_mask = None, None
2924 hash_join_available = how == "inner" and not sort and lk.dtype.kind in "iufb"
2925 if hash_join_available:
2926 rlab = rizer.factorize(rk_data, mask=rk_mask)
2927 if rizer.get_count() == len(rlab):
2928 ridx, lidx = rizer.hash_inner_join(lk_data, lk_mask)
2929 return lidx, ridx, -1
2930 else:
2931 llab = rizer.factorize(lk_data, mask=lk_mask)
2932 else:
2933 llab = rizer.factorize(lk_data, mask=lk_mask)
2934 rlab = rizer.factorize(rk_data, mask=rk_mask)
2936 assert llab.dtype == np.dtype(np.intp), llab.dtype
2937 assert rlab.dtype == np.dtype(np.intp), rlab.dtype
2939 count = rizer.get_count()
2941 if sort:
2942 uniques = rizer.uniques.to_array()
2943 llab, rlab = _sort_labels(uniques, llab, rlab)
2945 # NA group
2946 lmask = llab == -1
2947 lany = lmask.any()
2948 rmask = rlab == -1
2949 rany = rmask.any()
2951 if lany or rany:
2952 if lany:
2953 np.putmask(llab, lmask, count)
2954 if rany:
2955 np.putmask(rlab, rmask, count)
2956 count += 1
2958 return llab, rlab, count
2961def _convert_arrays_and_get_rizer_klass(
2962 lk: ArrayLike, rk: ArrayLike
2963) -> tuple[type[libhashtable.Factorizer], ArrayLike, ArrayLike]:
2964 klass: type[libhashtable.Factorizer]
2965 if is_numeric_dtype(lk.dtype):
2966 if lk.dtype != rk.dtype:
2967 dtype = find_common_type([lk.dtype, rk.dtype])
2968 if isinstance(dtype, ExtensionDtype):
2969 cls = dtype.construct_array_type()
2970 if not isinstance(lk, ExtensionArray):
2971 lk = cls._from_sequence(lk, dtype=dtype, copy=False)
2972 else:
2973 lk = lk.astype(dtype, copy=False)
2975 if not isinstance(rk, ExtensionArray):
2976 rk = cls._from_sequence(rk, dtype=dtype, copy=False)
2977 else:
2978 rk = rk.astype(dtype, copy=False)
2979 else:
2980 lk = lk.astype(dtype, copy=False)
2981 rk = rk.astype(dtype, copy=False)
2982 if isinstance(lk, BaseMaskedArray):
2983 klass = _factorizers[lk.dtype.type]
2984 elif isinstance(lk.dtype, ArrowDtype):
2985 klass = _factorizers[lk.dtype.numpy_dtype.type]
2986 else:
2987 klass = _factorizers[lk.dtype.type]
2989 else:
2990 klass = libhashtable.ObjectFactorizer
2991 lk = ensure_object(lk)
2992 rk = ensure_object(rk)
2993 return klass, lk, rk
2996def _sort_labels(
2997 uniques: np.ndarray, left: npt.NDArray[np.intp], right: npt.NDArray[np.intp]
2998) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
2999 llength = len(left)
3000 labels = np.concatenate([left, right])
3002 _, new_labels = algos.safe_sort(uniques, labels, use_na_sentinel=True)
3003 new_left, new_right = new_labels[:llength], new_labels[llength:]
3005 return new_left, new_right
3008def _get_join_keys(
3009 llab: list[npt.NDArray[np.int64 | np.intp]],
3010 rlab: list[npt.NDArray[np.int64 | np.intp]],
3011 shape: Shape,
3012 sort: bool,
3013) -> tuple[npt.NDArray[np.int64], npt.NDArray[np.int64]]:
3014 # how many levels can be done without overflow
3015 nlev = next(
3016 lev
3017 for lev in range(len(shape), 0, -1)
3018 if not is_int64_overflow_possible(shape[:lev])
3019 )
3021 # get keys for the first `nlev` levels
3022 stride = np.prod(shape[1:nlev], dtype="i8")
3023 lkey = stride * llab[0].astype("i8", subok=False, copy=False)
3024 rkey = stride * rlab[0].astype("i8", subok=False, copy=False)
3026 for i in range(1, nlev):
3027 with np.errstate(divide="ignore"):
3028 stride //= shape[i]
3029 lkey += llab[i] * stride
3030 rkey += rlab[i] * stride
3032 if nlev == len(shape): # all done!
3033 return lkey, rkey
3035 # densify current keys to avoid overflow
3036 lkey, rkey, count = _factorize_keys(lkey, rkey, sort=sort)
3038 llab = [lkey, *llab[nlev:]]
3039 rlab = [rkey, *rlab[nlev:]]
3040 shape = (count, *shape[nlev:])
3042 return _get_join_keys(llab, rlab, shape, sort)
3045def _should_fill(lname, rname) -> bool:
3046 if not isinstance(lname, str) or not isinstance(rname, str):
3047 return True
3048 return lname == rname
3051def _any(x) -> bool:
3052 return x is not None and com.any_not_none(*x)
3055def _validate_operand(obj: DataFrame | Series) -> DataFrame:
3056 if isinstance(obj, ABCDataFrame):
3057 return obj
3058 elif isinstance(obj, ABCSeries):
3059 if obj.name is None:
3060 raise ValueError("Cannot merge a Series without a name")
3061 return obj.to_frame()
3062 else:
3063 raise TypeError(
3064 f"Can only merge Series or DataFrame objects, a {type(obj)} was passed"
3065 )
3068def _items_overlap_with_suffix(
3069 left: Index, right: Index, suffixes: Suffixes
3070) -> tuple[Index, Index]:
3071 """
3072 Suffixes type validation.
3074 If two indices overlap, add suffixes to overlapping entries.
3076 If corresponding suffix is empty, the entry is simply converted to string.
3078 """
3079 if not is_list_like(suffixes, allow_sets=False) or isinstance(suffixes, dict):
3080 raise TypeError(
3081 f"Passing 'suffixes' as a {type(suffixes)}, is not supported. "
3082 "Provide 'suffixes' as a tuple instead."
3083 )
3085 to_rename = left.intersection(right)
3086 if len(to_rename) == 0:
3087 return left, right
3089 lsuffix, rsuffix = suffixes
3091 if not lsuffix and not rsuffix:
3092 raise ValueError(f"columns overlap but no suffix specified: {to_rename}")
3094 def renamer(x, suffix: str | None):
3095 """
3096 Rename the left and right indices.
3098 If there is overlap, and suffix is not None, add
3099 suffix, otherwise, leave it as-is.
3101 Parameters
3102 ----------
3103 x : original column name
3104 suffix : str or None
3106 Returns
3107 -------
3108 x : renamed column name
3109 """
3110 if x in to_rename and suffix is not None:
3111 return f"{x}{suffix}"
3112 return x
3114 lrenamer = partial(renamer, suffix=lsuffix)
3115 rrenamer = partial(renamer, suffix=rsuffix)
3117 llabels = left._transform_index(lrenamer)
3118 rlabels = right._transform_index(rrenamer)
3120 dups = []
3121 if not llabels.is_unique:
3122 # Only warn when duplicates are caused because of suffixes, already duplicated
3123 # columns in origin should not warn
3124 dups.extend(llabels[(llabels.duplicated()) & (~left.duplicated())].tolist())
3125 if not rlabels.is_unique:
3126 dups.extend(rlabels[(rlabels.duplicated()) & (~right.duplicated())].tolist())
3127 # Suffix addition creates duplicate to pre-existing column name
3128 dups.extend(llabels.intersection(right.difference(to_rename)).tolist())
3129 dups.extend(rlabels.intersection(left.difference(to_rename)).tolist())
3130 if dups:
3131 raise MergeError(
3132 f"Passing 'suffixes' which cause duplicate columns {set(dups)} is "
3133 "not allowed.",
3134 )
3136 return llabels, rlabels