Counting Records by Attribute in a Large XML Export

How many orders are in each status? How many events of each type? How many products per category? The question is a group-by on one attribute over a big file, and the answer should take seconds. It does, with the right tool. This post counts 400,000 orders by status with three tools, compares what each costs and when each can be trusted, and ends with the cross-check that tells you the counts are complete.

The file and the question

The orders are repeated order elements under a root, each with an attribute status of open, paid or shipped:

<order id="1" status="paid">
  <customer>customer-42</customer>
  ...
</order>

The question: how many orders per status?

Quick: grep

grep -o 'status="[a-z]*"' o400k.xml | sort | uniq -c

 134011 status="open"
 133769 status="paid"
 132220 status="shipped"

grep -o prints only the matching text, one match per line, sort groups equal lines and uniq -c counts them. It took 0.4 seconds on a 132 MB file. For a first look at an unfamiliar export this is hard to beat, and it works on any size, since nothing is held in memory but the pipe.

It is also fragile in four ways, and you should know them before trusting it.

• It matches text, not structure. If the attribute name status occurs on another element type in the file, those are counted too. Here only order has it.

• It depends on the quoting. XML allows status='paid' with single quotes. A pattern for double quotes misses those silently.

• It sees comments and text. <!-- status="paid" --> or a CDATA section containing the pattern is counted.

• It sees line breaks wrongly. An attribute split across two lines, or the value on the next line, is not matched.

For a machine-written file whose format you have inspected, none of these happens. For a file from an unknown source, the answer is a hint, to be confirmed.

Reliable: a streaming parser

from collections import Counter
from lxml import etree

c = Counter()
for _, el in etree.iterparse("o400k.xml", events=("end",), tag="order"):
    c[el.get("status")] += 1
    el.clear()
    while el.getprevious() is not None:
        del el.getparent()[0]
print(dict(c))

{'paid': 133769, 'open': 134011, 'shipped': 132220} 2.0s

The same three numbers, in 2.0 seconds. This is correct by construction: the parser knows what an element and an attribute are, handles both quote styles, ignores comments and does not care about layout. The tag="order" argument restricts events to order elements, so the loop ignores everything else. The only memory that grows is the Counter, which has one key per distinct value; this stays small unless the attribute has millions of distinct values. For an attribute like a user id, a Counter of 400,000 keys is large, and the right tool is a sort or a database.

Because only counts are kept, the same script works on a file ten or a hundred times bigger with the same memory, at a proportionally longer time (parsing big XML files fast has the numbers).

Declarative: xmlstarlet

xmlstarlet sel -t -m '/orders/order' -v '@status' -n o400k.xml | sort | uniq -c

 134011 open
 133769 paid
 132220 shipped

Same counts, 5.1 seconds. xmlstarlet builds the document tree to evaluate the XPath, which is why it is slower than the streaming script and why it needs memory proportional to the file (see how much memory a DOM parser needs). It is a good fit when the file is small enough, when the expression is complicated, or when you want the same tool for counting and for selecting. For a file near your memory limit, use the streaming script.

Check the counts against the total

Counts by value only mean something if they cover everything. Add the total as a separate measurement, made a different way:

grep -c '<order ' o400k.xml

400000

And add the three counts: 134,011 + 133,769 + 132,220 = 400,000. They match. If they had summed to 399,990, ten orders have a status that the pattern did not capture, perhaps an empty one, a different case or a missing attribute. In the streaming script, Counter would show them as None for a missing attribute or as a stray value, which is why printing the whole counter is better than printing only the values you expect. A line like {'paid': 133769, 'open': 134011, 'shipped': 132220, None: 10} is the parser telling you something.

Normalize before counting when values vary in spelling: PAID, Paid and paid are three keys in a Counter. Decide whether that is a data quality finding to report or a variation to merge, and make the choice visible. Do not fold silently, because the number of spelling variants is itself a useful fact about the feed.

Counting more than one thing

The same pass can compute several counts at once, so a single read of a large file answers more than one question. Extend the loop with other counters: orders per customer, lines per order, orders per month from the placed element. Each is a Counter or a defaultdict. Reading the file once and keeping a handful of small dictionaries is cheaper than any number of separate passes. Print them together with the total record count so that the output is self-checking.

If you need a cross-tabulation, such as status by month, use a Counter keyed by a tuple, or write the extracted fields to a table and use SQL (GROUP BY status, month). Past two dimensions, the table is easier. Streaming XML into PostgreSQL without a DOM and using DuckDB to read XML through Python show how to get the file into something you can group.

Related reading

This post is part of answering questions from XML data. A neighbor covers the unknown-file case in counting elements and attributes in unknown XML, and the command-line options in counting nodes in XML from the shell. The streaming API is documented on the lxml parsing page, and grep -o in the GNU grep manual. Try the XPath in the XPath tester.

When counting by attribute needs a real parser

The file comes from someone you do not control. Use the parser. A grep count can be wrong in ways you cannot see.

The attribute lives on several element types. Restrict the count to the element you mean, which a parser does with tag= or an XPath, and grep cannot.

Values may be written in different forms. Entities (&), character references and different quote styles change the text a pattern sees. A parser returns the decoded value.

The count feeds a decision. Anything that goes in a report, a bill or an alert deserves the method that cannot misread the structure, plus the independent total as a check.