Transformation to 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))
8×3 DataFrame
Rowidid2v
Int64Int64Float64
1110.668046
2220.0217206
3310.825651
4420.835345
5110.0516351
6220.91682
7310.669437
8420.524971
groupby(x, :id)

GroupedDataFrame with 4 groups based on key: id

First Group (2 rows): id = 1
Rowidid2v
Int64Int64Float64
1110.668046
2110.0516351

Last Group (2 rows): id = 4
Rowidid2v
Int64Int64Float64
1420.835345
2420.524971
groupby(x, [])

GroupedDataFrame with 1 group based on key:

First Group (8 rows):
Rowidid2v
Int64Int64Float64
1110.668046
2220.0217206
3310.825651
4420.835345
5110.0516351
6220.91682
7310.669437
8420.524971
gx2 = groupby(x, [:id, :id2])

GroupedDataFrame with 4 groups based on keys: id, id2

First Group (2 rows): id = 1, id2 = 1
Rowidid2v
Int64Int64Float64
1110.668046
2110.0516351

Last Group (2 rows): id = 4, id2 = 2
Rowidid2v
Int64Int64Float64
1420.835345
2420.524971

get the parent DataFrame

parent(gx2)
8×3 DataFrame
Rowidid2v
Int64Int64Float64
1110.668046
2220.0217206
3310.825651
4420.835345
5110.0516351
6220.91682
7310.669437
8420.524971

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

vcat(gx2...)
8×3 DataFrame
Rowidid2v
Int64Int64Float64
1110.668046
2110.0516351
3220.0217206
4220.91682
5310.825651
6310.669437
7420.835345
8420.524971

the same as above

DataFrame(gx2)
8×3 DataFrame
Rowidid2v
Int64Int64Float64
1110.668046
2110.0516351
3220.0217206
4220.91682
5310.825651
6310.669437
7420.835345
8420.524971

drop grouping columns when creating a data frame

DataFrame(gx2, keepkeys=false)
8×1 DataFrame
Rowv
Float64
10.668046
20.0516351
30.0217206
40.91682
50.825651
60.669437
70.835345
80.524971

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

GroupedDataFrame with 4 groups based on keys: id, id2

First Group (2 rows): id = 1, id2 = 1
Rowidid2v
Int64Int64Float64
1110.668046
2110.0516351

Last Group (2 rows): id = 4, id2 = 2
Rowidid2v
Int64Int64Float64
1420.835345
2420.524971
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.668046
   2 │     1      1  0.0516351, 2×3 SubDataFrame
 Row  id     id2    v         
     │ Int64  Int64  Float64   
─────┼─────────────────────────
   1 │     1      1  0.668046
   2 │     1      1  0.0516351, 2×3 SubDataFrame
 Row  id     id2    v         
     │ Int64  Int64  Float64   
─────┼─────────────────────────
   1 │     1      1  0.668046
   2 │     1      1  0.0516351, 2×3 SubDataFrame
 Row  id     id2    v         
     │ Int64  Int64  Float64   
─────┼─────────────────────────
   1 │     1      1  0.668046
   2 │     1      1  0.0516351)

handling missing values

x = DataFrame(id=[missing, 5, 1, 3, missing], x=1:5)
5×2 DataFrame
Rowidx
Int64?Int64
1missing1
252
313
434
5missing5

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

groupby(x, :id)

GroupedDataFrame with 4 groups based on key: id

First Group (1 row): id = 1
Rowidx
Int64?Int64
113

Last Group (2 rows): id = missing
Rowidx
Int64?Int64
1missing1
2missing5

but we can change it; now they are sorted

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

GroupedDataFrame with 3 groups based on key: id

First Group (1 row): id = 1
Rowidx
Int64?Int64
113

Last Group (1 row): id = 5
Rowidx
Int64?Int64
152

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

groupby(x, :id, sort=false)

GroupedDataFrame with 4 groups based on key: id

First Group (2 rows): id = missing
Rowidx
Int64?Int64
1missing1
2missing5

Last Group (1 row): id = 3
Rowidx
Int64?Int64
134

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))
100×2 DataFrame
75 rows omitted
Rowidv
CharFloat64
1a0.170523
2d0.914176
3d0.204357
4d0.603191
5b0.887813
6b0.414486
7c0.0844315
8d0.0611222
9a0.960798
10b0.427352
11d0.527319
12a0.763995
13a0.0469857
89d0.15171
90b0.461184
91b0.583866
92c0.658358
93c0.24761
94c0.590359
95b0.432363
96a0.844712
97b0.825326
98a0.210774
99a0.0527467
100c0.985588

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
4×2 DataFrame
Rowidv_mean
CharFloat64
1a0.481783
2d0.534384
3b0.583599
4c0.45104
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
100×4 DataFrame
75 rows omitted
Rowidvid2v_mean
CharFloat64Int64Float64
1a0.17052310.481783
2d0.91417620.534384
3d0.20435730.534384
4d0.60319140.534384
5b0.88781350.583599
6b0.41448660.583599
7c0.084431570.45104
8d0.061122280.534384
9a0.96079890.481783
10b0.427352100.583599
11d0.527319110.534384
12a0.763995120.481783
13a0.0469857130.481783
89d0.15171890.534384
90b0.461184900.583599
91b0.583866910.583599
92c0.658358920.45104
93c0.24761930.45104
94c0.590359940.45104
95b0.432363950.583599
96a0.844712960.481783
97b0.825326970.583599
98a0.210774980.481783
99a0.0527467990.481783
100c0.9855881000.45104

note that combine reorders rows by group of GroupedDataFrame

@chain x begin
    groupby(:id)
    combine(:id2, :v => mean)
end
100×3 DataFrame
75 rows omitted
Rowidid2v_mean
CharInt64Float64
1a10.481783
2a90.481783
3a120.481783
4a130.481783
5a140.481783
6a160.481783
7a180.481783
8a230.481783
9a240.481783
10a280.481783
11a330.481783
12a340.481783
13a380.481783
89c440.45104
90c450.45104
91c600.45104
92c610.45104
93c660.45104
94c680.45104
95c770.45104
96c800.45104
97c920.45104
98c930.45104
99c940.45104
100c1000.45104

we give a custom name for the result column

@chain x begin
    groupby(:id)
    combine(:v => mean => :res)
end
4×2 DataFrame
Rowidres
CharFloat64
1a0.481783
2d0.534384
3b0.583599
4c0.45104

you can have multiple operations

@chain x begin
    groupby(:id)
    combine(:v => mean => :res1, :v => sum => :res2, nrow => :n)
end
4×4 DataFrame
Rowidres1res2n
CharFloat64Float64Int64
1a0.48178313.971729
2d0.53438413.359625
3b0.58359916.340828
4c0.451048.1187318

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
3×2 DataFrame
Rowidn
CharInt64
1a29
2d25
3b28

You can also produce multiple columns in a single operation:

df = DataFrame(id=[1, 1, 2, 2], val=[1, 2, 3, 4])
4×2 DataFrame
Rowidval
Int64Int64
111
212
323
424
@chain df begin
    groupby(:id)
    combine(:val => (x -> [x]) => AsTable)
end
2×3 DataFrame
Rowidx1x2
Int64Int64Int64
1112
2234
@chain df begin
    groupby(:id)
    combine(:val => (x -> [x]) => [:c1, :c2])
end
2×3 DataFrame
Rowidc1c2
Int64Int64Int64
1112
2234

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)
2×2 DataFrame
Rowpq
Int64Int64
112
234

automatic column names generated

df = DataFrame(a=[[1, 2], [3, 4]])
select(df, :a => AsTable)
2×2 DataFrame
Rowx1x2
Int64Int64
112
234

custom column names generated

select(df, :a => [:C1, :C2])
2×2 DataFrame
RowC1C2
Int64Int64
112
234

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

df = DataFrame(id=repeat(1:10, 10), x1=1:100, x2=101:200)
100×3 DataFrame
75 rows omitted
Rowidx1x2
Int64Int64Int64
111101
222102
333103
444104
555105
666106
777107
888108
999109
101010110
11111111
12212112
13313113
89989189
901090190
91191191
92292192
93393193
94494194
95595195
96696196
97797197
98898198
99999199
10010100200
@chain df begin
    groupby(:id)
    combine([:x1, :x2] .=> minimum)
end
10×3 DataFrame
Rowidx1_minimumx2_minimum
Int64Int64Int64
111101
222102
333103
444104
555105
666106
777107
888108
999109
101010110
@chain df begin
    groupby(:id)
    combine([:x1, :x2] .=> [minimum maximum])
end
10×5 DataFrame
Rowidx1_minimumx2_minimumx1_maximumx2_maximum
Int64Int64Int64Int64Int64
11110191191
22210292192
33310393193
44410494194
55510595195
66610696196
77710797197
88810898198
99910999199
101010110100200

Aggregation of a data frame using mapcols#

x = DataFrame(rand(10, 10), :auto)
10×10 DataFrame
Rowx1x2x3x4x5x6x7x8x9x10
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.8684590.006544180.4336370.9305470.231680.427770.75470.5202750.4250560.861338
20.1446810.01941370.1111780.6594970.5249440.07394650.5988110.1281280.2613840.512681
30.9739160.4819150.8000240.04075360.6299610.9135660.1103350.722250.7685120.692188
40.3415840.3124960.02840950.675260.1152660.922510.7412320.5022860.826530.354182
50.3876460.6709770.3254910.1730940.25060.9909140.317260.286570.5744340.132789
60.7175220.9069630.4475980.2656810.1648260.1367810.666080.4773390.6657170.995873
70.6690040.7632950.6865240.138380.1002310.9936520.5789470.4236810.1606220.959578
80.5739460.2654920.3125780.4173220.821730.4384930.05239870.3315110.5190.159041
90.5462380.1000020.4261460.170620.02329230.3214380.1505060.6746690.5449130.614595
100.747450.1927850.2616770.0814630.867660.07882690.4630880.3403940.7281780.672423
mapcols(mean, x)
1×10 DataFrame
Rowx1x2x3x4x5x6x7x8x9x10
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.5970450.3719880.3833260.3552620.3730190.529790.4433360.440710.5474350.595469

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.5970446200515614
 0.371988280043345
 0.3833261421741702
 0.35526177851212193
 0.3730189771544299
 0.5297896374308705
 0.4433358543846618
 0.4407101912829486
 0.5474346036000817
 0.5954687362554464

an iteration returns a Pair with column name and values

foreach(c -> println(c[1], ": ", mean(c[2])), pairs(eachcol(x)))
x1: 0.5970446200515614
x2: 0.371988280043345
x3: 0.3833261421741702
x4: 0.35526177851212193
x5: 0.3730189771544299
x6: 0.5297896374308705
x7: 0.4433358543846618
x8: 0.4407101912829486
x9: 0.5474346036000817
x10: 0.5954687362554464

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}:
 132.70712754424997
   7.452529393425048
   2.0209313586052504
   1.0930815434814145
   0.5777342899285142
   0.7911259171572509
   0.8764693136551583
   2.161819129709327
   5.4622427942466985
   3.8771071571962517

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.8684590156304837
 0.144681492763982
 0.9739162879177052
 0.341583618406165
 0.38764646475147546
 0.7175215916255719
 0.6690043125147219
 0.5739460726996791
 0.5462377762078239
 0.7474495679980068

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

ec = eachcol(x)
10×10 DataFrameColumns
Rowx1x2x3x4x5x6x7x8x9x10
Float64Float64Float64Float64Float64Float64Float64Float64Float64Float64
10.8684590.006544180.4336370.9305470.231680.427770.75470.5202750.4250560.861338
20.1446810.01941370.1111780.6594970.5249440.07394650.5988110.1281280.2613840.512681
30.9739160.4819150.8000240.04075360.6299610.9135660.1103350.722250.7685120.692188
40.3415840.3124960.02840950.675260.1152660.922510.7412320.5022860.826530.354182
50.3876460.6709770.3254910.1730940.25060.9909140.317260.286570.5744340.132789
60.7175220.9069630.4475980.2656810.1648260.1367810.666080.4773390.6657170.995873
70.6690040.7632950.6865240.138380.1002310.9936520.5789470.4236810.1606220.959578
80.5739460.2654920.3125780.4173220.821730.4384930.05239870.3315110.5190.159041
90.5462380.1000020.4261460.170620.02329230.3214380.1505060.6746690.5449130.614595
100.747450.1927850.2616770.0814630.867660.07882690.4630880.3403940.7281780.672423

you can access columns of a parent data frame directly

ec.x1
10-element Vector{Float64}:
 0.8684590156304837
 0.144681492763982
 0.9739162879177052
 0.341583618406165
 0.38764646475147546
 0.7175215916255719
 0.6690043125147219
 0.5739460726996791
 0.5462377762078239
 0.7474495679980068

Transposing#

you can transpose a data frame using permutedims:

df = DataFrame(reshape(1:12, 3, 4), :auto)
3×4 DataFrame
Rowx1x2x3x4
Int64Int64Int64Int64
114710
225811
336912
df.names = ["a", "b", "c"]
3-element Vector{String}:
 "a"
 "b"
 "c"
permutedims(df, :names)
4×4 DataFrame
Rownamesabc
StringInt64Int64Int64
1x1123
2x2456
3x3789
4x4101112

This notebook was generated using Literate.jl.