Using combine Julia function on GroupedDataFrame while using regex to reference columns

Question

As you will be able to tell from the question I am a VERY new user to Julia and just trying to do things that I have already done in python and stumbling a bit in the dark. What I am trying to do right now is to create some simple stats over multiple columns based on a certain grouping of the data. So after doing something like:

df = DataFrame(CSV.File(file));
gdf = groupby(df, :Class);

where df looks like:

df[1:3, [:Class, :V1, :V2, :V10]]

     Class  V1        V2           V10
     Int64  Float64   Float64      Float64
1    0      -1.35981  -0.0727812   0.0907942
2    1      1.19186   0.266151     -0.166974
3    0      -1.35835  -1.34016     0.207643

...

I know I can do something like:

combine(gdf, :V1 => maximum => :v1_max, :V1 => minimum => :v1_min, nrow)

But then I saw that I could use regex to reference multiple columns and so my thought was to do something simple like:

combine(gdf, r"V[0-9]{1,2}" => maximum)

and have Julia in a single line generate the max value for all of the columns matching the regex for the grouped DataFrame.

I finally was able to do this in what I am guessing is not a nice efficient way and so looking for anyone's help to help me improve my usage of Julia.

foo = DataFrame(Class=[0, 1])
for v in ["V$i" for i in 1:28]
    foo = join(foo, 
               combine(gdf, v => maximum => string(v, "_max")), 
               combine(gdf, v => minimum => string(v, "_min")), 
               on=:Class)
end

Bogumił Kamiński Bogumił Kamiński · Accepted Answer · 2020-07-05T19:58:48

Just write:

combine(gdf, names(gdf, r"V[0-9]{1,2}") .=> maximum)

(note the . in front of =>)

In this case the target column names will be automatically generated.

What I have written above is a shorthand for:

combine(gdf, [n => maximum for n in names(gdf, r"V[0-9]{1,2}")])

Another way to write it is:

combine(AsTable(r"V[0-9]{1,2}") => x -> map(maximum, x), gdf)

when the old column names get retained.

The combine syntax is very flexible. I recommend you to have a look at its docstring for all available options.

Consider the following examples:

julia> using DataFrames

julia> passthrough(x...) = (@show x; x)
passthrough (generic function with 1 method)

julia> df = DataFrame(Class=[1,1,2], V1=1:3, V2=11:13)
3×3 DataFrame
│ Row │ Class │ V1    │ V2    │
│     │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┤
│ 1   │ 1     │ 1     │ 11    │
│ 2   │ 1     │ 2     │ 12    │
│ 3   │ 2     │ 3     │ 13    │

julia> gdf = groupby(df, :Class)
GroupedDataFrame with 2 groups based on key: Class
First Group (2 rows): Class = 1
│ Row │ Class │ V1    │ V2    │
│     │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┤
│ 1   │ 1     │ 1     │ 11    │
│ 2   │ 1     │ 2     │ 12    │
⋮
Last Group (1 row): Class = 2
│ Row │ Class │ V1    │ V2    │
│     │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┤
│ 1   │ 2     │ 3     │ 13    │

julia> combine(gdf, r"V[0-9]{1,2}" .=> passthrough)
x = ([1, 2], [11, 12])
x = ([3], [13])
2×2 DataFrame
│ Row │ Class │ V1_V2_passthrough  │
│     │ Int64 │ Tuple…             │
├─────┼───────┼────────────────────┤
│ 1   │ 1     │ ([1, 2], [11, 12]) │
│ 2   │ 2     │ ([3], [13])        │

julia> combine(gdf, r"V[0-9]{1,2}" => passthrough)
x = ([1, 2], [11, 12])
x = ([3], [13])
2×2 DataFrame
│ Row │ Class │ V1_V2_passthrough  │
│     │ Int64 │ Tuple…             │
├─────┼───────┼────────────────────┤
│ 1   │ 1     │ ([1, 2], [11, 12]) │
│ 2   │ 2     │ ([3], [13])        │

julia> combine(gdf, names(gdf, r"V[0-9]{1,2}") .=> passthrough)
x = ([1, 2],)
x = ([3],)
x = ([11, 12],)
x = ([13],)
2×3 DataFrame
│ Row │ Class │ V1_passthrough │ V2_passthrough │
│     │ Int64 │ Tuple…         │ Tuple…         │
├─────┼───────┼────────────────┼────────────────┤
│ 1   │ 1     │ ([1, 2],)      │ ([11, 12],)    │
│ 2   │ 2     │ ([3],)         │ ([13],)        │

julia> combine(gdf, names(gdf, r"V[0-9]{1,2}") => passthrough)
x = ([1, 2], [11, 12])
x = ([3], [13])
2×2 DataFrame
│ Row │ Class │ V1_V2_passthrough  │
│     │ Int64 │ Tuple…             │
├─────┼───────┼────────────────────┤
│ 1   │ 1     │ ([1, 2], [11, 12]) │
│ 2   │ 2     │ ([3], [13])        │

In particular it is crucial to understand what gets passed to combine:

julia> r"V[0-9]{1,2}" .=> passthrough
r"V[0-9]{1,2}" => passthrough

julia> r"V[0-9]{1,2}" => passthrough
r"V[0-9]{1,2}" => passthrough

julia> names(gdf, r"V[0-9]{1,2}") .=> passthrough
2-element Array{Pair{String,typeof(passthrough)},1}:
 "V1" => passthrough
 "V2" => passthrough

julia> names(gdf, r"V[0-9]{1,2}") => passthrough
["V1", "V2"] => passthrough

So as you can see, all depends what gets passed to combine. In particular r"V[0-9]{1,2}" .=> passthrough and r"V[0-9]{1,2}" => passthrough are parsed as exactly the same expression, in which case passthrough is called only ONCE per group getting multiple positional arguments.

On the other hand names(gdf, r"V[0-9]{1,2}") .=> passthrough makes passthrough get called for each column separately for each group.

Using combine Julia function on GroupedDataFrame while using regex to reference columns

1 Answers