I need help / guidance with my code below to see if I am doing wrong or what i need to add. I am trying to create three tables using joins in pandas. Can anyone tell or help me out with my code below. Right now, it is getting an slight error ValueError: All arrays must be of the same length for the table1 last varchar2 data type. Thanks for the help. here is my code.
#folder_location = '/content/0_Test/' #You need to mount google drive and change this directory string
folder_location = '/content/drive/MyDrive/ITA HRP Data Conversion Materials/Compensation Extractor/0_Test/'
df = pandas.read_excel(folder_location+'Compensation Worksheet.xlsx')#,dtype=str)
df = df[['Row ID',
'Existing P Level',
'Existing P Code',
'Existing P Variation Code',
'Considered "Adds to Rate"? (Y/N)',
'Calc Method',
'Calculation Sequence',
'MOU',
'Frequency',
'Rate or Flat Amount',
'Percent',
'FMS Dept.',
'Class Code',
'Reference ID',
'Comp Mapping Tab'
]
]
#The main filter is to avoid all Blanks and N/A on the Reference ID columns
df = df.dropna(subset = ['Reference ID'])
df[~df['Reference ID'].str.contains('N/A', case = False)].any()
df = df.fillna("") #make sure NaNs are simply empty strings
def basic_formatting(x):
str_x = str(x)
str_x = str_x.strip()
str_x = str_x.replace(' IN ','')
str_x = str_x.replace("\n",";")
str_x = str_x.replace(',',';')
str_x = str_x.replace(';;',';')
str_x = str_x.replace('‒','-')
return str_x
def specific_formatting(x):
str_x = str(x)
str_x = str_x.strip()
str_x = str_x.upper().replace('AND',';')
str_x = str_x.upper().replace('&',';')
str_x = str_x.upper().replace('MOUS','')
str_x = str_x.upper().replace('MOU','')
str_x = re.sub('^ALL\s*EXCEPT\s','NOT', str_x)
return str_x
def class_formatting(x):
str_x = str(x)
str_x = str_x.strip()
str_x = re.sub(u'\2014','',str_x)
return str_x
df[['MOU','FMS Dept.','Class Code','Existing P Variation Code','Existing P Code','Existing P Level']] = df[['MOU','FMS Dept.','Class Code','Existing P Variation Code','Existing P Code','Existing P Level']].applymap(basic_formatting)
df[['Class Code']] = df[['Class Code']].applymap(class_formatting)
df[['MOU','FMS Dept.','Class Code','Existing P Variation Code']] = df[['MOU','FMS Dept.','Class Code','Existing P Variation Code',]].applymap(specific_formatting)
wd_m_compensation_information = [['Reference ID',
'Row ID',
'Comp Mapping Tab',
'Considered "Adds to Rate"? (Y/N)',
'Calc Method',
'Calculation Sequence',
'Frequency',
'Rate or Flat Amount',
'Percent'
]]
wd_m_compensation_p_codes_and_levels = [['Reference ID','Row ID','P Bonus Code', 'P Level']]
wd_m_compensation_p_varcodes = [['Reference ID','Row ID','P Varcode']]
wd_m_compensation_mou_inclusions = [['Reference ID','Row ID','MOU']]
wd_m_compensation_mou_exclusions = [['Reference ID','Row ID','MOU']]
wd_m_compensation_fms_inclusions = [['Reference ID','Row ID','FMS']]
wd_m_compensation_fms_exclusions = [['Reference ID','Row ID','FMS']]
wd_m_compensation_class_inclusions = [['Reference ID','Row ID','CLASS']]
wd_m_compensation_class_exclusions = [['Reference ID','Row ID','CLASS']]
for index, row in df.iterrows():
#Comp information table
ref_id = row['Reference ID']
row_id = row['Row ID']
r_wd_m_compensation_information = [ row['Reference ID'],
row['Row ID'],
row['Comp Mapping Tab'],
row['Considered "Adds to Rate"? (Y/N)'],
row['Calc Method'],
row['Calculation Sequence'],
row['Frequency'],
row['Rate or Flat Amount'],
row['Percent']
]
wd_m_compensation_information.append(r_wd_m_compensation_information)
l_p_codes = str.split(row['Existing p Code'],';')
l_p_levels = str.split(row['Existing p Level'],';')
l_cross_codes_level = [(i,j) for i in l_p_codes for j in l_p_levels]
for (code,level) in l_cross_codes_level:
if( (code is not None and len(code) > 0) and (level is not None and len(level) > 0)):
wd_m_compensation_p_codes_and_levels.append([ref_id, row_id, code, level])
#Varcodes
l_varcodes = str.split(row['Existing P Variation Code'],';')
for varcode in l_varcodes:
if( len(varcode.strip() ) ):
wd_m_compensation_p_varcodes.append([ref_id, row_id, varcode.strip()])
#MOUs
l_mous = str.split(row['MOU'],';')
for mou in l_mous:
mou = mou.strip()
if( len(mou) > 0):
if 'NOT' not in mou:
mou = re.sub("[^\d]+",'',mou)
wd_m_compensation_mou_inclusions.append([ref_id, row_id, mou])
else:
mou = mou.replace('NOT','')
mou = re.sub("[^\d]+",'',mou)
wd_m_compensation_mou_exclusions.append([ref_id, row_id, mou])
#FMSs
l_fms = str.split(row['FMS Dept.'],';')
for fms in l_fms:
fms = fms.strip()
if( len(fms) > 0 ):
if 'NOT' not in fms:
fms = re.sub("[^\d]+",'',fms)
wd_m_compensation_fms_inclusions.append([ref_id, row_id, fms])
else:
fms = fms.replace('NOT','')
fms = re.sub("[^\d]+",'',fms)
wd_m_compensation_fms_exclusions.append([ref_id, row_id, fms])
#Class Codes
l_cCodes = str.split(row['Class Code'],';')
for cCode in l_cCodes:
cCode = cCode.strip()
if( len(cCode) > 0):
if 'NOT' not in cCode:
wd_m_compensation_class_inclusions.append([ref_id, row_id, cCode])
else:
cCode = cCode.replace('NOT','')
wd_m_compensation_class_exclusions.append([ref_id, row_id, cCode])
table1look like?table1should just look like a normal table as I specify in my code with its column names and the table name.