Generate a summary of all parts and their costs given a prices.csv file and mfpns assigned on components

cost_rollup.py

import numpy as np
import pandas
import xml.etree.ElementTree as ET


def get_component_field(comp_el, field_name, default=None):
    field_el = comp_el.find("./fields/field[@name='{0}']".format(field_name))
    if field_el is not None:
        return field_el.text
    else:
        return default

# Find all parts
prices = pandas.read_csv('prices.csv', index_col=0)

tree = ET.parse('design.xml')
root = tree.getroot()

components = root.findall('./components/comp') 

parts_list = pandas.DataFrame(columns=['ref', 'mfpn'])

for c in components:
    data = {
        'ref': c.attrib['ref'],
        'mfpn': get_component_field(c, 'mfpn', 'unknown')
    }

    parts_list = parts_list.append([data], ignore_index=True)

parts_list = parts_list.sort('ref')
parts_list = parts_list.set_index('ref')

# Build the roll-up
parts = parts_list.groupby('mfpn').groups
rollup = pandas.DataFrame(columns=['mfpn', 'count', 'refs'])

for mfpn, refs in parts.iteritems():
    data = {
        'mfpn': mfpn,
        'count': len(refs),
        'refs': ', '.join(refs)
    }
    
    rollup = rollup.append(data, ignore_index=True)

rollup = rollup.set_index('mfpn')
rollup = rollup.join(prices)

rollup['total_1'] = rollup['price_1'] * rollup['count']
rollup['total_100'] = rollup['price_100'] * rollup['count']
rollup['total_1000'] = rollup['price_1000'] * rollup['count']

rollup.to_csv('rollup.csv')
rollup = rollup.sort('total_1000', ascending=False)
print rollup

print "Total parts count:     {0}".format(rollup['count'].sum())
print "BOM cost (1 unit):     {0}".format(rollup['total_1'].sum())
print "BOM cost (100 units):  {0}".format(rollup['total_100'].sum())
print "BOM cost (1000 units): {0}".format(rollup['total_1000'].sum())