Repository: pandas-dev/pandas
Pull request: Class to read OpenDocument Tables
closes #2311 

This is primarily intended for LibreOffice calc spreadsheets but will
also work with LO Writer and probably with LO Impress documents.

This is an alternate solution to https://github.com/pandas-dev/pandas/pull/9070
There are test cases with several different problematic LibreOffice spread sheets.
git diff upstream/master -u | flake8 appeared to pass.

... I didn't do the whats new entry. Though I expect there's some more work to do before submitting this. I just wanted to get the core code in for comments.

The open issues is, the workaround for https://github.com/pandas-dev/pandas/issues/25422 is embedded in the current code (so all my tests pass right now) but that, or a better solution should move closer to the iso 8601 parser.

Also I don't have the parser class hooked up to a read_excel or read_ods function.

Using read_excel bothers me some... because its not an excel file. I think it would be more clear if there was either a separate read_ods function or a generic read_spreadsheet function than just pressing read_excel into being a generic spreadsheet reader.

Also this just a reader. I had never gotten around to implementing a writer.


Problem statement:
Wish: Input / output for Open Document Spreadsheet (ODS)
It would be nice to have, along with XLS and XLSX, also support for ODS (used notably by OpenOffice.org and Libreoffice, but not only that). 

The main problem I saw is that there's a plethora of libraries to handle that: however not sure which is the "best" one (with a licensing that would fit with pandas).

Hints from issue discussion:
It seems to me the best option for this would be:
- Start with a simple test case for input and output, and handle that using this library: http://simple-odspy.sourceforge.net/ -- sample code: http://simple-odspy.sourceforge.net/?q=node/5
- If there are more complex cases where simple-odspy is not enough, one can use the more heavyweight http://pypi.python.org/pypi/odfpy library

This would be a rather substantial bit of work.

A good start for the test cases would be to look at pandas/io/tests/test_excel.py and write test cases that mirror what is in that file.

+1 for this feature.

Working on an open-source data wrangling project and I would like to be able to interact with spreadsheet software that is not Excel.

@H0R5E for now, you could just convert the resulting excel file to ODS (it's pretty much a simple set of numbers, nothing more): http://stackoverflow.com/questions/15257032/python-convert-excel-file-xls-or-xlsx-to-from-ods or you could use json as an intermediary and use `tablib` to export to ODS: https://pypi.python.org/pypi/tablib

+1 for this!

I want to tune in with some additional notes:
- [odfpy](https://github.com/eea/odfpy) is now living on Github, and seems to get some attention now and then.
- I bumped into [ezodf](https://github.com/T0ha/ezodf), which, contrary to simple-odspy, seems to be still under development. It does not depend on odfpy. The docs are [here](http://packages.python.org/ezodf), and some examples [here](https://github.com/T0ha/ezodf/tree/master/examples).

Based on some initial looks, ezodf has a straight forward interface. For example, loading an ods spreadsheet into a DataFrame could go as follows:

``` python
import pandas as pd
import ezodf
doc = ezodf.opendoc('somefile.ods')

print("Spreadsheet contains %d sheets.\n" % len(doc.sheets))
for sheet in doc.sheets:
    if sheet.name == 'data':
        data = sheet
    print("Sheet name: '%s'" % sheet.name)
    print("Size of Sheet : (rows=%d, cols=%d)" % (sheet.nrows(), sheet.ncols()) )
    print("-"*40)

# a random cell
pos = (row, col)
data.get_cell(pos).display_form

df_dict = {}
for i, col in enumerate(data.columns()):
    # col is a list that contains all the cells (rows of that column)
    # assume the column name is on the first row
    colname = col[0].display_form
    df_dict[colname] = []
    print(colname)
    for j, row in enumerate(col):
        # ignore the header
        if j == 0:
            continue
        else:
            df_dict[col_name].append(row.display_form)
# and convert to a DataFrame
df = pd.DataFrame(df_dict)
```

I have no idea how odfpy and ezodf compare performance wise, or how their different memory footprints are for very large spreadsheets. I haven't tried to use odfpy because that seems more complicated to get going with. I also have not checked how robust the writing process of ezodf is. I'll try to see if I can get something similar going as for pandas/io/tests/test_excel.py. 

I will also mention @sorenroug from odfpy and @T0ha from ezodf just in case someone from upstream is interested in this discussion.

After a short exploration, it seems doable to create an ods prototype reader based on the structure of pandas/io/excel.py using the ezodf module. I've forked pandas [here](https://github.com/davidovitch/pandas) and will implement an ods prototype reader in the io-ods branch. 

@davidovitch - one thing to keep in mind if you choose to handle both writing and reading: you can register an ODS writer in the excel module (just implementing the required methods as listed in the code) and then you can just add a simple subclass of the excel writer tests and get a ton of test cases for free. Just a thought.

I would like to give an update on the current status. My [fork](https://github.com/davidovitch/pandas) can currently read ODF ODS files, but writing is not implemented yet. The corresponding pull request is still pending review, and it has been a lot more work than I originally anticipated.

Just a few days ago I bumped into yet another library to read/write spreadsheet: [pyexcel](https://github.com/chfw/pyexcel). What is different about this library is that it aims at creating a single API for all the different read/write libraries, and it builds on all the existing libraries out there (ezodf, xlrd, etc). This is actually kind of similar to what pandas currently has, and to what I am trying to extend in PR #9070. So I am wondering, would it make sense to use a single API library (with optional dependencies to all the relevant readers/writers for the different spreadsheet types) instead of developing something similar, but tailored to pandas use case? I am speculating that a lot of code in io/excel.py could be removed when relying on pyexcel, but it adds yet another dependency. At this stage I am not sure how all different edge cases on data types and other magic happening inside the spreadsheet interpretation differs between the current pandas implementation and pyexcel. 

The ods reader is not ready for the upcoming 0.17 release. PR #9070 is closed (see the PR for a technical discussion), and a new improved PR will be made by someone at some point in the future. I have a working version in the [ezodf_reader](https://github.com/davidovitch/pandas/tree/ezodf_reader) branch of my pandas fork in case someone wants to have a look at it. Suggestions and improvements are welcome :-)

@davidovitch thanks for your efforts! Its a shame that that reading and writing to ODF is not better supported in python, otherwise I'm sure this would be less of an ordeal.

Commit message: Remove leading underscore from ODFParser

Patch to review:
diff --git a/ci/deps/travis-36-cov.yaml b/ci/deps/travis-36-cov.yaml
index 606b5bac8306d..69d4a499e41ad 100644
--- a/ci/deps/travis-36-cov.yaml
+++ b/ci/deps/travis-36-cov.yaml
@@ -15,6 +15,7 @@ dependencies:
   - nomkl
   - numexpr
   - numpy=1.15.*
+  - odfpy
   - openpyxl
   - pandas-gbq
   # https://github.com/pydata/pandas-gbq/issues/271
diff --git a/doc/source/whatsnew/v0.25.0.rst b/doc/source/whatsnew/v0.25.0.rst
index 9db8bef8debcd..7d09e44128deb 100644
--- a/doc/source/whatsnew/v0.25.0.rst
+++ b/doc/source/whatsnew/v0.25.0.rst
@@ -41,6 +41,7 @@ Other Enhancements
 - :meth:`DataFrame.query` and :meth:`DataFrame.eval` now supports quoting column names with backticks to refer to names with spaces (:issue:`6508`)
 - :func:`merge_asof` now gives a more clear error message when merge keys are categoricals that are not equal (:issue:`26136`)
 - :meth:`pandas.core.window.Rolling` supports exponential (or Poisson) window type (:issue:`21303`)
+- :func:`pandas.io.excel.read_excel` supports reading OpenDocument tables. Specify engine='odf' to enable. (:issue:`9070`)
 
 .. _whatsnew_0250.api_breaking:
 
diff --git a/pandas/io/excel/_base.py b/pandas/io/excel/_base.py
index c0678575fd6f0..8c245743f146f 100644
--- a/pandas/io/excel/_base.py
+++ b/pandas/io/excel/_base.py
@@ -780,9 +780,11 @@ class ExcelFile:
     """
 
     from pandas.io.excel._xlrd import _XlrdReader
+    from pandas.io.excel._odfreader import ODFReader
 
     _engines = {
         'xlrd': _XlrdReader,
+        'odf': ODFReader,
     }
 
     def __init__(self, io, engine=None):
diff --git a/pandas/io/excel/_odfreader.py b/pandas/io/excel/_odfreader.py
new file mode 100644
index 0000000000000..ae4901ed0104f
--- /dev/null
+++ b/pandas/io/excel/_odfreader.py
@@ -0,0 +1,164 @@
+import pandas as pd
+
+from pandas.io.parsers import TextParser
+
+
+class ODFReader(object):
+    """Read tables out of OpenDocument formatted files
+
+    Parameters
+    ----------
+    filepath_or_buffer: string, path to be parsed or
+        an open readable stream.
+    """
+    def __init__(self, filepath_or_buffer):
+        try:
+            from odf.opendocument import load as document_load
+            from odf.table import Table
+        except ImportError:
+            raise ImportError("Install odfpy >= 1.3 for OpenDocument support")
+
+        self.filepath_or_buffer = filepath_or_buffer
+        self.document = document_load(filepath_or_buffer)
+        self.tables = self.document.getElementsByType(Table)
+
+    @property
+    def sheet_names(self):
+        """Return table names is the document"""
+        from odf.namespaces import TABLENS
+        return [t.attributes[(TABLENS, 'name')] for t in self.tables]
+
+    def get_sheet_by_index(self, index):
+        return self.tables[index]
+
+    def get_sheet_by_name(self, name):
+        i = self.sheet_names.index(name)
+        return self.tables[i]
+
+    def _get_sheet(self, name):
+        """Given a sheet name or index, return the root ODF Table node
+        """
+        if isinstance(name, str):
+            return self.get_sheet_by_name(name)
+        elif isinstance(name, int):
+            return self.get_sheet_by_index(name)
+        else:
+            raise ValueError(
+                'Unrecognized sheet identifier type {}. Please use'
+                'a string or integer'.format(type(name)))
+
+    def parse(self, sheet_name=0, **kwds):
+        tree = self._get_sheet(sheet_name)
+        data = self.get_sheet_data(tree, convert_float=False)
+        parser = TextParser(data, **kwds)
+        return parser.read()
+
+    def get_sheet_data(self, sheet, convert_float):
+        """Parse an ODF Table into a list of lists
+        """
+        from odf.table import TableCell, TableRow
+
+        sheet_rows = sheet.getElementsByType(TableRow)
+        table = []
+        empty_rows = 0
+        max_row_len = 0
+        for i, sheet_row in enumerate(sheet_rows):
+            sheet_cells = sheet_row.getElementsByType(TableCell)
+            empty_cells = 0
+            table_row = []
+            for j, sheet_cell in enumerate(sheet_cells):
+                value = self._get_cell_value(sheet_cell)
+                column_repeat = self._get_cell_repeat(sheet_cell)
+
+                if len(sheet_cell.childNodes) == 0:
+                    empty_cells += column_repeat
+                else:
+                    if empty_cells > 0:
+                        table_row.extend([None] * empty_cells)
+                        empty_cells = 0
+                    table_row.extend([value] * column_repeat)
+
+            if max_row_len < len(table_row):
+                max_row_len = len(table_row)
+
+            row_repeat = self._get_row_repeat(sheet_row)
+            if self._is_empty_row(sheet_row):
+                empty_rows += row_repeat
+            else:
+                if empty_rows > 0:
+                    # add blank rows to our table
+                    table.extend([[None]] * empty_rows)
+                    empty_rows = 0
+                table.append(table_row)
+
+        # Make our table square
+        for row in table:
+            if len(row) < max_row_len:
+                row.extend([None] * (max_row_len - len(row)))
+
+        return table
+
+    def _get_row_repeat(self, row):
+        """Return number of times this row was repeated
+
+        Repeating an empty row appeared to be a common way
+        of representing sparse rows in the table.
+        """
+        from odf.namespaces import TABLENS
+        repeat = row.attributes.get((TABLENS, 'number-rows-repeated'))
+        if repeat is None:
+            return 1
+        return int(repeat)
+
+    def _get_cell_repeat(self, cell):
+        from odf.namespaces import TABLENS
+        repeat = cell.attributes.get((TABLENS, 'number-columns-repeated'))
+        if repeat is None:
+            return 1
+        return int(repeat)
+
+    def _is_empty_row(self, row):
+        """Helper function to find empty rows
+        """
+        for column in row.childNodes:
+            if len(column.childNodes) > 0:
+                return False
+
+        return True
+
+    def _get_cell_value(self, cell):
+        from odf.namespaces import OFFICENS
+        cell_type = cell.attributes.get((OFFICENS, 'value-type'))
+        if cell_type == 'boolean':
+            cell_value = cell.attributes.get((OFFICENS, 'boolean'))
+            return bool(cell_value)
+        elif cell_type in ('float', 'percentage'):
+            cell_value = cell.attributes.get((OFFICENS, 'value'))
+            return float(cell_value)
+        elif cell_type == 'string':
+            return str(cell)
+        elif cell_type == 'currency':
+            cell_value = cell.attributes.get((OFFICENS, 'value'))
+            return float(cell_value)
+        elif cell_type == 'date':
+            cell_value = cell.attributes.get((OFFICENS, 'date-value'))
+            return pd.Timestamp(cell_value)
+        elif cell_type == 'time':
+            cell_value = cell.attributes.get((OFFICENS, 'time-value'))
+            return(pandas_isoduration_compatibility(cell_value))
+        elif cell_type is None:
+            return None
+        else:
+            raise ValueError('Unrecognized type {}'.format(cell_type))
+
+
+def pandas_isoduration_compatibility(duration):
+    """Libreoffice returns durations without any day attributes
+
+    For example PT3H45M0S. The current pandas Timedelta
+    parse requires the presence of a day component.
+    Workaround for https://github.com/pandas-dev/pandas/issues/25422
+    """
+    if duration.startswith('PT'):
+        duration = 'P0DT' + duration[2:]
+    return pd.Timedelta(duration)
diff --git a/pandas/tests/io/data/blank-row-repeat.ods b/pandas/tests/io/data/blank-row-repeat.ods
new file mode 100644
index 0000000000000..e6712854eb5cd
Binary files /dev/null and b/pandas/tests/io/data/blank-row-repeat.ods differ
diff --git a/pandas/tests/io/data/datatypes.ods b/pandas/tests/io/data/datatypes.ods
new file mode 100644
index 0000000000000..7f532b9260831
Binary files /dev/null and b/pandas/tests/io/data/datatypes.ods differ
diff --git a/pandas/tests/io/data/headers.ods b/pandas/tests/io/data/headers.ods
new file mode 100644
index 0000000000000..753c24762a9ec
Binary files /dev/null and b/pandas/tests/io/data/headers.ods differ
diff --git a/pandas/tests/io/data/invalid_value_type.ods b/pandas/tests/io/data/invalid_value_type.ods
new file mode 100644
index 0000000000000..75a7a40b25d79
Binary files /dev/null and b/pandas/tests/io/data/invalid_value_type.ods differ
diff --git a/pandas/tests/io/data/lowerdiagonal.ods b/pandas/tests/io/data/lowerdiagonal.ods
new file mode 100644
index 0000000000000..6d7e0fab084d1
Binary files /dev/null and b/pandas/tests/io/data/lowerdiagonal.ods differ
diff --git a/pandas/tests/io/data/runlengthencoding.ods b/pandas/tests/io/data/runlengthencoding.ods
new file mode 100644
index 0000000000000..f28355a635c5b
Binary files /dev/null and b/pandas/tests/io/data/runlengthencoding.ods differ
diff --git a/pandas/tests/io/data/writertable.odt b/pandas/tests/io/data/writertable.odt
new file mode 100644
index 0000000000000..113bd651e8cd0
Binary files /dev/null and b/pandas/tests/io/data/writertable.odt differ
diff --git a/pandas/tests/io/test_excel.py b/pandas/tests/io/test_excel.py
index 112d14795d9bf..6b4c9466ca7a6 100644
--- a/pandas/tests/io/test_excel.py
+++ b/pandas/tests/io/test_excel.py
@@ -2556,3 +2556,128 @@ def test_excelwriter_fspath(self):
         with tm.ensure_clean('foo.xlsx') as path:
             writer = ExcelWriter(path)
             assert os.fspath(writer) == str(path)
+
+
+@td.skip_if_no('odf')
+class TestODFReader(SharedItems):
+    def test_get_sheet(self):
+        from pandas.io.excel._odfreader import ODFReader
+
+        pth = os.path.join(self.dirpath, 'datatypes.ods')
+        book = ODFReader(pth)
+
+        assert len(book.sheet_names) == 1
+        assert book.sheet_names == ['Sheet1']
+
+    def test_get_sheet_raises(self):
+        from pandas.io.excel._odfreader import ODFReader
+
+        pth = os.path.join(self.dirpath, 'datatypes.ods')
+        book = ODFReader(pth)
+
+        with pytest.raises(ValueError):
+            book._get_sheet(3.14)
+
+        with pytest.raises(ValueError):
+            book.get_sheet_by_name("Invalid Sheet 77")
+
+        with pytest.raises(IndexError):
+            book.get_sheet_by_index(-33)
+
+    def test_read_types(self):
+        sheet = self.get_exceldf(
+            'datatypes', '.ods', header=None, engine='odf')
+
+        expected = DataFrame(
+            [[1.0],
+             [1.25],
+             ['a'],
+             [pd.Timestamp(2003, 1, 2)],
+             [False],
+             [0.35],
+             [pd.Timedelta(hours=3, minutes=45),
+              pd.Timedelta(hours=17, minutes=53),
+              pd.Timedelta(hours=14, minutes=8)],
+             # though what should the value of a hyperlink be?
+             ['UBERON:0002101']])
+        tm.assert_equal(sheet, expected)
+
+    def test_read_invalid_types_raises(self):
+        # the invalid_value_type.ods required manually editing
+        # of the included content.xml file
+        with pytest.raises(ValueError,
+                           match="Unrecognized type awesome_new_type"):
+            self.get_exceldf(
+                'invalid_value_type', '.ods', header=None, engine='odf')
+
+    def test_read_lower_diagonal(self):
+        # Make sure we can parse:
+        # 1
+        # 2 3
+        # 4 5 6
+        # 7 8 9 10
+
+        sheet = self.get_exceldf(
+            'lowerdiagonal', '.ods', 'Sheet1',
+            index_col=None, header=None, engine='odf')
+
+        assert sheet.shape == (4, 4)
+
+    def test_read_headers(self):
+        sheet = self.get_exceldf(
+            'headers', '.ods', 'Sheet1', index_col=0, engine='odf')
+
+        expected = DataFrame.from_dict(OrderedDict([
+            ("Header", ["Row 1", "Row 2"]),
+            ("Column 1", [1.0, 2.0]),
+            ("Column 2", [3.0, 4.0]),
+            # Empty Column
+            ("Column 4", [7.0, 8.0]),
+            # Empty Column 2
+            ("Column 6", [11.0, 12.0])]))
+        expected.set_index("Header", inplace=True)
+        columns = ["Column 1", "Column 2", "Column 4", "Column 6"]
+        tm.assert_equal(sheet[columns], expected)
+        empties = [None, 'None.1']
+        for name in empties:
+            for value in sheet[name]:
+                assert pd.isnull(value)
+
+    def test_read_writer_table(self):
+        # Also test reading tables from an text OpenDocument file
+        # (.odt)
+
+        table = self.get_exceldf(
+            'writertable', '.odt', 'Table1', index_col=0, engine='odf')
+
+        assert table.shape == (3, 3)
+        expected = DataFrame.from_dict(OrderedDict([
+            ("Header", ["Row 1", "Row 2", "Row 3"]),
+            ("Column 1", [1.0, 2.0, 3.0]),
+            ("Unnamed: 2", [nan, nan, nan]),
+            ("Column 3", [7.0, 8.0, 9.0])]))
+        expected.set_index("Header", inplace=True)
+        columns = ["Column 1", "Column 3"]
+        tm.assert_equal(table[columns], expected[columns])
+
+        # make sure pandas gives a name to the unnamed column
+        for i in range(3):
+            assert pd.isnull(table["Unnamed: 2"][i])
+
+    def test_blank_row_repeat(self):
+        table = self.get_exceldf(
+            'blank-row-repeat', '.ods', 'Value', engine='odf')
+
+        assert table.shape == (14, 2)
+        assert table['value'][7] == 9.0
+        assert pd.isnull(table['value'][8])
+        assert not pd.isnull(table['value'][11])
+
+    def test_runlengthencoding(self):
+        sheet = self.get_exceldf(
+            'runlengthencoding', '.ods', 'Sheet1', header=None, engine='odf')
+        assert sheet.shape == (5, 3)
+        # check by column, not by row.
+        assert list(sheet[0]) == [1.0, 1.0, 2.0, 2.0, 2.0]
+        assert list(sheet[1]) == [1.0, 2.0, 2.0, 2.0, 2.0]
+        assert list(sheet[2]) == [1.0, 2.0, 2.0, 2.0, 2.0]


Code review comments:
diff_hunk: @@ -0,0 +1,164 @@
+import pandas as pd
+
+from pandas.io.parsers import TextParser
+
+
+class ODFReader(object):
path: pandas/io/excel/_odfreader.py
text: @user1:
Should remove `(object)` here - we recently get rid of the Python2 "new style" classes so keeping this is what is causing your CI failure

@author:
Thanks for the hint...

It first complained that I had a leading underscore in _ODFReader which i fixed, but I guess there were more lint checks hiding behind that one.