Tables and spreadsheets

What you will be able to do

Read a supplier’s price list as one clean table, with one record per product. The table continues on a second page, repeats its header and prints each category once. Then read the same list as a spreadsheet. Every cell keeps its value, its unit and its place: a page and a box, or a sheet and a cell address.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• Blocks and section paths, from The idea: blocks with a section path in Sections and hierarchy.
• Boxes in points on a PDF page, from A tiny example: one receipt line by line in Layout and meaning.
• How a spreadsheet names its cells: a column letter and a row number, such as E6. Nothing more about spreadsheets is needed.

What you need on your computer

• Your project at the end of the previous lesson: python -m pytest -q prints 68 passed.
• The labs repository in ~/projects/nextia-learning-labs, up to date.

Tested with Python 3.12.13, pdfplumber 0.11.10 and openpyxl 3.1.5 on macOS (Apple silicon). The Windows and Linux commands are not tested. The supplier, AquaFlow Supply, and its prices are made up by the course’s generator.

Get the lesson’s files

1. In the project folder, copy the two modules and their tests from the snapshot:

   macOS / Linux

   S=../nextia-learning-labs/C13/snapshots/end-of-m03
   cp $S/doc_extract/tables.py $S/doc_extract/sheets.py doc_extract/
   cp $S/tests/test_tables.py $S/tests/test_sheets.py tests/

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C13\snapshots\end-of-m03"
   foreach ($f in 'tables.py', 'sheets.py') { Copy-Item "$S\doc_extract\$f" doc_extract\ }
   foreach ($f in 'test_tables.py', 'test_sheets.py') { Copy-Item "$S\tests\$f" tests\ }

   tables.py reads tables in a PDF; sheets.py reads spreadsheets.

2. Run the tests:

   python -m pytest -q

   The last line is 74 passed.

Copying a catalogue into a notebook

Think of a paper catalogue from a garden supplier. The prices are in a table. The category, “Hose reels”, is printed once, in a tall box next to its four products. The table does not fit on one page, so page 2 prints the column titles again, and writes “Sprinklers (cont.)” at the top.

Now copy the catalogue into a notebook, one line per product. You do three things without thinking. You write “Hose reels” on each of the four lines, because each product belongs to it. You do not copy the column titles of page 2 as if they were a product. And you write “Sprinklers”, not “Sprinklers (cont.)”: it is the same group. A program must do the same three things, and it must be told.

The problem: the table as the PDF library gives it

D056 is AquaFlow’s price list for Larkfield’s purchasing team. Its table of 41 products starts on page 1 and continues on page 2. The PDF library, pdfplumber, finds the table on each page and gives back its rows. Here is that first, quick look:

python -m doc_extract.tables D056 --naive

   ['Category', 'SKU', 'Description', 'Unit', 'Price (CAD)', None]
   [None, None, None, None, 'Each', 'Case of 6']
   ['Hose reels', 'HR-30', 'Wall hose reel, 30 m', 'each', '59.00', '318.60']
   [None, 'HR-50', 'Hose reel cart, 50 m', 'each', '119.00', '642.60']
   [None, 'HR-15C', 'Compact reel, 15 m', 'each', '34.00', '183.60']
   ['...']
   [None, 'SP-PUL', 'Pulse sprinkler on spike', 'each', '23.50', '126.90']
   [None, 'SP-DRIP', 'Drip kit, 20 plants', 'kit', '44.00', '237.60']
   [None, 'SP-MIST', 'Misting kit, patio', 'kit', '36.00', '194.40']
   ['Category', 'SKU', 'Description', 'Unit', 'Price (CAD)', None]
   [None, None, None, None, 'Each', 'Case of 6']
   ['Sprinklers (cont.)', 'SP-TRV', 'Travelling sprinkler', 'each', '89.00', '480.60']
45 rows (header rows counted as data), cells correct: 172 of 246 (70%)

Each row is read without a single wrong character. Yet only 172 of the 246 cells that the purchasing team needs are right: 41 products × 6 columns. Which cells are lost? A short check counts the wrong cells by column:

{'Category': 34, 'Price (CAD) / Case of 6': 40}

