<jupyter_start><jupyter_text>The script below creates a Supply Chain BOM based on Enginerring BOM using data from a out-dated supply chain BOM. It also enables the supply chain BOM to sync with the Engineering BOM. Developed by <EMAIL><jupyter_code>import pandas as pd
import numpy as np
import xlrd, openpyxl
import csv, datetime<jupyter_output><empty_output><jupyter_text>Load EBOM, Chi's Supply Chain BOM and Shaolong's BOM<jupyter_code>#Engineering BOM (EBOM). Updated weekly and has the most up-to-date Part Number and Revision. 
#Supply Chain BOM is based on EBOM with additional sourcing and supplier quality information.

EBOM = pd.read_excel("/home/yifei/Documents/BOM_tool/EBOM 8.23.18.xlsx")
# new_header = EBOM.iloc[0]
# EBOM = EBOM[1:]
# EBOM.columns = new_header
EBOM.columns = EBOM.columns.str.replace("\(R\)\ ", "") # trim (R) away from the header
print("Engineering VP BOM Shape: ",EBOM.shape)
EBOM.head(5)
# print(EBOM.columns.values)
# EBOM.shape




print(EBOM.columns.values)<jupyter_output>['Identifier' 'Title' 'Revision' 'Description' 'QTY' 'UOM'
 'Purchased Part Type' 'Maturity' 'Part Type' 'System' 'SubSystem'
 'Legacy Part Number' 'Legacy Part Revision' 'Configuration']<jupyter_text>Removing Phantom part and Fasterners and filter only "Buy" part leaves us 1461 Unique PNs<jupyter_code>EBOM_filtered = EBOM[(EBOM["Purchased Part Type"]=="Buy") & (EBOM["Part Type"]!="Phantom") & (EBOM["System"]!="Y Fasteners")]
print(EBOM_filtered.shape)

#Chi's SCBOM. We need the column names from Chi's SCBOM
SCBOM_chi = pd.read_excel("/home/yifei/Documents/BOM_tool/SCBOM.xlsx")
print("Supply Chain BOM Shape: ",SCBOM_chi.shape)
SCBOM_chi.head(20)

SCBOM_chi.columns
PreAP_BOM_chi = SCBOM_chi[SCBOM_chi["Program"]=="PAP"]
print("Pre AP BOM shape: ", PreAP_BOM_chi.shape)
AP_BOM_chi = SCBOM_chi[SCBOM_chi["Program"]=="AP"]
print("AP BOM shape: ", AP_BOM_chi.shape)
VP_BOM_chi = SCBOM_chi[SCBOM_chi["Program"]=="VP"]
print("VP BOM shape: ", VP_BOM_chi.shape)

#Active VP BOM with latest part revision
VP_BOM_Activate_chi = VP_BOM_chi[(VP_BOM_chi["PartActive"]=="Active")]
print("VP Active BOM: ", VP_BOM_Activate_chi.shape)

VPM_BOM_Activate_chi_removePhantomPart = VP_BOM_Activate_chi[VP_BOM_Activate_chi["Type"]!="Phantom"]
print("removed phatom", VPM_BOM_Activate_chi_removePhantomPart.shape)

                                                   

#Load shaolong's SC BOM which has the most sourcing information updated by buyers and SQEs. 
SCBOM_shaolong = pd.read_excel("/home/yifei/Documents/BOM_tool/Shaolong BOM.xlsx", index_col=None)
# SCBOM_shaolong.reset_index()
SCBOM_shaolong
SCBOM_shaolong.columns.values
print("Supply Chain BOM Shape: ",SCBOM_shaolong.shape)<jupyter_output>Supply Chain BOM Shape:  (1516, 46)<jupyter_text>Step 0: Create a blank Supply Chain BOM using EBOM<jupyter_code># copy EBOM to the new SCBOM
print("EBOM shape is: ", EBOM.shape)
SCBOM = EBOM.copy()
print("SCBOM shape is: ", SCBOM.shape)
#specify SCBOM columns
SCBOM.columns.values
ebom_column_header = EBOM.columns.values

