PDEP-9: Allow third-party projects to register pandas connectors with a standard API
@pandas-dev/pandas-core another PDEP. There is a short summary at the beginning of the proposal for people not immediately having the time to read the whole document.
> The current implementation allows us some control over these methods so that there is a more consistent user experience between different engines. By having third parties solely responsible for the connector, I believe it is then completely out of our hands, which could make for a worse user experience.

That's an interesting point. I personally think that the current proposal already guarantees a consistent behavior, leaving only the parameters up to the developers of the connectors. This would prevent things like using the builder/fluid pattern, that if I'm not wrong PySpark is using, and afaik users seem to like. Talking about something like `.from_csv(fname).set_delimiter(',').ignore_header(True)...`. I'm not sure it's a good thing to prevent developers to implement this API. Maybe I'd actually let them return a lazy builder too.

Do you have a particular example that can show the kind of inconsistencies you're worried about? I find it difficult to understand the problem without an example.

> This is making me think that for some important methods (`to_excel` in particular), the long term plan should be to leave the implementation within pandas (but moved to `.io`).

Not a strong opinion, but in my mind, in the long term I'd only have in pandas the connectors provided by PyArrow. That doesn't mean we can make a exception for Excel. I don't consider it so important, but of course that depends of the domain of each one. But the main reason I'd move it out is because there are 4 different engines, and I think each of them should be a separate project, so users could simply install whichever is more convenient for them. I don't think keeping 4 connectors or more for Excel in pandas is desirable, and I wouldn't make a choice on which is the "good" one.

As said, this for the long term, I wouldn't make any change to Excel for now. Does it make sense to you? Or what would be your preferred option for the long term? > > This is making me think that for some important methods (`to_excel` in particular), the long term plan should be to leave the implementation within pandas (but moved to `.io`).
> 
> ... But the main reason I'd move it out is because there are 4 different engines, and I think each of them should be a separate project, so users could simply install whichever is more convenient for them. I don't think keeping 4 connectors or more for Excel in pandas is desirable, and I wouldn't make a choice on which is the "good" one.

Excel is actually an interesting example: first, AFAIK those 4 engines are not about "choice" (for _reading_ at least), but each of them implement different file formats and so which engine gets used depends on the type of file you are reading. 

But so that brings up the issue that _if_ each of them would be implemented in a separate project, they can't all use the name "excel" (for OpenDocument spreadsheets it probably make sense to use another name, but for .xls vs .xlsx vs .xlsb files less so). 
Of course, the ones that would want to use the same name could also be combined in a single external project.

(Sidenote: according to our user survey, at the time in 2019, excel was the _second_ most used IO method after CSV. I suppose that since then other formats like Parquet will have become more popular, but I assume excel is still one of the most used methods) > But so that brings up the issue that _if_ each of them would be implemented in a separate project, they can't all use the name "excel" (for OpenDocument spreadsheets it probably make sense to use another name, but for .xls vs .xlsx vs .xlsb files less so). Of course, the ones that would want to use the same name could also be combined in a single external project.

