API (string dtype): implement hierarchy (NA > NaN, pyarrow > python) for consistent comparisons between different string dtypes
Closes https://github.com/pandas-dev/pandas/issues/60639


This does not yet handle the case of comparison to object dtype.


- [ ] [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
- [ ] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

@jorisvandenbossche - I've merged main and pushed a commit here. If you have any objections, I can pull it off.

- Adds whatsnew to 2.3.
- Simplifies conditionals in a test.
- Fixes behavior of ArrowExtensionArray and adds tests for it.

For the last one, previously ArrowExtensionArray vs Nan-Python was giving back NumPy bool. This was the only case where ArrowExtensionArray was not resulting in ArrowExtensionArray.

> This does not yet handle the case of comparison to object dtype.

object dtype looks correct to me here. > > object dtype looks correct to me here.
> 
> Hmm, not entirely sure anymore what I meant with that object dtype was not yet covered. I thought maybe the case where the object dtype does not contain just strings, but also that seems to work fine

One case related to object dtype that is still failing is comparing with an object series that has mixed types:

```python
In [3]: ser1 = pd.Series(["a", None, "b"], dtype=pd.StringDtype("pyarrow", na_value=np.nan))

In [4]: ser2 = pd.Series(["a", None, 2], dtype=object)

In [5]: ser1 == ser2
...
File ~/scipy/repos/pandas/pandas/core/arrays/arrow/array.py:517, in ArrowExtensionArray._box_pa_array(cls, value, pa_type, copy)
    514     pa_array = pa.array(value, type=pa_type, from_pandas=True)
    515 except (pa.ArrowInvalid, pa.ArrowTypeError):
    516     # GH50430: let pyarrow infer type, then cast
--> 517     pa_array = pa.array(value, from_pandas=True)
    519 if pa_type is None and pa.types.is_duration(pa_array.type):
    520     # Workaround https://github.com/apache/arrow/issues/37291
    521     from pandas.core.tools.timedeltas import to_timedelta
...
ArrowTypeError: Expected bytes, got a 'int' object

In [6]: ser1 = pd.Series(["a", None, "b"], dtype=pd.StringDtype("python", na_value=np.nan))

In [7]: ser1 == ser2
Out[7]: 
0     True
1    False
2    False
```

So with just object dtype, such a comparison works. And it also works with the python-backed string dtype. But fails with the pyarrow-backed string dtype, because in this case the comparison defers to the ArrowExtensionArray implementation, which tries to convert the other side to a pyarrow array, which is not supported for mixed types. While we generally (although in many cases definitely not best practice) mixed-types object dtype in pandas.

(but let's consider this for a separate issue/PR) Thank @jorisvandenbossche and @rhshadrach  Owee, I'm MrMeeseeks, Look at me.

There seem to be a conflict, please backport manually. Here are approximate instructions:

1. Checkout backport branch and update it.

```
git checkout 2.3.x
git pull
```

2. Cherry pick the first parent branch of the this PR on top of the older branch:
```
git cherry-pick -x -m1 6177e2233974643d17d1560a4d61804800534900
```

3. You will likely have some merge/cherry-pick conflict here, fix them and commit:

```
git commit -am 'Backport PR #61138: API (string dtype): implement hierarchy (NA > NaN, pyarrow > python) for consistent comparisons between different string dtypes'
```

4. Push to a named branch:

```
git push YOURFORK 2.3.x:auto-backport-of-pr-61138-on-2.3.x
```

5. Create a PR against branch 2.3.x, I would have named this PR:

> "Backport PR #61138 on branch 2.3.x (API (string dtype): implement hierarchy (NA > NaN, pyarrow > python) for consistent comparisons between different string dtypes)"

And apply the correct labels and milestones.

Congratulations — you did some good work! Hopefully your backport PR will be tested by the continuous integration and merged soon!

Remember to remove the `Still Needs Manual Backport` label once the PR gets merged.

If these instructions are inaccurate, feel free to [suggest an improvement](https://github.com/MeeseeksBox/MeeseeksDev).
                 It appears that this has not been included in the 2.3 release as intended. see #61570

we probably need to now move the release note to 2.3.1 on main and backport that too.

probably also best to have a PR to create a 2.3.1 whatsnew just in case anyway. If we abandon a release, we just change the milestones and move the entries in the whatsnew in a separate PR. And I see that there are a few more issues with "Still Needs Manual Backport" label (https://github.com/pandas-dev/pandas/issues?q=label%3A%22Still+Needs+Manual+Backport%22+is%3Aclosed)  that we should check if that is indeed still the case Backport -> https://github.com/pandas-dev/pandas/pull/61649