Is there a way to subtract multiple dataframe columns at once?

user_11555612 0
Viewed 392

I'm pretty new to Julia, so I apologize if this is a super basic question. From R I'm used to doing basic operations on multiple columns of a dataframe at once. I tried to do this in Julia the following way:

I have two dataframes, let's call them data_1 and data_2:

using DataFrames
data_1 = DataFrame(rand(4,6))
data_2 = DataFrame(zeros(4,6))

And now I want to fill data_2 as the difference of certain rows from data_1, e.g.:

data_2[1,:] = data_1[1,:] - data_1[2,:]

but this produces an error. So how can I modify this approach to successfully subtract multiple columns of dataframe rows?

Thank you very much!

julia
1 Answers

This particular task is unfortunately a bit tricky, as we try to retain consistency with Julia Base.

Here are the ways to do it:

Option 1

Use iteration:

julia> data_1 = DataFrame(reshape(1:24, 4, 6))
4×6 DataFrame
│ Row │ x1    │ x2    │ x3    │ x4    │ x5    │ x6    │
│     │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┼───────┼───────┼───────┤
│ 1   │ 1     │ 5     │ 9     │ 13    │ 17    │ 21    │
│ 2   │ 2     │ 6     │ 10    │ 14    │ 18    │ 22    │
│ 3   │ 3     │ 7     │ 11    │ 15    │ 19    │ 23    │
│ 4   │ 4     │ 8     │ 12    │ 16    │ 20    │ 24    │

julia> data_2 = DataFrame(zeros(4,6))
4×6 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

julia> foreach(i -> data_2[1,i] = data_1[1, i] - data_1[2, i], axes(data_1, 2))

julia> data_2
4×6 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

Option 2

Use broadcasting of data frames:

julia> data_1 = DataFrame(reshape(1:24, 4, 6))
4×6 DataFrame
│ Row │ x1    │ x2    │ x3    │ x4    │ x5    │ x6    │
│     │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┼───────┼───────┼───────┤
│ 1   │ 1     │ 5     │ 9     │ 13    │ 17    │ 21    │
│ 2   │ 2     │ 6     │ 10    │ 14    │ 18    │ 22    │
│ 3   │ 3     │ 7     │ 11    │ 15    │ 19    │ 23    │
│ 4   │ 4     │ 8     │ 12    │ 16    │ 20    │ 24    │

julia> data_2
4×6 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

julia> data_2[1:1,:] .= data_1[1:1,:] .- data_1[2:2,:]
1×6 SubDataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │

julia> data_2
4×6 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

As you can see the trick is to use broadcasting (the .) and slices (1:1 etc.) not single indices.

The problem with single indices is that DataFrameRow does not support broadcasting now:

julia> data_2[1,:] .= data_1[1,:] .- data_1[2,:]
ERROR: ArgumentError: broadcasting over `DataFrameRow`s is reserved

because it is undecided how broadcasting will work for NamedTuple objects in Base, as you can see here:

julia> (a=1,b=2) .- (a=1,b=2)
ERROR: ArgumentError: broadcasting over dictionaries and `NamedTuple`s is reserved

(once Base supports broadcasting over NamedTuples we will add this support to DataFrameRows)

Option 3

It is a workaround of the no-broadcasting issue of DataFrameRow object:

julia> data_1 = DataFrame(reshape(1:24, 4, 6))
4×6 DataFrame
│ Row │ x1    │ x2    │ x3    │ x4    │ x5    │ x6    │
│     │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┼───────┼───────┼───────┤
│ 1   │ 1     │ 5     │ 9     │ 13    │ 17    │ 21    │
│ 2   │ 2     │ 6     │ 10    │ 14    │ 18    │ 22    │
│ 3   │ 3     │ 7     │ 11    │ 15    │ 19    │ 23    │
│ 4   │ 4     │ 8     │ 12    │ 16    │ 20    │ 24    │

julia> data_2 = DataFrame(zeros(4,6))
4×6 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

julia> data_2[1,:] = Vector(data_1[1,:]) - Vector(data_1[2,:])
DataFrameRow
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │

julia> data_2
4×6 DataFrame
│ Row │ x1      │ x2      │ x3      │ x4      │ x5      │ x6      │
│     │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │ -1.0    │
│ 2   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 3   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │

as you can see the trick is to transform RHS into Vectors which support -.

Finally (as an additional reference that might be useful in some cases) you can write Vector(data_1[1,:]) - Vector(data_1[2,:]) shorter just as:

julia> -(Vector.((data_1[1,:],data_1[2,:]))...)
6-element Array{Int64,1}:
 -1
 -1
 -1
 -1
 -1
 -1
user_12695670
Related