I don't know much about the Excel connectors to be honest, but very good point regardless of the example. We could in theory solve it in the same way we solved it until now, that it's having two different levels, the format, and the engine. The problem with it (which this approach would make way more visible) is that all engines of a format must have the same exact parameters. Which we've been doing so far, but of course if anyone can implement connectors, we can't enforce anymore (unless we implement every format signature, which doesn't seem a good idea at all). The best idea I've got is that connectors find different names. For excel actually seems to be an easy case, using `from_xls`, `from_xlsx`,... and even more if the implementations being used are different. But for csv we already have 3 engines for the same format, something like `from_csv_pyarrow`, `from_csv_c`, `from_csv_python` or whatever should be used instead (of course we could use `from_csv` for one of them). Not the most beautiful clearly, but having the name explicit is good in some ways (users forced to know there are different options, code being clear on what is being used...).

Surely happy to hear other ideas.

> (Sidenote: according to our user survey, at the time in 2019, excel was the _second_ most used IO method after CSV. I suppose that since then other formats like Parquet will have become more popular, but I assume excel is still one of the most used methods)

Yes, very good point indeed. Not important for me, not even sure if I ever used it, but obviously important in general. Unless we want to move pd.read_foo to become DataFrame methods (IIRC there used to exist aliases there and we deprecated them) i think it would make sense for read_foo methods to get pinned in the pd.io namespace rather than DataFrame.io.  Not a strong opinion though. > Unless we want to move pd.read_foo to become DataFrame methods (IIRC there used to exist aliases there and we deprecated them) i think it would make sense for read_foo methods to get pinned in the pd.io namespace rather than DataFrame.io. Not a strong opinion though.

To me a single namespace seems simpler, and having import functions as DataFrame methods feels natural (also to have them as pandas module function). But open to this, and also open to discuss other possible APIs. Not sure if it's crazy, but we can consider something like:

```python
import pandas

df  = pandas.read('csv').engine('pyarrow').params(fname='dataset.csv', delimiter=';')

df.write('parquet').engine('fastparquet').params(fname='dataset.parquet')
```
This would have some advantages, like not needing an accessor, avoiding the conflicts in naming when more than one engine exists. Clearly more verbose, and a more difficult to get used to it coming from the current API, but worth considering this API too, and others that people can think of.

**UPDATE**: I Initially used `pandas.from()` and `df.to` in the example, but `from` is a reserved word in Python, so this should be something else, like read/write as  I used now.

**UPDATE 2**: PySpark uses something similar to this, maybe nicer to use the format as the function name (we would still need to figure out how to set the engine in case disambiguation is needed):

```python
spark_session.read.parquet("/tmp/output/people.parquet")
``` these builder patterns are way too verbose; -1 on anything but read_xxx and to_xxx

we don't need to disrupt the world - changing apis should receive very careful consideration. Instead of adding the io accessor could we not maintain a function registry for read / write functions? That is how pyarrow / arrow for instance allow you to bring your own functions. I think something like:

```
import foolib
import pandas as pd

pd.register_read_function("foo", foolib.read_dataframe)
pd.read_foo("afile.foo")
```

maintains the read_ / write_ interfaces Jeff is looking for.

 I'm am -1 on the import registry proposal described here - I think that pattern is too much of a blackbox to work reliably > Instead of adding the io accessor could we not maintain a function registry for read / write functions? That is how pyarrow / arrow for instance allow you to bring your own functions.

Interesting point. Personally I think the custom registration of methods is a big barrier for many users. Probably not needed, since you could have just `import connector_package`, and it could make that registration for you. That's how projects implementing custom accessors already do it. I totally understand your point about the magic of entrypoints, felt the same exact way when I first discovered it. But a good idea or not it's the standard API of Python, and I think it's not magic if you know that it exists and how it works. So, while I totally understand you, I think the "magic" entrypoint API is the correct way to do things, and the other options are the hacks, even if many people may feel the other way round initially. Probably providing clear documentaiton about how it works, and maybe functionality to see what's been installed (e.g. `DataFrame.io.list_connectors()`) would make that magic become something natural and predictable. What do you think? Do you know of any other packages that currently leverage the entry points like you describe? > Do you know of any other packages that currently leverage the entry points like you describe?

Pytest, Ibis and Pylons that I know. There are lots using them to create "binaries", suck as Jupyter, Flake8, pytest... But that use case it's quite different. > A possible way around this is to define a base class `PandasIO` that defines the protocol for reading/writing data, and instead of doing `DataFrame.io.read_foo()`, you write a class called `FooIO` with a base class of `PandasIO` and then have something like:

@Dr-Irv That sounds like a good idea. I don't think the class should be in pandas, since the connectors could potentially be used by other projects, but a base class, and maybe a generic test suite that let connector developers know if they are breaking the API or a convention in a simpler way can be something useful. And if that helps with typing, then even better. I see that we basically have four API proposals at the moment

1) entry_points to register `.to_foo` DataFrame methods and `.from_foo` methods and/or top level functions
2) a way to register import functions using say `pandas.read('csv').engine('pyarrow')`
3) maintain a function registry for read / write functions say `pd.register_read_function("foo", foolib.read_dataframe) and then do `pd.read_foo("afile.foo")`
4) define a base class PandasIO that defines the protocol for reading/writing data

I think that we could maybe have a rejected ideas section in the PDEP outlining the ones that are not chosen.

At the risk of introducing a fifth option...

I see we have an issue open for entry points for the SQL engine #41728. Now in this PDEP we mention of DuckDB and the solution would be a `from_duckdb` method.

Now, personally, I've never used `pandas.read_sql` but see that we also have `pandas.read_sql_query` and `read_sql_table` methods.

I also see that @rhshadrach bought up the issue of a consistent API when it comes to function parameters.

So my question is...

Could there maybe a plug-in structure, where 3rd parties can add engines to any of our existing IO   routines instead of directly into the `.io` namespace? > Could there maybe a plug-in structure, where 3rd parties can add engines to any of our existing IO routines instead of directly into the `.io` namespace?

I think my proposal of a `PandasIO` base class does something similar.  Each implemented subclass could live as a separate package that people install, and they directly reference the subclass to handle read/write operations.  

It's similar in concept to how python handles I/O.  Base class `IOBase`, with 3 ABC's on top of that, and then implementations like `FileIO`, `BytesIO`, etc. on top of that.

From a transition point of view, we could leave in the existing `read_xxx()` and `to_yyy()` routines, and switch their implementations to the new class structure, and decide at a later date whether to deprecate and eventually remove `read_xxx()` and `to_yyy()`.  We could add a parameter to things like `read_sql()` that indicates the class to use, which would allow people to substitute in their own implementations rather than the one we provide.
 Thanks @simonjayhawkins for the summary and the proposal. Adding the same code samples for the different options on what they mean for the users, I think it makes it easier to see the differences and helps the discussion (let me know if I missed any option (the exact naming from/to, read/write can probably be discussed separately once we decide the API option):

```python

# option 1 (original and current proposal in the PR)
df = pandas.DataFrame.io.from_myformat(...)
df.io.to_myformat(...)


# option 2 (same, but separate namespaces for from/to, proposed by Brock)
df = pandas.io.from_myformat(...)
df.io.to_myformat(...)


# option 3 (single methods using a builder pattern
df = pandas.read('myformat').engine('myengine').params(...)
df.write('myformat').engine('myengine').params(...)


# option 4 (register into the existing `pandas` and `DataFrame` namespaces)
df = pandas.from_myformat(...)
df.to_myformat(...)


# option 5 (register engines, not formats. This is different from the rest, 
# since signatures for all formats should be defined in pandas,
# and new formats can't be added by third-parties, proposed by Simon)
df = pandas_from_knownformat(engine='third-party-engine', ...)
df.to_knownformat(engine='third-party-engine', ...)


# option 6 (make connectors be subclasses of a pandas defined class, and use them directly, this can already be done, we would just standardize things, proposed by Irv)
import MyFormatConnector

df = MyFormatConnector.read(...)
MyFormatConnector.write(df)


# option 7 (register connectors directly. I see this as the same as 4,
# except that instead of using entrypoints the import would be used,
# since the registration is likely to happen inside the modules.
# This can be done already monkeypatching, the only addition would be to
# provide the register methods to make it official we're happy with it. Proposed by Will)
import myconnector

pandas.register_read_function("myformat", myconnector.read_dataframe)
pandas.register_write_function("myformat", myconnector.write_dataframe)

df = pandas.read_myformat(...)
df.to_myformat(...)

```

My opinion:

Some variation of 1, 2 or 3 seem reasonable to me.

4 and 7 seem a bit messy. Maybe in the long term if we remove many formats users will only have a small number of from/to methods, and my only concern is the magic of mixing core methods with third-party dynamic methods in the same namespace. But the transition period with the status quo plus new formats seems chaotic.

I think 5 doesn't solve the problem of users being able to use domain-specific formats in a standard way, and keeps the maintenance cost high (discussing which formats are supported...)

I think 6 is not very pandas consistent or friendly API, and also doesn't support method chaining. > I think 6 is not very pandas consistent or friendly API, and also doesn't support method chaining.

It's not consistent with `pandas`, but it is closer to what is done in python, which is why I suggested it.  It supports method chaining on reading, but not writing, but that can be overcome (admittedly repeating `write` is a bit awkward):
```python
import MyFormatConnector

df = MyFormatConnector.read(...)
df.write(MyFormatConnector.write(...))
```

Having said that, if we choose one of the other options, I still think implementing things this way might make it easier for people to extend the API and make it more like a plug-in.

My main reason for doing option 6 would be so that users get type checking.  I think that type checking could also be provided to users with option 3.  I don't see how the other options could easily support type checking for users.

 > My main reason for doing option 6 would be so that users get type checking. I think that type checking could also be provided to users with option 3. I don't see how the other options could easily support type checking for users.

option 5 is also type-checking friendly

> I think 5 doesn't solve the problem of users being able to use domain-specific formats in a standard way, and keeps the maintenance cost high (discussing which formats are supported...)

In combination with say a `pandas.read` and a `DataFrame.write` like option 3 but without the builder. So would be akin to the base class of option 6. And could control parameters.

So we would have entrypoints for common formats (using existing signatures), e,g, excel, csv, sql and a generic read/write with a generic signature. e.g. for read something like....

```
df = pandas.read('myformat', *, engine='myengine'|None, index=..., columns = ..., dtype=..., storage_options=..., use_nullable_dtypes=..., , **kwargs) 
```

so 3rd party plugins could define a group with format and optional engine and the other arguments are passed to the 3rd party connector.

 A single `pandas.read()` function would be quite nice, but all format specific parameters would have to be `*args, **kwargs`. If it wasn't for that, it'd surely would be my preferred option. Still worth considering.

I see an important benefit of this proposal to allow in the long term to have a single and consistent API for all formats. I personally don't like having to make the choice of which format is important and which is not.

I wonder if it'd make sense to split this PDED in two:

- Create a standard for connectors based in Arrow and entrypoints that pandas can leverage to support any format implemented with the standard
- What's the exact API pandas implements to support the connectors from this standard

There was very good feedback on concerns and limitations to the first point, but feels (correct me if I'm wrong) that there is mostly agreement to move forward with this, and the proposal is not far from ready.

Feels like there are lots of different opinions about the API, and it will be hard to make progress. And even more to make a decision, at least before the decision making part of the governance is address, which feels like it'll take months.

I wonder if two PDEPs would allow to start implementing the internal changes while we keep the current API, and have most things ready by the time an API is decided. > A single `pandas.read()` function would be quite nice, but all format specific parameters would have to be `*args, **kwargs`. If it wasn't for that, it'd surely would be my preferred option. Still worth considering.

From the typing stubs perspective, you could still define overloads for each `format` option in a  `pandas.read(format="predefined_format', option1=..., option2=..., # etc)` so while the main entry point is `pandas.read(format: str, *args, **kwargs)`, the stubs can be explicit about the arguments for specific format types.

Issue there is that we'd have to find a way to move from documenting `pd.read_csv()`, `pd.read_excel()`, `pd.read_sql()`, etc., to have separate pages for `pd.read(format="csv", ...)`, `pd.read(format="excel", ...)`, `pd.read(format="sql", ...)`, and doing that in the docs might take some effort to get right.
  > I wonder if it'd make sense to split this PDED in two:
> 
> * Create a standard for connectors based in Arrow and entrypoints that pandas can leverage to support any format implemented with the standard
> * What's the exact API pandas implements to support the connectors from this standard

We also have to address whether we transition people from the current API's that have separate methods for each format to the new API that supports connectors, if we choose to have an API that is more "generic", in the sense that it could support new formats without auto-creating new methods in `DataFrame` or at the top level of pandas. > I see an important benefit of this proposal to allow in the long term to have a single and consistent API for all formats

Is that really on the table here?  Like you mentioned, it seems like the only way to accomplish that would be with `*args, **kwargs`, which while "consistent", certainly has some downsides.

> but feels (correct me if I'm wrong) that there is mostly agreement to move forward with this, and the proposal is not far from ready

I'm keeping an open mind, but currently skeptical. > Is that really on the table here? Like you mentioned, it seems like the only way to accomplish that would be with `*args, **kwargs`, which while "consistent", certainly has some downsides.

Sorry I think I express very poorly here. I wasn't referring to the `pandas.read()` API here. I meant one of the benefits of having a PDEP like this one in general is to try to provide users with a consistent API (whatever that API is). The comment was regarding implementing two different APIs depending on whether we consider a format important to be a core format, or not.

> I'm keeping an open mind, but currently skeptical.

Thanks for clarifying, that's good to know. Is it more the general ideal, that you don't think it adds too much value? Or is it a concern about the implementation proposal? It'd be great if you can expand on what makes you feel skeptical about this. I can be biased, but to me in the long term this has the potential to be one of the most changes to pandas with bigger impact. In terms or reducing complexity, providing a clear and intuitive API, in sharing the maintenance cost of connectors, in terms of quickly adding new possibilities to pandas, like having an easy-to-use and fast to develop reader from DuckDB or the new Arrow ADBC to fetch data from DBs more efficiently... I'm on board with connectors, but isn't `pd.read_csv` too widely used and engrained to be considered for deprecation? It's literally everywhere. I'm not sold that the user benefit would be worth the API change in this case, but I'm open to changing my mind

Of the options listed in https://github.com/pandas-dev/pandas/pull/51799#issuecomment-1458504702, I think the last one (option 7) is the one I prefer (and the only one I'd currently be slightly positive on) > * Create a standard for connectors based in Arrow and entrypoints that pandas can leverage to support any format implemented with the standard

I think this side is easier to move forward with since it doesn't mean overhauling the pandas API which is harder to do at this point when compared to adding new functionality. 

I don't quite see why arrow should be a hard requirement.  NumPy is virtually guaranteed to be used by software dealing with numeric data (even PyArrow has a build dependence on Numpy).  It seems limiting to require arrow when it might not be needed by the package aside from possibly trying to leverage some pandas-provided infrastructure. 

I would also vote for option 7, with one mod - I wouldn't place external connectors in the top level of pandas. Some thought would also be needed to handle name conflicts.  > It'd be great if you can expand on what makes you feel skeptical about this

1) I'm ambivalent about allowing/encouraging 3rd-party libraries to implement pd.read_foo/DataFrame.from_bar/frame.to_baz/whatever.
a) On the reading end, the status quo would be using something in that 3rd party's namespace `foo.read_bar` which is more explicit than `pd.read_bar(engine="foo")`.
b) On the writing end, I agree `frame.to_whatever(...)` is more convenient than `whatever.write(frame, ...)`.  (Though in general I find method-chaining nice but less compelling than others)
c) When a 3rd party plugin has bugs or performance issues or [...] those complaints will be incorrectly addressed to us.

