# needed_sap_cols = [re.sub("^[a-zA-Z]*.", "", x) for x in temp]
# # print(needed_sap_cols)
# sap_fields = sap_data.ix[sap_data['Field'].isin(set(needed_sap_cols)),:]
# sap_fields = sap_fields.reset_index(range(0,len(sap_fields)))
# # print(sap_fields)
### using legacy's comment to match
# """
# # call the matching function
# # """
# -- Directory into which matched results spreadsheet is saved
outFile = os.path.join(outDir, "sap to legacy mapping")
match_vals = nfm.fuzzyWordMatch(sap_data_clean, 'sap', legacy_data, 'legacy', 'Measurement unit description',
'DESCRIPTION', 'ISO code', 'UOM_CODE', outFile, 3)
### now add the answer to the output
answer_file = os.path.join(inDir, "value_table_uom(Legacy_To_SAP_Mapping).xlsx")
answer_table = pd.read_excel(answer_file, sheetname=0, header=0)
answer_table = answer_table[['LEGACY_VALUE', "ECCISOUOM"]]
print(answer_table)
# merge the above table w the answer table produced by the fuzzy matching fxn
output_all = pd.merge(answer_table, match_vals, how='right', left_on='LEGACY_VALUE', right_on='legacy_original')
print(output_all)
output_all.to_excel(str(outFile + ".xlsx"))
# outFile = os.path.join(outDir, "legacy to sap field mapping.xlsx")
# match_vals = nfm.fuzzyWordMatch(legacy_data, 'legacy', sap_data_clean, 'sap', 'DESCRIPTION',
# 'Measurement unit description', 'UOM_CODE', 'ISO code', outFile, 3)
inDir = os.path.join(baseDir, 'data')
outDir = os.path.join(baseDir, "mapping/output")
# -- In case preferred path does not already exist
if not os.path.exists(outDir):
os.makedirs(outDir)
legacy_file = os.path.join(inDir, "legacy value.csv")
sap_file = os.path.join(inDir, "sap value.csv")
data_file = os.path.join(inDir, "mock data.csv")
field_mapping_file = os.path.join(outDir,
"field_match_based_on_description.csv")
# -- Directory into which matched results spreadsheet is saved
outFile = os.path.join(outDir, "value mapping")
match_vals = nfm.fuzzyWordMatch(sap_file, legacy_file, 'DESCRIPTION',
'DESCRIPTION', 'VALUE', 'VALUE', outFile, 1)
# print(match_vals)
legacy_value = pd.read_csv(legacy_file,
quotechar='"',
skipinitialspace=True,
sep=',')
match_vals.insert(0, 'Column', legacy_value['COLUMN'])
match_vals.to_csv(str(outFile + ".csv"))
legacy_data = pd.read_csv(legacy_file,
quotechar='"',
skipinitialspace=True,
sep=',')
legacy_data = pd.DataFrame(legacy_data)
col_to_check = legacy_data['COLUMN'].unique()
needed_sap_col_file = os.path.join(
inDir, 'CUSTOMER/CDD0053_CUSTOMER_MASTER_BILL_TO-Target.xlsx')
needed_sap_col_data = pd.read_excel(needed_sap_col_file,
sheetname='Data',
header=1,
skiprows=0,
na_values="")
# when you read excel using pandas read_excel function, the result is ALREADY in pandas dataframe type
# print(needed_sap_col_data.columns.values)
# print(needed_sap_col_data.iloc[:5, :5])
### now see which sap fields in the sap_data are also in needed_sap_col, and you'll only keep those
sap_fields = sap_data.ix[
sap_data['Field'].isin(set(needed_sap_col_data['FIELD'])), :]
sap_fields = sap_fields.reset_index(range(0, len(sap_fields)))
# print(sap_fields)
"""
# call the matching function
# """
# -- Directory into which matched results spreadsheet is saved
outFile = os.path.join(outDir, "sap to legacy mapping")
match_vals = nfm.fuzzyWordMatch(sap_fields, 'sap', legacy_data_clean, 'legacy',
'Description', 'Comments', 'Field', 'Name',
outFile, 2)
outFile = os.path.join(outDir, "legacy to sap field mapping")
match_vals = nfm.fuzzyWordMatch(legacy_data_clean, 'legacy', sap_fields, 'sap',
'Comments', 'Description', 'Name', 'Field',
outFile, 2)