Pandas: BUG: DataFrame.append fails to append tz-aware column to empty DataFrame

[ajenkins-cargometrics]

This is something that works in pandas 0.16.2 but fails in pandas 0.18. If you append to an empty DataFrame, and the DataFrame you are appending contains a tz-aware datetime column, the append fails.

import pandas

df = pandas.DataFrame(columns=['dates'])

df_to_append = pandas.DataFrame({'dates': pandas.date_range('2015', periods=3, tz='utc')})

# Fails here
df.append(df_to_append)

Here is the failure I get:

Traceback (most recent call last):
  File "/Users/ajenkins/Library/Preferences/PyCharm50/scratches/test.py", line 8, in <module>
    df.append(df_to_append)
  File "/Users/ajenkins/dev/pandas/pandas/core/frame.py", line 4334, in append
    verify_integrity=verify_integrity)
  File "/Users/ajenkins/dev/pandas/pandas/tools/merge.py", line 846, in concat
    return op.get_result()
  File "/Users/ajenkins/dev/pandas/pandas/tools/merge.py", line 1038, in get_result
    copy=self.copy)
  File "/Users/ajenkins/dev/pandas/pandas/core/internals.py", line 4545, in concatenate_block_managers
    for placement, join_units in concat_plan]
  File "/Users/ajenkins/dev/pandas/pandas/core/internals.py", line 4642, in concatenate_join_units
    for ju in join_units]
  File "/Users/ajenkins/dev/pandas/pandas/core/internals.py", line 4915, in get_reindexed_values
    missing_arr = np.empty(self.shape, dtype=empty_dtype)
TypeError: data type not understood

The error is because pandas is passing an instance of DatetimeTZDtype to numpy, and numpy doesn't recognize it as a valid dtype.

output of pd.show_versions()

INSTALLED VERSIONS
------------------
commit: 13a07055dedb0f1d06abf30b185a289220ce5c85
python: 2.7.9.final.0
python-bits: 64
OS: Darwin
OS-release: 15.4.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8

pandas: 0.18.0+154.g13a0705.dirty
nose: 1.3.7
pip: 8.1.1
setuptools: 20.9.0
Cython: 0.24
numpy: 1.11.0
scipy: 0.15.1
statsmodels: None
xarray: None
IPython: 3.1.0
sphinx: None
patsy: None
dateutil: 2.5.3
pytz: 2016.4
blosc: None
bottleneck: None
tables: None
numexpr: None
matplotlib: 1.4.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: None
httplib2: None
apiclient: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
boto: None
pandas_datareader: None

[jreback]

hmm, thought we fixed all of the concat issues.

[ajenkins-cargometrics]

I'm able to make this problem go away with this patch:

diff --git a/pandas/core/internals.py b/pandas/core/internals.py
index abfc5c9..fa2f1e3 100644
--- a/pandas/core/internals.py
+++ b/pandas/core/internals.py
@@ -4593,6 +4593,9 @@ def get_empty_dtype_and_na(join_units):
         else:
             upcast_cls = 'float'

+        if com.is_extension_type(dtype):
+            dtype = dtype.base
+
         # Null blocks should not influence upcast class selection, unless there
         # are only null blocks, when same upcasting rules must be applied to
         # null upcast classes.

I'd submit a PR but I'm not sure if that works the way the get_empty_dtype_and_na function is intended. The only place way I see it being used is to compute a dtype to pass to numpy.empty.

[jreback]

this patch may solve a couple of issues

[jorisvandenbossche]

@ajenkins-cargometrics Do you want to do a PR for this patch?

[grutts]

If you are happy with this patch I can make a PR with tests tonight? We're very keen to see this in 19 if it's not too late.

[jreback]

if u want to out up a PR with tests great

[ajenkins-cargometrics]

Hi, sorry for ignoring this for a bit. The reason I hadn't submitted a PR is because it turns out my simple fix doesn't really work correctly. I wrote a test for it, which you can see here:

https://github.com/pydata/pandas/compare/master...ajenkins-cargometrics:GH12985?expand=1

which fails because the dtypes of the columns end up changing when appending to an empty DataFrame. I tried changing the test to pass check_dtype=False to assert_frame_equal, and then the test fails because the timezone gets stripped from the appended datetime column, resulting in a "Cannot compare tz-naive and tz-aware timestamps" error.

It seems that a more complicated fix is necessary to preserve custom dtypes when appending to an empty DataFrame. It wasn't obvious to me where to make this fix, and unfortunately I don't have a lot of time right now to work on it.

[grutts]

This wasn't obvious to me, but it doesn't just fail for empty dataframes.

import pandas as pd

df1 = pd.DataFrame({'a': pd.Categorical(['a', 'b', 'c']), 'b': [pd.NaT]*3})
df2 = pd.DataFrame({'a': pd.Categorical(['a', 'b', 'a', 'c']), 'b': pd.date_range('2015', periods=4, tz='utc')})
df1.append(df2)

I've tried, but not found, ways to preserve the extended dtype on the empty columns; probably because these internals are quite new to me. Would it be unacceptable to introduce ajenkins-cargometrics' change, which might convert 'b' above to object but at least not break or reduce any information? IMO it would be an improvement.

[jreback]

you can try to introduce that fix and see what happens (need several addtl tests)

[sidverm]

I just faced this problem. Is this bug open for almost a year since reported?

[jreback]

dupe of #12396

[willgdjones]

I have also just encountered this bug.