Introduction to DataFrames

10_transforms

DataFrame v1.2, Julia 1.6.1

using DataFrames, Pipe, BenchmarkTools

Split-apply-combine

Grouping a data frame

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

8 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	2	2	0.837793
3	3	1	0.222869
4	4	2	0.40303
5	1	1	0.415974
6	2	2	0.907121
7	3	1	0.0626348
8	4	2	0.513873

groupby(x,:id)

GroupedDataFrame with 4 groups based on key: id

First Group (2 rows): id = 1

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

⋮

Last Group (2 rows): id = 4

	id	id2	v
	Int64	Int64	Float64
1	4	2	0.40303
2	4	2	0.513873

groupby(x,[])

GroupedDataFrame with 1 group based on key:

First Group (8 rows):

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	2	2	0.837793
3	3	1	0.222869
4	4	2	0.40303
5	1	1	0.415974
6	2	2	0.907121
7	3	1	0.0626348
8	4	2	0.513873

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

GroupedDataFrame with 4 groups based on keys: id, id2

First Group (2 rows): id = 1, id2 = 1

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

⋮

Last Group (2 rows): id = 4, id2 = 2

	id	id2	v
	Int64	Int64	Float64
1	4	2	0.40303
2	4	2	0.513873

p1 = parent(gx2) # get the parent DataFrame

8 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	10	0.670434
2	2	2	0.837793
3	3	1	0.222869
4	4	2	0.40303
5	1	1	0.415974
6	2	2	0.907121
7	3	1	0.0626348
8	4	2	0.513873

parent는 parent의 주소를 돌려 준다.

p1 === x

true

# back to the DataFrame, but in a different order of rows than the original
vcat(gx2...)

8 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974
3	2	2	0.837793
4	2	2	0.907121
5	3	1	0.222869
6	3	1	0.0626348
7	4	2	0.40303
8	4	2	0.513873

DataFrame(gx2) # the same

8 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974
3	2	2	0.837793
4	2	2	0.907121
5	3	1	0.222869
6	3	1	0.0626348
7	4	2	0.40303
8	4	2	0.513873

# drop grouping columns when creating a data frame
DataFrame(gx2,keepkeys=false)

8 rows × 1 columns

	v
	Float64
1	0.670434
2	0.415974
3	0.837793
4	0.907121
5	0.222869
6	0.0626348
7	0.40303
8	0.513873

# vector of names of grouping variables
groupcols(gx2)

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

valuecols(gx2) # and non-grouping variables

1-element Vector{Symbol}:
 :v

groupindices(gx2) # group indices in parent(gx2)

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

kgx2 = keys(gx2)

