BUG: unsupported type Interval when writing dataframe to excel by cbertinato · Pull Request #19244 · pandas-dev/pandas

cbertinato · 2018-01-15T04:08:46Z

closes df.to_excel() error.Unsupported type <type 'pandas._libs.interval.Interval'> in write() #19242
closes to_excel with engine='xlwt' doesn't support pandas.tslib.Timedelta #9155
whatsnew entry

codecov · 2018-01-15T06:03:32Z

Codecov Report

Merging #19244 into master will not change coverage.
The diff coverage is n/a.

@@           Coverage Diff           @@
##           master   #19244   +/-   ##
=======================================
  Coverage   91.65%   91.65%           
=======================================
  Files         150      150           
  Lines       48724    48724           
=======================================
  Hits        44658    44658           
  Misses       4066     4066

Flag	Coverage Δ
#multiple	`90.02% <ø> (ø)`	⬆️
#single	`41.74% <ø> (ø)`	⬆️

Continue to review full report at Codecov.

Legend - Click here to learn more
Δ = absolute <relative> (impact), ø = not affected, ? = missing data
Powered by Codecov. Last update 5f6c80b...8859dc5. Read the comment docs.

jreback · 2018-01-15T13:31:35Z

doc/source/whatsnew/v0.23.0.txt

 ^^^^^

 - Improved error message when attempting to use a Python keyword as an identifier in a ``numexpr`` backed query (:issue:`18221`)
+- Bug in :func:`DataFrame.to_excel` where an exception was raised indicating unsupported type when writing a data frame containing a column of Interval type (:issue:`19242`)


move to IO. just say unsupported type

jreback · 2018-01-15T13:34:29Z

pandas/io/excel.py

@@ -788,6 +789,8 @@ def _conv_value(val):
        val = "{val}".format(val=val)
    elif is_list_like(val):


this routine needs some re-factoring, something like

# doc-string here if is_integer(val): val = int(val) elif is_float(val): val = float(val) elif is_bool(val): val = bool(val) elif isinstance(val, (datetime, timedelta)): pass else: # comment here val = str(val) return val

with some additional tests

This raises another issue. I take your meaning here since datetime and date are treated later on in write_cells, but timedelta is not explicitly dealt with. Turns out that writing timedelta is not supported when exporting .xls. Looks like this is an old issue (#9155).

I suggest making the same conversion that xlsxwriter makes in the appropriate write_cells.

@cbertinato yes this function actually could be moved to the top-level.

pep8speaks · 2018-01-16T20:36:35Z

Hello @cbertinato! Thanks for updating the PR.

Cheers ! There are no PEP8 issues in this Pull Request. 🍻

Comment last updated on January 27, 2018 at 02:19 Hours UTC

jreback · 2018-01-17T00:05:32Z

doc/source/whatsnew/v0.23.0.txt

 - Bug in :func:`read_sas` where a file with 0 variables gave an ``AttributeError`` incorrectly. Now it gives an ``EmptyDataError`` (:issue:`18184`)
 - Bug in :func:`DataFrame.to_latex()` where pairs of braces meant to serve as invisible placeholders were escaped (:issue:`18667`)
 - Bug in :func:`read_json` where large numeric values were causing an ``OverflowError`` (:issue:`18842`)
+- Type ``Interval`` now supported in :func:`DataFrame.to_excel` for all Excel file types (:issue:`19242`)


use :class:`Interval` , and :class`Timdelta` (this is more user friendly as you almost always have a Timedelta)

jreback · 2018-01-17T00:05:42Z

pandas/io/excel.py

                              get_filepath_or_buffer, _NA_VALUES,
                              _stringify_path)
-from pandas.core.indexes.period import Period
+# from pandas.core.indexes.period import Period


Import no longer necessary because the elif on line 787 was folded into the else. I'll remove it instead of commenting.

jreback · 2018-01-17T00:06:42Z

pandas/io/excel.py

                num_format_str = self.date_format

+            if isinstance(cell.val, timedelta):
+                delta = cell.val


just use delta.total_seconds()

this should be an elif

jreback · 2018-01-17T00:07:10Z

pandas/tests/io/test_excel.py

                # to use df_expected to check the result
                tm.assert_frame_equal(rs2, df_expected)

+    # GH19242 - test writing Interval without labels


comments go inside the function

jreback · 2018-01-17T00:08:52Z

pandas/tests/io/test_excel.py

+            rand = np.random.randint(-10, 10, size=(20, 2))
+            frame = DataFrame(rand)
+            intervals = pd.cut(frame[0], 10)
+            frame['new'] = intervals


can you make this a little less verbose
use expected for the comparison variable

gfyoung · 2018-01-24T10:28:46Z

pandas/io/excel.py

+    """ Convert numpy types to Python types for the Excel writers.
+        :obj:`datetime` and :obj:`date` formatting must be handled in the
+        writer. :obj:`str` representation is returned for all other types.
+    """


Write an expanded docstring with Parameters and Returns section if you're going to do this.

jreback · 2018-01-24T11:22:54Z

small doc string comments, and pls rebase. ping on green.

cbertinato · 2018-01-26T15:04:33Z

Looks like the AppVeyor failures are in the parquet tests. Are those coming from the rebase?

jreback · 2018-01-27T01:15:16Z

can you rebase.

jreback · 2018-01-27T01:15:53Z

can you rebase. and ping on green.

cbertinato · 2018-01-27T15:01:43Z

All green!

jreback · 2018-01-27T16:56:50Z

thanks @cbertinato ! keep em coming!

jreback requested changes Jan 15, 2018

View reviewed changes

jreback added Dtype Conversions Unexpected or buggy dtype conversions IO Excel read_excel, to_excel labels Jan 15, 2018

jreback requested changes Jan 17, 2018

View reviewed changes

gfyoung reviewed Jan 24, 2018

View reviewed changes

jreback added this to the 0.23.0 milestone Jan 24, 2018

cbertinato force-pushed the issue-19242 branch 2 times, most recently from 1464ec0 to 9b89a6e Compare January 26, 2018 01:26

jreback approved these changes Jan 27, 2018

View reviewed changes

BUG: Interval and Timedelta unsupported when writing DataFrame to excel

8859dc5

cbertinato force-pushed the issue-19242 branch from 9b89a6e to 8859dc5 Compare January 27, 2018 02:19

jreback merged commit 24d9509 into pandas-dev:master Jan 27, 2018

WillAyd mentioned this pull request Feb 27, 2018

Consistent Timedelta Writing for all Excel Engines #19921

Merged

4 tasks

WillAyd mentioned this pull request Jun 22, 2019

to_excel() TyperError: Unsupported type <class 'pandas._libs.tslibs.period.Period'> in write() #26999

Closed

		@@ -788,6 +789,8 @@ def _conv_value(val):
		val = "{val}".format(val=val)
		elif is_list_like(val):

Uh oh!

Conversation

cbertinato commented Jan 15, 2018 • edited by jreback Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

codecov bot commented Jan 15, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

cbertinato Jan 15, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

pep8speaks commented Jan 16, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Comment last updated on January 27, 2018 at 02:19 Hours UTC

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

jreback commented Jan 24, 2018

Uh oh!

cbertinato commented Jan 26, 2018

Uh oh!

jreback commented Jan 27, 2018

Uh oh!

jreback commented Jan 27, 2018

Uh oh!

cbertinato commented Jan 27, 2018

Uh oh!

jreback commented Jan 27, 2018

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

cbertinato commented Jan 15, 2018 •

edited by jreback

Loading

codecov bot commented Jan 15, 2018 •

edited

Loading

cbertinato Jan 15, 2018 •

edited

Loading

pep8speaks commented Jan 16, 2018 •

edited

Loading