ML 1: Data Wrangling

Packages Used: No new packages are used in this notebook.

It is often said that preparing data for subsequent processing (a.k.a. data wrangling) represents 80% of the effort in most data analysis efforts. A variety of tools are available in the DataFrames package to simplify the effort.

1. Tabular Data

Most data wrangling tools are designed to operate on tabular data:

• Cell = Values: The raw data represented in the table are the values in each cell of the table. Some cells may be missing data.
• Columns = Variables: Each column of the table represents a different attribute of the data, represented as a different variable.
• Rows = Observations: Each row of the table represents all of the values of the attributes of a single entity represented as a different observation.

Ex 1: Array vs. Tabular Representations

Three variables: var1 with possible values {A,B}; var2 with values {a,b}; var3 with values {1,2,3,4}:

Array:

	a	b
A	1	2
B	3	4

Table:

var1	var2	var3
A	a	1
A	b	2
B	a	3
B	b	4

Array representation is very concise and often used to efficiently represent data for up to three variables, but it can be hard to manipulate for more than three variables. For example, the three variables needed to represent a network (i, j, w) can be represented as an adjacency matrix array (using an efficient sparse matrix) as compared to using a tabular arc list representation. For more than three variables, it is easier to manipulate tabular data; for example, if a new variable, var4, needs to be added in Ex 1, above, only another column needs to be added to the table, while the array would need to become 3-D.

2. Table Joins and Split-Apply-Combine Strategy

Ex 2: Activity Profiling

Activity profiling is the systematic analysis of the items and orders handled in a warehouse in order to improve its design and operation. In the design of an order picking system, a representative set of customer orders are used together with the item master file to generate parameters that are used for a variety of different warehousing decisions, including equipment and method selection and slotting. If available, the previous three months to one year of customer orders provide a reasonably representative set of orders (a.k.a. an order set).

An example of activity profiling is presented in the figure above. Each customer order is composed of one or more lines, where each line represents an item–quantity pair (e.g., order 1 has five lines, with the first line indicating five units of item A have been ordered). In this example, the five customer orders along with the item master file are used to create the following warehouse design parameters:

1. Total Lines—the total number of lines for all items in all orders over some period of time (representative of total picking activity); used to select piece-picking methods.
2. Lines per Order—the average number of different items (i.e., lines or SKUs) in an order; used to select piece-picking method.
3. Cube per Order—the average total cubic volume of all of the units (i.e., pieces) in an order; used to select piece-picking methods.
4. Flow per Item—the total number of storage and retrieval operations performed for the item over some period of time; used to select pallet-picking equipment.
5. Lines per Item (a.k.a. popularity)—the total number of lines for the item in all orders over some period of time (representative of picking activity for an item); used to select case- and piece-picking equipment and for slotting.
6. Cube Movement—the total unit demand of the item over some period of time times the cubic volume of each unit (representative of the cube in storage for the item); used to select pallet-, case-, and piece-picking equipment.
7. Demand Correlation—the percent (or just the number) of orders in which both items appear; used for zoning and slotting.

In the figure above, the item master file includes the dimensions, cubic volume, and weight of each item. Note that an item's cube can be less than the product of its dimensions (e.g., items C and D). In addition, the unit of measure (UOM) is typically included. The UOM is a description of whether the quantity of inventory for an item refers to individual units (eaches or pieces, EA), cases (CA), or pallets (PA). A conversion ratio is used whenever multiple units of measure are used for the same item.

using DataFrames
dfO = DataFrame(ORDER = [1,1,1,1,2,2,3,4,5,5,5],
    SKU = [1,2,3,4,1,3,1,2,3,4,5],
    QTY = [5,3,2,6,4,1,2,2,1,12,6])

11 rows × 3 columns

	ORDER	SKU	QTY
	Int64	Int64	Int64
1	1	1	5
2	1	2	3
3	1	3	2
4	1	4	6
5	2	1	4
6	2	3	1
7	3	1	2
8	4	2	2
9	5	3	1
10	5	4	12
11	5	5	6

Total Lines (11)

The total number of lines for all items in all orders over some period of time (representative of total picking activity); used to select piece-picking methods.

nrow(dfO)

11

@doc Dict

Dict([itr])

Dict{K,V}() constructs a hash table with keys of type K and values of type V. Keys are compared with isequal and hashed with hash.

Given a single iterable argument, constructs a Dict whose key-value pairs are taken from 2-tuples (key,value) generated by the argument.

Examples

jldoctest
julia> Dict([("A", 1), ("B", 2)])
Dict{String, Int64} with 2 entries:
  "B" => 2
  "A" => 1

Alternatively, a sequence of pair arguments may be passed.

jldoctest
julia> Dict("A"=>1, "B"=>2)
Dict{String, Int64} with 2 entries:
  "B" => 2
  "A" => 1

dp = Dict()
dp[:Total_Lines] = nrow(dfO)
dp

Dict{Any, Any} with 1 entry:
  :Total_Lines => 11

Lines per Order (2.2)

The average number of different items (i.e., lines or SKUs) in an order; used to select the piece-picking method.

dfO[!,:ORDER]

11-element Vector{Int64}:
 1
 1
 1
 1
 2
 2
 3
 4
 5
 5
 5

unique(dfO[!,:ORDER])

5-element Vector{Int64}:
 1
 2
 3
 4
 5

length(unique(dfO[!,:ORDER]))

5

dp[:Total_Lines]/length(unique(dfO[!,:ORDER]))

2.2

dp[:Lines_per_Order] = dp[:Total_Lines]/length(unique(dfO[!,:ORDER]))
dp

Dict{Any, Any} with 2 entries:
  :Total_Lines     => 11
  :Lines_per_Order => 2.2

Cube per Order (493.2)

The average total cubic volume of all of the units (i.e., pieces) in an order; used to select piece-picking methods. The unit of measure (UOM) is typically included. The UOM is a description of whether the quantity of inventory for an item refers to individual units (eaches or pieces, EA), cases (CA), or pallets (PA). A conversion ratio is used whenever multiple units of measure are used for the same item.

dfIM = DataFrame(SKU = [1,2,3,4,5],
    LENGTH = [5,3,8,4,6],
    WIDTH = [3,2,6,4,4],
    DEPTH = [2,4,5,3,5],
    CUBE = [30,24,180,32,120],
    WEIGHT = [1.25,4.75,9.65,6.35,8.2],
    UOM = ["EA","EA","CA","EA","CA"])

5 rows × 7 columns

	SKU	LENGTH	WIDTH	DEPTH	CUBE	WEIGHT	UOM
	Int64	Int64	Int64	Int64	Int64	Float64	String
1	1	5	3	2	30	1.25	EA
2	2	3	2	4	24	4.75	EA
3	3	8	6	5	180	9.65	CA
4	4	4	4	3	32	6.35	EA
5	5	6	4	5	120	8.2	CA

df = innerjoin(dfO,dfIM,on=:SKU)

11 rows × 9 columns

	ORDER	SKU	QTY	LENGTH	WIDTH	DEPTH	CUBE	WEIGHT	UOM
	Int64	Int64	Int64	Int64	Int64	Int64	Int64	Float64	String
1	1	1	5	5	3	2	30	1.25	EA
2	1	2	3	3	2	4	24	4.75	EA
3	1	3	2	8	6	5	180	9.65	CA
4	1	4	6	4	4	3	32	6.35	EA
5	2	1	4	5	3	2	30	1.25	EA
6	2	3	1	8	6	5	180	9.65	CA
7	3	1	2	5	3	2	30	1.25	EA
8	4	2	2	3	2	4	24	4.75	EA
9	5	3	1	8	6	5	180	9.65	CA
10	5	4	12	4	4	3	32	6.35	EA
11	5	5	6	6	4	5	120	8.2	CA

describe(df)

9 rows × 7 columns

	variable	mean	min	median	max	nmissing	eltype
	Symbol	Union…	Any	Union…	Any	Int64	DataType
1	ORDER	2.72727	1	2.0	5	0	Int64
2	SKU	2.63636	1	3.0	5	0	Int64
3	QTY	4.0	1	3.0	12	0	Int64
4	LENGTH	5.36364	3	5.0	8	0	Int64
5	WIDTH	3.90909	2	4.0	6	0	Int64
6	DEPTH	3.63636	2	4.0	5	0	Int64
7	CUBE	78.3636	24	32.0	180	0	Int64
8	WEIGHT	5.73636	1.25	6.35	9.65	0	Float64
9	UOM		CA		EA	0	String

First Approach: Regular Julia

df.CUBE[df.ORDER.==1]

4-element Vector{Int64}:
  30
  24
 180
  32

df.QTY[df.ORDER.==1].*df.CUBE[df.ORDER.==1]

4-element Vector{Int64}:
 150
  72
 360
 192

Total cubic volume for Order 1:

sum(df.QTY[df.ORDER.==1].*df.CUBE[df.ORDER.==1])

774

[sum(df.QTY[df.ORDER.==i].*df.CUBE[df.ORDER.==i]) for i in unique(df.ORDER)]

5-element Vector{Int64}:
  774
  300
   60
   48
 1284

using Statistics
mean([sum(df.QTY[df.ORDER.==i].*df.CUBE[df.ORDER.==i]) for i in unique(df.ORDER)])

493.2

Second Approach: Split-Assign-Combine Strategy

Split: DataFrame grouped by ORDER

gdf = groupby(df,:ORDER)

GroupedDataFrame with 5 groups based on key: ORDER

First Group (4 rows): ORDER = 1

	ORDER	SKU	QTY	LENGTH	WIDTH	DEPTH	CUBE	WEIGHT	UOM
	Int64	Int64	Int64	Int64	Int64	Int64	Int64	Float64	String
1	1	1	5	5	3	2	30	1.25	EA
2	1	2	3	3	2	4	24	4.75	EA
3	1	3	2	8	6	5	180	9.65	CA
4	1	4	6	4	4	3	32	6.35	EA

⋮

Last Group (3 rows): ORDER = 5

	ORDER	SKU	QTY	LENGTH	WIDTH	DEPTH	CUBE	WEIGHT	UOM
	Int64	Int64	Int64	Int64	Int64	Int64	Int64	Float64	String
1	5	3	1	8	6	5	180	9.65	CA
2	5	4	12	4	4	3	32	6.35	EA
3	5	5	6	6	4	5	120	8.2	CA

[i for i in gdf]

5-element Vector{SubDataFrame{DataFrame, DataFrames.Index, Vector{Int64}}}:
 4×9 SubDataFrame
 Row │ ORDER  SKU    QTY    LENGTH  WIDTH  DEPTH  CUBE   WEIGHT   UOM    
     │ Int64  Int64  Int64  Int64   Int64  Int64  Int64  Float64  String 
─────┼───────────────────────────────────────────────────────────────────
   1 │     1      1      5       5      3      2     30     1.25  EA
   2 │     1      2      3       3      2      4     24     4.75  EA
   3 │     1      3      2       8      6      5    180     9.65  CA
   4 │     1      4      6       4      4      3     32     6.35  EA
 2×9 SubDataFrame
 Row │ ORDER  SKU    QTY    LENGTH  WIDTH  DEPTH  CUBE   WEIGHT   UOM    
     │ Int64  Int64  Int64  Int64   Int64  Int64  Int64  Float64  String 
─────┼───────────────────────────────────────────────────────────────────
   1 │     2      1      4       5      3      2     30     1.25  EA
   2 │     2      3      1       8      6      5    180     9.65  CA
 1×9 SubDataFrame
 Row │ ORDER  SKU    QTY    LENGTH  WIDTH  DEPTH  CUBE   WEIGHT   UOM    
     │ Int64  Int64  Int64  Int64   Int64  Int64  Int64  Float64  String 
─────┼───────────────────────────────────────────────────────────────────
   1 │     3      1      2       5      3      2     30     1.25  EA
 1×9 SubDataFrame
 Row │ ORDER  SKU    QTY    LENGTH  WIDTH  DEPTH  CUBE   WEIGHT   UOM    
     │ Int64  Int64  Int64  Int64   Int64  Int64  Int64  Float64  String 
─────┼───────────────────────────────────────────────────────────────────
   1 │     4      2      2       3      2      4     24     4.75  EA
 3×9 SubDataFrame
 Row │ ORDER  SKU    QTY    LENGTH  WIDTH  DEPTH  CUBE   WEIGHT   UOM    
     │ Int64  Int64  Int64  Int64   Int64  Int64  Int64  Float64  String 
─────┼───────────────────────────────────────────────────────────────────
   1 │     5      3      1       8      6      5    180     9.65  CA
   2 │     5      4     12       4      4      3     32     6.35  EA
   3 │     5      5      6       6      4      5    120     8.2   CA

Apply: Total cubic volume of each order

[i.QTY for i in gdf]

5-element Vector{SubArray{Int64, 1, Vector{Int64}, Tuple{Vector{Int64}}, false}}:
 [5, 3, 2, 6]
 [4, 1]
 [2]
 [2]
 [1, 12, 6]

[i.QTY.*i.CUBE for i in gdf]

5-element Vector{Vector{Int64}}:
 [150, 72, 360, 192]
 [120, 180]
 [60]
 [48]
 [180, 384, 720]

[sum(i.QTY.*i.CUBE) for i in gdf]

5-element Vector{Int64}:
  774
  300
   60
   48
 1284

Combine: Mean of total cubic volume per order

mean([sum(i.QTY.*i.CUBE) for i in gdf])

493.2

dp[:Cube_per_Order] = mean([sum(i.QTY.*i.CUBE) for i in gdf])
dp

Dict{Any, Any} with 3 entries:
  :Total_Lines     => 11
  :Lines_per_Order => 2.2
  :Cube_per_Order  => 493.2

Flow per Item (11 for SKU A,5 for SKU B,...)

The total number of storage and retrieval operations performed for the item over some period of time; used to select pallet-picking equipment.

Split:

gdf = groupby(df,:SKU)

GroupedDataFrame with 5 groups based on key: SKU

First Group (3 rows): SKU = 1

	ORDER	SKU	QTY	LENGTH	WIDTH	DEPTH	CUBE	WEIGHT	UOM
	Int64	Int64	Int64	Int64	Int64	Int64	Int64	Float64	String
1	1	1	5	5	3	2	30	1.25	EA
2	2	1	4	5	3	2	30	1.25	EA
3	3	1	2	5	3	2	30	1.25	EA

⋮

Last Group (1 row): SKU = 5

	ORDER	SKU	QTY	LENGTH	WIDTH	DEPTH	CUBE	WEIGHT	UOM
	Int64	Int64	Int64	Int64	Int64	Int64	Int64	Float64	String
1	5	5	6	6	4	5	120	8.2	CA

Apply then Combine: Apply sum function to total quantities for each item and then combine into a new dataframe skudf.

combine(gdf,:QTY => sum)

5 rows × 2 columns

	SKU	QTY_sum
	Int64	Int64
1	1	11
2	2	5
3	3	4
4	4	18
5	5	6

skudf = combine(gdf,:QTY => sum => :Flow_per_Item)

5 rows × 2 columns

	SKU	Flow_per_Item
	Int64	Int64
1	1	11
2	2	5
3	3	4
4	4	18
5	5	6

Lines per Item (3 for SKU A,2 for SKU B,...)

The total number of lines for the item in all orders over some period of time (representative of picking activity for an item); used to select case- and piece-picking equipment and for slotting.

combine(gdf,nrow)

5 rows × 2 columns

	SKU	nrow
	Int64	Int64
1	1	3
2	2	2
3	3	3
4	4	2
5	5	1

skudf = innerjoin(skudf,combine(gdf,nrow => :Lines_per_Item),on=:SKU)

5 rows × 3 columns

	SKU	Flow_per_Item	Lines_per_Item
	Int64	Int64	Int64
1	1	11	3
2	2	5	2
3	3	4	3
4	4	18	2
5	5	6	1

