[Python] pa.RecordBatch.from_pandas doesn't accept DataFrame with numeric column names

[asfimport]

import pyarrow as pa
import pandas as pd

df = pd.DataFrame([1])
pa.RecordBatch.from_pandas(df)

Exception:

TypeError                                 Traceback (most recent call last)
<ipython-input-5-670ba4a2ddb2> in <module>()
      3 
      4 df = pd.DataFrame([1])
----> 5 pa.RecordBatch.from_pandas(df)

table.pxi in pyarrow.lib.RecordBatch.from_pandas()

table.pxi in pyarrow.lib._dataframe_to_arrays()

/home/icexelloss/miniconda3/envs/spark-dev/lib/python3.5/site-packages/pyarrow/pandas_compat.py in construct_metadata(df, index_levels, preserve_index, types)
    187                         arrow_type=arrow_type
    188                     )
--> 189                     for name, arrow_type in zip(df.columns, df_types)
    190                 ] + (
    191                     [

/home/icexelloss/miniconda3/envs/spark-dev/lib/python3.5/site-packages/pyarrow/pandas_compat.py in <listcomp>(.0)
    187                         arrow_type=arrow_type
    188                     )
--> 189                     for name, arrow_type in zip(df.columns, df_types)
    190                 ] + (
    191                     [

/home/icexelloss/miniconda3/envs/spark-dev/lib/python3.5/site-packages/pyarrow/pandas_compat.py in get_column_metadata(column, name, arrow_type)
    125         raise TypeError(
    126             'Column name must be a string. Got column {} of type {}'.format(
--> 127                 name, type(name).__name__
    128             )
    129         )

TypeError: Column name must be a string. Got column 0 of type int64

Reporter: Li Jin / @icexelloss
Assignee: Wes McKinney / @wesm

_{Note: This issue was originally created as ARROW-1291. Please see the migration documentation for further details.}

[asfimport]

Wes McKinney / @wesm:
This is a known limitation because Arrow schemas must have all string field names. We might consider a default casting behavior (like stringifying non-string columns), since it's better than failing. We can always choose to persist the original column labels (pickled, if necessary) in the schema metadata

cc @cpcloud

[asfimport]

Li Jin / @icexelloss:
I think stringifying non-string columns is fine. Having metadata containing the original column labels sounds good but I feel it will likely to get lost somewhere because other systems, for instance, Spark SQL, does not support non-string column labels.

[asfimport]

Phillip Cloud / @cpcloud:
I'm -1 on allowing numeric column names since it adds an IMO unnecessary coupling to pandas semantics. With such a change, any tool that wants to read data out of an arrow array must now consider the origin of the data's column names, and cannot simply assume that the columns in the schema are always a simple list of strings. I don't think it's easy to make this behavior transparent to tools that use arrow, while OTOH a list of strings is easy to deal with in pretty much any system that arrow is a part of or will be a part of.

Since this is really only useful when doing pandas -> arrow -> pandas, and users of pandas can already refer to columns by positional index with .iloc I'm not convinced we should allow this.

I think adding metadata for indexes has less far-reaching effects because it's an optional feature of pandas that isn't a core part of arrow, while column names are non-negotiable.

I don't think it's too much to ask people to explicitly write out their column names as strings.

I am willing to be convinced though :)

[asfimport]

Li Jin / @icexelloss:
The use case I have is that I am passing a user provided pandas dataframe to Spark using Arrow. In my particular case, I don't care about the name of the column in the pandas DataFrame because the column names are defined in the Spark's schema, so it's weird to ask for people to write out their column names in pandas and just to throw them away later...

I think it's more friendly behavior that to cast numeric columns to string than to throw this exception. My use case is a bit special that I don't care about the column names, so I could do the casting in my code. But I think other user might also find the current behavior surprising.

I agree it's probably not worth it for arrow to preserve the numeric column names.

[asfimport]

Wes McKinney / @wesm:
How about we convert non-string column labels to strings for now and wait and see if it becomes a real need to preserve the original labels on the back? I think efforts beyond that may fall into the YAGNI category for the moment.

[asfimport]

Li Jin / @icexelloss:
+1

[asfimport]

Phillip Cloud / @cpcloud:
That could work, but then the round trip conversion is no longer exact.

It seems like the choice is "where should the surprise be?" or maybe "what's least surprising to users?" and that there are three options.

1. Leave the behavior as is, and users of arrow need to handle their own input columns before sending dataframes to arrow. This is the current behavior.
2. Add casting to strings in one direction, when the input is a dataframe with numeric columns. This gives IMO behavior that is more surprising than an error: when you call .to_pandas() you get back something different than what you put in. It's also not easy to tell that it's different by looking at the dataframe because of the way dataframes repr.
3. Add enough metadata in to preserve the current round trip behavior.

I favor #1 the most and #3 if we decide it really is necessary to allow numeric columns. With 3 we still lose some compatibility with other systems that want to read and write data that came from dataframes unless those systems want to handle integer columns.

I think #2 isn't a great option because it results in behavior in the public API that isn't obvious unless you know something about how both arrow and pandas work.

Additionally, we can't just call str on every column and be done, we have to make additional decisions like do we allow mixed string and integer column names? Though, maybe that's a red herring and we can just say "Int64Index s only" though we still have to make that decision as well.

[asfimport]

Wes McKinney / @wesm:
I'm more in favor of #2, mostly because renaming the columns on a DataFrame without destroying the original object will generally involve a memory doubling. You can assign to df.columns to avoid this, but df.rename(columns=str) will double memory

[asfimport]

Wes McKinney / @wesm:
PR: #911

[asfimport]

Li Jin / @icexelloss:
I think it's ok to not maintain "roundtrip exact conversion" between Arrow and other data representation. It's inevitable that other data representation has some exotic feature that Arrow cannot support, it's a little bit too strict in my opinion to error out in all cases. Just to provide another data point, (not saying this is correct, just for reference), Spark/Pandas conversion also casts int column names to string.

[asfimport]

Wes McKinney / @wesm:
Issue resolved by pull request 911
#911