bfb_delivery.lib.formatting package

Submodules

bfb_delivery.lib.formatting.data_cleaning module

Data cleaning utilities.

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_address(df)

Format the address column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_box_type(df)

Format the box type column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_driver(df)

Format the driver column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_email(df)

Format and validate the email column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_name(df)

Format the name column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_names_base(df, column)

Format a column with names.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_names_title(df, column)

Format a column with names.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_names_to_upper(df, column)

Format a column with names.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_neighborhood(df)

Format the neighborhood column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_order_count(df)

Format the order count column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_phone(df)

Format and validate the phone column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_product_or_box_type(df, column)

Format the box type column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_product_type(df)

Format the box type column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_and_validate_stop_no(df)

Format the stop number column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_int(df, column)

Basic formatting for an integer column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_notes(df)

Format the notes column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._format_string(df, column)

Basic formatting for a string column. Note: Casts to string.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._validate_col_not_empty(df, column)

No nulls or empty strings in column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._validate_greater_than_zero(df, column)

Validate column is greater than zero.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._validate_order_count(df)

Validate the order count column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning._validate_stop_no(df)

Validate the stop number column.

Return type:

None

bfb_delivery.lib.formatting.data_cleaning.format_and_validate_data(df, columns)

Clean, format, and validate selected columns in a DataFrame.

Operates in place.

Parameters:

  • df (DataFrame) – The DataFrame to clean.

  • columns (list[str]) – The columns to clean.

Return type:

None

Returns:

None

Raises:

  • ValueError – If columns are not found in the DataFrame.

  • ValueError – If no formatter is found for a column.

bfb_delivery.lib.formatting.data_cleaning.format_column_names(columns)

Clean column names.

Just strips whitespace for now.

Parameters:

columns (list[str]) – The column names to clean.

Return type:

list[str]

Returns:

The cleaned column names.

bfb_delivery.lib.formatting.sheet_shaping module

Functions for shaping and formatting spreadsheets.

bfb_delivery.lib.formatting.sheet_shaping._add_aggregate_block(ws, agg_dict, sheet_name)

Append left and right aggregation blocks to the worksheet row by row.

Return type:

int

bfb_delivery.lib.formatting.sheet_shaping._add_header_row(ws)

Append a reusable formatted row to the worksheet.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._aggregate_route_data(df, extra_notes_df)

Aggregate data for a single route.

Parameters:

  • df (DataFrame) – The route data to aggregate.

  • extra_notes_df (DataFrame) – Extra notes to include in the manifest if tagged.

Return type:

dict[str, Any]

Returns:

Dictionary of aggregated data.

bfb_delivery.lib.formatting.sheet_shaping._append_extra_notes(ws, extra_notes)

Append extra notes to the worksheet.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._auto_adjust_column_widths(ws, df_start_row)

Auto-adjust column widths to fit the dataframe.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._get_driver_sets(drivers, n_books, book_one_drivers_file)

Split drivers into n_books sets.

Return type:

list[list[str]]

bfb_delivery.lib.formatting.sheet_shaping._get_left_block(date, driver_name, agg_dict)
Return type:

list[list[dict[str, None]] | list[dict[str, str]]]

bfb_delivery.lib.formatting.sheet_shaping._get_right_block(thin_border, agg_dict)
Return type:

list[list[dict]]

bfb_delivery.lib.formatting.sheet_shaping._group_numbered_drivers(driver_sets)

Merge drivers with numbers into a single set.

Return type:

list[list[str]]

bfb_delivery.lib.formatting.sheet_shaping._make_manifest_sheet(wb, agg_dict, route_df, sheet_name, sheet_idx)

Create a manifest sheet.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._merge_and_wrap_neighborhoods(ws, neighborhoods_row_number)

Merge the neighborhoods cell and wrap the text.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._move_book_one_drivers_to_front(drivers, book_one_drivers_file)

Move book one drivers to the front of the list.

Return type:

list[str]

bfb_delivery.lib.formatting.sheet_shaping._split_driver_list(drivers, n_books)

Split drivers into n_books sets, in order passed.

Return type:

list[list[str]]

bfb_delivery.lib.formatting.sheet_shaping._word_wrap_column(ws, start_row, end_row, col_letter, width)

Word wrap column, and set width.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._word_wrap_columns(ws)

Word wrap the notes column, and set width.

Return type:

None

bfb_delivery.lib.formatting.sheet_shaping._write_data_to_sheet(ws, df)

Write and format the dataframe itself.

Return type:

int

bfb_delivery.lib.formatting.sheet_shaping.combine_route_tables(input_dir, output_dir, output_filename)

Combines the driver route CSVs into a single workbook.

This is used after optimizing and exporting the routes to individual CSVs. It prepares the worksheets to be formatted with bfb_delivery.api.public.format_combined_routes().

If output_dir is specified, will create the directory if it doesn’t exist.

Note

Changes “Product Type” column name back to “Box Type”.

See Combine Driver Route Tables into a Single Workbook for more information.

Parameters:

  • input_dir (Path | str) – The directory containing the driver route CSVs.

  • output_dir (Path | str) – The directory to write the output workbook to. Empty string (default) saves to the input_dir directory.

  • output_filename (str) – The name of the output workbook. Empty string (default) will name the file ‘combined_routes_{date}.xlsx’.

Raises:

ValueError – If input_paths is empty.

Return type:

Path

Returns:

The path to the output workbook.

bfb_delivery.lib.formatting.sheet_shaping.create_manifests(input_dir, output_dir, output_filename, extra_notes_file)

From Circuit route CSVs, creates driver manifest workbook ready to print.

This is used after optimizing and exporting the routes to individual CSVs. Reads in driver route CSVs from input_dir and creates a formatted workbook with driver manifests ready to print, with headers, aggregate data, and color-coded box types. Each driver’s route is a separate sheet in the workbook.

The workbook is saved to output_dir with the name output_filename. Will create output_dir if it doesn’t exist.

Note

Uses the date of the front of each CSV name to set the manifest date field. I.e., each sheet should be named something like “08.08 Richard N”, and, e.g., this would set the manifest date field to “Date: 08.08”.

Just wraps bfb_delivery.api.public.combine_route_tables() and bfb_delivery.api.public.format_combined_routes(). Creates an intermediate output workbook with all routes combined, then formats it.

See Create Printable Manifests from Downloaded Optimized Routes for more information.

Parameters:

  • input_dir (Path | str) – The directory containing the driver route CSVs.

  • output_dir (Path | str) – The directory to write the formatted manifest workbook to. Empty string (default) saves to the input_dir directory.

  • output_filename (str) – The name of the output workbook.Empty string sets filename to “final_manifests_{date}.xlsx”.

  • extra_notes_file (str) – Path to the extra notes file. If empty (default), uses a constant DataFrame. See bfb_delivery.lib.constants.ExtraNotes.

Return type:

Path

Returns:

Path to the formatted manifest workbook.

bfb_delivery.lib.formatting.sheet_shaping.format_combined_routes(input_path, output_dir, output_filename, extra_notes_file)

Formats the combined routes table into driver manifests to print.

Adds headers and aggregate data. Color codes box types.

This is used after combining the driver route CSVs into a single workbook using bfb_delivery.api.public.combine_route_tables().

If output_dir is specified, will create the directory if it doesn’t exist.

Note

Uses the date of the front of each sheet name to set the manifest date field. I.e., each sheet should be named something like “05.27 Oscar W”, and, e.g., this would set the manifest date field to “Date: 05.27”.

See Format Manifests for more information.

Parameters:

  • input_path (Path | str) – The path to the combined routes table.

  • output_dir (Path | str) – The directory to write the formatted table to. Empty string (default) saves to the input path’s parent directory.

  • output_filename (str) – The name of the formatted workbook. Empty string (default) will name the file “formatted_routes_{date}.xlsx”.

  • extra_notes_file (str) – The path to the extra notes file. If empty (default), uses a constant DataFrame. See bfb_delivery.lib.constants.ExtraNotes.

Return type:

Path

Returns:

The path to the formatted table.

bfb_delivery.lib.formatting.sheet_shaping.split_chunked_route(input_path, output_dir, output_filename, n_books, book_one_drivers_file, date)

Split route sheet into n workbooks with sheets by driver.

Sheets by driver allows splitting routes by driver on Circuit upload. Multiple workbooks allows team to split the uploads among members, so one person doesn’t have to upload all routes. This process follows the “chunking” process in the route generation, where routes are split into smaller “chunks” by driver (i.e., each stop is labeled with a driver).

Reads a route spreadsheet at input_path. Writes n_books Excel workbooks with each sheet containing the stops for a single driver. Writes adjacent to the original workbook unless output_dir specified. If specified, will create the directory if it doesn’t exist.

Note

Renames “Box Type” column name to “Product Type”, per Circuit API.

Note

The date passed sets the date in the sheet names of the output workbooks, and that date in the sheet name is used for the manifest date field in later functions that make the manifests: bfb_delivery.api.public.format_combined_routes() and bfb_delivery.api.public.create_manifests_from_circuit() (which wraps the former).

See Split Chunked Route Sheet into Multiple Files for more information.

Parameters:

  • input_path (Path | str) – Path to the chunked route sheet that this function reads in and splits up.

  • output_dir (Path | str) – Directory to save the output workbook. Empty string saves to the input input_path directory.

  • output_filename (str) – Name of the output workbook. Empty string sets filename to “split_workbook_{date}_{i of n_books}.xlsx”.

  • n_books (int) – Number of workbooks to split into.

  • book_one_drivers_file (str) – Path to the book-one driver’s file. If empty, uses a constant list. See bfb_delivery.lib.constants.BookOneDrivers.

  • date (str) – The date to use in the output workbook sheetnames. Empty string (default) uses the soonest Friday.

Raises:

  • ValueError – If n_books is less than 1.

  • ValueError – If n_books is greater than the number of drivers in the input workbook.

Return type:

list[Path]

Returns:

Paths to the split chunked route workbooks.

bfb_delivery.lib.formatting.utils module

Utility functions for the formatting module.

bfb_delivery.lib.formatting.utils.get_book_one_drivers(file_path)

Get the drivers from the book-one driver’s file, or the constant if no path.

Parameters:

file_path (str) – Path to the book-one driver’s file. If empty, uses a constant list.

Return type:

list[str]

Returns:

The drivers to include in book one of split chunked routes.

bfb_delivery.lib.formatting.utils.get_extra_notes(file_path)

Get the extra notes from the file, or the constant if no path.

Parameters:

file_path (str) – Path to the extra notes file. If empty, uses a constant DataFrame.

Return type:

DataFrame

Returns:

The extra notes to include in the combined routes.

bfb_delivery.lib.formatting.utils.get_phone_number(key, config_path='config.ini')

Get the phone number from the config file.

Parameters:

  • key (str) – The key in the config file.

  • config_path (str) – The path to the config file.

Return type:

str

Returns:

The phone number.

bfb_delivery.lib.formatting.utils.map_columns(df, column_name_map, invert_map)

Map column names in a DataFrame.

Operates in place.

Parameters:

  • df (DataFrame) – The DataFrame to map.

  • column_name_map (dict[str, str]) – The mapping of column names.

  • invert_map (bool) – Whether to invert the mapping.

Return type:

None

Module contents

Formatting module.