Repository: pandas-dev/pandas
Pull request: ERR: Raise error in to_excel when saving datetimes with timezones
- [x] closes #27008
- [x] closes #7056
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

Raise a consistent error, regardless of the writing engine, when saving datetimes with timezones in Excel since they are unsupported.

Problem statement:
to_excel can't handle index with time zones
Hi

Something is wrong when trying to save a dataframe with tz-aware timestamps to xlsx, using pd.Dataframe.to_excel:

``` Python
df = pd.DataFrame([1], index=[pd.Timestamp('2014-05-02', tz='CET')])
df.to_excel('test.xlsx')
```

yields an exception on my system:

```
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-2-4e18a4be2a71> in <module>()
----> 1 df.to_excel('test.xlsx')

/home/silvio/prod34/lib/python3.4/site-packages/pandas/core/frame.py in to_excel(self, excel_writer, sheet_name, na_rep, float_format, cols, header, index, index_label, startrow, startcol, engine, merge_cells)
   1202         formatted_cells = formatter.get_formatted_cells()
   1203         excel_writer.write_cells(formatted_cells, sheet_name,
-> 1204                                  startrow=startrow, startcol=startcol)
   1205         if need_save:
   1206             excel_writer.save()

/home/silvio/prod34/lib/python3.4/site-packages/pandas/io/excel.py in write_cells(self, cells, sheet_name, startrow, startcol)
    771                 wks.write(startrow + cell.row,
    772                           startcol + cell.col,
--> 773                           cell.val, style)
    774 
    775     def _convert_to_style(self, style_dict, num_format_str=None):

/home/silvio/prod34/lib/python3.4/site-packages/xlsxwriter/worksheet.py in cell_wrapper(self, *args, **kwargs)
     55             if len(args):
     56                 int(args[0])
---> 57             return method(self, *args, **kwargs)
     58         except ValueError:
     59             # First arg isn't an int, convert to A1 notation.

/home/silvio/prod34/lib/python3.4/site-packages/xlsxwriter/worksheet.py in write(self, row, col, *args)
    374         # Write datetime objects.
    375         if isinstance(token, date_types):
--> 376             return self.write_datetime(row, col, *args)
    377 
    378         # Write number types.

/home/silvio/prod34/lib/python3.4/site-packages/xlsxwriter/worksheet.py in cell_wrapper(self, *args, **kwargs)
     55             if len(args):
     56                 int(args[0])
---> 57             return method(self, *args, **kwargs)
     58         except ValueError:
     59             # First arg isn't an int, convert to A1 notation.

/home/silvio/prod34/lib/python3.4/site-packages/xlsxwriter/worksheet.py in write_datetime(self, row, col, date, cell_format)
    666 
    667         # Convert datetime to an Excel date.
--> 668         number = self._convert_date_time(date)
    669 
    670         # Add the default date format.

/home/silvio/prod34/lib/python3.4/site-packages/xlsxwriter/worksheet.py in _convert_date_time(self, dt_obj)
   3265     def _convert_date_time(self, dt_obj):
   3266         # Convert a datetime object to an Excel serial date and time.
-> 3267         return datetime_to_excel_datetime(dt_obj, self.date_1904)
   3268 
   3269     def _options_changed(self):

/home/silvio/prod34/lib/python3.4/site-packages/xlsxwriter/utility.py in datetime_to_excel_datetime(dt_obj, date_1904)
    577 
    578     # Convert a Python datetime.datetime value to an Excel date number.
--> 579     delta = dt_obj - epoch
    580     excel_time = (delta.days
    581                   + (float(delta.seconds)

/home/silvio/prod34/lib/python3.4/site-packages/pandas/tslib.cpython-34m.so in pandas.tslib._Timestamp.__sub__ (pandas/tslib.c:11918)()

TypeError: can't subtract offset-naive and offset-aware datetimes
```

Using Python 3.4.0 on Arch Linux, list of installed packages:

```
~% pip freeze
Cython==0.20.1
Jinja2==2.7.2
MarkupSafe==0.21
Pygments==1.6
SQLAlchemy==0.9.4
XlsxWriter==0.5.3
ecdsa==0.11
ipdb==0.8
ipython==2.0.0
ipython-sql==0.3.1
lxml==3.3.5
matplotlib==1.3.1
mysql-connector-python==1.1.6
nose==1.3.1
numpy==1.8.1
openpyxl==1.8.5
pandas==0.13.1
paramiko==1.13.0
patsy==0.2.1
prettytable==0.7.2
psycopg2==2.5.2
pycrypto==2.6.1
pyodbc==3.0.7
pyparsing==2.0.2
python-dateutil==2.2
pytz==2014.2
requests==2.2.1
scipy==0.13.3
six==1.6.1
sqlparse==0.1.11
statsmodels==0.6.0
tornado==3.2
xlrd==0.9.3
```


to_excel Mishandles Mixing of tz-aware datetimes
#### Code Sample, a copy-pastable example if possible

```
In [14]: import pandas as pd
In [14]: ew = pd.ExcelWriter('test.xlsx')

In [15]: df = pd.DataFrame([pd.to_datetime('2019-06-22 01:11'), pd.to_datetime('2019-06-22 01:11
    ...: Z'), pd.to_datetime('2019-06-22 01:11MSK')])

In [16]: df
Out[16]:
                           0
0        2019-06-22 01:11:00
1  2019-06-22 01:11:00+00:00
2  2019-06-22 01:11:00+03:00

In [17]: df.to_excel(ew, sheet_name='sh1')

In [18]: ew.save()

In [19]: ew.close()

```
(MSK is +03:00)

#### Problem description

ExcelWriter seems to incorrectly convert timestamps with timezones. Time part seems to be converted correctly, however date part looks like it was unchanged.

#### Expected excel sheet content
0 | 2019-06-22 01:11:00
1 | 2019-06-22 01:11:00
2 | 2019-06-21 22:11:00

#### Actual excel sheet content
0 | 2019-06-22 01:11:00
1 | 2019-06-22 01:11:00
2 | 2019-06-**22** 22:11:00

pandas is 0.24.2

Hints from issue discussion:
cc @jmcnamara 

does excel support tz's in dates even?

not sure what this should do

> does excel support tz's in dates even?

No. Excel doesn't have any support for timezones.

I don't really know how this should be handled. 

ok....maybe should just report a better error for now

@sboehler what do you think it should do?

I'd expect either localized or UTC timestamps (both without tz information)
to be written to the file. Personally I vote for localized timestamps, as
the user by localizing his series explicitly opted for a certain timezone,
and this should be respected. If one wanted UTC output, a simple
tz_localize of the data before writing out solved the issue.

Agree?

2014-05-06 18:36 GMT+02:00 jreback notifications@github.com:

> ok....maybe should just report a better error for now
> 
> @sboehler https://github.com/sboehler what do you think it should do?
> 
> —
> Reply to this email directly or view it on GitHubhttps://github.com/pydata/pandas/issues/7056#issuecomment-42326246
> .

that makes sense, so choices I think are:
- raise with helpful message (unambiguous)
- provide a `convert_timezones='local'|'utc'` keyword; works but is 'silent', and could be ambiguous if you have multiple-columns/indicies that may need conversion
- could do a local translation and add another column with the tz

or:
- local translation without tz-information, relying on Excel users to know what they're doing (which is generally assumed, given Excel's poor support for date/time handling). If Excel is ever tz-aware, the obvious migration is to add back the tz information.

The global "convert_timezones" argument is problematic with several columns of timestamps - a user might want to output multiple columns with local timestamps and UTC timestamps to address Excels lack of tz handling.

ok...

@sboehler so this is pretty straightfoward....PR?

I just noticed, the bug does not occur when I uninstall xlsxwriter! Pandas must be internally choosing the xlsx engine, depending on what's available in terms of packages, and use something else if xlsxwriter is not present. 

