pyBOM

A Python program for flattening a layered bill-of-material (BOM) based on Excel
files. Part quantities are combined and a total quantity or
minimum-required-package-to-buy amount is calculated, in addition to extended
costs. A tree structure of the BOM hierarchy can also be created and converted
to DOT syntax for further graphics generation.

Motivation

The main problem solved is to combine identical parts from various
sub-assemblies and locations in your product BOM. Additionally, it is to be used
with Excel since Excel is common, easy, and does not require a separate program
or server to run. Flattening tells you the total QTY of a part when it may be
used in many sub-assemblies and levels in your product structure. This is
necessary to calculate the total QTY of a part and therefore determine the
mininum packages of the product to buy, since many parts come in packs greater
than QTY 1.

Structure

BOMs are created by storing parts and assemblies in Excel files.

In a separate directory, put an Excel file named Parts list.xlsx to serve as
the master parts list “database”. Then, each additional assembly is described
by a separate .xlsx file. Thus you might have:

my_project/
   Parts list.xlsx     <-- master parts list
   SKA-100.xlsx        <-- top level/root assembly
   TR-01.xlsx          <-- subassembly
   WH-01.xlsx          <-- subassembly

Root and sub-assemblies are inferred from item number relationships and do not
have to be explicitly identified.

Parts list.xlsx serves as the single point of reference for part information.
For example, it may have the following:

PN Name Description Cost Item Supplier Supplier PN Pkg QTY Pkg Price
SK1001-01 Bearing Wheel bearing part XYZ Bearing Co. 74295-942 1 2.99
SK1002-01 Board Standard type 13.42 part
SK1003-01 Truck half Truck fixed part Skatr Dude Inc. TR1-A 1 9.87
SK1004-01 Truck half Truck movable part Skatr Dude Inc. TR1-B 1 12.25
SK1005-01 Truck screw 1/4-20 SHCS part Bolts R Us 92220A 50 12.86
SK1006-01 Wheel Hard clear urethane part Skatr Dude Inc. WHL-PRX 4 9.87
SK1007-01 Nut 1/4-20 Hex nut part Bolts R Us 95479A 50 4.88

For each assembly, all that is required is the part identification number and
quantity which correspond to the following fields:

  • PN
  • QTY

Example:

PN QTY
SK1003-01 1
SK1004-01 1

Certain fields are used in calculating totals, such as in BOM.BOM.summary,
which are:

Pkg QTY
: The quantity of items in a specific supplier SKU (i.e. a bag of 100 screws)

Pkg Price
: The cost of a specific supplier SKU

Usage

After installing, (i.e. pip install .), import:

import pyBOM

Create a folder and create the necessary files (parts list and assemblies as
individual Excel files (the file name becomes the assembly item number by
default). Then, in a script call class method from_folder to instantiate the
BOM structure and return the top-level bill-of-material:

import pyBOM
bom = pyBOM.BOM.from_folder('Example')

Then, call methods or properties on BOM objects to obtain derived information:

BOM.parts
: Get a list of all direct-child parts

>>> print(bom.parts)
[Part SK1002-01, Part SK1005-01, Part SK1007-01]

BOM.assemblies
: Get a list of all direct-child assemblies

>>> print(bom.assemblies)
[WH-01, TR-01]

BOM.quantities
: Get the quantity of each direct-child part in the BOM

>>> print(bom.quantities)
{Part SK1002-01: 1, Part SK1005-01: 1, Part SK1007-01: 3, Part SK1006-01: 1,
Part SK1001-01: 1, Part SK1003-01: 1, Part SK1004-01: 1}

BOM.aggregate
: Get the aggregated quantity of each part/assembly from the current
BOM level down

>>> print(bom.aggregate)
{Part SK1002-01: 1, Part SK1005-01: 8, Part SK1007-01: 14, Part SK1006-01: 8,
Part SK1001-01: 4, Part SK1003-01: 2, Part SK1004-01: 2}

BOM.summary
: Get a summary in the form of a DataFrame containing the master parts
list with each item’s aggregated quantity and the required packages
to buy if the Pkg QTY field is not 1.

>>> print(bom.summary)
        PN         Name          Description  ...  Total QTY Purchase QTY Subtotal
0  SK1001-01      Bearing        Wheel bearing  ...          4            4    11.96
1  SK1002-01        Board        Standard type  ...          1            1    13.42
2  SK1003-01   Truck half          Truck fixed  ...          2            2    19.74
3  SK1004-01   Truck half        Truck movable  ...          2            2    24.50
4  SK1005-01  Truck screw          1/4-20 SHCS  ...          8            1    12.86
5  SK1006-01        Wheel  Hard clear urethane  ...          8            2    19.74
6  SK1007-01          Nut       1/4-20 Hex nut  ...         14            1     4.88

BOM.tree
: Return a string representation of the BOM tree hierarchy

>>> print(bom.tree)
SKA-100
├── Part SK1002-01    
├── WH-01
│   ├── Part SK1006-01
│   ├── Part SK1001-01
│   └── Part SK1007-01
├── TR-01
│   ├── Part SK1003-01
│   ├── Part SK1004-01
│   └── Part SK1007-01
├── Part SK1005-01
└── Part SK1007-01

Calling this on child assemblies shows the tree from that reference point:

>>> sa = bom.assemblies[0]
>>> sa
WH-01
>>> print(sa.tree)
WH-01
├── Part SK1006-01
├── Part SK1001-01
└── Part SK1007-01

Command Line

Some quick functionality is extended to the command line via python module mode:

> python -m pyBOM FOLDER ACTION

Where ACTION is what to do and is just a property call on the resulting
top-level BOM:

> python -m pyBOM Example tree
SKA-100
├── Part SK1002-01
├── WH-01
│   ├── Part SK1006-01
│   ├── Part SK1001-01
│   └── Part SK1007-01
├── TR-01
│   ├── Part SK1003-01
│   ├── Part SK1004-01
│   └── Part SK1007-01
├── Part SK1005-01
└── Part SK1007-01

Dependencies

  • pandas
  • anytree

GitHub

View Github