sourcing_column_header = ["Commodity Package", "Engineer", "GSM", "Senior GSM", "RFQ Sent", "RFQ Received", 
                          "Sourcing Council Scheduled Date", "Sourcing Council Aproved Date", 
                          "Supplier Name", "Sourcing Status", "Supplier Category", "Supplier Contact Person",
                          "Supplier Contact Email", "Supplier Manufacturing Region", 
                          "Supplier Manufacturing Country", "Incoterm", "GTC", "LOI", "Purchase Contract", 
                          "JIS", "Tier", "Ship to", "Daily Production Capability","Order Leader Time ","MOQ",
                          "Purchae Requisition","Purchase Order", "First Pickup Date", "Last Pickup Date", 
                         ]

cost_column_header = ["Unit Price", "Tooling Cost", "ED&T","Oversea Pick Up Location","Package cost confirmed?",
                      "Transport Price quoted?","Total Investment","Currency",    
                     ]

logistics_column_header = ["HS code Export", "HS code Import", "Regulatory Requirements (Y/N)", "Logistic Planned (Y/N)", 
                          "Packaging Proposal Approved","EDI Setup"
                          ]

quality_column_header = ["SQE", "Priority", "APQP Kick Off", "APQP Status","Tooling Lead Time", 
                     "Tooling Kick Off Date", "Tooling Completion Date", "Home Line", "FTN", 
                     "Grain", "AAR", "Run At Rate", "PV Completion Date", "Phase 1 PSW Date", 
                     "Phase 3 PSW Date", "Comments"
                        ]

log_column_header = ["Engineering Release Estimtae", "Engineering Release Actual", "Assembly Station", 
                     "Maturity", "Configuration", "Part Creation Date", "Last Modified Date", "Part Status", 
                     "Part Active"
                    ]

column_header = sourcing_column_header + cost_column_header + logistics_column_header + quality_column_header + log_column_header
# add additional columns
def add_columns(df, columns):
    df2 = pd.DataFrame(columns=columns)
    df = df.append(df2, sort=False, verify_integrity=True)
    return df
SCBOM = add_columns(SCBOM, column_header)
SCBOM.shape<jupyter_output><empty_output><jupyter_text>Step 1: Update new SCBOM using Shaolong's BOM<jupyter_code>df = pd.DataFrame({'BoolCol': [True, False, False, True, True], 'Name': [1,2,3,4,5]},
       index=[10,20,30,40,50])
idx = df.index[df['BoolCol']]
idx
df.loc[idx[0]]["Name"]
df
df.loc[(df["Name"]==2) & (df["BoolCol"]==False)].index.values
df.loc[[10], ["Name"]] = df.loc[[20], 'Name'].values
df
# save dataframe to Excel
def save(df):
    #https://stackoverflow.com/questions/28837057/pandas-writing-an-excel-file-containing-unicode-illegalcharactererror
    df = df.applymap(lambda x: x.encode('unicode_escape').
                 decode('utf-8') if isinstance(x, str) else x)

    writer = pd.ExcelWriter('Supply Chain BOM.xlsx')
    df.to_excel(writer, sheet_name="Supply Chain BOM", na_rep="" )
    writer.save()

#     df.to_csv("Supply Chain BOM.csv",quoting=csv.QUOTE_NONE, escapechar="\\")
# search PN and Rev in shaolong's SCBOM
def search(PN):
    # shaolong's data doesn't have Rev.
#     df = SCBOM_shaolong.loc[(SCBOM_shaolong["BYTON P/N"]==PN) & (SCBOM_shaolong["BYTON REVISION"]==Rev)]
    df = SCBOM_shaolong.loc[(SCBOM_shaolong["BYTON P/N"]==PN)]
    index = df.index
    if (len(index)==1):
        return index
    else:
        #return the first index if duplicate PN found
        return index[0:1]
        
print(search("AA-AF003416NN-00"))
print(search("AA-YF000148NN-00"))
def replace_bracket(data):
    value = str(data).replace('[', '').replace(']', '').replace('\'', '')
    if value == 'nan':
        return ""
    return value

def change_datetime_to_string(data):
    if data.size == 0:
        return ""
    elif (data == np.array([' '])):
        return ""
    elif data == "nan":
        return ""
    else:
        return data[0]
    
#     if data == np.array([' ']):
#         return ""
#     if data == "nan":
#         return ""
#     else:
#         return data[0].strftime('%m/%d/%Y')
# copy Shaolong's BOM info and paste into SCBOM
def copy_and_paste_row(df1, index1, df2, index2):
    #sourcing
    df1.loc[index1,["Engineer"]] = replace_bracket(df2.loc[index2,["ENGINEER"]].values)
    df1.loc[index1,["GSM"]] = replace_bracket(df2.loc[index2,["GSM"]].values)
    df1.loc[index1,["Senior GSM"]] = replace_bracket(df2.loc[index2,["Senior GSM"]].values)
    df1.loc[index1,["Supplier Name"]] = replace_bracket(df2.loc[index2,["VP\nSUPPLIER\n(Name)"]].values)
    df1.loc[index1,["Sourcing Status"]] = replace_bracket(df2.loc[index2,["Supplier Sourced?"]].values)
    df1.loc[index1,["Supplier Contact Person"]] = replace_bracket(df2.loc[index2,["Supplier \nContact Person"]].values)
    df1.loc[index1,["Supplier Contact Email"]] = replace_bracket(df2.loc[index2,["Supplier \nContact E-mail"]].values)
    df1.loc[index1,["Supplier Manufacturing Country"]] = replace_bracket(df2.loc[index2,["Supplier Location"]].values)
    df1.loc[index1,["GTC"]] = replace_bracket(df2.loc[index2,["GTC & Contract"]].values)

    
    #Logistics
    
    #SQE
    df1.loc[index1,["SQE"]] = replace_bracket(df2.loc[index2,["SQE"]].values)
    df1.loc[index1,["Priority"]] = replace_bracket(df2.loc[index2,["SQE Priority?"]].values)
    df1.loc[index1,["APQP Kick Off"]] = replace_bracket(df2.loc[index2,["APQP \nKICK OFF"]].values)
    df1.loc[index1,["APQP Status"]] = replace_bracket(df2.loc[index2,["Unnamed: 33"]].values)
    df1.loc[index1,["Tooling Lead Time"]] = replace_bracket(df2.loc[index2,["Tooling\nLead time"]].values)
    df1.loc[index1,["Tooling Kick Off Date"]] = change_datetime_to_string(df2.loc[index2,["Tool Kick off Plan"]].values)
    df1.loc[index1,["Tooling Completion Date"]] = change_datetime_to_string(df2.loc[index2,["Hard tool Completion"]].values)
    df1.loc[index1,["Home Line"]] = change_datetime_to_string(df2.loc[index2,["Home line"]].values)
    df1.loc[index1,["FTN"]] = replace_bracket(df2.loc[index2,["FTN"]].values)
    df1.loc[index1,["Grain"]] = replace_bracket(df2.loc[index2,["Grain"]].values)
    df1.loc[index1,["FTN"]] = replace_bracket(df2.loc[index2,["FTN"]].values)
    df1.loc[index1,["AAR"]] = replace_bracket(df2.loc[index2,["AAR"]].values)
    df1.loc[index1,["Run At Rate"]] = change_datetime_to_string(df2.loc[index2,["Run at rate"]].values)
    df1.loc[index1,["PV Completion Date"]] = change_datetime_to_string(df2.loc[index2,["PV Completion"]].values)
    df1.loc[index1,["Phase 1 PSW Date"]] = change_datetime_to_string(df2.loc[index2,["Phase 1 PSW"]].values)
    df1.loc[index1,["Phase 3 PSW Date"]] = change_datetime_to_string(df2.loc[index2,["Phase 3 PSW"]].values)
    df1.loc[index1,["Comments"]] = replace_bracket(df2.loc[index2,["Comments"]].values)
    
    
    #log
#   log_column_header = ["Engineering Release Estimtae", "Engineering Release Actual", "Assembly Station", 
#                      "Maturity", "Configuration", "Part Creation Date", "Last Modified Date", "Part Status", 
#                      "Part Active"]
    df1.loc[index1,["Part Status"]] = "Latest revision"
    df1.loc[index1,["Part Active"]] = "Active"
        
        
    return df1
    
    
    
     
