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_html names each column after its header cell. The Shades header spans 11 columns, so pandas repeats the name and makes each copy unique: Shades, Shades.1, and so on through Shades.10. Deephaven column names must be valid Java identifiers, so to_table replaces characters that a column name can't contain, such as ., with _, which gives Shades_1 through Shades_10.
  • By default, read_html keeps only the text of each cell. Its extract_links argument can also keep link URLs, but read_html doesn't return any other cell attributes. An empty cell becomes NaN, so if a table stores its values in attributes such as title instead of in the cell text, those columns contain only NaN values.

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_html converts columns of numeric text to numeric columns but leaves other values, such as dates, as strings.
  • BeautifulSoup returns every value as a string.
  • to_table gives each Deephaven column the type that matches the DataFrame column's type, so a column of text becomes a String column. to_table doesn'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 update or another of Deephaven's selection methods.
  • In the DataFrame, with the pandas astype method.

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: