-
Notifications
You must be signed in to change notification settings - Fork 0
/
part_horizontal2vertical.r
105 lines (91 loc) · 4.64 KB
/
part_horizontal2vertical.r
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
# this script will transform horizontal parts with muliple attributes to a vertical list
# load libraries
library(readxl)
library(plyr)
library(dbplyr)
#define functions
# define function VLOOKUP (x = return value dataframe and column,
# y = lookup value dataframe and column, z = lookup dataframe and column
# x and z should be from the same dataframe)
vlookup <- function(x,y,z){
x[match(y,z)]
}
# define function: is not in
'%!in%' <- function(x,y)!('%in%'(x,y))
df <- read_excel("~/GitHub/arctos-r/input/parts.xlsx") # read in horizontal part file
colnames(df) <- tolower(colnames(df)) # lower case column names
# # Get max number of parts in horizontal file
# parts <- 0 # set count to zero
# for(i in 1:ncol(df)) { # for every column in the file
# name <- colnames(df)[i] # review each column name
# if(grepl('part_name', name, perl = TRUE) == TRUE) { # if column name contains "part_name" then
# parts <- parts + 1 # add one to part count
# }
# }
#
# # create part bulkload GUID Prefix file
# # pb <- subset(df, select=c("guid_prefix", "cat_num")) # retain GUID Prefix and catalog number columns
# # pb[ ,c("status","part_name","condition","disposition","lot_count","remarks","container_barcode","parent_part_barcode","parent_part_name")] <- NA # add part bulkload column headers (excluding attributes)
#
# # Loop through the numbers of parts to create separate dataframes
# for(i in 1:parts){
# # create part bulkload GUID Prefix file
# pb <- subset(df, select=c("guid_prefix", "cat_num")) # retain GUID Prefix and catalog number columns
# pb[ ,c("status","part_name","condition","disposition","lot_count","remarks","container_barcode","parent_part_barcode","parent_part_name")] <- NA # add part bulkload column headers (excluding attributes)
# # assign(paste('pb',i,sep=''),pb) # create a dataframe for part number
# partname <- paste('part_name_', i, sep="")
# condition <- paste('condition_', i, sep="")
# disposition <- paste('disposition_', i, sep="")
# lotcount <- paste('lot_count_', i, sep="")
# remark <- paste('remarks_', i, sep="")
# # barcode <- paste('container_barcode_', i, sep="")
# # parentbar <- paste('parent_part_barcode_', i, sep="")
# # parentname <- paste('parent_part_name_', i, sep="")
# for(j in 1:nrow(pb)){
# pb$part_name[j] <- vlookup(df[[partname]],pb$cat_num[j],df$cat_num) # look up part name by cat num
# pb$condition[j] <- vlookup(df[[condition]],pb$cat_num[j],df$cat_num) # look up part condition by cat num
# pb$disposition[j] <- vlookup(df[[disposition]],pb$cat_num[j],df$cat_num) # look up part disposition by cat num
# pb$lot_count[j] <- vlookup(df[[lotcount]],pb$cat_num[j],df$cat_num) # look up part lot count by cat num
# pb$remarks[j] <- vlookup(df[[remark]],pb$cat_num[j],df$cat_num) # look up part remarks by cat num
# # pb$part_barcode[j] <- vlookup(df[[barcode]],pb$cat_num[j],df$cat_num) # look up part barcode by cat num
# # pb$parent_barcode[j] <- vlookup(df[[parentbar]],pb$cat_num[j],df$cat_num) # look up part parent barcode by cat num
# # pb$parent_name[j] <- vlookup(df[[parentname]],pb$cat_num[j],df$cat_num) # look up part parent name by cat num
# }
# assign(paste('pb',i,sep=''),pb) # put result in a separate dataframe
# }
notnamelist <- c()
for(x in 1:ncol(df)) { # for every column in the file
notname <- colnames(df)[x] # review each column name
if(grepl("other_id", notname, perl = TRUE) == TRUE) { # if column name contains "part_name" then
notnamelist <- c(notnamelist,notname)
}
}
df <- df[ , which(names(df) %!in% c(notnamelist))]
parts <- 0 # set count to zero
for(i in 1:ncol(df)) { # for every column in the file
name <- colnames(df)[i] # review each column name
if(grepl('part_name', name, perl = TRUE) == TRUE) { # if column name contains "part_name" then
parts <- parts + 1 # add one to part count
}
}
# dfnamelist <- c()
final <- c()
for(i in 1:parts){
colnamelist <- c("guid_prefix","cat_num")
name2 <- paste("part_name_",i,sep = "")
for(j in 1:ncol(df)) { # for every column in the file
name <- colnames(df)[j] # review each column name
if(grepl(i, name, perl = TRUE) == TRUE) { # if column name contains "part_name" then
colnamelist <- c(colnamelist,name)
}
pb <- subset(df, select=c(colnamelist))
pb <- pb[which(!is.na(pb[[name2]])),] # use this but need variable for part name number...
}
assign(paste('pb',i,sep=''),pb) # put result in a separate dataframe
dfname <- paste('pb',i,sep="")
# dfnamelist <- c(dfnamelist,dfname)
}
pb <- rbind(unlist(dfnamelist))
pb <- bind_rows(dfnamelist, .id = "column_label")
pb <- df <- do.call("rbind", dfnamelist)
extract.year <- function(my.year) lapply(x, function(y) y[[my.year]])