ENH:read_html() handles tables with multiple header rows #13434
State: closed

 - [x] closes #13434
 - [x] 2 tests added / passed
 - [x] Multiple tr rows within a thead are now parsed, creating a multiindex header.


Labels:
name: Enhancement
name: IO HTML
description: read_html, to_html, Styler.apply, Styler.applymap

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=h1) Report
> Merging [#15242](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/abf16978cfcb1188ca856819204e6bc7500af179?src=pr&el=desc) will **decrease** coverage by `0.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/15242/graphs/tree.svg?height=150&width=650&token=eZ4WkYLtcO&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #15242      +/-   ##
==========================================
- Coverage   90.97%   90.95%   -0.02%     
==========================================
  Files         143      143              
  Lines       49429    49442      +13     
==========================================
+ Hits        44970    44972       +2     
- Misses       4459     4470      +11
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `88.71% <100%> (-0.01%)` | :arrow_down: |
| #single | `40.69% <0%> (-0.12%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/html.py](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=tree#diff-cGFuZGFzL2lvL2h0bWwucHk=) | `84.81% <100%> (+0.32%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29tbW9uLnB5) | `90.96% <0%> (-0.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.56% <0%> (-0.1%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=footer). Last update [abf1697...fc1c80e](https://codecov.io/gh/pandas-dev/pandas/pull/15242?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
I looked for a good real-world url to write a test around, but a lot of the examples I could find seemed like they were from websites where the data may change over time, causing the test to fail. If you want I could add an html file to /io/tests/data and write something around that.
hmm, you are not specifying the header here, so this can *infer* automatically? (that its a multi-index)?
Yes, for instances where a `<table>` contains a `<thead>` and multiple `<tr>`, `_data_to_frame()` infers all header rows whereas currently it only infers the first row, omitting the rest from the dataframe.
@brianhuey ok, please make a note of this in doc-string and show an example in io.rst.

do we ever need to turn this off? (e.g. false positives)?
Currently there is no way to turn off the automatic parsing of `<thead>` elements in to column headers. Since this enhancement only makes that parsing more complete, I would say that adding a parameter might not be necessary. It could be manually turned off by converting the multiindex to a dataframe and concatenating it with the `read_html()` dataframe.
can you rebase (tests have moved).

@jorisvandenbossche @TomAugspurger comments?
@brianhuey looking at the original issue. This *does* work, though still lots of unnamed levels.

Is this expected?
```
In [1]: df = pd.DataFrame(
   ...:     columns=["Name", "Age", "Party"], 
   ...:     data = [("Hillary", 68, "D"), ("Bernie", 74, "D"), ("Donald", 69, "R")])
   ...: df = df.set_index("Name")
   ...: html = df.to_html()
   ...: 

In [2]: pd.read_html(html)[0]
Out[2]: 
  Unnamed: 0_level_0                Age              Party
                Name Unnamed: 1_level_1 Unnamed: 2_level_1
0            Hillary                 68                  D
1             Bernie                 74                  D
2             Donald                 69                  R

```
Yes, I could rename the unnamed levels to a blank string, but I was trying to maintain consistency with the original HTML parser which names blank column headers to 'Unnamed'. I assume it was done this way originally to remove any ambiguity amongst column names. I'm open to any suggestions on how to make this better.
@brianhuey I don't think thats right actually. 

```
In [2]: df
Out[2]: 
         Age Party
Name              
Hillary   68     D
Bernie    74     D
Donald    69     R
```
This is not a multi-line header. Rather ``Name`` is the index name. I *think* the ``to_html()`` is correct as well.

This is a case that you can actually detect (and we do in csv reading and such).
IOW If you have ALL empty string levels that you don't actually have a multi-line header.
In fact I would *make* the user specify it anyhow (IOW, ``headers=None``) means only a single header.

``headers=[0,1]`` is very explict and that is the ONLY way to create a multi-line header.

So I would say that you are actually reading a single line header, but with an index label. Which I can't believe is actually that common (except if we are generating it).
Correct, the test DataFrame has index 'Name' and cols 'Age' and 'Party'. When this is converted to HTML it becomes a table with headers that look something like this:
`<thead>`
`            <tr>`
`                  <th></th><th>Age</th><th>Party</th>`
`            </tr>`
`            <tr>`
`                   <th>Name</th><th></th><th></th>`
`            </tr>`
`</thead>`
Which is essentially the multi-line HTML table header described in [https://github.com/pandas-dev/pandas/issues/13434](url). You could imagine a similar table that looks like this:
`<thead>`
`            <tr>`
`                  <th></th><th>Age</th><th>Party</th><th>Party</th>`
`            </tr>`
`            <tr>`
`                   <th>Name</th><th></th><th></th><th></th>`
`            </tr>`
`</thead>`
My understanding is that selecting a specific 'Party' column becomes ambiguous unless you select by position. This was my rationale for using 'Unnamed' rather than making it look better by inserting blank strings.
@brianhuey can you post rendered versions of those as well (in the issue)
<table border="1" class="dataframe">  <thead>    <tr style="text-align: right;">      <th></th>      <th>Age</th>      <th>Party</th>    </tr>    <tr>      <th>Name</th>      <th></th>      <th></th>    </tr>  </thead>  <tbody>    <tr>      <th>Hillary</th>      <td>68</td>      <td>D</td>    </tr>    <tr>      <th>Bernie</th>      <td>74</td>      <td>D</td>    </tr>    <tr>      <th>Donald</th>      <td>69</td>      <td>R</td>    </tr>  </tbody></table>

<table border="1" class="dataframe">  <thead>    <tr style="text-align: right;">      <th></th>      <th>Age</th>      <th>Party</th>   <th>Party</th> </tr>    <tr>      <th>Name</th>      <th></th>      <th></th>  <th></th>  </tr>  </thead>  <tbody>    <tr>      <th>Hillary</th>      <td>68</td>      <td>D</td>   <td>B</td> </tr>    <tr>      <th>Bernie</th>      <td>74</td>      <td>D</td>   <td>C</td> </tr>    <tr>      <th>Donald</th>      <td>69</td>      <td>R</td>  <td>F</td>  </tr>  </tbody></table>
@brianhuey ok I think you can then treat this like we do in read_csv. Assuming that ``header`` is an int (or None), and NOT a list. If you find the pattern like you do above then you will have a named Index.
In the case of a multi-row HTML header, wouldn't we want to pass a list of header rows? For instance, in the function below, `head` contains our two HTML header rows: `[['','Age','Party'], ['Name','','']` These are appended to the dataframe, and then specified  (`header=[0,1]`) in TextParser. 
```
def _data_to_frame(**kwargs):
    head, body, foot = kwargs.pop('data')
    header = kwargs.pop('header')
    kwargs['skiprows'] = _get_skiprows(kwargs['skiprows'])
    if head:
        rows = lrange(len(head))
        body = head + body
        if header is None:  # special case when a table has <th> elements
            header = 0 if rows == [0] else rows

    if foot:
        body += [foot]

    # fill out elements of body that are "ragged"
    _expand_elements(body)
    tp = TextParser(body, header=header, **kwargs)
    df = tp.read()
    return df
```
@brianhuey 

I wouldn't expect to *need* to pass anything in this case for ``header`` (or ``header=0``) is equivalent. I think that it should/can infer that the ``Name`` row is actually an index label. The logic is not too complex and we do this in csv parsing. (in fact we accept *both* cases, IOW a named Index and no row at all between the header and the data).

This IMHO is NOT a multi-header at all.
But what about HTML tables where there are actually two rows of headers (i.e. two <tr> within a <thead> tag). The original issue raised was that pandas does not parse these multi-header row HTML tables correctly. If we are dealing with an HTML table like the one below, I would expect the parser would not automatically set the "Name" column to be the index, but I would expect the parser to identify the first two rows of the table as column headers and set those accordingly.

<table border="1" class="dataframe">  <thead>    <tr style="text-align: right;">      <th>Zero</th>      <th>Age</th>      <th>Party</th>    </tr>    <tr>      <th>Name</th>      <th>One</th>      <th>Two</th>    </tr>  </thead>  <tbody>    <tr>      <th>Hillary</th>      <td>68</td>      <td>D</td>    </tr>    <tr>      <th>Bernie</th>      <td>74</td>      <td>D</td>    </tr>    <tr>      <th>Donald</th>      <td>69</td>      <td>R</td>    </tr>  </tbody></table>


@brianhuey so for that one, I would say that you would have to specify ``header=[0,1]`` 
I am not sure if it is worth adding this complexity to read_html of inferring index names due to strange formatting of the headers. That is something pandas-specific, and `read_html` is not really regarded as something you store data to later read it again (as opposed to csv). 
I personally think that the last example of @brianhuey (two header levels) is indeed something that `read_html` could do automatically to set the two column rows as the MultiIndexed columns, as this is actually specified by the html source.
@brianhuey ok with @jorisvandenbossche suggestion. What we want is to have a simple / understandable approach that is the least magical as possible, but at the same time can get the job done.
I totally agree. The last table example is the one that I had in mind when I started out. Perhaps the test example confuses things as because indexing the 'Name' column is only done in order to generate a two-row HTML header when `df.to_html()` is called. I could easily add the last table example as the test case instead.
I am having hard time reading the table with multi-indexed columns from [census India](http://www.censusindia.gov.in/Census_Data_2001/Census_Data_Online/Language/parta.htm) using read_html. I just found the issue reported here and the discussion about how the original example reported was not multi-indexed, so thought it might be useful to mention this example. 
@AashitaK the table you linked is a little more complex than the ones I'm trying to handle here as it has multiple column headers but they span a variable number of columns. I am interested in tackling this problem next.
@brianhuey Thanks.
@brianhuey ?
I believe the code achieves what @jorisvandenbossche suggests. It reads multiple header rows from HTML syntax, and parses them as a multiindex column, producing a dataframe with columns that match the structure of an HTML table. I'm open to any suggestions on where to improve how this is handled (i.e. the 'Unnamed: level' thing, specifying multiple headers).
thanks @brianhuey 

followups always welcome!
I appreciate you working with me through this. First time contributing to an open source project!
@brianhuey you did great! 

keep em coming!