Read HTML files into Deephaven tables
While Deephaven does not have its own methods for reading HTML tables, you can read them with pandas or BeautifulSoup. This guide demonstrates multiple ways to pull data from HTML tables, such as those on web pages, into Deephaven tables.
Example table and setup
We'll use this HTML table in the examples below:
Color | Shades | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| primary | #e5faff | #d7f7ff | #c8f4ff | #b9f1ff | #a9edff | #99eaff | #7fc6d9 | #65a4b4 | #4d8391 | #35636f | #1f454f |
| secondary | #6a81f2 | #596fe9 | #495ee0 | #384cd6 | #2439cb | #0625c0 | #0121aa | #001e94 | #001a7e | #011569 | #031155 |
| negative | #f883a0 | #f87694 | #f76989 | #f65a7d | #f54a72 | #f33666 | #da2e59 | #c1254c | #a91d40 | #911534 | #7a0d28 |
| positive | #9dfc7e | #93fa72 | #89f966 | #7ef758 | #72f549 | #65f336 | #58d92e | #4cc025 | #3fa71d | #348f15 | #28780d |
| warn | #ffe590 | #ffe183 | #fedd75 | #fed966 | #fdd455 | #fdd041 | #e8be38 | #d4ac2f | #c09b27 | #ac8a1e | #997915 |
| info | #f08df9 | #ee7ff8 | #eb70f7 | #e960f6 | #e64df4 | #e336f3 | #cd34dd | #b732c7 | #a22fb2 | #8d2c9d | #792989 |
| fg | #f3f7fa | #eef2f5 | #e9edf0 | #e4e9ec | #dfe4e7 | #dadfe2 | #c6cbcf | #b2b8bc | #9fa5aa | #8c9298 | #798086 |
| bg | #434f56 | #3c474d | #353e44 | #2f363c | #282f33 | #22272b | #1f2327 | #1b2023 | #181c1f | #15181b | #121517 |
The examples read this table from a string of HTML, html_str, rather than from a web page, so reading the table doesn't need a network connection. The following code defines html_str and installs BeautifulSoup and lxml, the HTML parser the examples use:
pandas.read_html
pandas.read_html is a convenient method for reading HTML tables into a list of pandas DataFrames. The method also reads tables from a URL or a local file. To read HTML that is already in a string, wrap the string in io.StringIO. pandas 2.1 deprecated passing literal HTML as a plain string, and pandas 3 treats a plain string as a URL or file path.
Let's start with pandas.read_html. The following example reads the color table in html_str and converts it to a Deephaven table with to_table. The Example table and setup section defines html_str and installs lxml, the parser that read_html uses by default. read_html returns one DataFrame for each <table> element in its input, so the example converts the first one:
This approach is simple, but you have little control over the column names or which part of each cell it reads:
read_htmlnames each column after its header cell. TheShadesheader spans 11 columns, so pandas repeats the name and makes each copy unique:Shades,Shades.1, and so on throughShades.10. Deephaven column names must be valid Java identifiers, soto_tablereplaces characters that a column name can't contain, such as., with_, which givesShades_1throughShades_10.- By default,
read_htmlkeeps only the text of each cell. Itsextract_linksargument can also keep link URLs, butread_htmldoesn't return any other cell attributes. An empty cell becomesNaN, so if a table stores its values in attributes such astitleinstead of in the cell text, those columns contain onlyNaNvalues.
BeautifulSoup
BeautifulSoup is a Python library for pulling data out of HTML and XML files. It takes more code than read_html, but you decide what to read from each cell and what to name each column.
In this example, we'll use BeautifulSoup to read the color table in html_str and convert it to a Deephaven table with new_table and string_col. The Example table and setup section defines html_str and installs BeautifulSoup. The Shades header spans 11 columns, so the code generates a name for each of them. The result matches the read_html example's result, so you can compare the two approaches:
If a table stores a cell's value in an attribute, such as title, instead of in the cell text, replace td.text.strip() with td.get("title", td.text.strip()). The cells in html_str have no title attribute, so this change doesn't alter the example's result.
To read a table from a local HTML file, open the file and pass it to BeautifulSoup in place of html_str, for example with open("/path/to/file.html") as f: soup = BeautifulSoup(f, "lxml"). To read a table from a web page, download the page first and pass the result to BeautifulSoup. For example, install the requests library with pip install requests and use requests.get(url).content.
Data types
HTML tables store all data as plain text and have no concept of data types, so check each column's type after you import a table. Each tool handles types differently:
- By default,
pandas.read_htmlconverts columns of numeric text to numeric columns but leaves other values, such as dates, as strings. - BeautifulSoup returns every value as a string.
to_tablegives each Deephaven column the type that matches the DataFrame column's type, so a column of text becomes aStringcolumn.to_tabledoesn't parse text, so numbers stored as strings stay strings.
Whether you use pandas or BeautifulSoup, you can set each column's data type in either of two places:
- In the Deephaven table, with
updateor another of Deephaven's selection methods. - In the DataFrame, with the pandas
astypemethod.
The examples in this section use a small HTML table of stock trades with Symbol, Price, Size, and Timestamp columns. We'll read it with BeautifulSoup, so every column starts as text. With pandas.read_html, Price and Size would already be numeric, and only Timestamp would need converting. The code puts the rows in a pandas DataFrame, so both typing methods below can start from the same data.
The following code reads the table into trades_df, a DataFrame of strings. It then uses to_table to convert trades_df to trades, a Deephaven table of String columns:
Typing with update
In this example, we start from trades, the table of String columns created in Data types. It sets the correct type for each column with Deephaven's update method, using Java's Double.parseDouble and Integer.parseInt methods and the built-in parseInstant function:
Typing with astype
In this example, we start from trades_df, the DataFrame of strings created in Data types. It sets each column's type with astype before it converts the DataFrame to a Deephaven table. to_table turns the float64 column into a double column, the int32 column into an int column, and the datetime64 column into an Instant column: