Outline¶
- Missings
- DataFrames
- Query
DataFrames¶
In [1]:
using Statistics
using DataFrames, CSV, Query
using RDatasets
In [2]:
using Pkg
Pkg.status(["DataFrames", "CSV", "Query", "RDatasets"])
Missing¶
missingis used to represent a missing value in Julia, and it is a built-in object.missingis the singleton ofMissingtype.- Whenever operations including
missingare undetermined will returnmissing.
In [3]:
true && missing
Out[3]:
In [4]:
1 + missing
Out[4]:
Short-cut logic¶
In [5]:
false && missing
Out[5]:
In [6]:
true || missing
Out[6]:
In [7]:
ismissing(missing)
Out[7]:
DataFrame¶
DataFrameis a table-like data structure containing a series of vectors.- Each column corresponds to a variable and has the same length.
In [8]:
df = DataFrame(A = 1:4, B = ["M", "F", "F", "M"])
Out[8]:
In [9]:
df = DataFrame() # 先初始化一個空的，再放入資料
df[!, :A] = 1:8
df[!, :B] = ["M", "F", "F", "M", "F", "M", "M", "F"]
df
Out[9]:
Initialize by rows¶
In [10]:
df = DataFrame(A=Int64[], B=String[])
Out[10]:
In [11]:
push!(df, (1, "M"))
Out[11]:
In [12]:
push!(df, [2, "F"])
Out[12]:
In [13]:
push!(df, Dict(:B => "M", :A => 4))
Out[13]:
Initialize by matrix¶
In [14]:
mat = [1 "M"; 2 "F"; 3 "F"]
DataFrame(mat, [:A, :B])
Out[14]:
Initialize by dictionary¶
In [15]:
d = Dict("A" => [1, 2, 3], "B" => ["M", "F", "F"])
DataFrame(d)
Out[15]:
Get column¶
In [16]:
df[!, :A]
Out[16]:
In [17]:
df[!, 1]
Out[17]:
In [18]:
df.A
Out[18]:
Indexing by integer¶
In [19]:
df[1, 1]
Out[19]:
Indexing by column name¶
In [20]:
df[1, :A]
Out[20]:
Dimension information¶
In [21]:
size(df)
Out[21]:
In [22]:
nrow(df)
Out[22]:
In [23]:
ncol(df)
Out[23]:
Get column names¶
In [24]:
names(df)
Out[24]:
In [25]:
df[1:3, :]
Out[25]:
Get slice¶
In [26]:
df[1:3, [:B, :A]]
Out[26]:
Assign column¶
In [27]:
df[!, :C] = ['a', 'b', 'c']
df
Out[27]:
In [28]:
df[!, :B] = ['α', 'β', 'γ']
df
Out[28]:
Conditioning¶
In [29]:
df[df[!,:A] .% 2 .== 0, :] # 取符合條件的列，跟所有欄位
Out[29]:
Calculation¶
In [30]:
df[!, :D] = df[!, :B] .* df[!, :C]
df
Out[30]:
Aggregation¶
In [31]:
mean(df[!,1]) # 對第1欄取平均
Out[31]:
In [32]:
median(df[!,:A]) # 對第一個欄位取中位數
Out[32]:
In [33]:
df = DataFrame(A = 1:4, B = randn(4))
mapcols(cumsum, df)
Out[33]:
Storage¶
In [34]:
CSV.write("test.csv", df)
Out[34]:
In [35]:
df = CSV.read("test.csv")
Out[35]:
Play with data¶
In [36]:
first(RDatasets.datasets(), 6) # 可以選你要的資料集
Out[36]:
In [37]:
iris = dataset("datasets", "iris")
first(iris, 6)
Out[37]:
In [38]:
size(iris) # 看一下他有幾列幾行
Out[38]:
Concatenate DataFrames¶
In [39]:
size(vcat(iris, iris))
Out[39]:
In [40]:
size(hcat(iris, iris, makeunique=true))
Out[40]:
Check if missing exists in each row¶
In [41]:
completecases(iris) # true means no missing
Out[41]:
Select complete cases¶
In [42]:
first(iris[completecases(iris), :], 10) # equivalent to complete_cases!(iris)
Out[42]:
Get unique combinations¶
In [43]:
first(unique(iris), 10)
Out[43]:
In [44]:
iris[!,:PetalArea] = iris[!,:PetalLength] .* iris[!,:PetalWidth]
first(iris)
Out[44]:
Sorting¶
In [45]:
first(sort!(iris, :SepalLength), 10)
Out[45]:
Sorting multiple columns and specify the order¶
In [46]:
first(sort!(iris, [:Species, :SepalLength, :SepalWidth], rev=[true, false, false]), 10)
Out[46]:
Join¶
In [47]:
employees = DataFrame(ID = [1, 2, 3], Name = ["John Doe", "Jane Doe", "Andy Doe"])
Out[47]:
In [48]:
jobs = DataFrame(ID = [1, 2, 4], Job = ["Lawyer", "Doctor", "Chief"])
Out[48]:
Inner join¶
In [49]:
full = innerjoin(employees, jobs, on=:ID)
Out[49]:
Left join¶
In [50]:
left_join = leftjoin(employees, jobs, on=:ID)
Out[50]:
Right join¶
In [51]:
right_join = rightjoin(employees, jobs, on=:ID)
Out[51]:
Outer join¶
In [52]:
outer_join = outerjoin(employees, jobs, on=:ID)
Out[52]:
Other join¶
- Semi join: similar to inner join，but only outputs left table
- Anti join
- Cross join: Cartesian product
In [53]:
cross_join = crossjoin(employees, jobs, makeunique=true) # 不需要key
Out[53]:
Split-Apply-Combine Strategy¶
In [54]:
by(iris, :Species, size)
Out[54]:
More complex situation¶
In [55]:
by(iris, :Species) do df
DataFrame(μ = mean(df[!,:PetalLength]), σ = var(df[!,:PetalLength]))
end
Out[55]:
In [56]:
aggregate(iris, :Species, [sum, mean])
Out[56]:
Group by¶
In [57]:
for subdf in groupby(iris, :Species)
println(size(subdf, 1))
end
Group by and combine¶
In [58]:
gdf = groupby(iris, :Species)
Out[58]:
In [59]:
combine(gdf, :PetalLength => mean)
Out[59]:
Reshape¶
Original data format is wide format¶
In [60]:
iris[!,:id] = 1:size(iris, 1)
first(iris, 10)
Out[60]:
Stack specified column into data and transform into long format¶
In [61]:
d = stack(iris, [:SepalLength, :SepalWidth, :PetalLength, :PetalWidth])
first(d, 10)
Out[61]:
Stack other columns¶
In [62]:
d = stack(iris, [:SepalLength, :SepalWidth], :Species)
first(d, 10)
Out[62]:
Unstack¶
In [63]:
d = stack(iris, [:SepalLength, :SepalWidth, :PetalLength, :PetalWidth])
first(unstack(d, :id, :variable, :value), 10)
Out[63]:
Not specify identifier¶
In [64]:
first(unstack(d, :variable, :value), 10)
Out[64]:
Application¶
Calculate the average of every feature grouped by :Species
In [65]:
d = stack(iris)
x = by(d, [:variable, :Species], df -> DataFrame(vsum = mean(df[!,:value])))
unstack(x, :Species, :vsum)
Out[65]:
Query¶
In [66]:
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,5,2])
Out[66]:
select-from-where¶
In [67]:
x = @from i in df begin
@where i.age>50
@select {i.name, i.children}
@collect DataFrame
end
Out[67]:
Sorting¶
In [68]:
df = DataFrame(a=[2,1,1,2,1,3],b=[2,2,1,1,3,2])
x = @from i in df begin
@orderby descending(i.a), i.b
@select i
@collect DataFrame
end
Out[68]:
Filtering¶
In [69]:
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,5,2])
x = @from i in df begin
@where i.age > 30. && i.children > 2
@select i
@collect DataFrame
end
Out[69]:
Projecting¶
In [70]:
data = [1,2,3]
x = @from i in data begin
@select i^2
@collect
end
Out[70]:
Flattening¶
In [71]:
source = Dict(:a=>[1,2,3], :b=>[4,5])
q = @from i in source begin
@from j in i.second
@select {Key=i.first,Value=j}
@collect DataFrame
end
Out[71]:
Grouping¶
In [72]:
df = DataFrame(name=["John", "Sally", "Kirk"], age=[23., 42., 59.], children=[3,2,2])
x = @from i in df begin
@group i.name by i.children
@collect
end
Out[72]: