Build, Optimize, and Distribute Circuit Routes from Chunked Routes Sheet¶
Once you have a large single-route sheet labeled by driver, representing individual driver routes, you need to upload them to Circuit, optimized them, distribute them to driver apps, and produce driver manifests ready to print, with headers, aggregate data, and color-coded box types. You can do this all at once with the build_routes_from_chunked tool.
This tool replaces the manual tasks of:
Splitting the single route grouped by driver into individual Excel worksheets for each driver, grouped into a handful of workbooks for each staff member to upload to Circuit.
Uploading to Circuit, mapping fields and routes to drivers, optimizing routes, and distributing them to driver apps.
Downloading the optimized routes, copying each route into a single workbook, running an Excel macro, and finishing up with some manual steps.
Important
Before using this tool, you need to log in to Circuit and activate the drivers you want to assign routes to.
Python API documentation at bfb_delivery.api.public.build_routes_from_chunked().
CLI documentation at CLI.
Usage¶
You call build_routes_from_chunked with the input_path of the chunked spreadsheet, along with any other optional arguments. It will then prompt you to confirm driver assignments to each route. The tool then runs for up to a minute per route and finally prints the filepath to the final manifest.
In Python:
from bfb_delivery import build_routes_from_chunked
build_routes_from_chunked(input_path="path/to/master_chunked_sheet.xlsx")
With CLI:
build_routes_from_chunked --input_path "path/to/master_chunked_sheet.xlsx"
The function will return the filepath to the final manifest. If you’re using the CLI, the filepath will print to the console.
Note
This takes up to a minute per route to run as it uploads the routes to Circuit, optimizes them, and downloads the optimized routes. So, for a typical number of routes, it will take about 30 minutes.
Examining output¶
Below is an example with three routes (usually about 40-50 routes). The tool will prompt you to assign drivers to each route, e.g. “Enter the number of the driver for ‘02.14 Eric’(ctl+c to start over):”. You use the driver numbers printed to the console to assign drivers to each route, 48 in this example (see input below). The tool will then run for up to a minute per route and finish successfully by printing the filepath to the final manifest.
Note
Notice how the driver status is printed with the driver. The tool will not allow you to assign an inactive driver to a route, re-prompting you if you do enter an inactive driver. If you need to assign an inactive driver, you will need to activate them in Circuit first.
(bfb_delivery_py3.12) MacBook-Pro:bfb_delivery me$ build_routes_from_chunked --input_path .test_data/scratch/test_master_chunked_sample.xlsx --output_dir .test_data/scratch/
2025-02-12 17:49:38,585 - INFO - Writing split chunked workbooks to /Users/me/repos/bfb_delivery/.test_data/scratch/split_chunked
2025-02-12 17:49:38,620 - INFO - Getting all drivers from Circuit ...
2025-02-12 17:49:39,568 - INFO - Finished getting drivers.
1. Active: Hank Hill hank@propane.com
... (more drivers) ...
84. Active: Nosferatu pinebox@bfb.com
Using the driver numbers above, assign drivers to each route:
Route 02.14 Eric:
Best guesses:
48. Active: Eric Cartman emoney@southpark.com
Enter the number of the driver for '02.14 Eric'(ctl+c to start over):48
Assigned 02.14 Eric to Eric Cartman.
Route 02.14 Hank:
Best guesses:
1. Active: Hank Hill hank@propane.com
23. Inactive: Hankie Who hankie@realtalk.com
Enter the number of the driver for '02.14 Hank'(ctl+c to start over):1
Assigned 02.14 Hank to Hank Hill.
Route 02.14 Nos:
Best guesses:
84. Active: Nosferatu pinebox@bfb.com
Enter the number of the driver for '02.14 Nos'(ctl+c to start over):84
Assigned 02.14 Nos to Nosferatu.
02.14 Eric: Eric Cartman, emoney@southpark.com
02.14 Hank: Hank Hill, hank@propane.com
02.14 Nos: Nosferatu pinebox@bfb.com
Confirm the drivers above? (y/n): y
2025-02-12 17:50:16,179 - INFO - Initializing plans ...
2025-02-12 17:50:18,067 - INFO - Finished initializing plans. Initialized 3 plans.
2025-02-12 17:50:18,126 - INFO - Uploading stops. Allow 6+ seconds per plan ...
2025-02-12 17:50:44,995 - INFO - Finished uploading stops. Uploaded 50 stops for 3 plans.
2025-02-12 17:50:45,023 - INFO - Initializing route optimizations. Allow 20+ seconds per plan ...
2025-02-12 17:51:49,759 - INFO - Finished initializing route optimizations for 3 plans.
2025-02-12 17:51:49,782 - INFO - Confirming optimizations have finished ...
2025-02-12 17:51:50,980 - INFO - Finished optimizing routes. Optimized 3 routes.
2025-02-12 17:51:51,019 - INFO - Distributing routes ...
2025-02-12 17:51:53,200 - INFO - Finished distributing routes for 3 plans.
2025-02-12 17:51:53,222 - INFO -
route_title initialized writable stops_uploaded optimized distributed
0 02.14 Eric True True True True True
1 02.14 Hank True True True True True
2 02.14 Nos True True True True True
Plans attempted: 3
Plans initialized: 3
Plans with stops: 3
Plans optimized: 3
Plans distributed: 3
2025-02-12 17:51:53,231 - INFO - Getting route plans from Circuit ...
2025-02-12 17:51:53,692 - INFO - Finished getting route plans.
2025-02-12 17:51:53,711 - INFO - Getting stops from Circuit ...
2025-02-12 17:51:57,597 - INFO - Finished getting stops.
2025-02-12 17:51:57,629 - WARNING - Missing neighborhood for 50 stops. Imputing best guesses from Circuit-supplied address.
2025-02-12 17:51:57,655 - WARNING - Output directory exists /Users/me/repos/bfb_delivery/.test_data/scratch/routes_2025-02-14. Overwriting.
2025-02-12 17:51:57,655 - INFO - Writing route CSVs to /Users/me/repos/bfb_delivery/.test_data/scratch/routes_2025-02-14
2025-02-12 17:51:57,676 - INFO - Writing combined routes to /Users/me/repos/bfb_delivery/.test_data/scratch/combined_routes_20250212.xlsx
2025-02-12 17:51:57,711 - INFO - Writing formatted routes to /Users/me/repos/bfb_delivery/.test_data/scratch/final_manifests_20250212.xlsx
2025-02-12 17:51:57,715 - INFO - Formatted workbook saved to:
/Users/me/repos/bfb_delivery/.test_data/scratch/final_manifests_20250212.xlsx
(bfb_delivery_py3.12) MacBook-Pro:bfb_delivery me$
Attention
Pay attention to the output, especially in the middle once optimization/distribution is complete. There’s a summary of the number of plans attempted, initialized, with stops, optimized, and distributed. Just above that is a table of the routes and their statuses, True indicating success for a route at a given step, False indicating failure. You can also find the table as a CSV file in a subdirectory of the output directory, {output_dir}/plans/plans.csv. If any of the routes are not successul at a step, the tool will warn you and skip them on all following steps. You will want to investigate why and run that route the rest of the way from the last successful step, manually or with the underlying tools (see Note on tools this tool wraps below).
Note that the filepaths to some intermediate files will print to the console as well, before finally printing the filepath to the final manifest workbook. In addition to the final manifest, there will be:
An Excel workbook of the pre-optmized routes split into separate spreadsheets that you could upload manually. In the example:
2025-02-12 17:49:38,585 - INFO - Writing split chunked workbooks to /Users/me/repos/bfb_delivery/.test_data/scratch/split_chunkedOptmized route CSVs downloaded from Circuit that you could combine into a single Excel workbook. In the example:
2025-02-12 17:51:57,655 - INFO - Writing route CSVs to /Users/me/repos/bfb_delivery/.test_data/scratch/routes_2025-02-14An unformatted Excel workbook of all the routes that you could run the old macro on to produce the final manifests. In the example:
2025-02-12 17:51:57,676 - INFO - Writing combined routes to /Users/me/repos/bfb_delivery/.test_data/scratch/combined_routes_20250212.xlsx
This can be helpful if you need to revert to the old method for one of the steps for some reason.
Reviewing final manifests¶
You should review the manifests before printing them, as you may want to make some final touchups, like adjusting row heights or column widths, or adding notes. These final touchups are slated to be added to the tool in the future.
Optional arguments¶
You can use optional arguments to specify a few things about the manifest workbook. Use –help to see all the optional arguments in the CLI.
build_routes_from_chunked --help
Output directory¶
Use the optional argument output_dir to specify the directory to save the workbook file in. The default if not passed is a new directory in the present working directory, named “deliveries_{date}”.
In Python:
build_routes_from_chunked(
input_path="path/to/master_chunked_sheet.xlsx",
output_dir="path/to/output_dir/",
)
With CLI:
build_routes_from_chunked --input_path "path/to/master_chunked_sheet.xlsx" --output_dir "path/to/output_dir/"
Start date¶
Use the optional argument start_date to specify the beginning of the date range to search Circuit for routes. The default if not passed in is the soonest Friday.
from bfb_delivery import build_routes_from_chunked
build_routes_from_chunked(
input_path="path/to/master_chunked_sheet.xlsx",
start_date="1947-10-14",
)
With CLI:
build_routes_from_chunked --input_path "path/to/master_chunked_sheet.xlsx" --start_date 1957-10-04
Skip distribution¶
By default, the tool will distribute the routes to the driver apps. Use the optional argument no_distribute to skip this.
In Python:
build_routes_from_chunked(
input_path="path/to/master_chunked_sheet.xlsx",
no_distribute=True,
)
With CLI:
build_routes_from_chunked --input_path "path/to/master_chunked_sheet.xlsx" --no_distribute
Verbose output¶
Use the optional argument verbose to print more information to the console. This can be useful for debugging, but it is pretty noisy.
In Python:
build_routes_from_chunked(
input_path="path/to/master_chunked_sheet.xlsx",
verbose=True,
)
With CLI:
build_routes_from_chunked --input_path "path/to/master_chunked_sheet.xlsx" --verbose
Supplying extra notes¶
Use the optional argument extra_notes_file to specify a CSV file with extra notes to include in the manifest. The CSV file should have two columns: tag and note. The tag is the text (usually asterisked) that appears in the standard notes field for a delivery. The note is then added to the bottom of the manifest with the tag. For example:
tag,note
Cedarwood Apartments special instructions *,Please call the recipient when you arrive.
This file will put the note “Please call the recipient when you arrive.” at the bottom of the manifest (once) if any stops have a note that contains the text “Cedarwood Apartments special instructions *”.
If you don’t provide extra_notes_file provide, the tool will use the constant notes in the codebase: bfb_delivery.lib.constants.ExtraNotes (currently empty).
In Python:
build_routes_from_chunked(
input_path="path/to/master_chunked_sheet.xlsx",
extra_notes_file="path/to/extra_notes.csv",
)
With CLI:
build_routes_from_chunked --input_path "path/to/master_chunked_sheet.xlsx" --extra_notes_file path/to/extra_notes.csv
Note on tools this tool wraps¶
build_routes_from_chunked wraps other tools that run each segment of the pipeline. First it runs split_chunked_route to split the master chunked sheet into spreadsheets for each route in a single workbook before uploading. It then runs create_manifests_from_circuit, so you don’t have to download and move files around and format them. create_manifests_from_circuit actually wraps create_manifests, which in turn wraps two other tools, combine_route_tables and format_combined_routes into one tool. You can still use any of those tools if you wish, but you can instead just use build_routes_from_chunked. Calling these intermediate tools from build_routes_from_chunked wasn’t necessary, but it was convenient to develop that way and has the added benefit of producing intermediate files at each step that you can use if you need to revert to the old method for some of the steps for some reason (say there was an error in one of the route optimizations and you want to retry it without running all of them again).
graph TD;
A[**build_routes_from_chunked**] --> B[**split_chunked_route**]
B --> B1[Splits master chunked sheet into individual driver route sheets.]
A --> C[Uploads routes to Circuit, optimizes, and distributes to drivers.]
A --> D[**create_manifests_from_circuit**]
D --> E[Gets routes from Circuit.]
D --> F[**create_manifests**]
F --> G[**combine_route_tables**]
F --> H[**format_combined_routes**]
G --> G1[Combines downloaded routes CSVs into a single workbook.]
H --> H1[Formats the combined routes into printable manifests.]
Subtools wrapped and alternatively available for use¶
For instance, say you’ve found a bug when using build_routes_from_chunked where the routes uploaded and optimized but for some reason didn’t produce the final printable manifest. You could try downloading the routes manually then running create_manifests, or downloading manually then running combine_route_tables and passing its output to format_combined_routes. For whichever of those steps fails, you can revert to using your old method, but you can still ostensibly use a tool for the other piece that didn’t fail. For example, say combine_route_tables ran fine, but format_combined_routes threw an error, so you reverted to using the old Excel macro and manually formatting. See Split Chunked Route Sheet into Multiple Files, Create Printable Manifests from Optimized Routes from Circuit, Create Printable Manifests from Downloaded Optimized Routes, Combine Driver Route Tables into a Single Workbook and Format Manifests.
Most likely you’ll find that the tool works fine unless the underlying data schemata have changed, but it’s good to know you have options to explore instead of doing it all manually again.
See Also¶
The Delivery-Planning Workflow
Split Chunked Route Sheet into Multiple Files
Create Printable Manifests from Optimized Routes from Circuit
Create Printable Manifests from Downloaded Optimized Routes