Transformation to DataFrames - Julia DataFrames

Split-apply-combine

using DataFrames

Grouping a data frame¶

groupby

x = DataFrame(id=[1, 2, 3, 4, 1, 2, 3, 4], id2=[1, 2, 1, 2, 1, 2, 1, 2], v=rand(8))

groupby(x, :id)

groupby(x, [])

gx2 = groupby(x, [:id, :id2])

get the parent DataFrame

parent(gx2)

back to the DataFrame, but in a different order of rows than the original

vcat(gx2...)

the same as above

DataFrame(gx2)

drop grouping columns when creating a data frame

DataFrame(gx2, keepkeys=false)

vector of names of grouping variables

groupcols(gx2)

2-element Vector{Symbol}:
 :id
 :id2

and non-grouping variables

valuecols(gx2)

1-element Vector{Symbol}:
 :v

group indices in parent(gx2)

groupindices(gx2)

8-element Vector{Union{Missing, Int64}}:
 1
 2
 3
 4
 1
 2
 3
 4

kgx2 = keys(gx2)

4-element DataFrames.GroupKeys{DataFrames.GroupedDataFrame{DataFrames.DataFrame}}:
 GroupKey: (id = 1, id2 = 1)
 GroupKey: (id = 2, id2 = 2)
 GroupKey: (id = 3, id2 = 1)
 GroupKey: (id = 4, id2 = 2)

You can index into a GroupedDataFrame like to a vector or to a dictionary. The second form accepts GroupKey, NamedTuple or a Tuple.

gx2

k = keys(gx2)[1]

GroupKey: (id = 1, id2 = 1)

ntk = NamedTuple(k)

(id = 1, id2 = 1)

tk = Tuple(k)

(1, 1)

the operations below produce the same result and are proformant

gx2[1], gx2[k], gx2[ntk], gx2[tk]

(2×3 SubDataFrame
 Row │ id     id2    v        
     │ Int64  Int64  Float64  
─────┼────────────────────────
   1 │     1      1  0.141917
   2 │     1      1  0.62969, 2×3 SubDataFrame
 Row │ id     id2    v        
     │ Int64  Int64  Float64  
─────┼────────────────────────
   1 │     1      1  0.141917
   2 │     1      1  0.62969, 2×3 SubDataFrame
 Row │ id     id2    v        
     │ Int64  Int64  Float64  
─────┼────────────────────────
   1 │     1      1  0.141917
   2 │     1      1  0.62969, 2×3 SubDataFrame
 Row │ id     id2    v        
     │ Int64  Int64  Float64  
─────┼────────────────────────
   1 │     1      1  0.141917
   2 │     1      1  0.62969)

handling missing values

x = DataFrame(id=[missing, 5, 1, 3, missing], x=1:5)

by default groups include missing values and their order is not guaranteed

groupby(x, :id)

but we can change it; now they are sorted

groupby(x, :id, sort=true, skipmissing=true)

and now they are in the order they appear in the source data frame

groupby(x, :id, sort=false)

Performing transformations¶

by group using combine, select, select!, transform, and transform!

using Statistics
using Chain

x = DataFrame(id=rand('a':'d', 100), v=rand(100))

apply a function to each group of a data frame combine keeps as many rows as are returned from the function

@chain x begin
    groupby(:id)
    combine(:v => mean)
end

x.id2 = axes(x, 1)

Base.OneTo(100)

Select and transform keep as many rows as are in the source data frame and in correct order. Additionally, transform keeps all columns from the source.

@chain x begin
    groupby(:id)
    transform(:v => mean)
end

note that combine reorders rows by group of GroupedDataFrame

@chain x begin
    groupby(:id)
    combine(:id2, :v => mean)
end

we give a custom name for the result column

@chain x begin
    groupby(:id)
    combine(:v => mean => :res)
end

you can have multiple operations

@chain x begin
    groupby(:id)
    combine(:v => mean => :res1, :v => sum => :res2, nrow => :n)
end

Additional notes:

select! and transform! perform operations in-place
The general syntax for transformation is source_columns => function => target_column
if you pass multiple columns to a function they are treated as positional arguments
ByRow and AsTable work exactly like discussed for operations on data frames in 05_columns.ipynb
you can automatically groupby again the result of combine, select etc. by passing ungroup=false keyword argument to them
similarly keepkeys keyword argument allows you to drop grouping columns from the resulting data frame