The default seems to be to output the localized timestamp, omitting the timezone info.

@jreback As you can see from my github profile I have no experience with developing pandas, but I am happy to give it a go. This could take a few days, however...

you can specify `engine='openpyxl'` to get the same behavior (yes , the default is `xlsxwriter` as its much faster)

give it a go and let us know any issues

from #7060 

```
That sounds right, but then the error message is not informative and it should still be possible to   
output the dates as a text string with tz offset, the same way they appear when they output to the 
terminal. MIgrating pandas types into excel types when outputting is nice but it's not a requirement.

It's not only a xlsxwriter problem actually, xlwt and xlsxwriter fail with this error and openpyxl 
creates the file but throws away the timezone information so it actually silently corrupts the data. 
This is a little broken all around.
```

I _think_ this is happening because you are mixing tz-aware and tz-naive timestamps. This seems to work:

```python
df = pd.DataFrame([pd.to_datetime('2019-06-22 01:11Z'), pd.to_datetime('2019-06-22 01:11Z'), pd.to_datetime('2019-06-22 01:11:00+03')])
df.to_excel('foo.xlsx')
```

If you'd like to take a look would certainly take a PR
Unfortunately no. 

`pd.DataFrame([pd.to_datetime('2019-06-22 01:11MSK')])`

this one, with timezone, also gives incorrect result - 2019-06-22 22:11:00.
Hmm OK. Interestingly enough if you use the xlsxwriter this will fail:

```python
>>> df.to_excel('~/Desktop/foo.xlsx', engine='xlsxwriter')
TypeError: Excel doesn't support timezones in datetimes. Set the tzinfo in the datetime/time object to None or use the 'remove_timezone' Workbook() option
```

Is your expectation to just have everything converted to UTC?
Initially I wanted it in my local timezone so it seems that the solution would be to remove tz information so that no conversion takes place.
> Initially I wanted it in my local timezone so it seems that the solution would be to remove tz information so that no conversion takes place.

The catch is that in your original example not all Timestamps have a timezone, so conversion to a local timezone wouldn't be entirely applicable.

@mroeschke do we have a standard way of dealing with IO conversions for timestamps when timezone information isn't supported?

For SQL, we export `datetime64[tz, ns]` as naive local time for databases that don't support timezones (I cannot confidently say this is the rule across all IO formats). For the mixed use case in the issue, I agree that conversion to local would result in loss of information and that's not ideal. I would say exporting as text/string is almost the best option in that edge case.

xref Another excel timezone export issue: https://github.com/pandas-dev/pandas/issues/7056
xref Generally, parsing non-pytz timezone abbreviations (e.g. MSK) is not fully supported: https://github.com/pandas-dev/pandas/issues/18702
Just to clarify - the problem reproduces not only in the mixed use case I've mentioned initially. Just one cell with tz-aware datetime produces incorrect result.
Thanks for the ref @mroeschke . Per this comment https://github.com/pandas-dev/pandas/issues/7056#issuecomment-42335435 I think this should raise

@shashurup what do you think about that? The problem here is that the intention is rather ambiguous so by raising it would at least indicate that to the end user that further steps would need to be taken to make the intention clearer
I agree that the intention must be clarified, i.e. the timezone we want Excel to display must be specified somehow.
However, first the incorrect timezone conversion must be fixed
2019-06-22 01:11MSK  != 2019-06-22 22:11:00UTC
the correct UTC conversion is 2019-06-**21** 22:11:00UTC  !!!!!
Note, the date you are passing gets dispatched to dateutil, and while pandas's current minimum dateutil version supports parsing `MSK` as a timezone (though dateutil just drops the timezone), this behavior is getting deprecated:

```
In [5]: pd.DataFrame([pd.to_datetime('2019-06-22 01:11MSK')])
   ...:
   ...:
/anaconda3/envs/pandas-dev/lib/python3.7/site-packages/dateutil/parser/_parser.py:1206: UnknownTimezoneWarning: tzname MSK identified but not understood.  Pass `tzinfos` argument in order to correctly return a timezone-aware datetime.  In a future version, this will raise an exception.
  category=UnknownTimezoneWarning)
Out[5]:
                    0
0 2019-06-22 01:11:00

# A more recent dateutil version
In [6]: dateutil.__version__
Out[6]: '2.8.0'
```

Commit message: Remove unnecessary setting of engine

Patch to review:
diff --git a/doc/source/whatsnew/v0.25.0.rst b/doc/source/whatsnew/v0.25.0.rst
index 008f6f0b8643e..bddf02e29110c 100644
--- a/doc/source/whatsnew/v0.25.0.rst
+++ b/doc/source/whatsnew/v0.25.0.rst
@@ -561,6 +561,7 @@ Other API changes
 - The ``.str``-accessor has been disabled for 1-level :class:`MultiIndex`, use :meth:`MultiIndex.to_flat_index` if necessary (:issue:`23679`)
 - Removed support of gtk package for clipboards (:issue:`26563`)
 - Using an unsupported version of Beautiful Soup 4 will now raise an ``ImportError`` instead of a ``ValueError`` (:issue:`27063`)
+- :meth:`Series.to_excel` and :meth:`DataFrame.to_excel` will now raise a ``ValueError`` when saving timezone aware data. (:issue:`27008`, :issue:`7056`)
 
 .. _whatsnew_0250.deprecations:
 
diff --git a/pandas/io/formats/excel.py b/pandas/io/formats/excel.py
index 5792f6e2a5a08..07de3db181a54 100644
--- a/pandas/io/formats/excel.py
+++ b/pandas/io/formats/excel.py
@@ -402,6 +402,10 @@ def _format_value(self, val):
                 val = '-{inf}'.format(inf=self.inf_rep)
             elif self.float_format is not None:
                 val = float(self.float_format % val)
+        if getattr(val, 'tzinfo', None) is not None:
+            raise ValueError('Excel does not support datetimes with timzones. '
+                             'Please ensure that datetimes are timezone '
+                             'unaware before writing to Excel.')
         return val
 
     def _format_header_mi(self):
diff --git a/pandas/tests/io/excel/test_writers.py b/pandas/tests/io/excel/test_writers.py
index a4fdcdf70a3ea..8f20136f1ea4b 100644
--- a/pandas/tests/io/excel/test_writers.py
+++ b/pandas/tests/io/excel/test_writers.py
@@ -1178,6 +1178,21 @@ def test_merged_cell_custom_objects(self, engine, merge_cells, ext):
         expected.index = expected.index.astype(np.float64)
         tm.assert_frame_equal(expected, result)
 
+    @pytest.mark.parametrize('dtype', [None, object])
+    def test_raise_when_saving_timezones(self, engine, ext, dtype,
+                                         tz_aware_fixture):
+        # GH 27008, GH 7056
+        tz = tz_aware_fixture
+        data = pd.Timestamp('2019', tz=tz)
+        df = DataFrame([data], dtype=dtype)
+        with pytest.raises(ValueError, match="Excel does not support"):
+            df.to_excel(self.path)
+
+        data = data.to_pydatetime()
+        df = DataFrame([data], dtype=dtype)
+        with pytest.raises(ValueError, match="Excel does not support"):
+            df.to_excel(self.path)
+
 
 class TestExcelWriterEngineTests:
 


Code review comments:
diff_hunk: @@ -402,6 +402,10 @@ def _format_value(self, val):
                 val = '-{inf}'.format(inf=self.inf_rep)
             elif self.float_format is not None:
                 val = float(self.float_format % val)
+        if getattr(val, 'tzinfo', None) is not None:
+            raise ValueError('Excel does not support datetimes with timzones. '
path: pandas/io/formats/excel.py
text: @user1:
small typo here for timezones if you want to fix locally (otherwise can do before merge)