Repository: pandas-dev/pandas (python)

Issue:
ENH: Add anti-joins to pandas.merge
#### Is your feature request related to a problem?

Pandas does not allow anti-joins. It would be helpful to have these added
Pyspark has implemented this on its 'join' command.
https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-join.html


#### Describe the solution you'd like

Add anti-left and anti-right joins to 'how' 
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.merge.html

#### API breaking implications

None

#### Describe alternatives you've considered

You can replicate an anti-join by doing an outer join, and filtering.

My code does work but sometimes gives too many columns - not sure why. Also my code relies on the column name not already containing '_drop' - which obviously could be an issue.

#### Additional context

[add any other context, code examples, or references to existing implementations about the feature request here]

```python
import pandas as pd
def left_anti_join(df1,df2,left_on,right_on):
   """ Perfoms left anti join of two data frames"""
   df=pd.merge(df1,df2,left_on=left_on,right_on=right_on,suffixes=('','_drop'), how='outer', indicator=True)
   df=df[df['_merge']=='left_only']
   df.drop([col for col in df.columns if '_drop' in col],axis=1, inplace=True)
   df.drop(columns="_merge", inplace=True)
   return df
```


Pull request:
ENH: Support 'left_anti' and 'right_anti' joins in pd.merge
- [ ] closes #42916
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [ ] Added an entry in the latest `doc/source/whatsnew/v3.0.0.rst` file if fixing a bug or adding a new feature.


Patch:
diff --git a/doc/source/whatsnew/v3.0.0.rst b/doc/source/whatsnew/v3.0.0.rst
index 95b5f7eea5..7ebbfd5bf7 100644
--- a/doc/source/whatsnew/v3.0.0.rst
+++ b/doc/source/whatsnew/v3.0.0.rst
@@ -35,6 +35,7 @@ Other enhancements
 - :class:`pandas.api.typing.NoDefault` is available for typing ``no_default``
 - :func:`DataFrame.to_excel` now raises an ``UserWarning`` when the character count in a cell exceeds Excel's limitation of 32767 characters (:issue:`56954`)
 - :func:`pandas.merge` now validates the ``how`` parameter input (merge type) (:issue:`59435`)
+- :func:`pandas.merge`, :meth:`DataFrame.merge` and :meth:`DataFrame.join` now support anti joins (``left_anti`` and ``right_anti``) in the ``how`` parameter (:issue:`42916`)
 - :func:`read_spss` now supports kwargs to be passed to pyreadstat (:issue:`56356`)
 - :func:`read_stata` now returns ``datetime64`` resolutions better matching those natively stored in the stata format (:issue:`55642`)
 - :meth:`DataFrame.agg` called with ``axis=1`` and a ``func`` which relabels the result index now raises a ``NotImplementedError`` (:issue:`58807`).
@@ -68,6 +69,7 @@ Other enhancements
 - :meth:`Series.map` can now accept kwargs to pass on to func (:issue:`59814`)
 - :meth:`Series.str.get_dummies` now accepts a  ``dtype`` parameter to specify the dtype of the resulting DataFrame (:issue:`47872`)
 - :meth:`pandas.concat` will raise a ``ValueError`` when ``ignore_index=True`` and ``keys`` is not ``None`` (:issue:`59274`)
+- :py:class:`frozenset` elements in pandas objects are now natively printed (:issue:`60690`)
 - Implemented :meth:`Series.str.isascii` and :meth:`Series.str.isascii` (:issue:`59091`)
 - Multiplying two :class:`DateOffset` objects will now raise a ``TypeError`` instead of a ``RecursionError`` (:issue:`59442`)
 - Restore support for reading Stata 104-format and enable reading 103-format dta files (:issue:`58554`)
@@ -631,6 +633,7 @@ Datetimelike
 - Bug in :func:`date_range` where using a negative frequency value would not include all points between the start and end values (:issue:`56147`)
 - Bug in :func:`tseries.api.guess_datetime_format` would fail to infer time format when "%Y" == "%H%M" (:issue:`57452`)
 - Bug in :func:`tseries.frequencies.to_offset` would fail to parse frequency strings starting with "LWOM" (:issue:`59218`)
+- Bug in :meth:`DataFrame.min` and :meth:`DataFrame.max` casting ``datetime64`` and ``timedelta64`` columns to ``float64`` and losing precision (:issue:`60850`)
 - Bug in :meth:`Dataframe.agg` with df with missing values resulting in IndexError (:issue:`58810`)
 - Bug in :meth:`DatetimeIndex.is_year_start` and :meth:`DatetimeIndex.is_quarter_start` does not raise on Custom business days frequencies bigger then "1C" (:issue:`58664`)
 - Bug in :meth:`DatetimeIndex.is_year_start` and :meth:`DatetimeIndex.is_quarter_start` returning ``False`` on double-digit frequencies (:issue:`58523`)
diff --git a/pandas/_libs/interval.pyx b/pandas/_libs/interval.pyx
index 564019d7c0..5d0876591a 100644
--- a/pandas/_libs/interval.pyx
+++ b/pandas/_libs/interval.pyx
@@ -209,6 +209,12 @@ cdef class IntervalMixin:
         """
         Indicates if an interval is empty, meaning it contains no points.
 
+        An interval is considered empty if its `left` and `right` endpoints
+        are equal, and it is not closed on both sides. This means that the
+        interval does not include any real points. In the case of an
+        :class:`pandas.arrays.IntervalArray` or :class:`IntervalIndex`, the
+        property returns a boolean array indicating the emptiness of each interval.
+
         Returns
         -------
         bool or ndarray
diff --git a/pandas/_libs/tslibs/period.pyx b/pandas/_libs/tslibs/period.pyx
index f697180da5..087d3119c3 100644
--- a/pandas/_libs/tslibs/period.pyx
+++ b/pandas/_libs/tslibs/period.pyx
@@ -2140,6 +2140,12 @@ cdef class _Period(PeriodMixin):
         """
         Get day of the month that a Period falls on.
 
+        The `day` property provides a simple way to access the day component
+        of a `Period` object, which represents time spans in various frequencies
+        (e.g., daily, hourly, monthly). If the period's frequency does not include
+        a day component (e.g., yearly or quarterly periods), the returned day
+        corresponds to the first day of that period.
+
         Returns
         -------
         int
diff --git a/pandas/_typing.py b/pandas/_typing.py
index b515305fb6..4365ee85f7 100644
--- a/pandas/_typing.py
+++ b/pandas/_typing.py
@@ -442,7 +442,9 @@ PlottingOrientation = Literal["horizontal", "vertical"]
 AnyAll = Literal["any", "all"]
 
 # merge
