05.15 使用R语言对上百份文件合并，并做判断后画图

公司SPC会对制程的Online数据进行判断，但是缺少可视化的东西，所以每次都导出来人为判断，但是文件数量大，耗时长。使用R Shiny进行数据读入->合并->画图。

实现逻辑思维：

1.先批量读入数据 2.因为采集样本不同，所以读入后对缺失值进行填充，填充的数据使用合格数据 3.对数据进行判断，任何一个超出，即标记为1 4.利用ggplot进行画图

数据路径：

路径

数据格式

读入R后的数据如下：

合并并判断后的数据：

备注：因为使用DT，所以看不到完整的路径

全部代码如下：

#load package

library(ggplot2)

library(shinydashboard)

library(shiny)

library(dplyr)

library(stringr)

library(DT)

library(readr)

library(grid)

library(tidyr)

# layout

dashboardPage(skin="yellow",

dashboardHeader(title="SPC Control Item" ),

dashboardSidebar(

sidebarMenu(

menuItem("Data Import",tabName = "Upload",icon=icon("beer"),

menuItem("File Path",tabName="Filepath",icon=icon("chain"))),

menuItem("Data Handling",tabName="Datahandling",icon=icon("list-alt"),

menuItem("Calculate Result",tabName="CalResult",icon=icon("building"))),

menuItem("Chart",tabName="chart",icon=icon("bar-chart"),

menuItem("SPC Result Chart",tabName="ResultChart",icon=icon("line-chart")),

menuItem("Group Result Chart",tabName="GroupChart",icon=icon("line-chart"))),

menuItem("Help",tabName="help",icon=icon("certificate"))

)

),

dashboardBody(

tabItems(

tabItem(tabName = "Filepath",

fluidRow(

box(width=12,solidHeader=TRUE,status="warning",textInput("path","Please input file path",value=""),

verbatimTextOutput("textest"),DT::dataTableOutput("spc_file")),

column(width=12,actionButton("get","Get",icon=icon("car"))))),

tabItem(tabName="CalResult",

title="Result Data",width=12,solidHeader = TRUE,status="warning",DT::dataTableOutput("result_spcdata"),

column(width=12,

actionButton("calculate","Calculate",icon=icon("car")),

downloadButton("Download","Download"))),

tabItem(tabName="ResultChart",box(title="Statistics SPC Item which is Out of SPEC ",width=12,solidHeader = TRUE,status="warning",

plotOutput("result_chart"))),

tabItem(tabName="GroupChart",box(width=12,solidHeader = TRUE,status="warning",plotOutput("group_chart")))

#tabItem(tabName="help",

# column(3,

# h3("helptext"),

# helpText("1.input file path C:\\Users\\chengh\\Desktop\\Kevin\\Kevin",

# "2.Datahandling/CalculateResult/Calculate",

# "3.Chart/SPC Chart Result",

# "4.Download format:download.csv.")))

)

)

)

#options(shiny.maxRequestSize=30*1024^2)

function(input,output,session){

spcfile

#filename

#filepath

#read first file:

#spc

#for(i in 2:length(filepath)){

# spc01

# spc

#}

setwd(input$path)

file_list=list.files(input$path)

spc

for (batch in file_list){

#print(batch)

spc01=data.frame(read.csv(batch,header=FALSE,blank.lines.skip=FALSE,fill=TRUE,skip=1,col.names = (1:26),stringsAsFactors=FALSE))

spc

}

names(spc)

"USL","SL","LSL","CONTROLGROUP_ID","X1","X2","X3","X4","X5","X6","X7","X8","X9","X10","X11","X12","X13")

spc%>%filter(is.na(X1)==FALSE)->spc

spc

})

output$textest

#output$textpath

#output$path

output$spc_file

DT::datatable(spcfile(),rownames = F)

})

result_spc

spc

#更换类型：

as.numeric(spc$LSL)->spc$LSL

as.numeric(spc$USL)->spc$USL

as.numeric(spc$SL)->spc$SL

# 样本数更换：

spc$X3[str_count(spc$X3,'[0-9]')==1]

spc$X6[str_count(spc$X6,'[0-9]')==1]

spc$X10[str_count(spc$X10,'[0-9]')==1]

# 将SL & USL & LSL赋值给NA：