print(str(SCBOM_shaolong.loc[1000, ["GSM"]].values))
print(str(SCBOM_shaolong.loc[1000, ["GSM"]].values).replace('[', '').replace(']', '').replace('\'', ''))
SCBOM_shaolong.loc[:, ["Tool Kick off Plan"]].values
SCBOM_shaolong.loc[41, ["Tool Kick off Plan"]].values[0]
# update SCBOM with inputs from Shaolong's BOM
for index, row in SCBOM.iterrows():
    #loop through EBOM by PN
    PN = row["Title"]
    Rev = row["Revision"]
    #find out if this PN and Rev exist in Shaolong's BOM, and return the index
    index_shaolong = search(PN)
    #if not found
    if (index_shaolong.values.size == 0):
        #do nothing
        continue
    #if PN found, update SCBOM with Shaolong's BOM info
    else:
        #copy shaolong's BOM info to SCBOM
        copy_and_paste_row(SCBOM, index, SCBOM_shaolong, index_shaolong)


        
            
        
        
    
    
SCBOM.loc[[21]]
save(SCBOM)<jupyter_output><empty_output><jupyter_text>Create a new jupyter notebook for this step Step 2: Maintain and update SCBOM using updated EBOM We need to maintain the integrity of the EBOM, so everytime EBOM is updated, a new corresponding supply chain BOM is created with identifical information copied from EBOM. Then the supply chain BOM is updated with information from previous supply chain BOM, parts info whose PN and Rev are identical with EBOM will be copy and pasted in, parts that are no longer identifical will be marked as inactive and/or removed parts.<jupyter_code>import pandas as pd
import numpy as np
import xlrd, openpyxl
import csv, datetime
import time
EBOM_PATH = './Data/AA-ZZ000001NN-03_VP BoM_9-21-18 at 3.42 PM.xlsx'
SCBOM_PATH = './Data/Supply Chain BOM V2 (version 1).xlsx'
#load EBOM
def load():
    # load Engineering BOM
    EBOM = pd.read_excel(EBOM_PATH, sheet_name="BOM", skiprows=[0])
    EBOM.columns = EBOM.columns.str.replace("\(R\)\ ", "") # trim (R) away from the header
    EBOM = EBOM.reset_index(drop=True)
    # load Supply Chain BOM
    SCBOM = pd.read_excel(SCBOM_PATH, sheet_name="Supply Chain BOM")
    SCBOM = SCBOM.reset_index(drop=True)
    return EBOM, SCBOM

EBOM, SCBOM = load()
SCBOM_columns = SCBOM.columns.tolist()
SCBOM_columns_size = len(SCBOM_columns)
EBOM_columns = EBOM.columns.tolist()
EBOM_columns_size = len(EBOM_columns)
print("EBOM shape: ", EBOM.shape)
print("SCBOM shape: ", SCBOM.shape)
print("# of columns in EBOM: ", EBOM_columns_size)
print("# of columns in SCBOM: ", SCBOM_columns_size)
# create a updated SCBOM with columns from SCBOM and data from EBOM
SCBOM_updated = EBOM.copy()
for each in SCBOM.columns.tolist()[15:]:
    SCBOM_updated[each] = ""
SCBOM_updated.shape
# search PN and Rev in SCBOM
def search(df, PN, Rev):
    df = df.loc[(df["Title"]==PN) & (df["Revision"]==Rev)]
    index = df.index.values
    
    # only one entry found
    if (len(index)==1):
        return index   
    
    # multiple entries found
    elif (len(index)>1):
        #return the first index if duplicate PN and Rev found
        return index.tolist()   # returned indexes are a numpy.ndarray, we convert to a list and get the first value
    
    # no entry found
    else:
        return None
        