2) Churn.  I think a large majority of users are fine with `pd.read_csv` and `frame.to_csv` etc.  
a) flip-side: getting the IO methods out of generic.py/frame.py would be nice bc those files are too big.

3) the pyarrow focus seems unnecessary, counterproductive. > I don't quite see why arrow should be a hard requirement. NumPy is virtually guaranteed to be used by software dealing with numeric data (even PyArrow has a build dependence on Numpy). It seems limiting to require arrow when it might not be needed by the package aside from possibly trying to leverage some pandas-provided infrastructure.

> 3\. the pyarrow focus seems unnecessary, counterproductive.

@bashtage @jbrockmendel I'm not sure if the Arrow part of the proposal is clear (or maybe I just don't understand your comments), let me try to express it clearer.

I see two main options for connectors to interact with pandas:

- **1. Import pandas and return a dataframe**: This has two things I don't like:
  - Connectors are pandas specific, and if another project wants to use them needs to depend on pandas
  - Connectors are free to build the dataframe the way they want, using extension arrays, and potentially being quite coupled with parts of pandas, that if we change, will make the connectors break.

- **2. Give us the data in memory**: This overcomes the previous problems:
  - Connectors are generic, and developing and maintaining connectors is done for the whole ecosystem, which means less work for us, and more quality for the connectors
  - There is a clear API in the agreed data format, and pandas and connectors are properly decoupled, and they won't cause trouble to each other.

