Execute R Error
Warren_Yabsley
New Altair Community Member
Hello Everyone
I am trying to run an R script in RM but have been encountering error messages. I am new to RM and have very limited programming knowledge (script written externally) but have tested the script in R and it is running without errors.
I want to determine a predefined score per job based on four variables that need to be extracted from a very large dataset. As there are many instances per job, the maximum score then needs to be selected. Data are contained in six Excel spreadsheets.
Running the complete process generates the error message: “Execution of the R script failed”. Running part of the script that relates to the first Excel spreadsheet does not produce any errors nor when the script is expanded to incorporate the second spreadsheet. However, when the third spreadsheet is included, the above error message is displayed.
I added a breakpoint before Execute R and the six data spreadsheets have loaded correctly. Their file names match those in the rm_main and within the script.
Below is the xml code.
Any help with this would be much appreciated.
Warren
I am trying to run an R script in RM but have been encountering error messages. I am new to RM and have very limited programming knowledge (script written externally) but have tested the script in R and it is running without errors.
I want to determine a predefined score per job based on four variables that need to be extracted from a very large dataset. As there are many instances per job, the maximum score then needs to be selected. Data are contained in six Excel spreadsheets.
Running the complete process generates the error message: “Execution of the R script failed”. Running part of the script that relates to the first Excel spreadsheet does not produce any errors nor when the script is expanded to incorporate the second spreadsheet. However, when the third spreadsheet is included, the above error message is displayed.
I added a breakpoint before Execute R and the six data spreadsheets have loaded correctly. Their file names match those in the rm_main and within the script.
Below is the xml code.
Any help with this would be much appreciated.
Warren
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="7.0.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="7.0.001" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="retrieve" compatibility="7.0.001" expanded="true" height="68" name="Retrieve Analytics_Four_Months_SAPBW_Extract_May14" width="90" x="45" y="34">
<parameter key="repository_entry" value="//Local Repository/data/R 4 Months/Analytics_Four_Months_SAPBW_Extract_May14"/>
</operator>
<operator activated="true" class="retrieve" compatibility="7.0.001" expanded="true" height="68" name="Retrieve Distance_Map" width="90" x="45" y="136">
<parameter key="repository_entry" value="//Local Repository/data/R 4 Months/Distance_Map"/>
</operator>
<operator activated="true" class="retrieve" compatibility="7.0.001" expanded="true" height="68" name="Retrieve Four_Months_Orders_In_One_Table_May14" width="90" x="45" y="340">
<parameter key="repository_entry" value="//Local Repository/data/R 4 Months/Four_Months_Orders_In_One_Table_May14"/>
</operator>
<operator activated="true" class="retrieve" compatibility="7.0.001" expanded="true" height="68" name="Retrieve Reading_Map" width="90" x="45" y="187">
<parameter key="repository_entry" value="//Local Repository/data/R 4 Months/Reading_Map"/>
</operator>
<operator activated="true" class="retrieve" compatibility="7.0.001" expanded="true" height="68" name="Retrieve Risk_Score_Map" width="90" x="45" y="238">
<parameter key="repository_entry" value="//Local Repository/data/R 4 Months/Risk_Score_Map"/>
</operator>
<operator activated="true" class="retrieve" compatibility="7.0.001" expanded="true" height="68" name="Retrieve Risk_Score_Calculation_2D_ Matrix" width="90" x="179" y="387">
<parameter key="repository_entry" value="//Local Repository/data/R 4 Months/Risk_Score_Calculation_2D_ Matrix"/>
</operator>
<operator activated="true" class="r_scripting:execute_r" compatibility="7.0.000" expanded="true" height="187" name="Execute R" width="90" x="313" y="34">
<parameter key="script" value="# rm_main is a mandatory function, # the number of arguments has to be the number of input ports (can be none) rm_main = function(Analytics_Four_Months_SAPBW_Extract_May14.XLSX,Four_Months_Orders_In_One_Table_May14.xlsx,Risk_Score_Calculation_2D_Matrix.xlsx,Reading_Map.xlsx,Risk_Score_Map.xlsx,Distance_Map.xlsx) { rm(list=ls()) setwd('C:/Enzen/WWU/R Code') library(readxl) ## process 1 a=read_excel('Analytics_Four_Months_SAPBW_Extract_May14.XLSX',col_types = c(rep('text',11),'date',rep('text',3)),sheet = 1) a=a[,!names(a)%in%'R'] names(a)=c("OBJ_ZP2_AUFNR", "OBJ_ZP2_CLAS", "OBJ_ZP2_ATNAM","OBJ_ZP2_ATZHL","OBJ_ZP2_FENUM","OBJ_ZP2_POSNR","OBJ_ZP2_QMNUM","OBJ_ZP2_ATWRT","OBJ_ZP2_ATWTB","OBJ_ZP2_CFAW","OBJ_0ERDAT","OBJ_0AEDAT","OBJ_ZP2_ERNAM","OBJ_ZP2_CHGBY") a[,c(1,4:7,12,14)]=apply(a[,c(1,4:7,12,14)],2,as.numeric) ZP2_OWCF_temp=a ## process 2 ZP2_OWCF=with( ZP2_OWCF_temp, OBJ_ZP2_CFAW == 'A' & ( OBJ_ZP2_CLAS %in% c('EM_LIS_BARHOLE', 'EM_GAS_BARHOLE') |( OBJ_ZP2_CLAS == 'EM_LIS_SITE_DTS' & (OBJ_ZP2_ATNAM %in%c('EM_LISHAZARDCAT', 'EM_LISPRESSURE') & OBJ_ZP2_ATWRT != 'ZZZ') ) ) ) ColNames=c('OBJ_0AEDAT','OBJ_0ERDAT','OBJ_ZP2_ATNAM', 'OBJ_ZP2_ATWRT', 'OBJ_ZP2_ATWTB', 'OBJ_ZP2_ATZHL', 'OBJ_ZP2_AUFNR', 'OBJ_ZP2_CFAW','OBJ_ZP2_CHGBY', 'OBJ_ZP2_CLAS', 'OBJ_ZP2_ERNAM', 'OBJ_ZP2_FENUM', 'OBJ_ZP2_POSNR', 'OBJ_ZP2_QMNUM') ZP2_OWCF=ZP2_OWCF_temp[ZP2_OWCF,names(ZP2_OWCF_temp)%in%ColNames] ## process 3 PRESSURE = with( ZP2_OWCF_temp, OBJ_ZP2_CFAW == 'A' & ( OBJ_ZP2_CLAS == 'EM_LIS_SITE_DTS' & (OBJ_ZP2_ATNAM == 'EM_LISPRESSURE' & OBJ_ZP2_ATWRT != 'ZZZ') ) ) ColNames=c('OBJ_0AEDAT', 'OBJ_0ERDAT', 'OBJ_ZP2_ATNAM', 'OBJ_ZP2_ATWRT', 'OBJ_ZP2_ATWTB', 'OBJ_ZP2_ATZHL', 'OBJ_ZP2_AUFNR','OBJ_ZP2_CFAW', 'OBJ_ZP2_CHGBY', 'OBJ_ZP2_CLAS', 'OBJ_ZP2_ERNAM', 'OBJ_ZP2_FENUM', 'OBJ_ZP2_POSNR', 'OBJ_ZP2_QMNUM') PRESSURE=ZP2_OWCF_temp[PRESSURE,names(ZP2_OWCF_temp)%in%ColNames] ## process 4 HAZARDCAT = with( ZP2_OWCF_temp, OBJ_ZP2_CFAW == 'A' & ( OBJ_ZP2_CLAS == 'EM_LIS_SITE_DTS' & (OBJ_ZP2_CLAS == 'EM_LIS_SITE_DTS' & OBJ_ZP2_ATNAM == 'EM_LISHAZARDCAT') ) ) ColNames=c('OBJ_0AEDAT', 'OBJ_0ERDAT', 'OBJ_ZP2_ATNAM', 'OBJ_ZP2_ATWRT', 'OBJ_ZP2_ATWTB', 'OBJ_ZP2_ATZHL','OBJ_ZP2_AUFNR', 'OBJ_ZP2_CFAW', 'OBJ_ZP2_CHGBY', 'OBJ_ZP2_CLAS', 'OBJ_ZP2_ERNAM', 'OBJ_ZP2_FENUM', 'OBJ_ZP2_POSNR', 'OBJ_ZP2_QMNUM') HAZARDCAT=ZP2_OWCF_temp[HAZARDCAT,names(ZP2_OWCF_temp)%in%ColNames] ## process 5 masterfields=unique(ZP2_OWCF$OBJ_ZP2_ATNAM) vfieldnos=length(masterfields) fields=unique(ZP2_OWCF[,c('OBJ_ZP2_AUFNR', 'OBJ_ZP2_FENUM')]) for(i in 1:vfieldnos){ vfield = masterfields tmp=ZP2_OWCF[ZP2_OWCF$OBJ_ZP2_ATNAM%in%vfield,c('OBJ_ZP2_AUFNR','OBJ_ZP2_FENUM','OBJ_ZP2_ATWTB')] fields=merge(fields,tmp,by = c('OBJ_ZP2_AUFNR', 'OBJ_ZP2_FENUM'),all.x=T) names(fields)[i+2]=vfield } ## process 6 # ,'OBJ_ZP2_FENUM' PRESSURE_VAL=PRESSURE[,c('OBJ_ZP2_AUFNR','OBJ_ZP2_ATWTB')] names(PRESSURE_VAL)[2]='PRESSURE_VAL' HAZARDCAT_VAL=HAZARDCAT[,c('OBJ_ZP2_AUFNR','OBJ_ZP2_ATWTB')] names(HAZARDCAT_VAL)[2]='HAZARDCAT_VAL' fields=merge(fields,PRESSURE_VAL,by=c('OBJ_ZP2_AUFNR'),all.x = T,all.y = F) fields=merge(fields,HAZARDCAT_VAL,by=c('OBJ_ZP2_AUFNR'),all.x = T) ## process 7 b=read_excel('Four_Months_Orders_In_One_Table_May14.xlsx',col_types = c('numeric','text','date','numeric',rep('text',2)),sheet = 3) Hour12Table=b[,c('OBJ_JOBS_SUPORDER', 'OBJ_JOBS_SUPUSERSTATUS', 'OBJ_JOBS_CREATEDON', 'GAS_STOP_DURATION' )] names(Hour12Table)[1]='OBJ_ZP2_AUFNR' bb=unlist(sapply(Hour12Table$OBJ_JOBS_SUPUSERSTATUS ,function(i){ length(c(grep('LIM',i),grep('LIP',i)))>0 })) Hour12Table=Hour12Table[bb,] Hour12Table=merge(fields,Hour12Table,by = 'OBJ_ZP2_AUFNR',all.y = T) idx12=Hour12Table$OBJ_ZP2_AUFNR%in%unique(fields$OBJ_ZP2_AUFNR) Hour12Table=Hour12Table[idx12,] ## process 8 c=read_excel('Risk_Score_Calculation_2D_Matrix.xlsx',col_types = c('numeric',rep('text',4),rep('numeric',3)),sheet = 1) RiskScore=c[1:360,] RiskScore1=RiskScore[,names(RiskScore)%in%c('Hazard Category','Pressure','Distance From Property','Lower Threshold Vented Gas Reading','Upper Threshold Vented Gas Reading','Risk Score Value')] IterNo= RiskScore[,'Upper Threshold Vented Gas Reading'] - RiskScore[,'Lower Threshold Vented Gas Reading'] + 1 RiskScore1$GasReading=RiskScore1[,'Lower Threshold Vented Gas Reading']+IterNo-1 RiskScore1=do.call(rbind,by(RiskScore1,INDICES = RiskScore1$'Risk Score Value',function(i){ tmp=i out0=lapply(1:nrow(tmp),function(j){ tmp1=tmp[j,] tmp1_u=tmp1$'Upper Threshold Vented Gas Reading' tmp1_l=tmp1$'Lower Threshold Vented Gas Reading' IterNo=tmp1_u-tmp1_l+1 out1=matrix(rep(tmp1,IterNo),nrow=IterNo, byrow = T) out1=cbind(out1,tmp1_l-1+1:IterNo) out1 }) out=do.call(rbind,out0) out })) RiskScore1=t(apply(RiskScore1,1,as.character)) RiskScore1=data.frame(RiskScore1) names(RiskScore1)=c('Hazard Category','Pressure','Distance From Property','Lower Threshold Vented Gas Reading','Upper Threshold Vented Gas Reading','Risk Score Value','IterNo','GasReading') RiskScore1$"Lower Threshold Vented Gas Reading"=RiskScore1$IterNo=NULL ## process 9 Map_Pressure=data.frame('Category'=c('LP','IP','MP','HP','VHP'), 'Abbreviation'=c('Low Pressure','Intermediate Pressure','Medium Pressure','High Pressure','Very High Pressure')) ## process 10 Map_HazardCat=data.frame('Category'=c('C','A','B'), 'Abbreviation'=c('Over 1.75m Of Open Ground','No Open Ground','Less Than 1.75m Of Open Ground')) ## process 11 RiskScore2=merge(RiskScore1,Map_HazardCat,by.x = 'Hazard Category', by.y='Category') names(RiskScore2)[which(names(RiskScore2)=='Abbreviation')]='Hazardcat1' RiskScore2=merge(RiskScore2,Map_Pressure,by.x = 'Pressure', by.y='Category') names(RiskScore2)[which(names(RiskScore2)=='Abbreviation')]='Pressure1' RiskScore2$'Hazard Category'=RiskScore2$'Pressure'=NULL RiskScore2[,c('Upper Threshold Vented Gas Reading', 'Risk Score Value', 'GasReading')]=t(apply(RiskScore2[,c('Upper Threshold Vented Gas Reading', 'Risk Score Value', 'GasReading')],2,as.numeric)) ################### #### red words #### ################### # load info from 'Distance_Map.xlsx' d=read_excel('Distance_Map.xlsx',sheet = 'DistanceMap') DistanceMap=unique(d) vfieldnosD = length(DistanceMap$Distance) e=read_excel('Reading_Map.xlsx',sheet = 'ReadingMap') ReadingsMap=unique(e) vfieldnosR = length(ReadingMap$Readings) f=read_excel('Risk_Score_Map.xlsx',sheet = 'RiskScMap') RiskScoreMap=unique(f) vfieldnosRV = length(RiskScoreMap$RiskScore) # create new fields fields=Hour12Table for (i in #1:2 1:min(vfieldnosD, max(sapply(names(fields),function(i) suppressWarnings(as.numeric(substr(i,nchar(i),nchar(i))))),na.rm = T) ) ){ vfieldD = DistanceMap$Distance vfieldR = ReadingsMap$Readings vfieldRV = RiskScoreMap$RiskScore tmp=RiskScore2[,c('Hazardcat1','Pressure1','Distance From Property','GasReading','Risk Score Value')] names(tmp)=c('HAZARDCAT_VAL','PRESSURE_VAL',vfieldD,vfieldR,vfieldRV) tmp_fields_paste=mapply('paste',fields[vfieldD],fields[vfieldR]) tmp_paste=mapply('paste',tmp[vfieldD],tmp[vfieldR]) tmp_fields_paste=mapply('paste',tmp_fields_paste,fields$PRESSURE_VAL) tmp_paste=mapply('paste',tmp_paste,tmp$PRESSURE_VAL) tmp_fields_paste=mapply('paste',tmp_fields_paste,fields$HAZARDCAT_VAL) tmp_paste=mapply('paste',tmp_paste,tmp$HAZARDCAT_VAL) idx=tmp_paste%in%tmp_fields_paste tmp0=tmp[idx,] fields=merge(fields,tmp0,by = c('HAZARDCAT_VAL','PRESSURE_VAL',vfieldD,vfieldR),all.x = T) } # end new fields # fieldsRowNum=fields ColIdx=substr(names(fieldsRowNum),1,9)=='RiskScore' MaxFieldsRowNum=apply(fieldsRowNum,1,function(i){ tmp=suppressWarnings(as.numeric(i[ColIdx])) if(sum(is.na(tmp))==length(tmp)) out=NA if(sum(is.na(tmp))!=length(tmp)) out=max(tmp,na.rm = T) out }) fieldsRowNum$MaxRiskScore=MaxFieldsRowNum FinalTable1=do.call(rbind,by(fieldsRowNum,fieldsRowNum$OBJ_ZP2_AUFNR,function(i){ tmp=i if(ncol(tmp)==1) tmp=t(tmp) MaxRisk=tmp[,'MaxRiskScore'] if(sum(is.na(MaxRisk))==nrow(tmp)) out=NULL if(sum(is.na(MaxRisk))!=nrow(tmp)) out=tmp[which.max(as.numeric(MaxRisk)),] out })) FinalTable1$MaxMaxRiskScore=FinalTable1$MaxRiskScore FinalTable1$MaxRiskScore=NULL fieldsRowNum=merge(fieldsRowNum,FinalTable1,by = intersect(names(fieldsRowNum),names(FinalTable1)),all.x = T) }"/>
</operator>
<connect from_op="Retrieve Analytics_Four_Months_SAPBW_Extract_May14" from_port="output" to_op="Execute R" to_port="input 1"/>
<connect from_op="Retrieve Distance_Map" from_port="output" to_op="Execute R" to_port="input 4"/>
<connect from_op="Retrieve Four_Months_Orders_In_One_Table_May14" from_port="output" to_op="Execute R" to_port="input 2"/>
<connect from_op="Retrieve Reading_Map" from_port="output" to_op="Execute R" to_port="input 5"/>
<connect from_op="Retrieve Risk_Score_Map" from_port="output" to_op="Execute R" to_port="input 6"/>
<connect from_op="Retrieve Risk_Score_Calculation_2D_ Matrix" from_port="output" to_op="Execute R" to_port="input 3"/>
<connect from_op="Execute R" from_port="output 1" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
</process>
</operator>
</process>
0