Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 64 ยท Getting Started

Reading HTML, XML, Pickle, Clipboard, HDF5 and Parquet

Read and write less common formats with pandas: HTML tables, XML, pickle, the clipboard, HDF5, Parquet and Feather, with trade-offs for size, speed and safety.

CSV, Excel and JSON cover most files you meet, but real projects also bring web pages with tables, XML exports from older systems, files saved by another Python program, and big datasets that nobody wants to parse as text. pandas has a reader (and usually a writer) for each of them, and they all follow the same pattern: pd.read_something(...) returns a DataFrame, df.to_something(...) saves one.

This lesson tours the less common formats, shows which ones run in the browser editors, and ends with a decision guide: which format should you choose when you are the one saving the data? If you need the basics first, read Reading CSV Files and Writing and Formatting Data.

Note: Some readers need an optional helper library. The browser runner installs them with micropip (shown at the top of each example, and not needed if the library is already installed on your computer). The first run downloads the library and takes a few seconds. The clipboard and HDF5 need things a browser does not have, so they are shown as read-only code.

HTML tables: read_html

pd.read_html(source) finds every <table> in a page and returns a list of DataFrames, one per table. The source can be a URL, a file path or a string of HTML. A string must be wrapped in io.StringIO (passing raw HTML text directly is deprecated). The parser needs lxml (or beautifulsoup4 with html5lib).

Python Tables from HTML

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The comma in 6,371 is treated as a thousands separator by default, so the radius column arrives as numbers. A table that uses other separators (the European 1.234,5) needs thousands="." and decimal=",", as the last lines show: without them the column stays text. Useful options:

  • match="text" keeps only tables that contain that text. It is the easiest way to pick the right table on a busy page.
  • attrs={"id": ...} keeps tables with those HTML attributes.
  • header, index_col, skiprows work like in read_csv.
  • flavor="lxml" or "bs4" forces a parser.

On your own computer you can pass a URL directly: pd.read_html("https://example.com/prices"). Respect the site's terms and robots.txt, and remember that scraped tables change without warning, so validate the columns before trusting them. Many sites do not allow scripts to fetch their pages from the browser, which is another reason the examples here use a string.

XML: read_xml and to_xml

XML stores records as repeating elements. pd.read_xml turns the repeated elements (usually children of the root) into rows, and their child elements and attributes into columns.

Python Reading and writing XML

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

By default read_xml uses lxml and understands full XPath, for example xpath=".//book[year > 1900]". With parser="etree" it needs no extra library, but XPath is limited. Attributes such as id become columns just like child elements. Deeply nested XML (records inside records) does not fit one table: read each level with its own xpath, then join them with merge. For huge files read the data in pieces rather than all at once, as in the big-data lessons.

Pickle: Python objects on disk

df.to_pickle("data.pkl") and pd.read_pickle("data.pkl") save and load a DataFrame in Python's own object format. It keeps everything: dtypes, the index, categoricals, timezone-aware dates, even Python objects stored in cells. That makes it the most faithful and the quickest format for a temporary file ("save my work between sessions").

Python A pickle round trip keeps types

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Warning: Never unpickle a file from a source you do not trust. Loading a pickle can run arbitrary code on your computer. Pickle is also tied to Python and library versions, so a file written today may not load in five years. Use it for private scratch files only, and share data as CSV or Parquet instead.

The clipboard: read_clipboard

pd.read_clipboard() parses whatever you last copied (for example a range of cells selected in Excel or a table copied from a web page) as a table, using read_csv rules with a whitespace or tab separator. df.to_clipboard() does the reverse, so you can paste a result straight into a spreadsheet. It is perfect for quick exploration in a notebook, and a poor choice for scripts that must run unattended, because it depends on what happens to be on your clipboard. A browser sandbox has no clipboard access for the Python process, so this is read-only:

Python
# Select a few cells in Excel, press Ctrl+C, then:
df = pd.read_clipboard()          # tab-separated by default for Excel cells
df = pd.read_clipboard(sep=",")   # when you copied CSV text

df.describe().to_clipboard()      # now paste into a spreadsheet

On Linux you need xclip or xsel installed; Windows and macOS work out of the box.

HDF5: read_hdf and to_hdf

HDF5 is a binary container for large tables. df.to_hdf("store.h5", key="prices") writes a DataFrame under a name (the key) and a single file can hold many keys. With format="table" you can query part of the file on disk, for instance pd.read_hdf("store.h5", "prices", where="price > 100"), without loading everything.

Python
# needs: pip install tables
df.to_hdf("store.h5", key="prices", mode="w", format="table", data_columns=["price"])
cheap = pd.read_hdf("store.h5", "prices", where="price < 10")

HDF5 needs the PyTables package (tables), which is not available in the browser, so these lines are read-only. Today most teams choose Parquet instead: it is simpler, works across languages and has no single-file locking problems.

Parquet and Feather

Parquet is a columnar binary format. Values of one column are stored together and compressed, which gives small files, fast reads of selected columns and types that survive the round trip. It is the standard format for analytics data and is read by Spark, DuckDB, R and many warehouses. Feather (Arrow IPC) is a close relative that is faster to read and write but compresses less, so it suits short-lived files shared between Python and R.

Both need the pyarrow library (or fastparquet for Parquet). On your computer the pandas methods are one-liners:

Python
df.to_parquet("prices.parquet")                       # compression="snappy" by default
df = pd.read_parquet("prices.parquet", columns=["city", "price"])
df.to_feather("prices.feather")
df = pd.read_feather("prices.feather")

Note: In the browser runner pyarrow is a large download, so the first run takes a while. Pandas can also fail to find its Parquet engine there when it was imported before pyarrow, which happens when you ran another example on the page first. The example below therefore calls pyarrow directly: pa.Table.from_pandas(df) converts a DataFrame, pq.write_table saves it and .to_pandas() converts back. This is exactly what df.to_parquet does for you on your own computer.

Python CSV, pickle and Parquet compared

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Exact sizes depend on the library versions, but the pattern is stable: the CSV is about twice the size of the Parquet file here (and much more for text-heavy data), and the date column comes back from CSV as plain text (str) while Parquet restores the real datetime type. columns=[...] makes Parquet reads cheaper for wide tables, which is something CSV cannot do.

Which format should you pick?

Format Types kept Size and speed Readable by other tools Typical use
CSV no big, slow everything exchange with anyone
Excel partly slow spreadsheets reports for people
JSON partly big web, APIs nested data, APIs
Pickle all fast Python only, unsafe private scratch files
HDF5 most fast scientific tools large numeric tables on disk
Parquet yes small, fast most analytics tools datasets and pipelines
Feather yes fastest Python and R short-lived exchange

A good rule: CSV to share with a person, Parquet for data you will read again, and pickle only for throw-away files. The big-data lesson Big Data Options shows how Parquet and chunked reading help when a table no longer fits in memory.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does pd.read_html(source) return?

    read_html always returns a list, even when the page has only one table. Use match or attrs to pick the one you want.
  2. 2. Why should you never call read_pickle on a file from an unknown source?

    Unpickling rebuilds Python objects and can run code stored in the file. Only load pickles you created yourself.
  3. 3. Which advantage does Parquet have over CSV?

    Parquet is columnar and typed. That gives smaller files, faster reads and exact dtypes, but it needs pyarrow or fastparquet and is not human-readable.
  4. 4. A page shows a number as 1.234,5 (European style) and read_html gives you text. Which arguments fix it?

    thousands and decimal tell the parser which characters group digits and mark the fraction, so the column can be read as numbers.
  5. 5. Which parser option lets read_xml work without the lxml library?

    parser="etree" uses Python's built-in ElementTree. It supports only a limited XPath subset, while the default lxml parser supports full XPath.

Summary

  • read_html returns a list of DataFrames, one per <table>; use match and attrs to pick one, and thousands and decimal for other number styles.
  • read_xml and to_xml map repeated elements to rows; parser="etree" avoids the lxml dependency.
  • Pickle keeps everything but is Python-only and unsafe for untrusted files.
  • read_clipboard and to_clipboard are handy for interactive work, not for unattended scripts; HDF5 needs PyTables and is mostly replaced by Parquet.
  • Parquet and Feather need pyarrow, keep dtypes and are far smaller and faster than CSV.
  • Choose CSV for people, Parquet for data you will reuse, pickle for private scratch files.

Next: pandas for Excel Users translates spreadsheet habits into pandas.