4-element DataFrames.GroupKeys{GroupedDataFrame{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 GroupDataFrame like to a vector or to a dictionary. The second form acceps GroupKey,NameTuple or Tuple

gx2

GroupedDataFrame with 4 groups based on keys: id, id2

First Group (2 rows): id = 1, id2 = 1

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

⋮

Last Group (2 rows): id = 4, id2 = 2

	id	id2	v
	Int64	Int64	Float64
1	4	2	0.40303
2	4	2	0.513873

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 fast

@btime gx2[1]

  325.930 ns (3 allocations: 192 bytes)

2 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

@btime gx2[k]

  383.871 ns (3 allocations: 192 bytes)

2 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

@btime gx2[ntk]

  545.723 ns (3 allocations: 192 bytes)

2 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

@btime gx2[tk]

  483.284 ns (3 allocations: 192 bytes)

2 rows × 3 columns

	id	id2	v
	Int64	Int64	Float64
1	1	1	0.670434
2	1	1	0.415974

handling missing values

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

5 rows × 2 columns

	id	x
	Int64?	Int64
1	missing	1
2	5	2
3	1	3
4	3	4
5	missing	5

# by default group include missing values and are not sorted
groupby(x,:id)

GroupedDataFrame with 4 groups based on key: id

First Group (1 row): id = 1

	id	x
	Int64?	Int64
1	1	3

⋮

Last Group (2 rows): id = missing

	id	x
	Int64?	Int64
1	missing	1
2	missing	5

groupby(x,:id,sort=true,skipmissing=true) # but we can change it

GroupedDataFrame with 3 groups based on key: id

First Group (1 row): id = 1

	id	x
	Int64?	Int64
1	1	3

⋮

Last Group (1 row): id = 5

	id	x
	Int64?	Int64
1	5	2

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

using Statistics
using Chain

Reduce the number of rows in the output

ENV["LINES"] = 15

15

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

100 rows × 2 columns

	id	v
	Char	Float64
1	b	0.778459
2	c	0.965702
3	a	0.714609
4	a	0.883121
5	c	0.542032
6	d	0.115543
7	b	0.458695
8	c	0.277306
9	b	0.18459
10	b	0.565907
11	a	0.390352
12	a	0.670578
13	d	0.344171
14	b	0.462482
15	c	0.185975
⋮	⋮	⋮

Apply a function to each group of data frame

combine keeps as many rows as are returned from the function

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

4 rows × 2 columns

	id	v_mean
	Char	Float64
1	b	0.619295
2	c	0.538388
3	a	0.52231
4	d	0.54441

x.id2 = axes(x,1)
x

100 rows × 3 columns

	id	v	id2
	Char	Float64	Int64
1	b	0.778459	1
2	c	0.965702	2
3	a	0.714609	3
4	a	0.883121	4
5	c	0.542032	5
6	d	0.115543	6
7	b	0.458695	7
8	c	0.277306	8
9	b	0.18459	9
10	b	0.565907	10
11	a	0.390352	11
12	a	0.670578	12
13	d	0.344171	13
14	b	0.462482	14
15	c	0.185975	15
⋮	⋮	⋮	⋮

axes(x)

(Base.OneTo(100), Base.OneTo(3))

# 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 rows × 4 columns

	id	v	id2	v_mean
	Char	Float64	Int64	Float64
1	b	0.778459	1	0.619295
2	c	0.965702	2	0.538388
3	a	0.714609	3	0.52231
4	a	0.883121	4	0.52231
5	c	0.542032	5	0.538388
6	d	0.115543	6	0.54441
7	b	0.458695	7	0.619295
8	c	0.277306	8	0.538388
9	b	0.18459	9	0.619295
10	b	0.565907	10	0.619295
11	a	0.390352	11	0.52231
12	a	0.670578	12	0.52231
13	d	0.344171	13	0.54441
14	b	0.462482	14	0.619295
15	c	0.185975	15	0.538388
⋮	⋮	⋮	⋮	⋮

@pipe x |> groupby(_,:id)

GroupedDataFrame with 4 groups based on key: id

First Group (23 rows): id = 'b'

	id	v	id2
	Char	Float64	Int64
1	b	0.778459	1
2	b	0.458695	7
3	b	0.18459	9
4	b	0.565907	10
5	b	0.462482	14
6	b	0.335857	17
7	b	0.758001	22
8	b	0.916366	29
9	b	0.428636	31
10	b	0.610145	32
11	b	0.808621	36
12	b	0.865517	48
13	b	0.938382	52
14	b	0.17824	65
15	b	0.141403	71
⋮	⋮	⋮	⋮

⋮

Last Group (31 rows): id = 'd'

	id	v	id2
	Char	Float64	Int64
1	d	0.115543	6
2	d	0.344171	13
3	d	0.169468	18
4	d	0.583126	21
5	d	0.190657	25
6	d	0.921866	26
7	d	0.812919	30
8	d	0.169001	34
9	d	0.755057	39
10	d	0.277182	40
11	d	0.200827	46
12	d	0.792757	49
13	d	0.961878	50
14	d	0.920988	51
15	d	0.282094	53
⋮	⋮	⋮	⋮

# note that combine reorders rows by group of GroupedDataFrame
@chain x begin
  groupby(:id)
  combine(:id2,:v=>mean)
end

100 rows × 3 columns

	id	id2	v_mean
	Char	Int64	Float64
1	b	1	0.619295
2	b	7	0.619295
3	b	9	0.619295
4	b	10	0.619295
5	b	14	0.619295
6	b	17	0.619295
7	b	22	0.619295
8	b	29	0.619295
9	b	31	0.619295
10	b	32	0.619295
11	b	36	0.619295
12	b	48	0.619295
13	b	52	0.619295
14	b	65	0.619295
15	b	71	0.619295
⋮	⋮	⋮	⋮

# we give a custom name for the result column
@chain x begin
  groupby(:id)
  combine(:v=>mean=>:res)
end

4 rows × 2 columns

	id	res
	Char	Float64
1	b	0.619295
2	c	0.538388
3	a	0.52231
4	d	0.54441

# you can have multiple operations
@chain x begin
  groupby(:id)
  combine(:v=>mean=>:res1, :v=>sum=>:res2, nrow => :n, ncol)
end

4 rows × 5 columns

	id	res1	res2	n	x1
	Char	Float64	Float64	Int64	Int64
1	b	0.619295	14.2438	23	3
2	c	0.538388	12.9213	24	3
3	a	0.52231	11.4908	22	3
4	d	0.54441	16.8767	31	3

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

1 rows × 2 columns

	id	n
	Char	Int64
1	d	31

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

4 rows × 2 columns

	id	val
	Int64	Int64
1	1	1
2	1	2
3	2	3
4	2	4

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

2 rows × 3 columns

	id	x1	x2
	Int64	Int64	Int64
1	1	1	2
2	2	3	4

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

2 rows × 3 columns

	id	c1	c2
	Int64	Int64	Int64
1	1	1	2
2	2	3	4

df = DataFrame(a=[(p=1,q=2),(p=3,q=4)])

2 rows × 1 columns

	a
	NamedTu…
1	(p = 1, q = 2)
2	(p = 3, q = 4)

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

2 rows × 1 columns

	a
	Array…
1	[1, 2]
2	[3, 4]

select(df, :a)

2 rows × 1 columns

	a
	Array…
1	[1, 2]
2	[3, 4]

select(df, :a=>AsTable) # Automatic column names generated

2 rows × 2 columns

	x1	x2
	Int64	Int64
1	1	2
2	3	4

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

2 rows × 2 columns

	C1	C2
	Int64	Int64
1	1	2
2	3	4

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 rows × 3 columns

	id	x1	x2
	Int64	Int64	Int64
1	1	1	101
2	2	2	102
3	3	3	103
4	4	4	104
5	5	5	105
6	6	6	106
7	7	7	107
8	8	8	108
9	9	9	109
10	10	10	110
11	1	11	111
12	2	12	112
13	3	13	113
14	4	14	114
15	5	15	115
⋮	⋮	⋮	⋮

groupby(df,:id)

GroupedDataFrame with 10 groups based on key: id

First Group (10 rows): id = 1

	id	x1	x2
	Int64	Int64	Int64
1	1	1	101
2	1	11	111
3	1	21	121
4	1	31	131
5	1	41	141
6	1	51	151
7	1	61	161
8	1	71	171
9	1	81	181
10	1	91	191

⋮

Last Group (10 rows): id = 10

	id	x1	x2
	Int64	Int64	Int64
1	10	10	110
2	10	20	120
3	10	30	130
4	10	40	140
5	10	50	150
6	10	60	160
7	10	70	170
8	10	80	180
9	10	90	190
10	10	100	200

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

10 rows × 3 columns

	id	x1_minimum	x2_minimum
	Int64	Int64	Int64
1	1	1	101
2	2	2	102
3	3	3	103
4	4	4	104
5	5	5	105
6	6	6	106
7	7	7	107
8	8	8	108
9	9	9	109
10	10	10	110

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

10 rows × 3 columns

	id	x1_minimum	x2_maximum
	Int64	Int64	Int64
1	1	1	191
2	2	2	192
3	3	3	193
4	4	4	194
5	5	5	195
6	6	6	196
7	7	7	197
8	8	8	198
9	9	9	199
10	10	10	200

Aggregation of a data frame using mapcols

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

11 rows × 10 columns (omitted printing of 2 columns)

	x1	x2	x3	x4	x5	x6	x7	x8
	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	0.964745	0.647674	0.415917	0.189479	0.335055	0.593805	0.153637	0.418127
2	0.700304	0.771762	0.23039	0.0982404	0.936695	0.37123	0.172074	0.927579
3	0.300617	0.924364	0.32771	0.323717	0.0203839	0.134028	0.514571	0.931041
4	0.129859	0.967822	0.429138	0.411711	0.383203	0.932531	0.833455	0.114311
5	0.143695	0.265149	0.932398	0.329462	0.599305	0.964055	0.151135	0.0878731
6	0.766632	0.42877	0.909938	0.62583	0.411264	0.250753	0.123765	0.497648
7	0.85337	0.812304	0.772688	0.400719	0.580951	0.603107	0.62472	0.339668
8	0.790591	0.993734	0.163944	0.0558207	0.240677	0.276301	0.483674	0.929667
9	0.286851	0.616711	0.631963	0.498866	0.236067	0.181657	0.852193	0.546293
10	0.884825	0.92906	0.217142	0.256727	0.908738	0.21379	0.587612	0.240607
11	0.202399	0.0871188	0.0372764	0.330459	0.80785	0.164857	0.00841467	0.904867

mapcols(mean,x)

1 rows × 10 columns (omitted printing of 1 columns)

	x1	x2	x3	x4	x5	x6	x7	x8	x9
	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	0.547626	0.67677	0.460773	0.320094	0.496381	0.42601	0.409568	0.539789	0.46261

Mapping rows and columns using eachcol and eachrow

map(mean,eachcol(x)) # map a function over each column and return a vector

10-element Vector{Float64}:
 0.5476262044989846
 0.6767699633928886
 0.460773140618106
 0.32009375238924265
 0.4963808937498881
 0.42601035608155713
 0.40956819272961004
 0.5397891681041568
 0.46260956285273386
 0.32084170235808185

# an iteration returns a Pair with column name and values
foreach(c->println(c[1], ": ",mean(c[2])),pairs(eachcol(x)))

x1: 0.5476262044989846
x2: 0.6767699633928886
x3: 0.460773140618106
x4: 0.32009375238924265
x5: 0.4963808937498881
x6: 0.42601035608155713
x7: 0.40956819272961004
x8: 0.5397891681041568
x9: 0.46260956285273386
x10: 0.32084170235808185

keys(pairs(eachcol(x)))

10-element Vector{Symbol}:
 :x1
 :x2
 :x3
 :x4
 :x5
 :x6
 :x7
 :x8
 :x9
 :x10

values(pairs(eachcol(x)))

11×10 DataFrameColumns

	x1	x2	x3	x4	x5	x6	x7	x8
	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	0.964745	0.647674	0.415917	0.189479	0.335055	0.593805	0.153637	0.418127
2	0.700304	0.771762	0.23039	0.0982404	0.936695	0.37123	0.172074	0.927579
3	0.300617	0.924364	0.32771	0.323717	0.0203839	0.134028	0.514571	0.931041
4	0.129859	0.967822	0.429138	0.411711	0.383203	0.932531	0.833455	0.114311
5	0.143695	0.265149	0.932398	0.329462	0.599305	0.964055	0.151135	0.0878731
6	0.766632	0.42877	0.909938	0.62583	0.411264	0.250753	0.123765	0.497648
7	0.85337	0.812304	0.772688	0.400719	0.580951	0.603107	0.62472	0.339668
8	0.790591	0.993734	0.163944	0.0558207	0.240677	0.276301	0.483674	0.929667
9	0.286851	0.616711	0.631963	0.498866	0.236067	0.181657	0.852193	0.546293
10	0.884825	0.92906	0.217142	0.256727	0.908738	0.21379	0.587612	0.240607
11	0.202399	0.0871188	0.0372764	0.330459	0.80785	0.164857	0.00841467	0.904867

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

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

11-element Vector{Float64}:
 1.4895537041964226
 0.9074093264118998
 0.3252145507900127
 0.13417674795899745
 0.5419395347592332
 1.787979457065716
 1.0505552785308994
 0.7955757198996063
 0.46512965845343396
 0.952387303771245
 2.3232570241282002

map(c->mean(c),eachcol(x))

10-element Vector{Float64}:
 0.5476262044989846
 0.6767699633928886
 0.460773140618106
 0.32009375238924265
 0.4963808937498881
 0.42601035608155713
 0.40956819272961004
 0.5397891681041568
 0.46260956285273386
 0.32084170235808185

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

er = eachrow(x)

11×10 DataFrameRows

	x1	x2	x3	x4	x5	x6	x7	x8
	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	0.964745	0.647674	0.415917	0.189479	0.335055	0.593805	0.153637	0.418127
2	0.700304	0.771762	0.23039	0.0982404	0.936695	0.37123	0.172074	0.927579
3	0.300617	0.924364	0.32771	0.323717	0.0203839	0.134028	0.514571	0.931041
4	0.129859	0.967822	0.429138	0.411711	0.383203	0.932531	0.833455	0.114311
5	0.143695	0.265149	0.932398	0.329462	0.599305	0.964055	0.151135	0.0878731
6	0.766632	0.42877	0.909938	0.62583	0.411264	0.250753	0.123765	0.497648
7	0.85337	0.812304	0.772688	0.400719	0.580951	0.603107	0.62472	0.339668
8	0.790591	0.993734	0.163944	0.0558207	0.240677	0.276301	0.483674	0.929667
9	0.286851	0.616711	0.631963	0.498866	0.236067	0.181657	0.852193	0.546293
10	0.884825	0.92906	0.217142	0.256727	0.908738	0.21379	0.587612	0.240607
11	0.202399	0.0871188	0.0372764	0.330459	0.80785	0.164857	0.00841467	0.904867

# you can access columns of parent data frame directly
er.x1

11-element Vector{Float64}:
 0.9647450497886172
 0.7003041920539808
 0.3006167230205241
 0.12985920072205337
 0.14369459782730165
 0.7666322788507816
 0.8533699765785863
 0.7905908940520163
 0.28685063623595863
 0.8848252530238048
 0.20239944733520687

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

ec = eachcol(x)

11×10 DataFrameColumns

	x1	x2	x3	x4	x5	x6	x7	x8
	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	0.964745	0.647674	0.415917	0.189479	0.335055	0.593805	0.153637	0.418127
2	0.700304	0.771762	0.23039	0.0982404	0.936695	0.37123	0.172074	0.927579
3	0.300617	0.924364	0.32771	0.323717	0.0203839	0.134028	0.514571	0.931041
4	0.129859	0.967822	0.429138	0.411711	0.383203	0.932531	0.833455	0.114311
5	0.143695	0.265149	0.932398	0.329462	0.599305	0.964055	0.151135	0.0878731
6	0.766632	0.42877	0.909938	0.62583	0.411264	0.250753	0.123765	0.497648
7	0.85337	0.812304	0.772688	0.400719	0.580951	0.603107	0.62472	0.339668
8	0.790591	0.993734	0.163944	0.0558207	0.240677	0.276301	0.483674	0.929667
9	0.286851	0.616711	0.631963	0.498866	0.236067	0.181657	0.852193	0.546293
10	0.884825	0.92906	0.217142	0.256727	0.908738	0.21379	0.587612	0.240607
11	0.202399	0.0871188	0.0372764	0.330459	0.80785	0.164857	0.00841467	0.904867

ec.x1

11-element Vector{Float64}:
 0.9647450497886172
 0.7003041920539808
 0.3006167230205241
 0.12985920072205337
 0.14369459782730165
 0.7666322788507816
 0.8533699765785863
 0.7905908940520163
 0.28685063623595863
 0.8848252530238048
 0.20239944733520687

er[1]

DataFrameRow (10 columns)

	x1	x2	x3	x4	x5	x6	x7	x8
	Float64	Float64	Float64	Float64	Float64	Float64	Float64	Float64
1	0.964745	0.647674	0.415917	0.189479	0.335055	0.593805	0.153637	0.418127

ec[1]

11-element Vector{Float64}:
 0.9647450497886172
 0.7003041920539808
 0.3006167230205241
 0.12985920072205337
 0.14369459782730165
 0.7666322788507816
 0.8533699765785863
 0.7905908940520163
 0.28685063623595863
 0.8848252530238048
 0.20239944733520687

Transposing

you can transpose a data frame using permuteddims:

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

3 rows × 4 columns

	x1	x2	x3	x4
	Int64	Int64	Int64	Int64
1	1	4	7	10
2	2	5	8	11
3	3	6	9	12

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

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

df

3 rows × 5 columns

	x1	x2	x3	x4	names
	Int64	Int64	Int64	Int64	String
1	1	4	7	10	a
2	2	5	8	11	b
3	3	6	9	12	c

permutedims(df,:names)

4 rows × 4 columns

	names	a	b	c
	String	Int64	Int64	Int64
1	x1	1	2	3
2	x2	4	5	6
3	x3	7	8	9
4	x4	10	11	12