Sorting a Table / DataFrame

[Dan_Micsa]

I have a table like this:

* A B C
A 1 2 3
B 2 3 4
C 3 4 5

And I want to sort descending both on columns and rows after the sum something like this:

*  C B A  S
C  5 4 3 12
B  4 3 2  9
A  3 2 1  6
S 12 9 6  *

How can I do that the simplest?

thx.

[raman_kumar]

As far as i know DataFrames does not have row names. It indexes rows from 1:n.

Rename a row in DataFrames?

Is it possible to rename a row in DataFrames.jl? Currently it looks like rows are numbered by default 1:n. The function rename!() only changes column names, not row names. My hack is to add a column w/ my desired row names: insertcols!(se, 1, :coef => ["β0","β1","β2","β3"]) [image] I can’t find the relevant discussion on whether this was a deliberate design decision…

[bkamins]

I assume you do not want the "*" and "S" columns and do not want the last row in the output. In this case do:

julia> df = DataFrame("A" => 1:3, "B" => 2:4, "C" => 3:5)
3×3 DataFrame
 Row │ A      B      C
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     1      2      3
   2 │     2      3      4
   3 │     3      4      5

julia> df[sortperm(sum.(eachrow(df)), rev=true), sortperm(sum.(eachcol(df)), rev=true)]
3×3 DataFrame
 Row │ C      B      A
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     5      4      3
   2 │     4      3      2
   3 │     3      2      1

[rocco_sprmnt21]

julia>  sort(sort(m,dims=1, by=sum, rev=true),dims=2,rev=true)
3×3 Matrix{Int64}:
 5  4  3
 4  3  2
 3  2  1

using LinearAlgebra
rot180(sort(sort(m,dims=1, by=sum),dims=2))

[Dan_Micsa]

Thank you!

Something like this but I need the column label too and the sum if is possible too.

[Dan_Micsa]

That’s not the main problem I want to know after double sorting a label or index.

[raman_kumar]

julia> using DataFrames

julia> M=[1 2 3 ; 2 3 4 ; 3 4 5]
3×3 Matrix{Int64}:
 1  2  3
 2  3  4
 3  4  5

julia> r=sum(M,dims=1)
1×3 Matrix{Int64}:
 6  9  12

julia> l=sum(M,dims=2)
3×1 Matrix{Int64}:
  6
  9
 12

[Dan_Micsa]

I need to sort after the sum and to have the labels.

[bkamins]

All is doable, can you confirm that this is the exact way how your input data frame looks like and how your ouput should look? (then I can give you information how to transform input into output)

julia> input = DataFrame("*" => ["A", "B", "C"], "A" => 1:3, "B" => 2:4, "C" => 3:5)
3×4 DataFrame
 Row │ *       A      B      C
     │ String  Int64  Int64  Int64
─────┼─────────────────────────────
   1 │ A           1      2      3
   2 │ B           2      3      4
   3 │ C           3      4      5

julia> output = DataFrame("*" => ["C", "B", "A", "S"], "C" => [5:-1:3; 12], "B" => [4:-1:2; 9], "A" => [3:-1:1; 6], "S" => [12, 9, 6, "*"])
4×5 DataFrame
 Row │ *       C      B      A      S
     │ String  Int64  Int64  Int64  Any
─────┼──────────────────────────────────
   1 │ C           5      4      3  12
   2 │ B           4      3      2  9
   3 │ A           3      2      1  6
   4 │ S          12      9      6  *

[Dan_Micsa]

Yes the my problem has a table of over 1000x1000 with labels on raws and columns and I want to have in the top left corner the values that have the highest sums of absolute values. The initial table is matrix in Julia.

[bkamins]

Here are the steps to do (I designed them so that they should be hopefully easy to grasp - I did not want to squeeze too much into one step):

julia> input = DataFrame("*" => ["A", "B", "C"], "A" => 1:3, "B" => 2:4, "C" => 3:5)
3×4 DataFrame
 Row │ *       A      B      C
     │ String  Int64  Int64  Int64
─────┼─────────────────────────────
   1 │ A           1      2      3
   2 │ B           2      3      4
   3 │ C           3      4      5

julia> output = select(input, Not("*"))
3×3 DataFrame
 Row │ A      B      C
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     1      2      3
   2 │     2      3      4
   3 │     3      4      5

julia> select!(output, sortperm(sum.(eachcol(output)), rev=true))
3×3 DataFrame
 Row │ C      B      A
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     3      2      1
   2 │     4      3      2
   3 │     5      4      3

julia> output.S = sum(eachcol(output))
3-element Vector{Int64}:
  6
  9
 12

julia> insertcols!(output, 1, "*" => input."*")
3×5 DataFrame
 Row │ *       C      B      A      S
     │ String  Int64  Int64  Int64  Int64
─────┼────────────────────────────────────
   1 │ A           3      2      1      6
   2 │ B           4      3      2      9
   3 │ C           5      4      3     12

julia> sort!(output, "S", rev=true)
3×5 DataFrame
 Row │ *       C      B      A      S
     │ String  Int64  Int64  Int64  Int64
─────┼────────────────────────────────────
   1 │ C           5      4      3     12
   2 │ B           4      3      2      9
   3 │ A           3      2      1      6

julia> push!(output, ["S"; sum.(eachcol(output[:, Not(1, end)])); "*"], promote=true)
4×5 DataFrame
 Row │ *       C      B      A      S
     │ String  Int64  Int64  Int64  Any
─────┼──────────────────────────────────
   1 │ C           5      4      3  12
   2 │ B           4      3      2  9
   3 │ A           3      2      1  6
   4 │ S          12      9      6  *

Probably the trickiest part is the last step with sum.(eachcol(output[:, Not(1, end)])) where you want to sum everything except first and last column, which are special and you need to manually assign values to them.

[Dan_Micsa]

Many thx Bogumil!

[Dan_Micsa]

I did it like this using matrixes and I receive back the indexes of the rows and columns to aggregate future information.

function toIxes(m)
    mAbs = abs.(m)
    cSum = sum(mAbs; dims=1)
    rSum = sum(mAbs; dims=2)'
    cIx = sortperm(cSum, dims=2, rev=true)
    rIx = sortperm(rSum, dims=2, rev=true)
    (rIx, cIx)
end 

c = rand(5, 6)

toIxes(c)