library(dplyr)
library(data.table)
library(ggplot2)

train_numeric<-fread("../input/train_numeric.csv", showProgress = TRUE, na.strings = "")

##Save responses
responses <- train_numeric[, .(Id, Response)]
train_numeric$Response = NULL
station_names <- names(train_numeric)

##Replace measurements with 1 and NA with 0 to indicate if part was in station or not
train_numeric[!is.na(train_numeric)]=1
train_numeric[is.na(train_numeric)]=0

##Crete new data frame indicating if part was ever in that station
train_numeric_station<-data.frame(responses$Id)
station_names_new<-c("Id")


##Add up all of the columns for the features of each station
##If the part was ever in the station the the sum will be > 0
##Otherwise value is 0 indicating the part was never in the station
for (i in 0:14){
  tempstation<-paste0("L0_S",as.character(i))
  train_numeric_station<-cbind(train_numeric_station, rowSums(train_numeric[,startsWith(station_names,paste0(tempstation,"_")),with=FALSE]))
  station_names_new<-c(station_names_new, tempstation)
}
rm(train_numeric)

#Apply station names to name of dataframe
names(train_numeric_station)=station_names_new

#If sum is greater than zero set it equal to zero
train_numeric_station[train_numeric_station>0]=1
train_numeric_station$Id=responses$Id

#Create a new column that is a concatonation of of the values (0 or 1) of each column
#this creates a unique path fingerprint for each part
train_numeric_station$path<-apply( train_numeric_station[ , 2:dim(train_numeric_station)[2] ] , 1 , paste , collapse = "" )

rm(i,station_names,station_names_new,tempstation)

##Get all of the unique paths identified in the parts and assign a path Id to it
station_path<-data.frame(unique(train_numeric_station$path))
station_path$path_id<-seq(1,dim(station_path)[1],1)
names(station_path)<-c("path","path_id")

##Remove individual station columns, don't need now that we have unique path flow found
train_numeric_station[,2:(dim(train_numeric_station)[2]-1)]=NULL

#Assign a path ID to each part
train_numeric_station$Response<-responses$Response
train_numeric_station<-merge(train_numeric_station,station_path,by="path",sort=TRUE)
train_numeric_station$path=NULL

##Get a list of the failures by each path and the total part count per path
failures<-as.data.frame(table(train_numeric_station[train_numeric_station$Response==1,]$path_id))
names(failures)=c("path_id","failures")
path_count<-as.data.frame(table(train_numeric_station$path_id))
names(path_count)=c("path_id","path_count")
train_numeric_station$Response=NULL


##Merge failure and total count in path data frame
station_path=merge(station_path,failures,by="path_id",sort=TRUE,all=TRUE)
station_path=merge(station_path,path_count,by="path_id",sort=TRUE,all=TRUE)

#Set paths with no failures to 0 in failure column
station_path$failures[is.na(station_path$failures)]=0

#Determine path failure rate percentage
station_path$failure_rate<-station_path$failures/station_path$path_count

rm(failures,path_count,responses)


##Create new data frame binning uncommon paths into a single path
station_path_bin<-station_path[1:28,]
station_path_bin[28,4]=sum(station_path[28:dim(station_path)[1],]$path_count)
station_path_bin[28,3]=sum(station_path[28:dim(station_path)[1],]$failures)
station_path_bin[28,5]=station_path_bin[28,3]/station_path_bin[28,4]
overall_failure_rate<-sum((station_path_bin$failures))/sum((station_path_bin$path_count))

###Create bar plot of part count and failure rate "common" routes (the first 27) and "uncommon" routes have been
###binned together
ggplot(station_path_bin, aes(x = path_id, y=path_count)) + geom_bar(stat="identity" ) +
  xlab("Path Id") + ylab("Part Count for Path") + ggtitle("Part Count per Path")

ggplot(station_path_bin, aes(x = path_id, y=failure_rate)) + geom_bar(stat="identity" )+
  xlab("Path Id") + ylab("Failure Rate for Path") + ggtitle("Failure Rate per Path") + geom_hline(yintercept = overall_failure_rate)