Not sure if you agree 2 is better, in my opinion, until here it's all advantages, assuming the format itself don't bring disadvantages. About the format, I guess the main options are:
- **Python**: A Python dictionary of columns, a list of tuples or similar. Some connectors seem to use that. A list of tuples seems inefficient, for a dict we still need to define the format of each column, a Python list would also be inefficient.
- **NumPy**: Surely fine for int or float columns with no missing values, but inefficient for strings, complex for missing values...
- **Arrow**: Designed literally for this use case of exchanging columnar data among software. A well defined format, that covers all or most formats and use needs, and in general efficient.

We could not **require** Arrow, but **support** Arrow instead. That seems a fair point, but instead of an API between pandas and the connectors then we need to implement multiple. Surely worth considering, and for int and float columns without missing values doesn't seem so difficult, but supporting anything else becomes trickier and time consuming.

I guess the main problem requiring the use of an Arrow object is having to use PyArrow, and it's a concern I understand. First thing is that this proposal doesn't imply PyArrow being a required dependency, it can be to use connectors, but pandas would still work without PyArrow as now. Second, I think it's doable to use nanoarrow and be able to convert the Arrow data to numpy/Python objects if that's what we want. And last, if we're so much against PyArrow, should we have a discussion and consider other implementations, like the Rust ones?

What's your take, what is your preferred option, making the API use numpy data, writing specs for how to represent cases like int with NA...? Regarding the API, seems like the most popular option is to keep the status quo and avoid breaking changes and churn. That's fine with me. I see to things that are not ideal, but none of them is a big deal:
- Mixing dynamically created functions/methods with the core pandas ones
- The inconsistency of `read_` / `to_` (instead of `read/write` or `from/to`

I think an option that would have the benefit of option 7 (`pandas.register_read_function(...)`) of being more explicit, while providing a nicer API would be using entrypoints but requiring setting an option to load methods:

```python
import pandas

pandas.options.io.load_connectors = True

df = pandas.read_adbc(...)
```

Assuming that most people don't install packages unnecessarily, and that we could make some of the less popular readers (`read_sas`, `read_spss`, `read_gbq`) to eventually not load by default (and in the long term maybe live in separate packages), I think the namespaces for `pandas` and `pandas.DataFrame` should have just the loaders relevant to the user or the most popular ones.

Do people like this idea more than the `DataFrame.io` namespace? I'm not interested in going over the benefits and drawbacks of arrow.  If you want to define an API that returns arrow tables, that sounds like a registration mechanism that should live in pyarrow.  Focusing on it here only distracts from the API design decisions. > some of the less popular readers (read_sas, read_spss, read_gbq) to eventually not load by default

im working on a branch to lazify imports, mostly of pandas.io.  So +1 on this part. > I'm not interested in going over the benefits and drawbacks of arrow. If you want to define an API that returns arrow tables, that sounds like a registration mechanism that should live in pyarrow. Focusing on it here only distracts from the API design decisions.

Do I understand correctly that what you propose is to just leave the API between connectors and pandas undefined in this PDEP, not that you have a proposal for another possible API? Maybe 2 PDEPS if the two parts can be thought of truly orthogonally otherwise happy to keep one for now unless becomes unmanageable.

When we have a PDEP voting process then we will be likely talking in terms of 30-60 days for approval to give more time for comment/discussion/review? So I don't think that splitting to expedite the approval would be a great precedent.

For the connector interface can we enumerate the options also to help the discussion

I see these options from a quick scan here and related issues

1) arrow (pyarrow/nanoarrow) exchange using entry points (and presumably some marshalling?)
2) provide from_arrow/to_arrow methods #51769 (precursor to this proposal)
3) allow engine backends to existing methods #41728 (this PDEP mentions DuckDB)
4) dataframe intechange protocol #47368 (this PDEP mentions Polars)
5) do nothing. If arrow is the defacto in-memory format for exchanging columnar data then registration mechanism for that should live in pyarrow
6) expect the libraries have pandas as a dependency to create/read a pandas DataFrame (This could use entry points without marshalling?)
7) something else Python/NumPy https://github.com/pandas-dev/pandas/pull/51799#issuecomment-1460927574

It does appear that maybe the pandas api and the interchange protocol would be difficult to decouple at this stage