df.to_stata fails when a column of type object contains only None

[kylebarron]

Code Sample, a copy-pastable example if possible

import pandas as pd
df = pd.DataFrame({'a': ['a', None]})
df.to_stata('test.dta')
df = pd.DataFrame({'a': [None, 'a']})
df.to_stata('test.dta')
df = pd.DataFrame({'a': [None, None]})
df.to_stata('test.dta')
# ValueError: Writing general object arrays is not supported

Problem description

The df.to_stata() method writes columns containing None without error when there is at least one string value in the column, but fails if the column contains only None. It's unclear what data type to write a column of None as, so maybe that's why this isn't supported? I would propose that a column with values of only None be written as str1 with empty strings.

I came across this error because I read in a Parquet file with pd.read_parquet() and was unable to write the file to Stata format. In the Parquet schema, the column had type BYTE_ARRAY UTF8, but since the column had only missing values, it was read into Pandas as only None.

Expected Output

Stata file written to disk with missing values for the column with None.

Output of pd.show_versions()

Details

INSTALLED VERSIONS
------------------
commit: None
python: 3.6.4.final.0
python-bits: 64
OS: Linux
OS-release: 4.15.0-38-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.4
pytest: 3.3.2
pip: 18.0
setuptools: 40.0.0
Cython: 0.28.3
numpy: 1.14.2
scipy: 1.0.0
pyarrow: 0.11.1
xarray: None
IPython: 6.5.0
sphinx: 1.6.6
patsy: 0.5.0
dateutil: 2.7.3
pytz: 2017.3
blosc: None
bottleneck: 1.2.1
tables: 3.4.2
numexpr: 2.6.4
feather: None
matplotlib: 2.1.2
openpyxl: 2.4.10
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: 1.0.2
lxml: 4.1.1
bs4: 4.6.3
html5lib: 1.0.1
sqlalchemy: 1.1.14
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.10
s3fs: None
fastparquet: 0.1.5
pandas_gbq: None
pandas_datareader: None

[WillAyd]

Thanks for the report. Investigation and PRs are always welcome!

[bashtage]

What should it write? Empty strings, Nans (with with type, integers will the missing keystone value, or ['None', 'None']? This is an ambiguous case and the best solution is to raise with an exception. TBH the case with ['a',None] should also probably raise and so that columns containing only strings should be written as string columns.

[kylebarron]

My point was that None is already being coerced to an empty string whenever there is at least one string value in the column. So I didn't think it would be too much of a stretch to coerce None to an empty string when there are no strings in the column.

In [1]: import pandas as pd
   ...: df = pd.DataFrame({'a': ['a', None]})
   ...: df.to_stata('test.dta')
   ...: pd.read_stata('test.dta')
   ...:

Out[1]:
   index  a
0      0  a
1      1

[WillAyd]

I'm not familiar with stata but based off of the examples I agree with @bashtage here - I think it should raise rather than silently coerce for you. Raising would be an explicit sign to the user that they may want to replace or fill values before export, especially since its not a lossless round trip

[jtkiley]

It seems like there are two sub-issues being discussed:

1. What should happen to a column of None? I'm not sure, but Stata reads an empty csv column as a byte:

2. What should happen to a column of strings containing one or more Nones? In this case, I like the coercing behavior (see StataWriter for version 117 fails on None in a string column long enough to be a Stata StrL. #23633). Part of that is that I have a bunch of code in projects that relies on it, though I also think intent is a little clearer when the rest of the column is strings. I have a lot of datasets with text columns in them that tends to be sparse (e.g., correction text for newspaper articles, where corrections are decently rare overall), and they have a lot of Nones. I don't recall whether my parsing code does that directly or whether they're populated as None (cf. '') in some other way.

I'm also somewhat split on warning about it. On the one hand, it would be clearer for users. On the other, if you have a lot of code that relies on this, you'll get some warning fatigue. Another option is adding a parameter, but I wonder how often coercing is actually the wrong behavior.

[bashtage]

It is tempting to not change the current behavior vis-a-vis mixed string and None. This would require a deprecation cycle, and while more explicit isn't likely to be an issue for users who use Stata and know that a column with a string must be a string.

The correct behavior in a column with Nones and dtype 'object' is less well defined IMO. FWIW this is why it fails here:

inferred_dtype = infer_dtype(column.dropna())

This drops na-like values, including None and so with mixed this infers string but when all None it infers
empty, which is correct.

One issue that afects the ambiguity here is that both datetime and string arrays can be object and are supported. Converting all None to strings is making a choice about the column type from these two candidates.

[jtkiley]

That makes sense to me about the mixed None and strings.

The all None case does seem more difficult. For this particular issue, I wonder if the better change is changing pd.read_parquet() to make a fully missing column of BYTE_ARRAY UTF8 into empty strings. That doesn't solve this issue directly, but it would address one case that leads into all None columns, and it has a clue to intent. Note that those internals are probably beyond my skillset, so if this suggestion doesn't make sense, that would be why.

Beyond that, maybe the error text could be better. I'd like to know that (a) the more descriptive reason is that it's all None, and (b) the two easiest solutions are to (i) drop the column or (ii) fill one or more rows with either values or type-specific missingness (i.e. '' or NaN).

[kylebarron]

@jtkiley I looked into the Parquet details more and it turns out that a string of length 0 and a null string are two different values. So they map exactly to None and '' objects in Pandas.

It appears to me that the core issue is that Stata doesn't have a missing value for strings. In Pandas, '' is a string of length 0, not a missing value; None is the missing value for strings. This explains why Series.isna() returns False for the former and True for the latter. For that reason, I think I now agree with @bashtage that a column of None should not be automatically coerced to an empty string, because that's akin to replace null values with values of length zero.

[kylebarron]

I linked a PR with a proposed changed error message that

• mentions the issue is that the object column is non-string
• gives the name of the offending column