• The category is lost 34 times. “Hose reels” is one merged cell: one box that covers four rows. The library gives its text to the first row only, and None to the three rows below. That is 33 empty cells over the whole table. The 34th is “Sprinklers (cont.)”, which is not a category name.
• The case price is lost 40 times. The header has two rows. “Price (CAD)” is one merged cell over two columns, so the last column has no name in the first row, only None. A program that takes row 1 as the header cannot find a “Case of 6” column. The 41st case price is “n/a” (hose by the metre has no case price), which is right by chance.
• There are 45 rows for 41 products. The two header rows are counted as rows of data, on both pages.

So Omar’s question, “what do we pay for a case of SP-HOSE?”, has no answer. And a count of products per category is wrong.

The idea: one table, one record per row, one place per cell

Table recovery turns the pieces that the library returns into one table with named columns. It does four things, the same four as the person with the notebook:

1. Join the header rows into one name per column. An empty cell in the first header row belongs to the merged cell on its left. An empty cell in a lower row belongs to the cell above it. “Price (CAD)” over “Each” gives the name Price (CAD) / Each.
2. Take the units from the header. Text in brackets at the end of a name, such as (CAD), is the unit of the whole column. Each price cell holds only a number.
3. Fill the merged cells. A cell with no text and no box of its own is inside a merged cell. It gets the value of the merged cell above it, and it keeps the merged cell’s box, with a flag filled. You will see in the next lesson why the box matters.
4. Join the pages. A table on the next page with the same header rows continues the same table. Its header rows are skipped, and “(cont.)” is removed from a value.

Each cell of the result is a small record: the value, the page, the box in points, and filled.

A tiny example: two header rows and one merged cell

Take the two header rows exactly as the library gave them, and join them:

from doc_extract import tables
header = [['Category', 'SKU', 'Description', 'Unit', 'Price (CAD)', None],
          [None, None, None, None, 'Each', 'Case of 6']]
names = tables.column_names(header)
print(names)
print({n: tables.unit_of(n) for n in names})

['Category', 'SKU', 'Description', 'Unit', 'Price (CAD) / Each', 'Price (CAD) / Case of 6']
{'Category': None, 'SKU': None, 'Description': None, 'Unit': None, 'Price (CAD) / Each': 'CAD', 'Price (CAD) / Case of 6': 'CAD'}

Follow the last column by hand. In row 1, its cell is None, so it takes the value on its left: “Price (CAD)”. In row 2, it says “Case of 6”. The name is the two parts joined: Price (CAD) / Case of 6. The unit is the text in brackets, CAD. The first four columns have None in row 2, so they keep their name from row 1.

Now the merged category. These are the first two products, after recovery:

HR-30 Category = Hose reels | page 1 | box [40, 138, 150, 214] | filled False
HR-30 Price (CAD) / Each = 59.00 | page 1 | box [440, 138, 505, 157] | filled False
HR-50 Category = Hose reels | page 1 | box [40, 138, 150, 214] | filled True
HR-50 Price (CAD) / Each = 119.00 | page 1 | box [440, 157, 505, 176] | filled False

Look at the boxes. A price cell is one row high: 138 to 157 points, then 157 to 176. The category box of HR-30 runs from 138 to 214 points: 76 points, four rows of 19. That is the merged cell. HR-50’s category has the same box and filled True: its value comes from the merged cell, not from its own row.

The price list across its page break. (1) Two header rows with a merged cell; (2) a category printed once for four rows; (3) the header printed again on page 2; (4) a group that continues on page 2.

Text description of the diagram

Number 1: the header has two rows, and “Price (CAD)” is one merged cell over “Each” and “Case of 6”. The column names are “Price (CAD) / Each” and “Price (CAD) / Case of 6”, and the unit CAD comes from the header. Number 2: the category “Hose reels” is one merged cell, four rows high. The PDF library gives the value only to the first row, and the three rows below get it from the merged cell. In the whole table, 33 cells are filled like this. Number 3: page 2 prints the two header rows again. They are not products, so the program skips them. Number 4: “Sprinklers (cont.)” at the top of page 2 continues the group from page 1, so the value is “Sprinklers”.

Recover the price list

1. Recover the table:

   python -m doc_extract.tables D056

   aquaflow-price-list.pdf: one table on pages [1, 2], 2 header rows, 41 products
   columns: ['Category', 'SKU', 'Description', 'Unit', 'Price (CAD) / Each', 'Price (CAD) / Case of 6']
   units from the header: {'Price (CAD) / Each': 'CAD', 'Price (CAD) / Case of 6': 'CAD'}
     Category=Sprinklers* (p1), SKU=SP-MIST (p1), Description=Misting kit, patio (p1), Unit=kit (p1), Each=36.00 (p1), Case of 6=194.40 (p1)
     Category=Sprinklers (p2), SKU=SP-TRV (p2), Description=Travelling sprinkler (p2), Unit=each (p2), Each=89.00 (p2), Case of 6=480.60 (p2)
     Category=Sprinklers* (p2), SKU=SP-HOSE (p2), Description=Sprinkler hose 7.5 m (p2), Unit=each (p2), Each=16.00 (p2), Case of 6=86.40 (p2)
     Category=Sprinklers* (p2), SKU=SP-BASE (p2), Description=Sprinkler base, heavy (p2), Unit=each (p2), Each=12.50 (p2), Case of 6=67.50 (p2)
     Category=Spare parts (p2), SKU=SPR-WASH (p2), Description=Tap washers (10) (p2), Unit=bag (p2), Each=3.20 (p2), Case of 6=17.28 (p2)
     Category=Spare parts* (p2), SKU=SPR-ORING (p2), Description=O-rings for connectors (12) (p2), Unit=bag (p2), Each=4.10 (p2), Case of 6=22.14 (p2)
     (* = from a merged cell; the box is the merged cell's box)
   cells correct: 246 of 246 (100%)

2. Read the six rows around the page break: products 31 to 36 of 41. SP-MIST is the last product of page 1, and SP-TRV the first of page 2. The group “Sprinklers” goes on across the break, without “(cont.)”. A * marks a value that comes from a merged cell. Every cell also knows its page, p1 or p2.

3. Read the score. All 246 cells match the truth, against 172 in the quick look. The units of the two price columns came from the header, not from the cells.

4. Open doc_extract/tables.py and find recover(). Find the four steps. _header_rows counts the top rows that repeat on every page. column_names joins them. A loop skips the header on each page. And the test text is None and box is None finds a cell inside a merged cell.

Sam asks: “How does the program know that page 2 has the same table, and not a new one?” Mei answers: “It compares the top rows of each page’s table. Here, the first two rows are the same on both pages, so they are the header, and the table goes on. A new table would have other column names. A supplier that does not repeat the header would need another clue: the same number of columns at the same places.”

Spreadsheets: read the cells, never a picture

D057 is the same price list as an Excel file (.xlsx). Tomás suggests: “Draw it as a picture and send it through OCR, like everything else.” That would throw away the best thing a spreadsheet has: its cells already hold the exact values. The cell E6 holds the number 119, not a picture of “119.00” that OCR could misread. A spreadsheet is the one input of this course that needs no reading at all.

It still needs care, because people make spreadsheets for eyes, not for programs:

• The header is not on row 1. Row 1 is a title, row 2 a note, and row 3 is empty. The header is on row 4. The project takes the first row with at least three text cells and a number in the row below it.
• The units are in the header names: “Price each (CAD)”, “Pack weight (kg)”. The cells hold only numbers. Without the header, 6.2 could be kilograms or pounds.
• The categories are merged ranges. “Hose reels” is the merged range A5:A8. Its value is stored only in the top-left cell, A5. The cells A6, A7 and A8 are empty in the file.

D057 as the spreadsheet stores it. (1) A title above the header; (2) the header on row 4, with the units in the names; (3) a merged range whose value lives only in A5; (4) a number with its own address.

Text description of the diagram

The drawing shows the first 11 rows of the Prices sheet. The column letters A to G are on top, and the row numbers on the left. Number 1: row 1 is a title, one merged cell from A1 to G1, and row 2 is a note. So the header is not on row 1. Number 2: the header is on row 4, and the units are inside the names: CAD for the two prices, kg for the weight. Number 3: the category “Hose reels” is the merged range A5:A8. Its value is stored only in A5; the cells A6, A7 and A8 are empty. Number 4: the cell E6 holds the number 119, the price of HR-50. Its full address is Prices!E6: the sheet, then the cell.

1. Read the spreadsheet:

   python -m doc_extract.sheets D057

   aquaflow-price-list.xlsx: sheet Prices, header on row 4
   columns: ['Category', 'SKU', 'Description', 'Unit', 'Price each (CAD)', 'Price per case (CAD)', 'Pack weight (kg)']
   units from the header: {'Price each (CAD)': 'CAD', 'Price per case (CAD)': 'CAD', 'Pack weight (kg)': 'kg'}
     Category=Hose reels [Prices!A5], SKU=HR-30 [Prices!B5], Price each=59 [Prices!E5]
     Category=Hose reels [Prices!A6 from A5:A8], SKU=HR-50 [Prices!B6], Price each=119 [Prices!E6]
     Category=Hose reels [Prices!A7 from A5:A8], SKU=HR-15C [Prices!B7], Price each=34 [Prices!E7]
     Category=Hoses [Prices!A9], SKU=HS-15 [Prices!B9], Price each=21.5 [Prices!E9]
     Category=Hoses [Prices!A10 from A9:A14], SKU=HS-25 [Prices!B10], Price each=34.5 [Prices!E10]
   41 products; cells correct: 164 of 164 (100%)

   Each value carries its address, such as Prices!E6. A category filled from a merged range says so: A6 from A5:A8. The score checks 4 columns of 41 products: category, the two prices and the weight.

2. Read it again without filling the merged ranges:

   python -m doc_extract.sheets D057 --naive

   The last line is 41 products; cells correct: 130 of 164 (79%). The 34 lost cells are categories: Category=None [Prices!A6]. Only the 7 top cells of the 7 groups keep their category.

The two files of the same list tell the same story. Each time, the quick reading loses about a quarter of the cells, and recovery gets all of them:

• Quick reading
• Recovered

Cells right before and after recovery, for the same price list as a PDF table (246 cells) and as a spreadsheet (164 cells). The quick reading loses the merged cells, and in the PDF also the column without a name.

Text description of the chart

PDF table D056: the quick reading gets 172 of 246 cells right (69.9%), recovery 246 of 246 (100%). Spreadsheet D057: without the merged ranges, 130 of 164 (79.3%); with them, 164 of 164 (100%).

What this tells us: none of the lost cells was a reading error. Every character was right in both quick readings. The losses come from structure only: merged cells, a header in two rows, a header printed twice. So a better OCR engine or a model would not fix them; structure recovery does.

When to recover tables, and what it costs

Recover tables whenever the values in a table are used one by one: prices, quantities, durations. Both commands take about 0.1 seconds, with no model.

Prefer the spreadsheet when the supplier sends both files. Its cells are exact numbers, with addresses, and it has a column that the PDF does not print: the weight. Use the PDF table when the PDF is all you have.

Know what these rules cannot do:

• tables.py needs ruled tables. pdfplumber finds a table by its lines. D055’s small table on page 2 has no lines, and the command stops: garantie-legale-quebec.pdf: no table found. pdfplumber can also guess columns from the gaps between words (its “text” strategy). On D055 that finds the four rows, but it also cuts the title in two and drops a word of the header:

  ['Durées de garantie Larkfield (ex', 'emples)', '']
  ['Code', 'Produit', 'Garantie']
  ['PW-2200', 'Nettoyeur haute pression', '2 ans']

  So every new kind of table needs its own check against a few known rows.

• Scanned tables are not covered. OCR gives lines, not cells. The course has no scanned table, so this was not tested.

• A formula cell gives the value that the spreadsheet program computed and saved, not the formula. A file saved by a program that does not store that value gives an empty cell. D057 has no formulas.

Common mistake

A column without a name

Symptom: The purchasing report has every price per unit but no price per case. No error appears: the 41 case prices are not in the records.

Cause: The header has two rows, and “Price (CAD)” is one merged cell over two columns. The code took the first row as the header, so the last column was called None, and nobody asked for a column called None.

Diagnose: Print the header rows as the library returns them: tables.raw_tables(path)[0][1][:3]. Look for None in the first row and text below it. Count the values per column: a column that is always missing is a sign of a header problem, not a reading problem.

Fix: Find how many top rows repeat on each page (here 2), and join them into one name per column with column_names. Test that each expected column name exists, as test_one_table_over_two_pages_with_a_two_row_header does.

Consequence in AI work: a missing column is a silent error. Ask a model “what is the case price of SP-HOSE?” with these records. It has nothing to quote, and it may compute 6 × 16.00 = 96.00. The real case price is 86.40, with a discount.

Your turn: the second sheet

The spreadsheet has a second sheet, Discounts, with three discount levels. Change one condition: read that sheet instead of Prices. Predict the header row, the units and the address of the discount for an order of 2,000 CAD. Then check it in Python:

from doc_extract import documents, sheets
names, units, recs = sheets.read(documents.get("D057").path, sheet="Discounts")
print(names, units)
for r in recs:
    print({k: (c.value, c.cell) for k, c in r.items()})

Show the answer

['Order value (CAD)', 'Discount (%)'] {'Order value (CAD)': 'CAD', 'Discount (%)': '%'}
{'Order value (CAD)': ('0 - 999.99', 'A2'), 'Discount (%)': (0, 'B2')}
{'Order value (CAD)': ('1000 - 4999.99', 'A3'), 'Discount (%)': (5, 'B3')}
{'Order value (CAD)': ('5000 and more', 'A4'), 'Discount (%)': (8, 'B4')}

The header is on row 1, and the units are CAD and %. An order of 2,000 CAD is in the range “1000 - 4999.99”, so the discount is 5%, from Discounts!B3.

Look closely at why the header was found. The header rule wants at least three text cells in a row, and this sheet has only two columns. So the rule found nothing and used its default, row 1, which happens to be right. On a two-column sheet with a title above the header, it would be wrong. Notice also that the order values are text ranges, not numbers: to use them, a program must still parse “1000 - 4999.99”.

See it end to endExtract a table from a public reportCase study

The table case study reads a real table that runs over two pages of a US government report: a two-row header with merged cells, units, group rows and footnote marks. Columns from the printed rules and rows from the words match all 369 cells of the agency’s CSV; the PDF library’s default table finder matches none.

Knowledge check

Check your understanding

Explain: why does a filled cell keep the box of the merged cell, and a flag that says it was filled, instead of a box of its own?

Show the answer

A cell inside a merged cell has no text and no box of its own. On the page, the supplier printed the value once, in the merged box. So the true place of HR-50’s category “Hose reels” is the merged box [40, 138, 150, 214] on page 1. Pointing anywhere else would show a person an empty place. The flag filled says that the value was copied by a rule, not read on that row. A reviewer can then check the rule if a category looks wrong.

Check your understanding

Predict: next month, AquaFlow's list has 70 products on three pages, with the header repeated on pages 2 and 3, and a group that starts on page 2 and continues on page 3. Does tables.py need a change?

Show the answer

No. _header_rows compares the top rows of every page’s table, so two repeated header rows are found on three pages as well as on two. The loop skips them on each page, and the merged cells and “(cont.)” are handled on each page the same way. Check it, however: run the command and compare a few rows of each page with the PDF. If AquaFlow changes the header on one page, the header would no longer repeat, and the rule would count only one header row.

Check your understanding

Practical check: run python -m doc_extract.tables D056 --naive. Account for the 74 cells that the quick reading gets wrong, and for its 45 rows.

Show the answer

The quick reading gets 172 of 246 cells right. 34 categories are wrong: 33 cells inside merged cells come back empty, and one says “Sprinklers (cont.)”. 40 case prices are lost because the last column has no name in the first header row; the 41st is “n/a”, right by chance. 34 + 40 = 74. There are 45 rows for 41 products because the two header rows are counted as data on both pages: 41 + 2 × 2 = 45. No character is misread: all the losses come from the structure.

Recap

• A table read row by row, as the library returns it, loses cells without one wrong character. On D056: 172 of 246 cells right, and 45 rows for 41 products.
• Table recovery joins the header rows into one name per column and takes the units from the header. It fills merged cells, with the merged box and a filled flag. It joins the pages and skips the repeated header. D056: 246 of 246 cells, 33 filled from merged cells.
• A spreadsheet holds exact values: read its cells, never a picture of it. Find the header row, the units in the names and the merged ranges. D057: 164 of 164 cells; without the merged ranges, 130.
• Every cell keeps its place: a page and a box in points, or a sheet and an address such as Prices!E6.
• tables.py needs ruled tables. A table without lines needs another method, checked against known rows.

Next, you give every value a record of where it came from, and draw it back on the page.

Next: Provenance representation