ENH: Enable pytables to round-trip with StringDtype
State: closed

- [ ] closes #xxxx (Replace xxxx with the GitHub issue number)
- [ ] [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/vX.X.X.rst` file if fixing a bug or adding a new feature.

Replaces #60625

Writing out with object dtype still infers `str` when `infer_string=True`.


Labels:
name: IO HDF5
description: read_hdf, HDFStore

Comments:
More general question here: do we actually _need_ (or want) to fully roundtrip the string dtype in HDF IO?  

I don't think we do that in other IO methods (except for pickle, I assume). It seems we don't support other nullable or pyarrow dtypes right now in HDF IO, so can't compare with how we roundtrip those. But for example for Parquet we right now only (will, with the latest pyarrow) roundtrip "str" vs "string", but not the exact storage. 

I think in many cases it is good to "just" get the default string dtype for a file where a column has been stored "as a string column" (for example assume we already had StringDtype by default for a while but would now be changing the default storage from python to payrrow, then I think  ideally many people just start getting that default storage when reading existing files. As long as the idea is that the difference should not matter in 99.9% of the cases (except for performance benefits etc, of course)).  
In general we might not want to store every single detail of the dtype in the file, but only the general ("logical"?) type (while we of course have the specific NaN vs NA case for now, which might be a good exception)


That's a good question - yes generally I don't think it is important to restore the storage of the string types through I/O operations (even for Pickle). I think that even hurts the portability of pandas by potentially encoding information about the execution environment into the I/O storage
@jorisvandenbossche 

> (while we of course have the specific NaN vs NA case for now, which might be a good exception)

I take this to mean store sentinel values like `string-na` and `string-nan` and when reading in the data we infer the backend (python vs pyarrow) from the environment. Is that right?

What if str / string is written out but then read in with `infer_string` set to False?
> I take this to mean store sentinel values like `string-na` and `string-nan` and when reading in the data we infer the backend (python vs pyarrow) from the environment. Is that right?

Yes, but then it could also be `"str"` vs `"string"` since we already have that distinguishing string repr.

(now, I am fine with distinguishing those two, but personally I would also be fine with just storing string data the same and reading them the same depending on the option being set)

> What if str / string is written out but then read in with `infer_string` set to False?

Good question. I should check to be sure but for parquet I think it will then still use StringDtype, as in general it will try to preserve any extension dtype. I am personally fine with doing the same here, but also OK to explicitly not restore `"str"` in that case but read it as object dtype (while `"string"` is more explicit opt-in, so that I would restore regardless of the option?)
> Good question. I should check to be sure but for parquet I think it will then still use StringDtype, as in general it will try to preserve any extension dtype. I am personally fine with doing the same here, but also OK to explicitly not restore `"str"` in that case but read it as object dtype (while `"string"` is more explicit opt-in, so that I would restore regardless of the option?)

I think this proposal makes sense and works in both current and future versions of pandas. +1
@WillAyd @jorisvandenbossche - updated the logic here. Any thoughts on the whatsnew line?
Thanks @rhshadrach!
Backport -> https://github.com/pandas-dev/pandas/pull/60771