-MergeHow = Literal["left", "right", "inner", "outer", "cross"]
+MergeHow = Literal[
+    "left", "right", "inner", "outer", "cross", "left_anti", "right_anti"
+]
 MergeValidate = Literal[
     "one_to_one",
     "1:1",
diff --git a/pandas/core/frame.py b/pandas/core/frame.py
index b715e526e0..72fc099f57 100644
--- a/pandas/core/frame.py
+++ b/pandas/core/frame.py
@@ -315,7 +315,8 @@ Parameters
 ----------%s
 right : DataFrame or named Series
     Object to merge with.
-how : {'left', 'right', 'outer', 'inner', 'cross'}, default 'inner'
+how : {'left', 'right', 'outer', 'inner', 'cross', 'left_anti', 'right_anti'},
+    default 'inner'
     Type of merge to be performed.
 
     * left: use only keys from left frame, similar to a SQL left outer join;
@@ -328,6 +329,10 @@ how : {'left', 'right', 'outer', 'inner', 'cross'}, default 'inner'
       join; preserve the order of the left keys.
     * cross: creates the cartesian product from both frames, preserves the order
       of the left keys.
+    * left_anti: use only keys from left frame that are not in right frame, similar
+      to SQL left anti join; preserve key order.
+    * right_anti: use only keys from right frame that are not in left frame, similar
+      to SQL right anti join; preserve key order.
 on : label or list
     Column or index level names to join on. These must be found in both
     DataFrames. If `on` is None and not merging on indexes then this defaults
@@ -4793,6 +4798,10 @@ class DataFrame(NDFrame, OpsMixin):
         """
         Return a subset of the DataFrame's columns based on the column dtypes.
 
+        This method allows for filtering columns based on their data types.
+        It is useful when working with heterogeneous DataFrames where operations
+        need to be performed on a specific subset of data types.
+
         Parameters
         ----------
         include, exclude : scalar or list-like
@@ -10609,7 +10618,8 @@ class DataFrame(NDFrame, OpsMixin):
             values given, the `other` DataFrame must have a MultiIndex. Can
             pass an array as the join key if it is not already contained in
             the calling DataFrame. Like an Excel VLOOKUP operation.
-        how : {'left', 'right', 'outer', 'inner', 'cross'}, default 'left'
+        how : {'left', 'right', 'outer', 'inner', 'cross', 'left_anti', 'right_anti'},
+            default 'left'
             How to handle the operation of the two objects.
 
             * left: use calling frame's index (or column if on is specified)
@@ -10621,6 +10631,10 @@ class DataFrame(NDFrame, OpsMixin):
               of the calling's one.
             * cross: creates the cartesian product from both frames, preserves the order
               of the left keys.
+            * left_anti: use set difference of calling frame's index and `other`'s
+              index.
+            * right_anti: use set difference of `other`'s index and calling frame's
+              index.
         lsuffix : str, default ''
             Suffix to use from left frame's overlapping columns.
         rsuffix : str, default ''
diff --git a/pandas/core/nanops.py b/pandas/core/nanops.py
index d6154e2352..d1dc0ff809 100644
--- a/pandas/core/nanops.py
+++ b/pandas/core/nanops.py
@@ -1093,11 +1093,14 @@ def _nanminmax(meth, fill_value_typ):
         if values.size == 0:
             return _na_for_min_count(values, axis)
 
+        dtype = values.dtype
         values, mask = _get_values(
             values, skipna, fill_value_typ=fill_value_typ, mask=mask
         )
         result = getattr(values, meth)(axis)
-        result = _maybe_null_out(result, axis, mask, values.shape)
+        result = _maybe_null_out(
+            result, axis, mask, values.shape, datetimelike=dtype.kind in "mM"
+        )
         return result
 
     return reduction
@@ -1499,6 +1502,7 @@ def _maybe_null_out(
     mask: npt.NDArray[np.bool_] | None,
     shape: tuple[int, ...],
     min_count: int = 1,
+    datetimelike: bool = False,
 ) -> np.ndarray | float | NaTType:
     """
     Returns
@@ -1520,7 +1524,10 @@ def _maybe_null_out(
             null_mask = np.broadcast_to(below_count, new_shape)
 
         if np.any(null_mask):
-            if is_numeric_dtype(result):
+            if datetimelike:
+                # GH#60646 For datetimelike, no need to cast to float
+                result[null_mask] = iNaT
+            elif is_numeric_dtype(result):
                 if np.iscomplexobj(result):
                     result = result.astype("c16")
                 elif not is_float_dtype(result):
diff --git a/pandas/core/reshape/merge.py b/pandas/core/reshape/merge.py
index ab056c8cc7..09be82c59a 100644
--- a/pandas/core/reshape/merge.py
+++ b/pandas/core/reshape/merge.py
@@ -180,7 +180,8 @@ def merge(
         First pandas object to merge.
     right : DataFrame or named Series
         Second pandas object to merge.
-    how : {'left', 'right', 'outer', 'inner', 'cross'}, default 'inner'
+    how : {'left', 'right', 'outer', 'inner', 'cross', 'left_anti', 'right_anti},
+        default 'inner'
         Type of merge to be performed.
 
         * left: use only keys from left frame, similar to a SQL left outer join;
@@ -193,6 +194,10 @@ def merge(
           join; preserve the order of the left keys.
         * cross: creates the cartesian product from both frames, preserves the order
           of the left keys.
+        * left_anti: use only keys from left frame that are not in right frame, similar
+          to SQL left anti join; preserve key order.
+        * right_anti: use only keys from right frame that are not in left frame, similar
+          to SQL right anti join; preserve key order.
     on : label or list
         Column or index level names to join on. These must be found in both
         DataFrames. If `on` is None and not merging on indexes then this defaults
@@ -953,7 +958,7 @@ class _MergeOperation:
         self,
         left: DataFrame | Series,
         right: DataFrame | Series,
-        how: JoinHow | Literal["asof"] = "inner",
+        how: JoinHow | Literal["left_anti", "right_anti", "asof"] = "inner",
         on: IndexLabel | AnyArrayLike | None = None,
         left_on: IndexLabel | AnyArrayLike | None = None,
         right_on: IndexLabel | AnyArrayLike | None = None,
@@ -968,7 +973,7 @@ class _MergeOperation:
         _right = _validate_operand(right)
         self.left = self.orig_left = _left
         self.right = self.orig_right = _right
-        self.how = how
+        self.how, self.anti_join = self._validate_how(how)
 
         self.on = com.maybe_make_list(on)
 
@@ -998,14 +1003,6 @@ class _MergeOperation:
             )
             raise MergeError(msg)
 
-        # GH 59435: raise when "how" is not a valid Merge type
-        merge_type = {"left", "right", "inner", "outer", "cross", "asof"}
-        if how not in merge_type:
-            raise ValueError(
-                f"'{how}' is not a valid Merge type: "
-                f"left, right, inner, outer, cross, asof"
-            )
-
         self.left_on, self.right_on = self._validate_left_right_on(left_on, right_on)
 
         (
@@ -1035,6 +1032,37 @@ class _MergeOperation:
         if validate is not None:
             self._validate_validate_kwd(validate)
 
+    @final
+    def _validate_how(
+        self, how: JoinHow | Literal["left_anti", "right_anti", "asof"]
+    ) -> tuple[JoinHow | Literal["asof"], bool]:
+        """
+        Validate the 'how' parameter and return the actual join type and whether
+        this is an anti join.
+        """
+        # GH 59435: raise when "how" is not a valid Merge type
+        merge_type = {
+            "left",
+            "right",
+            "inner",
+            "outer",
+            "left_anti",
+            "right_anti",
+            "cross",
+            "asof",
+        }
+        if how not in merge_type:
+            raise ValueError(
+                f"'{how}' is not a valid Merge type: "
+                f"left, right, inner, outer, left_anti, right_anti, cross, asof"
+            )
+        anti_join = False
+        if how in {"left_anti", "right_anti"}:
+            how = how.split("_")[0]  # type: ignore[assignment]
+            anti_join = True
+        how = cast(JoinHow | Literal["asof"], how)
+        return how, anti_join
+
     def _maybe_require_matching_dtypes(
         self, left_join_keys: list[ArrayLike], right_join_keys: list[ArrayLike]
     ) -> None:
@@ -1405,6 +1433,11 @@ class _MergeOperation:
                 n = len(left_ax) if left_indexer is None else len(left_indexer)
                 join_index = default_index(n)
 
+        if self.anti_join:
+            join_index, left_indexer, right_indexer = self._handle_anti_join(
+                join_index, left_indexer, right_indexer
+            )
+
         return join_index, left_indexer, right_indexer
 
     @final
@@ -1447,6 +1480,48 @@ class _MergeOperation:
             return index.copy()
         return index.take(indexer)
 
+    @final
+    def _handle_anti_join(
+        self,
+        join_index: Index,
+        left_indexer: npt.NDArray[np.intp] | None,
+        right_indexer: npt.NDArray[np.intp] | None,
+    ) -> tuple[Index, npt.NDArray[np.intp] | None, npt.NDArray[np.intp] | None]:
+        """
+        Handle anti join by returning the correct join index and indexers
+
+        Parameters
+        ----------
+        join_index : Index
+            join index
+        left_indexer : np.ndarray[np.intp] or None
+            left indexer
+        right_indexer : np.ndarray[np.intp] or None
+            right indexer
+
+        Returns
+        -------
+        Index, np.ndarray[np.intp] or None, np.ndarray[np.intp] or None
+        """
+        # Make sure indexers are not None
+        if left_indexer is None:
+            left_indexer = np.arange(len(self.left))
+        if right_indexer is None:
+            right_indexer = np.arange(len(self.right))
+
+        assert self.how in {"left", "right"}
+        if self.how == "left":
+            # Filter to rows where left keys are not in right keys
+            filt = right_indexer == -1
+        else:
+            # Filter to rows where right keys are not in left keys
+            filt = left_indexer == -1
+        join_index = join_index[filt]
+        left_indexer = left_indexer[filt]
+        right_indexer = right_indexer[filt]
+
+        return join_index, left_indexer, right_indexer
+
     @final
     def _get_merge_keys(
         self,
diff --git a/pandas/io/formats/printing.py b/pandas/io/formats/printing.py
index b466e98645..5a52ee78cb 100644
--- a/pandas/io/formats/printing.py
+++ b/pandas/io/formats/printing.py
@@ -111,6 +111,8 @@ def _pprint_seq(
     """
     if isinstance(seq, set):
         fmt = "{{{body}}}"
+    elif isinstance(seq, frozenset):
+        fmt = "frozenset({body})"
     else:
         fmt = "[{body}]" if hasattr(seq, "__setitem__") else "({body})"
 
diff --git a/pandas/io/orc.py b/pandas/io/orc.py
index a945f3dc38..1a2d564d5b 100644
--- a/pandas/io/orc.py
+++ b/pandas/io/orc.py
@@ -45,6 +45,13 @@ def read_orc(
     """
     Load an ORC object from the file path, returning a DataFrame.
 
+    This method reads an ORC (Optimized Row Columnar) file into a pandas
+    DataFrame using the `pyarrow.orc` library. ORC is a columnar storage format
+    that provides efficient compression and fast retrieval for analytical workloads.
+    It allows reading specific columns, handling different filesystem
+    types (such as local storage, cloud storage via fsspec, or pyarrow filesystem),
+    and supports different data type backends, including `numpy_nullable` and `pyarrow`.
+
     Parameters
     ----------
     path : str, path object, or file-like object
diff --git a/pandas/io/pytables.py b/pandas/io/pytables.py
index e18db2e531..b4c78b063c 100644
--- a/pandas/io/pytables.py
+++ b/pandas/io/pytables.py
@@ -3524,6 +3524,12 @@ class Table(Fixed):
                     # Value of type "Optional[Any]" is not indexable  [index]
                     oax = ov[i]  # type: ignore[index]
                     if sax != oax:
+                        if c == "values_axes" and sax.kind != oax.kind:
+                            raise ValueError(
+                                f"Cannot serialize the column [{oax.values[0]}] "
+                                f"because its data contents are not [{sax.kind}] "
+                                f"but [{oax.kind}] object dtype"
+                            )
                         raise ValueError(
                             f"invalid combination of [{c}] on appending data "
                             f"[{sax}] vs current table [{oax}]"
@@ -5136,6 +5142,9 @@ def _maybe_convert_for_string_atom(
     data = bvalues.copy()
     data[mask] = nan_rep
 
+    if existing_col and mask.any() and len(nan_rep) > existing_col.itemsize:
+        raise ValueError("NaN representation is too large for existing column size")
+
     # see if we have a valid string type
     inferred_type = lib.infer_dtype(data, skipna=False)
     if inferred_type != "string":
diff --git a/pandas/plotting/_misc.py b/pandas/plotting/_misc.py
index 3f839cefe7..0e0fb23d92 100644
--- a/pandas/plotting/_misc.py
+++ b/pandas/plotting/_misc.py
@@ -30,6 +30,13 @@ def table(ax: Axes, data: DataFrame | Series, **kwargs) -> Table:
     """
     Helper function to convert DataFrame and Series to matplotlib.table.
 
+    This method provides an easy way to visualize tabular data within a Matplotlib
+    figure. It automatically extracts index and column labels from the DataFrame
+    or Series, unless explicitly specified. This function is particularly useful
+    when displaying summary tables alongside other plots or when creating static
+    reports. It utilizes the `matplotlib.pyplot.table` backend and allows
+    customization through various styling options available in Matplotlib.
+
     Parameters
     ----------
     ax : Matplotlib axes object


Test patch:
diff --git a/pandas/tests/frame/methods/test_join.py b/pandas/tests/frame/methods/test_join.py
index 479ea7d7ba..aaa9485cab 100644
--- a/pandas/tests/frame/methods/test_join.py
+++ b/pandas/tests/frame/methods/test_join.py
@@ -277,7 +277,20 @@ def test_join_index(float_frame):
     tm.assert_index_equal(joined.index, float_frame.index.sort_values())
     tm.assert_index_equal(joined.columns, expected_columns)
 
-    join_msg = "'foo' is not a valid Merge type: left, right, inner, outer, cross, asof"
+    # left anti
+    joined = f.join(f2, how="left_anti")
+    tm.assert_index_equal(joined.index, float_frame.index[:5])
+    tm.assert_index_equal(joined.columns, expected_columns)
+
+    # right anti
+    joined = f.join(f2, how="right_anti")
+    tm.assert_index_equal(joined.index, float_frame.index[10:][::-1])
+    tm.assert_index_equal(joined.columns, expected_columns)
+
+    join_msg = (
+        "'foo' is not a valid Merge type: left, right, inner, outer, "
+        "left_anti, right_anti, cross, asof"
+    )
     with pytest.raises(ValueError, match=re.escape(join_msg)):
         f.join(f2, how="foo")
 
diff --git a/pandas/tests/frame/test_reductions.py b/pandas/tests/frame/test_reductions.py
index 04b1456cdb..64e686d25f 100644
--- a/pandas/tests/frame/test_reductions.py
+++ b/pandas/tests/frame/test_reductions.py
@@ -1544,6 +1544,44 @@ class TestDataFrameReductions:
         exp = Series([pd.NaT], index=["foo"])
         tm.assert_series_equal(res, exp)
 
+    def test_min_max_dt64_with_NaT_precision(self):
+        # GH#60646 Make sure the reduction doesn't cast input timestamps to
+        # float and lose precision.
+        df = DataFrame(
+            {"foo": [pd.NaT, pd.NaT, Timestamp("2012-05-01 09:20:00.123456789")]},
+            dtype="datetime64[ns]",
+        )
+
+        res = df.min(axis=1)
+        exp = df.foo.rename(None)
+        tm.assert_series_equal(res, exp)
+
+        res = df.max(axis=1)
+        exp = df.foo.rename(None)
+        tm.assert_series_equal(res, exp)
+
+    def test_min_max_td64_with_NaT_precision(self):
+        # GH#60646 Make sure the reduction doesn't cast input timedeltas to
+        # float and lose precision.
+        df = DataFrame(
+            {
+                "foo": [
+                    pd.NaT,
+                    pd.NaT,
+                    to_timedelta("10000 days 06:05:01.123456789"),
+                ],
+            },
+            dtype="timedelta64[ns]",
+        )
+
+        res = df.min(axis=1)
+        exp = df.foo.rename(None)
+        tm.assert_series_equal(res, exp)
+
+        res = df.max(axis=1)
+        exp = df.foo.rename(None)
+        tm.assert_series_equal(res, exp)
+
     def test_min_max_dt64_with_NaT_skipna_false(self, request, tz_naive_fixture):
         # GH#36907
         tz = tz_naive_fixture
diff --git a/pandas/tests/io/formats/test_printing.py b/pandas/tests/io/formats/test_printing.py
index 3b63011bf8..f86b4af264 100644
--- a/pandas/tests/io/formats/test_printing.py
+++ b/pandas/tests/io/formats/test_printing.py
@@ -82,6 +82,9 @@ class TestPPrintThing:
     def test_repr_mapping(self):
         assert printing.pprint_thing(MyMapping()) == "{'a': 4, 'b': 4}"
 
+    def test_repr_frozenset(self):
+        assert printing.pprint_thing(frozenset([1, 2])) == "frozenset(1, 2)"
+
 
 class TestFormatBase:
     def test_adjoin(self):
diff --git a/pandas/tests/io/pytables/test_append.py b/pandas/tests/io/pytables/test_append.py
index 47658c0eb9..04241a78bf 100644
--- a/pandas/tests/io/pytables/test_append.py
+++ b/pandas/tests/io/pytables/test_append.py
@@ -823,12 +823,9 @@ because its data contents are not [string] but [mixed] object dtype"""
         store.append("df", df)
         df["foo"] = "bar"
         msg = re.escape(
-            "invalid combination of [values_axes] on appending data "
-            "[name->values_block_1,cname->values_block_1,"
-            "dtype->bytes24,kind->string,shape->(1, 30)] "
-            "vs current table "
-            "[name->values_block_1,cname->values_block_1,"
-            "dtype->datetime64[s],kind->datetime64[s],shape->None]"
+            "Cannot serialize the column [foo] "
+            "because its data contents are not [string] "
+            "but [datetime64[s]] object dtype"
         )
         with pytest.raises(ValueError, match=msg):
             store.append("df", df)
@@ -997,3 +994,29 @@ def test_append_to_multiple_min_itemsize(setup_path):
         )
         result = store.select_as_multiple(["index", "nums", "strs"])
         tm.assert_frame_equal(result, expected, check_index_type=True)
+
+
+def test_append_string_nan_rep(setup_path):
+    # GH 16300
+    df = DataFrame({"A": "a", "B": "foo"}, index=np.arange(10))
+    df_nan = df.copy()
+    df_nan.loc[0:4, :] = np.nan
+    msg = "NaN representation is too large for existing column size"
+
+    with ensure_clean_store(setup_path) as store:
+        # string column too small
+        store.append("sa", df["A"])
+        with pytest.raises(ValueError, match=msg):
+            store.append("sa", df_nan["A"])
+
+        # nan_rep too big
+        store.append("sb", df["B"], nan_rep="bars")
+        with pytest.raises(ValueError, match=msg):
+            store.append("sb", df_nan["B"])
+
+        # smaller modified nan_rep
+        store.append("sc", df["A"], nan_rep="n")
+        store.append("sc", df_nan["A"])
+        result = store["sc"]
+        expected = concat([df["A"], df_nan["A"]])
+        tm.assert_series_equal(result, expected)
diff --git a/pandas/tests/io/pytables/test_round_trip.py b/pandas/tests/io/pytables/test_round_trip.py
index 6b98a720e4..875a792467 100644
--- a/pandas/tests/io/pytables/test_round_trip.py
+++ b/pandas/tests/io/pytables/test_round_trip.py
@@ -213,12 +213,9 @@ def test_table_values_dtypes_roundtrip(setup_path):
 
         # incompatible dtype
         msg = re.escape(
-            "invalid combination of [values_axes] on appending data "
-            "[name->values_block_0,cname->values_block_0,"
-            "dtype->float64,kind->float,shape->(1, 3)] vs "
-            "current table [name->values_block_0,"
-            "cname->values_block_0,dtype->int64,kind->integer,"
-            "shape->None]"
+            "Cannot serialize the column [a] "
+            "because its data contents are not [float] "
+            "but [integer] object dtype"
         )
         with pytest.raises(ValueError, match=msg):
             store.append("df_i8", df1)
diff --git a/pandas/tests/io/test_pickle.py b/pandas/tests/io/test_pickle.py
index 5fe0f1265e..bab2c1561e 100644
--- a/pandas/tests/io/test_pickle.py
+++ b/pandas/tests/io/test_pickle.py
@@ -383,55 +383,6 @@ def test_pickle_buffer_roundtrip():
         tm.assert_frame_equal(df, result)
 
 
-# ---------------------
-# tests for URL I/O
-# ---------------------
-
-
-@pytest.mark.parametrize(
-    "mockurl", ["http://url.com", "ftp://test.com", "http://gzip.com"]
-)
-def test_pickle_generalurl_read(monkeypatch, mockurl):
-    def python_pickler(obj, path):
-        with open(path, "wb") as fh:
-            pickle.dump(obj, fh, protocol=-1)
-
-    class MockReadResponse:
-        def __init__(self, path) -> None:
-            self.file = open(path, "rb")
-            if "gzip" in path:
-                self.headers = {"Content-Encoding": "gzip"}
-            else:
-                self.headers = {"Content-Encoding": ""}
-
-        def __enter__(self):
-            return self
-
-        def __exit__(self, *args):
-            self.close()
-
-        def read(self):
-            return self.file.read()
-
-        def close(self):
-            return self.file.close()
-
-    with tm.ensure_clean() as path:
-
-        def mock_urlopen_read(*args, **kwargs):
-            return MockReadResponse(path)
-
-        df = DataFrame(
-            1.1 * np.arange(120).reshape((30, 4)),
-            columns=Index(list("ABCD"), dtype=object),
-            index=Index([f"i-{i}" for i in range(30)], dtype=object),
-        )
-        python_pickler(df, path)
-        monkeypatch.setattr("urllib.request.urlopen", mock_urlopen_read)
-        result = pd.read_pickle(mockurl)
-        tm.assert_frame_equal(df, result)
-
-
 def test_pickle_fsspec_roundtrip():
     pytest.importorskip("fsspec")
     with tm.ensure_clean():
diff --git a/pandas/tests/reshape/merge/test_merge.py b/pandas/tests/reshape/merge/test_merge.py
index f0abc1afc6..f0f67aebd8 100644
--- a/pandas/tests/reshape/merge/test_merge.py
+++ b/pandas/tests/reshape/merge/test_merge.py
@@ -1464,7 +1464,10 @@ class TestMerge:
         data2 = DataFrame(
             np.arange(20).reshape((5, 4)) + 1, columns=["a", "b", "x", "y"]
         )
-        msg = "'full' is not a valid Merge type: left, right, inner, outer, cross, asof"
+        msg = (
+            "'full' is not a valid Merge type: left, right, inner, outer, "
+            "left_anti, right_anti, cross, asof"
+        )
         with pytest.raises(ValueError, match=re.escape(msg)):
             data1.merge(data2, how="full")
 
diff --git a/pandas/tests/reshape/merge/test_merge_antijoin.py b/pandas/tests/reshape/merge/test_merge_antijoin.py
new file mode 100644
index 0000000000..006622c6e5
--- /dev/null
+++ b/pandas/tests/reshape/merge/test_merge_antijoin.py
@@ -0,0 +1,280 @@
+import numpy as np
+import pytest
+
+import pandas.util._test_decorators as td
+
+import pandas as pd
+from pandas import (
+    DataFrame,
+    MultiIndex,
+)
+import pandas._testing as tm
+from pandas.core.reshape.merge import merge
+
+
+def test_merge_antijoin():
+    # GH#42916
+    left = DataFrame({"A": [1, 2, 3]}, index=["a", "b", "c"])
+    right = DataFrame({"B": [1, 2, 4]}, index=["a", "b", "d"])
+
+    result = merge(left, right, how="left_anti", left_index=True, right_index=True)
+    expected = DataFrame({"A": [3], "B": [np.nan]}, index=["c"])
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_index=True, right_index=True)
+    expected = DataFrame({"A": [np.nan], "B": [4]}, index=["d"])
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_on_different_columns():
+    left = DataFrame({"A": [1.0, 2.0, 3.0], "B": ["a", "b", "c"]}).astype({"B": object})
+    right = DataFrame({"C": [1.0, 2.0, 4.0], "D": ["a", "d", "b"]}).astype(
+        {"D": object}
+    )
+
+    result = merge(left, right, how="left_anti", left_on="B", right_on="D")
+    expected = DataFrame(
+        {
+            "A": [3.0],
+            "B": ["c"],
+            "C": [np.nan],
+            "D": [np.nan],
+        },
+        index=[2],
+    ).astype({"B": object, "D": object})
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_on="B", right_on="D")
+    expected = DataFrame(
+        {
+            "A": [np.nan],
+            "B": [np.nan],
+            "C": [2.0],
+            "D": ["d"],
+        },
+        index=[1],
+    ).astype({"B": object, "D": object})
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_nonunique_keys():
+    left = DataFrame({"A": [1.0, 2.0, 3.0], "B": ["a", "b", "b"]}).astype({"B": object})
+    right = DataFrame({"C": [1.0, 2.0, 4.0], "D": ["b", "d", "d"]}).astype(
+        {"D": object}
+    )
+
+    result = merge(left, right, how="left_anti", left_on="B", right_on="D")
+    expected = DataFrame(
+        {
+            "A": [1.0],
+            "B": ["a"],
+            "C": [np.nan],
+            "D": [np.nan],
+        },
+        index=[0],
+    ).astype({"B": object, "D": object})
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_on="B", right_on="D")
+    expected = DataFrame(
+        {
+            "A": [np.nan, np.nan],
+            "B": [np.nan, np.nan],
+            "C": [2.0, 4.0],
+            "D": ["d", "d"],
+        },
+        index=[2, 3],
+    ).astype({"B": object, "D": object})
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_same_df():
+    left = DataFrame({"A": [1, 2, 3]}, index=["a", "b", "c"], dtype=np.int64)
+    result = merge(left, left, how="left_anti", left_index=True, right_index=True)
+    expected = DataFrame([], columns=["A_x", "A_y"], dtype=np.int64)
+    tm.assert_frame_equal(result, expected, check_index_type=False)
+
+
+def test_merge_antijoin_nans():
+    left = DataFrame({"A": [1.0, 2.0, np.nan], "C": ["a", "b", "c"]}).astype(
+        {"C": object}
+    )
+    right = DataFrame({"A": [3.0, 2.0, np.nan], "D": ["d", "e", "f"]}).astype(
+        {"D": object}
+    )
+    result = merge(left, right, how="left_anti", on="A")
+    expected = DataFrame({"A": [1.0], "C": ["a"], "D": [np.nan]}).astype(
+        {"C": object, "D": object}
+    )
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_on_datetime64tz():
+    # GH11405
+    left = DataFrame(
+        {
+            "key": pd.date_range("20151010", periods=2, tz="US/Eastern"),
+            "value": [1.0, 2.0],
+        }
+    )
+    right = DataFrame(
+        {
+            "key": pd.date_range("20151011", periods=3, tz="US/Eastern"),
+            "value": [1.0, 2.0, 3.0],
+        }
+    )
+
+    expected = DataFrame(
+        {
+            "key": pd.date_range("20151010", periods=1, tz="US/Eastern"),
+            "value_x": [1.0],
+            "value_y": [np.nan],
+        },
+        index=[0],
+    )
+    result = merge(left, right, on="key", how="left_anti")
+    tm.assert_frame_equal(result, expected)
+
+    expected = DataFrame(
+        {
+            "key": pd.date_range("20151012", periods=2, tz="US/Eastern"),
+            "value_x": [np.nan, np.nan],
+            "value_y": [2.0, 3.0],
+        },
+        index=[1, 2],
+    )
+    result = merge(left, right, on="key", how="right_anti")
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_multiindex():
+    left = DataFrame(
+        {
+            "A": [1, 2, 3],
+            "B": [4, 5, 6],
+        },
+        index=MultiIndex.from_tuples(
+            [("a", "x"), ("b", "y"), ("c", "z")], names=["first", "second"]
+        ),
+    )
+    right = DataFrame(
+        {
+            "C": [7, 8, 9],
+            "D": [10, 11, 12],
+        },
+        index=MultiIndex.from_tuples(
+            [("a", "x"), ("b", "y"), ("c", "w")], names=["first", "second"]
+        ),
+    )
+
+    result = merge(left, right, how="left_anti", left_index=True, right_index=True)
+    expected = DataFrame(
+        {
+            "A": [3],
+            "B": [6],
+            "C": [np.nan],
+            "D": [np.nan],
+        },
+        index=MultiIndex.from_tuples([("c", "z")], names=["first", "second"]),
+    )
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_index=True, right_index=True)
+    expected = DataFrame(
+        {
+            "A": [np.nan],
+            "B": [np.nan],
+            "C": [9],
+            "D": [12],
+        },
+        index=MultiIndex.from_tuples([("c", "w")], names=["first", "second"]),
+    )
+    tm.assert_frame_equal(result, expected)
+
+
+@pytest.mark.parametrize(
+    "dtype",
+    [
+        "Int64",
+        pytest.param("int64[pyarrow]", marks=td.skip_if_no("pyarrow")),
+        pytest.param("timestamp[s][pyarrow]", marks=td.skip_if_no("pyarrow")),
+        pytest.param("string[pyarrow]", marks=td.skip_if_no("pyarrow")),
+    ],
+)
+def test_merge_antijoin_extension_dtype(dtype):
+    left = DataFrame(
+        {
+            "join_col": [1, 3, 5],
+            "left_val": [1, 2, 3],
+        }
+    )
+    right = DataFrame(
+        {
+            "join_col": [2, 3, 4],
+            "right_val": [1, 2, 3],
+        }
+    )
+    left = left.astype({"join_col": dtype})
+    right = right.astype({"join_col": dtype})
+    result = merge(left, right, how="left_anti", on="join_col")
+    expected = DataFrame(
+        {
+            "join_col": [1, 5],
+            "left_val": [1, 3],
+            "right_val": [np.nan, np.nan],
+        },
+        index=[0, 2],
+    )
+    expected = expected.astype({"join_col": dtype})
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_empty_dataframe():
+    left = DataFrame({"A": [], "B": []})
+    right = DataFrame({"C": [], "D": []})
+
+    result = merge(left, right, how="left_anti", left_on="A", right_on="C")
+    expected = DataFrame({"A": [], "B": [], "C": [], "D": []})
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_on="A", right_on="C")
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_no_common_elements():
+    left = DataFrame({"A": [1, 2, 3]})
+    right = DataFrame({"B": [4, 5, 6]})
+
+    result = merge(left, right, how="left_anti", left_on="A", right_on="B")
+    expected = DataFrame({"A": [1, 2, 3], "B": [np.nan, np.nan, np.nan]})
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_on="A", right_on="B")
+    expected = DataFrame({"A": [np.nan, np.nan, np.nan], "B": [4, 5, 6]})
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_with_null_values():
+    left = DataFrame({"A": [1.0, 2.0, None, 4.0]})
+    right = DataFrame({"B": [2.0, None, 5.0]})
+
+    result = merge(left, right, how="left_anti", left_on="A", right_on="B")
+    expected = DataFrame({"A": [1.0, 4.0], "B": [np.nan, np.nan]}, index=[0, 3])
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_on="A", right_on="B")
+    expected = DataFrame({"A": [np.nan], "B": [5.0]}, index=[2])
+    tm.assert_frame_equal(result, expected)
+
+
+def test_merge_antijoin_with_mixed_dtypes():
+    left = DataFrame({"A": [1, "2", 3.0]})
+    right = DataFrame({"B": ["2", 3.0, 4]})
+
+    result = merge(left, right, how="left_anti", left_on="A", right_on="B")
+    expected = DataFrame({"A": [1], "B": [np.nan]}, dtype=object)
+    tm.assert_frame_equal(result, expected)
+
+    result = merge(left, right, how="right_anti", left_on="A", right_on="B")
+    expected = DataFrame({"A": [np.nan], "B": [4]}, dtype=object, index=[2])
+    tm.assert_frame_equal(result, expected)