print(search(EBOM, 'AA-MB001705NN-00', 'D.1'))
print(len(search(EBOM, 'AA-MB001705NN-00', 'D.1')))
print(search(EBOM, 'AA-MB001703NN-00', 'B.1'))
print(len(search(EBOM, 'AA-MB001703NN-00', 'B.1'))
# def replace_bracket(data):
#     value = str(data).replace('[', '').replace(']', '').replace('\'', '')
#     if value == 'nan':
#         return ""
#     return value

# def change_datetime_to_string(data):
#     if data.size == 0:
#         return ""
#     elif (data == np.array([' '])):
#         return ""
#     elif data == "nan":
#         return ""
#     else:
#         return data[0]
    
# #     if data == np.array([' ']):
# #         return ""
# #     if data == "nan":
# #         return ""
# #     else:
# #         return data[0].strftime('%m/%d/%Y')
# # copy EBOM info and paste into SCBOM
# def copy_and_paste_row(df1, index1, df2, index2):
#     # copy df2 info into df1
#     df1.loc[index1,["Identifier"]] = replace_bracket(df2.loc[index2,["Identifier"]].values)
#     df1.loc[index1,["Title"]] = replace_bracket(df2.loc[index2,["Title"]].values)
#     df1.loc[index1,["Revision"]] = replace_bracket(df2.loc[index2,["Revision"]].values)
#     df1.loc[index1,["Description"]] = replace_bracket(df2.loc[index2,["Description"]].values)
#     df1.loc[index1,["QTY"]] = replace_bracket(df2.loc[index2,["QTY"]].values)
#     df1.loc[index1,["UOM"]] = replace_bracket(df2.loc[index2,["UOM"]].values)
#     df1.loc[index1,["Purchased Part Type"]] = replace_bracket(df2.loc[index2,["Purchased Part Type"]].values)
#     df1.loc[index1,["UOM"]] = replace_bracket(df2.loc[index2,["UOM"]].values)
#     df1.loc[index1,["Maturity"]] = replace_bracket(df2.loc[index2,["Maturity"]].values)
#     df1.loc[index1,["Part Type"]] = replace_bracket(df2.loc[index2,["Part Type"]].values)
#     df1.loc[index1,["System"]] = replace_bracket(df2.loc[index2,["System"]].values)
#     df1.loc[index1,["SubSystem"]] = replace_bracket(df2.loc[index2,["SubSystem"]].values)
#     df1.loc[index1,["Legacy Part Number"]] = replace_bracket(df2.loc[index2,["Legacy Part Number"]].values)
#     df1.loc[index1,["Legacy Part Revision"]] = replace_bracket(df2.loc[index2,["Legacy Part Revision"]].values)
#     df1.loc[index1,["Configuration"]] = replace_bracket(df2.loc[index2,["Configuration"]].values)

#     return df1
# copy EBOM info and paste into SCBOM
def copy_and_paste_row(df1, index1, df2, index2):  # index1 and index2 are int
    # copy df2 info into df1
    offset = df2.shape[1]
    for i in range(offset-1, len(df2.columns.tolist()), 1):
        df1.loc[index1, df1.columns.tolist()[i]] = df2.loc[index2, df2.columns.tolist()[i]]
        
    return df1
# save dataframe to Excel
def save(df):
    #https://stackoverflow.com/questions/28837057/pandas-writing-an-excel-file-containing-unicode-illegalcharactererror
    df = df.applymap(lambda x: x.encode('unicode_escape').
                 decode('utf-8') if isinstance(x, str) else x)

    writer = pd.ExcelWriter('Updated Supply Chain BOM.xlsx')
    df.to_excel(writer, sheet_name="Updated Supply Chain BOM", na_rep="" )
    writer.save()

#     df.to_csv("Supply Chain BOM.csv",quoting=csv.QUOTE_NONE, escapechar="\\")
# load EBOM and SCBOM
EBOM, SCBOM = load()

# create a updated SCBOM with columns from SCBOM and data from EBOM
SCBOM_updated = EBOM.copy()
for each in SCBOM.columns.tolist()[14:]:
    SCBOM_updated[each] = ""
SCBOM_updated.shape


EBOM.columns
SCBOM.columns.tolist()[14:]
# loop through SCBOM 
for index, row in SCBOM.iterrows():
    PN = row["Title"]
    Rev = row["Revision"]
#     Identifier = row["Identifier"] # same part could be structured differently but the part is the same, so if same part found at multiple places, we will just pick one

    # search if this part exists in SCBOM_updated, return the index in SCBOM_updated
    index_SCBOM_updated = search(SCBOM_updated, PN, Rev)
    print(index, index_SCBOM_updated)
a = pd.DataFrame({"A": [1,2,3], "B": [4,5,6]})
b = pd.DataFrame({"A": [7,8,9], "B": [0,1,2]})
# a.append(b.loc[0], ignore_index=True)
b.loc[0, "B"] = 1
b.loc[0]
a.append(b.loc[0], ignore_index=True)


# loop through SCBOM 

start_time = time.time()

# count how many new parts are added
# how many old parts are removed
removed_parts_count = 0
same_parts = 0

for index, row in SCBOM.iterrows():
    PN = row["Title"]
    Rev = row["Revision"]
    # same part could be structured differently but the part is the same, 
    # so if same part found at multiple places, we will just pick one
#     Identifier = row["Identifier"] 

    # search if this part exists in SCBOM_updated, return the index in SCBOM_updated
    # search() returned a list or None type
    index_SCBOM_updated = search(SCBOM_updated, PN, Rev)


    #not found, deactivate the part, then append this part to SCBOM_updated
    if (index_SCBOM_updated == None):
        SCBOM.loc[index, ["Part Active"]] = "Inactivate"
        SCBOM.loc[index, ["Part Status"]] = "Removed"
        SCBOM.loc[index, ["Part Creation Date"]] = datetime.date(2019, 9, 14)
        SCBOM.loc[index, ["Last Modified Date"]] = datetime.date(2019, 9, 28)
        SCBOM_updated = SCBOM_updated.append(SCBOM.loc[index], ignore_index=True)
        removed_parts_count = removed_parts_count + 1
        


    # found one entry, copy the information to updated SCBOM    
    elif (len(index_SCBOM_updated) == 1):
        index_SCBOM_updated = index_SCBOM_updated[0]
        SCBOM_updated = copy_and_paste_row(SCBOM_updated, index_SCBOM_updated, SCBOM, index)
        SCBOM_updated.loc[index_SCBOM_updated, ["Part Creation Date"]] = datetime.date(2019, 9, 21)
        SCBOM_updated.loc[index_SCBOM_updated, ["Part Status"]] = "Lateste Revision"
        SCBOM_updated.loc[index_SCBOM_updated, ["Part Active"]] = "Active"
        same_parts = same_parts + 1

    # found multiple entries, copy the information to each of the entries in SCBOM_updated
    else:
        for each in index_SCBOM_updated:
            SCBOM_updated = copy_and_paste_row(SCBOM_updated, each, SCBOM, index)
            SCBOM_updated.loc[index_SCBOM_updated, ["Part Creation Date"]] = datetime.date(2019, 9, 21)
            SCBOM_updated.loc[index_SCBOM_updated, ["Part Status"]] = "Lateste Revision"
            SCBOM_updated.loc[index_SCBOM_updated, ["Part Active"]] = "Active"
            same_parts = same_parts + 1


save(SCBOM_updated)

print("\nAfter Sync")
print("EBOM shape: ", EBOM.shape)
print("SCBOM shape: ", SCBOM.shape)
print("SCBOM_updated ", SCBOM_updated.shape)

# num_of_parts_added = len(SCBOM_updated[SCBOM_updated["Part Creation Date"]=='']) 
# num_of_parts_removed = SCBOM_updated.shape[0] - EBOM.shape[0]
# print("\n# of new parts added: ", num_of_parts_added)
# print("# of old parts removewd: ", num_of_parts_removed)

print("\n# of new parts added: ", EBOM.shape[0]-same_parts)
print("# of old parts removewd: ", removed_parts_count)

execution_time = time.time() - start_time
print("\nThis script took --- {} seconds ---".format(execution_time))<jupyter_output>After Sync
EBOM shape:  (4570, 15)
SCBOM shape:  (3164, 78)
SCBOM_updated  (5423, 79)

# of new parts added:  1508
# of old parts removewd:  853


This script took --- 39.83958721160889 seconds ---
{'fraction_non_alphanumeric': 0.14718698557180207, 'fraction_numerical': 0.022755882637745715, 'mean_word_length': 3.912009512485137, 'pattern_counts': {'":': 8, '<': 23, '<?xml version=': 0, '>': 24, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '146305', 'license': 'MIT', 'n_tokens_mistral': 7694, 'n_tokens_neox': 7258, 'n_words': 1655, 'path': 'create a unified supply chain BOM.ipynb', 'repo_name': 'workingyifei/BOM_tool', 'size': '20665'}