DataFrames.jl indexing rules
Introduction
In DataFrames.jl 1.4 release we reached the target state of data frame indexing that we aimed for when we designed 1.0 release.
In this post I want to present you what mental model you should have when thinking about indexing into a data frame. The reason why learning this is important is that we needed to extend standard indexing rules that are defined for arrays in Julia to cover all scenarios that users need when working with data frames.
I will focus on working with a single column of a data frame as this is the most common indexing scenario.
The code was tested under Julia 1.8.2 and DataFrames.jl 1.4.2.
Rule 1: indexing always requires passing both row and column selector
When you index into a data frame you must pass exactly two dimensions: a row
selector and a column selector like df[row_selector, column_selector].
When indexing you can think of a data frame as of a matrix.
Rule 2: all indexing that works on matrices works the same way for data frames
The benefit of the rule that data frame follows matrix indexing is that it is easy to remember. If you know how matrix indexing works then all translates directly to a data frame.
Here are some examples of extracting a column or its fragment from a data frame.
julia> using DataFrames
julia> df = DataFrame(a=1:3, b=4:6)
3×2 DataFrame
Row │ a b
│ Int64 Int64
─────┼──────────────
1 │ 1 4
2 │ 2 5
3 │ 3 6
julia> df[1, 1]
1
julia> df[1:2, 1]
2-element Vector{Int64}:
1
2
julia> df[:, 2]
3-element Vector{Int64}:
4
5
6
(As I commented in the Introduction in this post I concentrate on getting a single column from a data frame so the second index was always an integer.)
One important rule of Julia Base indexing is that when you get a column or its
part from a matrix a copy is made, except if you extract a single element. This
is exactly what happens for data frame: df[:, 2] is a copy of a second
column stored in df. You can check it by writing e.g.:
julia> df[:, 2] === eachcol(df)[2]
false
julia> df[:, 2] == eachcol(df)[2]
true
We see that we get the same data, but not the same object.
If you instead want a view of a column without copying the data, you can use
@view exactly like you would do it with matrices:
julia> @view df[1, 1]
0-dimensional view(::Vector{Int64}, 1) with eltype Int64:
1
julia> @view df[1:2, 1]
2-element view(::Vector{Int64}, 1:2) with eltype Int64:
1
2
julia> @view df[:, 2]
3-element view(::Vector{Int64}, :) with eltype Int64:
4
5
6
As you can see, again all worked as if df were a matrix.
The same rules that worked for getting data from a data frame work for setting data in a data frame. You have two options here: normal assignment and broadcasted assignment.
julia> df[1:2, 1] = [11, 12]
2-element Vector{Int64}:
11
12
julia> df[:, 2] .= 100
3-element view(::Vector{Int64}, :) with eltype Int64:
100
100
100
julia> df
3×2 DataFrame
Row │ a b
│ Int64 Int64
─────┼──────────────
1 │ 11 100
2 │ 12 100
3 │ 3 100
These operations, again, work exactly like for matrices. In particular they are in-place, that is no memory is allocated when performing them. The data is written into already allocated column. This rule is important as it means that by such assignment you cannot change the element type of a column:
julia> df[:, 1] = ["a", "b", "c"]
ERROR: MethodError: Cannot `convert` an object of type String to an object of type Int64
julia> df[:, 2] .= "x"
ERROR: MethodError: Cannot `convert` an object of type String to an object of type Int64
In summary, standard array indexing works the same way for matrices and for data frames.
In what follows I describe the extensions to the indexing rules that are DataFrames.jl specific.
Rule 3: you can use strings or symbols to pass column names
The first extension is related to the fact that standard matrices have to be indexed by integers. In DataFrames.jl you can alternatively use string or symbol to select a column when indexing. Here are some basic examples:
julia> df[1:2, "a"]
2-element Vector{Int64}:
11
12
julia> df[:, :b] .= 1000
3-element view(::Vector{Int64}, :) with eltype Int64:
1000
1000
1000
This is intuitive so far. However, this rule leads to one extension. It is
related to the fact that you can pass a column name that does not exist yet
in a data frame. In this case if you pass : as a column selector a new column
in a data frame will be allocated (that is copy of the source data will be
performed) and the new column will be added at the end of the data frame:
julia> df[:, "d"] = [-1, -2, -3]
3-element Vector{Int64}:
-1
-2
-3
julia> df
3×3 DataFrame
Row │ a b d
│ Int64 Int64 Int64
─────┼─────────────────────
1 │ 11 1000 -1
2 │ 12 1000 -2
3 │ 3 1000 -3
This rule has two additional special cases. The first is when a data frame has no columns yet. In such a situation you can add any vector to a data frame:
julia> df2 = DataFrame()
0×0 DataFrame
julia> df2[:, :x] = [1, 2]
2-element Vector{Int64}:
1
2
julia> df2
2×1 DataFrame
Row │ x
│ Int64
─────┼───────
1 │ 1
2 │ 2
What is non-standard with this rule? We allow changing the number of rows in a data frame from zero to whatever is needed. For a data frame having some columns changing their number of rows is not allowed with indexing.
The second special case is when you try to create a new column in a view of
a data frame. It is not allowed in general, but if the view was created with
: as a column selector we accept it (the reason is that in this case view
subsets only rows, but does not change columns; this is a common use case
in practice). Here is an example:
julia> df
3×3 DataFrame
Row │ a b d
│ Int64 Int64 Int64
─────┼─────────────────────
1 │ 11 1000 -1
2 │ 12 1000 -2
3 │ 3 1000 -3
julia> dfv = @view df[[3, 1], :]
2×3 SubDataFrame
Row │ a b d
│ Int64 Int64 Int64
─────┼─────────────────────
1 │ 3 1000 -3
2 │ 11 1000 -1
julia> dfv[:, :e] = [1, 2]
2-element Vector{Int64}:
1
2
julia> dfv
2×4 SubDataFrame
Row │ a b d e
│ Int64 Int64 Int64 Int64?
─────┼─────────────────────────────
1 │ 3 1000 -3 1
2 │ 11 1000 -1 2
julia> df
3×4 DataFrame
Row │ a b d e
│ Int64 Int64 Int64 Int64?
─────┼──────────────────────────────
1 │ 11 1000 -1 2
2 │ 12 1000 -2 missing
3 │ 3 1000 -3 1
As you can see in this case a new column gets missing for rows that are not
present in the dfv data frame.
Rule 4: You can use Not to negate row selection
This is a pretty simple rule. Instead of selecting rows, you can use Not
to say which rows you want to drop when indexing:
julia> df
3×4 DataFrame
Row │ a b d e
│ Int64 Int64 Int64 Int64?
─────┼──────────────────────────────
1 │ 11 1000 -1 2
2 │ 12 1000 -2 missing
3 │ 3 1000 -3 1
julia> df[Not(2), :d]
2-element Vector{Int64}:
-1
-3
Rule 5: There is a special ! row selector
The special ! row selector selects all rows similarly to :, but it has
a different behavior in relation to column allocation.
When extracting the data from a data frame if you use ! no copying of data is
made. Instead, you just get a column as it is stored in the source data frame
(or an appropriate view if you work with a view of a data frame):
julia> df[!, 1]
3-element Vector{Int64}:
11
12
3
julia> df[!, 1] === eachcol(df)[1]
true
julia> dfv[!, 1]
2-element view(::Vector{Int64}, [3, 1]) with eltype Int64:
3
11
The reason why ! is allowed for when extracting a column from a data frame
is performance. In general it is not safe to use !. You should prefer :
as copying data (in R like style) is safer. However, in some cases, when your
operations are memory-bound or you need to maximize performance, you have !
at hand to avoid unnecessary operations.
For writing data into a data frame if you use ! selector it has also a
different behavior than :. Recall, that : is always in-place. Instead
! stores a fresh column in a data frame.
julia> df
3×4 DataFrame
Row │ a b d e
│ Int64 Int64 Int64 Int64?
─────┼──────────────────────────────
1 │ 11 1000 -1 2
2 │ 12 1000 -2 missing
3 │ 3 1000 -3 1
julia> df[!, "a"] = ["a", "b", "c"]
3-element Vector{String}:
"a"
"b"
"c"
julia> dfv[!, "e"] .= "x"
2-element Vector{String}:
"x"
"x"
julia> df
3×4 DataFrame
Row │ a b d e
│ String Int64 Int64 Any
─────┼───────────────────────────────
1 │ a 1000 -1 x
2 │ b 1000 -2 missing
3 │ c 1000 -3 x
How the fresh column is stored depends on the type of data frame you use and the type of operation:
- if you use standard assignment on a data frame (
df[!, "a"] = ["a", "b", "c"]) then source vector is not copied, but is stored as-is (so this kind of storage is the fastest way to store a column in a data frame). - if you use broadcasted assignment (
df[!, "a"] .= ["a", "b", "c"]) or assign into a view of a data frame (dfv[!, "a"] = ["x", "x"]) then a new column is freshly allocated (this kind of behavior is needed if you want to change element type of the column already present in a data frame, but want a copy of the source vector to be made for safety reasons).
So for assignment with ! the basic rule to remember is that it replaces the
existing column in a data frame. Then the additional rule is that normal
assignment on a data frame is non-copying, and broadcasted assignment or
using a view of a data frame allocates a copy.
These rules were designed to cover all possible scenarios that users might need when working with columns of a data frame.
Rule 6: property access works the same as if you used ! as row selector
With this rule we are getting to the point that changed in DataFrames.jl 1.4 (but was planned and announced much earlier).
When you write df.a it is always treated the same as df[!, "a"], for
extracting a column from a data frame, for assignment, and for broadcasted
assignment.
Since DataFrames.jl 1.4 this is all you need to remember about property access to a data frame. We decided on this rule as it is easy to remember and it does not add any new concepts or exceptions for users to learn.
However, unfortunately, this is the place where we had to deviate from how property access works in Base Julia. Let me explain the issue step by step.
If you write df.a = ["a", "b", "c"] then you expect that column a in df
data frame is replaced by ["a", "b", "c"]. And this is what happens. Recall
that this is what df[!, "a"] = ["a", "b", "c"] also does.
Now this means that, if we want df.a .= ["a", "b", "c"] to work the same as
df[!, "a"] .= ["a", "b", "c"], then both operations replace column a in df.
And here we have a slight inconsistency, as in Base Julia users would expect
that df.a .= ["a", "b", "c"] would work in-place, like
df[:, "a"] .= ["a", "b", "c"]. This is not the case.
Therefore you need to remember that in DataFrames.jl if you use property access syntax for setting data it always replaces the colum: both when doing assignment and broadcasted assignment.
The reason why we decided on this design is twofold. First is learning. We have
a simple rule: ! selector and property access work the same way always.
The second reason is convenience. Most users preferred the following operation:
julia> df3 = DataFrame(c=1:3)
3×1 DataFrame
Row │ c
│ Int64
─────┼───────
1 │ 1
2 │ 2
3 │ 3
julia> df3.c .= 'x'
3-element Vector{Char}:
'x': ASCII/Unicode U+0078 (category Ll: Letter, lowercase)
'x': ASCII/Unicode U+0078 (category Ll: Letter, lowercase)
'x': ASCII/Unicode U+0078 (category Ll: Letter, lowercase)
julia> df3
3×1 DataFrame
Row │ c
│ Char
─────┼──────
1 │ x
2 │ x
3 │ x
to replace column c with a vector of 'x' values. Note that instead if you
use : as row selector you get a bit surprising result:
julia> df3 = DataFrame(c=1:3)
3×1 DataFrame
Row │ c
│ Int64
─────┼───────
1 │ 1
2 │ 2
3 │ 3
julia> df3[:, "c"] .= 'x'
3-element view(::Vector{Int64}, :) with eltype Int64:
120
120
120
julia> df3
3×1 DataFrame
Row │ c
│ Int64
─────┼───────
1 │ 120
2 │ 120
3 │ 120
The reason of this behavior is that Char is silently converted to Int:
julia> convert(Int, 'x')
120
What is the general benefit of the behavior we adopted? When you write
df[!, "a"] .= 'x' or df.a .= 'x' you are sure that as a result you will
have a freshly allocated column "a" in df containing 'x' values as all
its elements, independent of the fact if column "a" was already present
in the data frame or not. So this is like git push --force operation.
It is guaranteed to succeed no matter what the starting state of the data
frame was (of course assuming that the object on the right hand side supports
broadcasting and has proper dimensions).
Why has it taken us so long to reach this state?
The reason why we landed with these rules only in DataFrames.jl 1.4 is that
before Julia 1.7 it was not possible to support the behavior we wanted.
Therefore we had to wait till Julia 1.7 to be released to achieve consistency
between property access and ! row selector behavior in DataFrames.jl in all
cases.
So people porting code from DataFrames.jl earlier than 1.4 or from Julia earlier
than 1.7 will notice a change that previously df.col .= value was in-place,
and currently it allocates a new column. We have considered the risks that this
change will be problematic for users, and the conclusion was that:
- In a vast majority of code this change will not affect the result and users will not even notice this change.
- It might affect the code that is performance critical (as an extra copy is now made). However, if this is the case it is easy to fix the issue (and it will not cause the code to be broken).
- It might affect the code that relied on the fact that previously a conversion
during in-place assignment was made (and e.g. relied on the fact that such
assignment does not change element type of the column). In this case such code
would indeed be broken. In this situation the conclusion was that such cases,
although possible in practice, are most likely quite rare and if present would
affect only experienced Julia users who have a good grasp of conversion upon
assignment rules in Base Julia. We concluded that such users would be able
to identify the problematic cases and change
df.col .= valuetodf[:, :col] .= valuein their code to get back the old behavior.
Conclusions
I hope you found this post useful in building your understanding how the details of indexing in DataFrames.jl work and what are their intended use cases.
Additionally, wanted to present you the mental process we went through when we were making hard design decisions in DataFrames.jl development team. In this process we had to balance three things:
- user convenience (especially taking into account the fact that target audience of DataFrames.jl are data scientists, who sometimes are not computer science experts);
- internal (within DataFrames.jl) consistency of rules that one needs to learn;
- minimization of cases when we diverge from what Base Julia defines for similar objects (the challenge was that data frame is neither a matrix nor a struct, and has different design requirements, but we borrow the syntax from both).
In this post I have not covered all the details of DataFrames.jl indexing rules. If you want to learn about the indexing behavior every supported scenario please check the Indexing section of the DataFrames.jl manual.