Cube Movement (330 for SKU A,120 for SKU B,...)

The total unit demand of the item over some period of time times the cubic volume of each unit (representative of the cube in storage for the item); used to select pallet-,case-,and piece-picking equipment.

[sum(i.QTY.*i.CUBE) for i in gdf]

5-element Vector{Int64}:
 330
 120
 720
 576
 720

combine(gdf,[:QTY,:CUBE] => ((i,j) -> sum(i.*j)))

5 rows × 2 columns

	SKU	QTY_CUBE_function
	Int64	Int64
1	1	330
2	2	120
3	3	720
4	4	576
5	5	720

skudf = innerjoin(skudf,
    combine(gdf,[:QTY,:CUBE] => ((x,y) -> sum(x.*y)) => :Cube_Movement),on=:SKU)

5 rows × 4 columns

	SKU	Flow_per_Item	Lines_per_Item	Cube_Movement
	Int64	Int64	Int64	Int64
1	1	11	3	330
2	2	5	2	120
3	3	4	3	720
4	4	18	2	576
5	5	6	1	720

dp[:SKU] = skudf
dp

Dict{Any, Any} with 4 entries:
  :Total_Lines     => 11
  :SKU             => 5×4 DataFrame…
  :Lines_per_Order => 2.2
  :Cube_per_Order  => 493.2

dp[:SKU]

5 rows × 4 columns

	SKU	Flow_per_Item	Lines_per_Item	Cube_Movement
	Int64	Int64	Int64	Int64
1	1	11	3	330
2	2	5	2	120
3	3	4	3	720
4	4	18	2	576
5	5	6	1	720

dp[:SKU].Flow_per_Item

5-element Vector{Int64}:
 11
  5
  4
 18
  6

Demand Correlation

The percent of orders in which both items appear. It can be used for zoning and slotting, which are warehouse-related layout problems. Demand correlation is used as the machine-to-machine flow matrix in the QAP-based layout procedures.

dfO

11 rows × 3 columns

	ORDER	SKU	QTY
	Int64	Int64	Int64
1	1	1	5
2	1	2	3
3	1	3	2
4	1	4	6
5	2	1	4
6	2	3	1
7	3	1	2
8	4	2	2
9	5	3	1
10	5	4	12
11	5	5	6

Note: In the following, indexin is used to assure that the number of orders and items are indexed from 1 to m and 1 to n, respectively, in the calculations. The rows and columns of the calculated W corresponed to the items in the sequence returned by unique(item).

order, item = dfO.ORDER, dfO.SKU
order = indexin(order,unique(order))        # Index from 1 to m
item = indexin(item,unique(item))           # Index from 1 to n
m = maximum(order)                          # Number of orders
Order = [Int64[] for i=1:m]                 # Create empty array of integer arrays
for (i,j) in zip(order,item)
    push!(Order[i],j)
end
Order

5-element Vector{Vector{Int64}}:
 [1, 2, 3, 4]
 [1, 3]
 [1]
 [2]
 [3, 4, 5]

n = maximum(item)                           # Number of items
W = zeros(n,n)
for o in Order, i = 1:length(o[1:end-1]), j in o[i+1:end]
    W[o[i],j] += 1
end
W /= m

5×5 Matrix{Float64}:
 0.0  0.2  0.4  0.2  0.0
 0.0  0.0  0.2  0.2  0.0
 0.0  0.0  0.0  0.4  0.2
 0.0  0.0  0.0  0.0  0.2
 0.0  0.0  0.0  0.0  0.0

dp[:DC] = W
dp

Dict{Any, Any} with 5 entries:
  :Total_Lines     => 11
  :SKU             => 5×4 DataFrame…
  :DC              => [0.0 0.2 … 0.2 0.0; 0.0 0.0 … 0.2 0.0; … ; 0.0 0.0 … 0.0 …
  :Lines_per_Order => 2.2
  :Cube_per_Order  => 493.2

3. Missing Data: Dropping vs. Imputation

For a variety of different reasons, some of the cells in a table may be missing data. In Julia, missing data is identified with the type missing. All subsequent results that use the missing data will themselves be identified as missing; as a result, it is necessary to either remove or replace the missing data:

• Dropping: removing any observations that contain cells with missing data
• Imputation: replacing any cells with missing data with a representative value

For large data sets with few missing values, any missing data is typically dropped; for smaller data sets, where the missing data occur in columns containing relatively unimportant variables, it may be possible to provide a reasonable value with which to replace the missing cell. For example, for numerical values, the replacement value could be the average of the non-missing values for the same variable; for categorical values, the replacement value could be the most likely or mode value of the non-missing values.

Ex 3: Add Orders with Missing Data to Ex 2

Note: We need to use the string data to include missing data because creating the table directly using DataFrame, as was done in Ex 2, will generate an error.

using DataFrames,CSV
data = """
ORDER,SKU,QTY
1,1,5
1,2,3
1,3,2
1,4,6
2,1,4
2,3,1
3,1,2
4,2,2
5,3,1
5,4,12
5,5,6
6,,
6,1,
6,4,6
7,,12
7,3,
"""
dfO = DataFrame(CSV.File(IOBuffer(data)))

16 rows × 3 columns

	ORDER	SKU	QTY
	Int64	Int64?	Int64?
1	1	1	5
2	1	2	3
3	1	3	2
4	1	4	6
5	2	1	4
6	2	3	1
7	3	1	2
8	4	2	2
9	5	3	1
10	5	4	12
11	5	5	6
12	6	missing	missing
13	6	1	missing
14	6	4	6
15	7	missing	12
16	7	3	missing

describe(dfO)

3 rows × 7 columns

	variable	mean	min	median	max	nmissing	eltype
	Symbol	Float64	Int64	Float64	Int64	Int64	Type
1	ORDER	3.875	1	4.5	7	0	Int64
2	SKU	2.64286	1	3.0	5	2	Union{Missing, Int64}
3	QTY	4.76923	1	4.0	12	3	Union{Missing, Int64}

For this particular example involving activity profiling, the number of different items (or SKUs) is an important factor indicating the difficulty of order picking individual pieces (EA) or cartons (CA), while the quantity of each item is less important. As a result, any observation with a missing SKU will be dropped, while any observation that is only missing the quantity (QTY) can be replaced with the average quantity ordered for that SKU in the other observations without missing values.

dropmissing!(dfO,:SKU)

14 rows × 3 columns

	ORDER	SKU	QTY
	Int64	Int64	Int64?
1	1	1	5
2	1	2	3
3	1	3	2
4	1	4	6
5	2	1	4
6	2	3	1
7	3	1	2
8	4	2	2
9	5	3	1
10	5	4	12
11	5	5	6
12	6	1	missing
13	6	4	6
14	7	3	missing

dfO.QTY[dfO.SKU.==1]                                  # Just looking at SKU 1

4-element Vector{Union{Missing, Int64}}:
 5
 4
 2
  missing

mean(dfO.QTY[dfO.SKU.==1])                            # Result of mean is missing

missing

mean(skipmissing(dfO.QTY[dfO.SKU.==1]))               # Skip missing values in mean calc

3.6666666666666665

Int(round(mean(skipmissing(dfO.QTY[dfO.SKU.==1]))))   # Replacement value for SKU 1

4

ismissing.(dfO.QTY)                                   # Looking at all SKUs with missing QTY

14-element BitVector:
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 0
 1
 0
 1

idx = findall(ismissing.(dfO.QTY))

2-element Vector{Int64}:
 12
 14

for i in idx
    dfO[i,:QTY] = Int(round(mean(skipmissing(dfO.QTY[dfO.SKU.==dfO[i,:SKU]]))))
end
dfO

14 rows × 3 columns

	ORDER	SKU	QTY
	Int64	Int64	Int64?
1	1	1	5
2	1	2	3
3	1	3	2
4	1	4	6
5	2	1	4
6	2	3	1
7	3	1	2
8	4	2	2
9	5	3	1
10	5	4	12
11	5	5	6
12	6	1	4
13	6	4	6
14	7	3	1