It is also allowed to pass a function to all these functions (also - as a special case, as a first argument). In this case the return value can be a table. In particular it allows for an easy dropping of groups if you return an empty table from the function.

If you pass a function you can use a do block syntax. In case of passing a function it gets a SubDataFrame as its argument.

Here is an example:

combine(groupby(x, :id)) do sdf
    n = nrow(sdf)
    n < 25 ? DataFrame() : DataFrame(n=n) ## drop groups with low number of rows
end

You can also produce multiple columns in a single operation:

df = DataFrame(id=[1, 1, 2, 2], val=[1, 2, 3, 4])

@chain df begin
    groupby(:id)
    combine(:val => (x -> [x]) => AsTable)
end

@chain df begin
    groupby(:id)
    combine(:val => (x -> [x]) => [:c1, :c2])
end

It is easy to unnest the column into multiple columns,

df = DataFrame(a=[(p=1, q=2), (p=3, q=4)])
select(df, :a => AsTable)

automatic column names generated

df = DataFrame(a=[[1, 2], [3, 4]])
select(df, :a => AsTable)

custom column names generated

select(df, :a => [:C1, :C2])

Finally, observe that one can conveniently apply multiple transformations using broadcasting:

df = DataFrame(id=repeat(1:10, 10), x1=1:100, x2=101:200)

@chain df begin
    groupby(:id)
    combine([:x1, :x2] .=> minimum)
end

@chain df begin
    groupby(:id)
    combine([:x1, :x2] .=> [minimum maximum])
end

Aggregation of a data frame using mapcols¶

x = DataFrame(rand(10, 10), :auto)

mapcols(mean, x)

Mapping rows and columns using eachcol and eachrow¶

map a function over each column and return a vector

map(mean, eachcol(x))

10-element Vector{Float64}:
 0.47805571597823826
 0.4034192613266079
 0.41279930088034067
 0.4963936251688487
 0.49467866325358967
 0.45527706225157283
 0.6170916394287363
 0.3196607126795527
 0.4788083299773283
 0.5672069408272322

an iteration returns a Pair with column name and values

foreach(c -> println(c[1], ": ", mean(c[2])), pairs(eachcol(x)))

x1: 0.47805571597823826
x2: 0.4034192613266079
x3: 0.41279930088034067
x4: 0.4963936251688487
x5: 0.49467866325358967
x6: 0.45527706225157283
x7: 0.6170916394287363
x8: 0.3196607126795527
x9: 0.4788083299773283
x10: 0.5672069408272322

now the returned value is DataFrameRow which works as a NamedTuple but is a view to a parent DataFrame

map(r -> r.x1 / r.x2, eachrow(x))

10-element Vector{Float64}:
  1.3894356729457515
  0.42259608799520976
  0.09657071071669557
  2.0719341997603697
  0.7727171150188653
  3.895277152152126
  1.1004675374270867
 31.30402907879296
 22.00825012356712
  0.7403687882869099

it prints like a data frame, only the caption is different so that you know the type of the object

er = eachrow(x)
er.x1 ## you can access columns of a parent data frame directly

10-element Vector{Float64}:
 0.7496183002955658
 0.4128341397039369
 0.0696269903073844
 0.5280708097857694
 0.3359251089854304
 0.49800906924137367
 0.392073617707525
 0.9892148475285459
 0.38007522982611863
 0.42510904640073266

it prints like a data frame, only the caption is different so that you know the type of the object

ec = eachcol(x)

you can access columns of a parent data frame directly

ec.x1

10-element Vector{Float64}:
 0.7496183002955658
 0.4128341397039369
 0.0696269903073844
 0.5280708097857694
 0.3359251089854304
 0.49800906924137367
 0.392073617707525
 0.9892148475285459
 0.38007522982611863
 0.42510904640073266

Transposing¶

you can transpose a data frame using permutedims:

df = DataFrame(reshape(1:12, 3, 4), :auto)

df.names = ["a", "b", "c"]

3-element Vector{String}:
 "a"
 "b"
 "c"

permutedims(df, :names)

This notebook was generated using Literate.jl.