← back
Vibe Coding, Hierarchy from Formulas
Created on: 2025-11-25
We are giving an subset of an Excel table and our objective is to "extract" a hierarchy relationship among rows based on provided formulas.
The input is an Nx4 table with the following columns:
- Column A: contains the original row number of each sheet (1, 15, 26, etc)
- Column B: contains the "name" or "content" of the row.
- Column C: contains a numeric value for the row "value"
- Column D: contains a formula used to calculate the numeric value, or is blank if no formula was involved.
We will assume that formulas will involve nested operations, ranges (within the same sheet), and sums.
Example formulas:
- =SUM(A1:A10)
- =ROUND(SUM(B1:B3) + B7, 1)
Hierarchy Definition
We say that Row X is the parent of Row Y if Y is included in Row X formula. Formulas might use different columns A1:A10, C5 - C6. For hierarchy purposes, we only care about rows, and can disregard the column name. If Row X has parents Row Y and Row Z but Row Y is the children of Row Z, then Row X has parent Row Y and Row Y keeps Row Z as it parent. A row can have only one parent, if multiple parents are found, and there is no relationship between them (as above), we should raise an error.
Examples:
- If
Row X formula is =SUM(A1:A5) - A11, rows 1, 2, 3, 4, 5, and 11 are children of row X.
- If
Row 10 formula is =SUM(C1:C10) and Row 20 formula is =SUM(C1:C20) then: Row 3 is a children of Row 10 and Row 10 is a children of Row 20.
Output
The output should be a Nx1 table where each row has the row number of the parent, or is empty if no parent was found.
Implementation Details
- Have a function that given a formula, calculates all of its children (1 function for this)
- Map each row number to a list of all of its children
- Propagate potential parents to each row (1 function for this)
- Resolve any potential "multi-parent", start for the "top of the hierarchy" to prevent issues.
Return the list of parents (if any) for each row.
We expect this to be wrapped as a single function: "detect_hierarchies_from_formula"
OBS: Beware of mixing "the row number column" with the actual row number, which are potentially different!
Testing Guidelines
- Create 2 sample tables, one with 5 rows, another with 10. Both should have formulas with mixed letters (B1:B5) and (D7 + D8) in the same table.
- One table should have a children with 2 parents, but those parents should be one children of another. Therefore, a solution should exist.
- Use pytest and place the tests in tests/transformations/detect_hierarchy
- Don't mock any of the functions.
After the fact notes
- Flew to close to the sun, kept asking things that were not very well specified in the TASK above and started making errors
- For some reason Claude tried to use a subagent that hid most of the thinking. Not great.
- Generating formulas for testing caused some issues with pandas not reading them properly.
- Overall, should not have tried to continue going and instead close this as a standalone PR.