for (t in 1:nrow(spc)){

for(i in 14:ncol(spc)){

if(is.na(spc[t,]$SL)==TRUE & is.na(spc[t,i])==TRUE){

spc[t,i]

else if(is.na(spc[t,]$SL)==FALSE & is.na(spc[t,i])==TRUE){

spc[t,i]

}

}

}

# end

# 分组：

spc%>%filter(is.na(spc$USL)==TRUE)->spc_usl

spc%>%filter(is.na(spc$LSL)==TRUE)->spc_lsl

spc%>%filter(is.na(spc$SL)==FALSE)->spc_all

# spc_all 判断：

spc_all001%select(c(14:25))%>%mutate_all(funs(ifelse(.>=spc_all$LSL&.<=spc_all$USL,1,0)))%>%

mutate(Result=pmin(X1,X2,X3,X4,X5,X6,X7,X8,X9,X10,X11,X12))%>%

select(Result)

spc_all

#spc_lsl判断：

spc_lsl001%select(c(14:25))%>%mutate_all(funs(ifelse(.<=spc_lsl$USL,1,0)))%>%

mutate(Result=pmin(X1,X2,X3,X4,X5,X6,X7,X8,X9,X10,X11,X12))%>%

select(Result)

spc_lsl

#spc_usl判断：

spc_usl001%select(c(14:25))%>%mutate_all(funs(ifelse(.>=spc_usl$LSL,1,0)))%>%

mutate(Result=pmin(X1,X2,X3,X4,X5,X6,X7,X8,X9,X10,X11,X12))%>%

select(Result)

spc_usl

# merge data：

rbind(spc_all,spc_lsl,spc_usl)->spc

as.factor(spc$Result)->spc$Result

attach(spc)

spc$Group[str_detect(CONTROLITEM_ID,"PI")==TRUE]

spc$Group[str_detect(CONTROLITEM_ID,"PR")==TRUE]

spc$Group[str_detect(CONTROLITEM_ID,"PL")==TRUE]

spc$Group[str_detect(CONTROLITEM_ID,"QC")==TRUE]

spc$Customer[str_detect(LOT,"JT")==TRUE]

spc$Customer[str_detect(LOT,"NP")==TRUE]

spc$Customer[str_detect(LOT,"XP")==TRUE]

detach(spc)

print(spc)

})

output$result_spcdata

DT::datatable(result_spc(),rownames = F)

})

output$group_chart

result_spc()%>%group_by(Group,Result)%>%summarise(number=n())%>%

ggplot(aes(x=Group,y=number,fill=Result))+geom_col(position="dodge",width=0.3)+

scale_fill_manual(values=c("0"="#E41A1C","1"="#4DAF4A"))+

theme(panel.background = element_blank(),

plot.background = element_blank(),

axis.text.y = element_blank(),

axis.ticks = element_blank(),

axis.title=element_blank(),

legend.position ="none",

legend.title=element_blank())+

geom_text(aes(x=Group,y=number,label=number),position = position_dodge(0.2),vjust=0.3)+

labs(title="Different Operation spc review")

})

output$result_chart

result_spc()->spc

spc%>%filter(is.na(X1)==FALSE)%>%group_by(Result)%>%summarise(number=n())%>%

spread(key=Result,value=number)->spc_spread

names(spc_spread)

spc_spread%>%mutate(OK_percent=round((OK/(OK+NG)*100),2))%>%mutate(NG_percent=round((NG/(OK+NG)*100),2))%>%

gather(key=Item,value=percent)->spc_gather

spc_gather[3:4,]->spc_percent

spc_gather[1:2,]->spc_number

chart1%

ggplot()+geom_col(aes(Item,percent,fill=Item),width=0.5)+

scale_fill_manual(values=c("NG"="#E41A1C","OK"="#4DAF4A"))+

theme(panel.background = element_blank(),

plot.background = element_blank(),

axis.text = element_blank(),

axis.ticks = element_blank(),

axis.title=element_blank(),

legend.position ="right",

legend.title=element_blank())+

geom_text(aes(x=Item,y=percent,label=percent))+

labs(title="number(ea)")

chart2%

ggplot()+geom_col(aes("Item",percent,fill=Item),width=0.5)+

scale_fill_manual(values=c("NG_percent"="#E41A1C","OK_percent"="#4DAF4A"))+

coord_polar(theta="y")+

theme(panel.background = element_blank(),

plot.background = element_blank(),

axis.text = element_blank(),

axis.ticks = element_blank(),

axis.title=element_blank(),

legend.position = "none") +

geom_text(aes(x="Item",y=percent-2,label=paste(percent,"%","")),check_overlap = TRUE)+

labs(title="Ratio(%)")

grid.newpage()

pushViewport(viewport(layout=grid.layout(1,2)))

vplayout

print(chart1,vp=vplayout(1,1))

print(chart2,vp=vplayout(1,2))

})

# download file

output$Download

filename=function(){

paste("spcfile",".csv",sep="")

},

content=function(file){

write.csv(result_spc(),file,row.names = FALSE)

}

)

}

效果图：

chart图

分组图