Creating variable depending on value of another with for loop in Julia DataFrame

user_12776809 0
Viewed 141

I have de following Julia dataframe

    grh  anc     anc1     anc2    anc3     anc4     anc5    anc6     anc7  
1     2    5  0.10000  0.12000  0.1800  0.14000  0.15000  0.1900  0.20000   
2     3    7  0.03299  0.05081  0.0355  0.02884  0.03054  0.0332  0.03115   
3     4    3  0.00000  0.00000  0.0000  0.00000  0.00000  0.0000  0.00000   
4     5    4  0.00000  0.00000  0.0000  0.00000  0.00000  0.0000  0.00000   
5     6    1  0.10000  0.10000  0.1000  0.10000  0.10000  0.1000  0.10000 




       anc8     anc9    anc10  
1   0.10000  0.21000  0.24000  
2   0.02177  0.04903  0.04399  
3   0.00000  0.00000  0.00000  
4   0.00000  0.00000  0.00000  
5   0.10000  0.10000  0.10000

I would like to add new columns with a forloop lap1, lap2, ....depending on the values of variable anc. For instance, on the first row, anc=5 so lap1 should be equal to the value of anc5 (0.1500), lap2 equal to anc6 (0.1900)...on the second row lap1=anc7 (0.03115), lap2=anc8 (0.02177),...

So, the output should look like

grh anc anc1    anc2    anc3    anc4    anc5    anc6    anc7    anc8    anc9    anc10   lap1    lap2    lap3
2   5   0.10000 0.12000 0.18000 0.14000 0.15000 0.19000 0.20000 0.1000  0.21000 0.24000 0.15000 0.19000 0.20000
3   7   0.03299 0.05081 0.0355  0.02884 0.03054 0.0332  0.03115 0.02177 0.04903 0.04399 0.03115 0.02177 0.04903
4   3   0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000
5   4   0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000 0.00000
6   1   0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000 0.10000

I am still a very beginner in Julia who would be very grateful if you have any idea. Thks

julia dataframe for-loop
1 Answers

This is the way to do it:

5×12 DataFrame
│ Row │ grh   │ anc   │ anc1    │ anc2    │ anc3    │ anc4    │ anc5    │ anc6    │ anc7    │ anc8    │ anc9    │ anc10   │
│     │ Int64 │ Int64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼───────┼───────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 2     │ 5     │ 0.1     │ 0.12    │ 0.18    │ 0.14    │ 0.15    │ 0.19    │ 0.2     │ 0.1     │ 0.21    │ 0.24    │
│ 2   │ 3     │ 7     │ 0.03299 │ 0.05081 │ 0.0355  │ 0.02884 │ 0.03054 │ 0.0332  │ 0.03115 │ 0.02177 │ 0.04903 │ 0.04399 │
│ 3   │ 4     │ 3     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 5     │ 4     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 5   │ 6     │ 1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │

julia> transform(df, [r"anc" => ByRow((x...) -> x[x[1]+i]) => "lap$i" for i in 1:3])
5×15 DataFrame
│ Row │ grh   │ anc   │ anc1    │ anc2    │ anc3    │ anc4    │ anc5    │ anc6    │ anc7    │ anc8    │ anc9    │ anc10   │ lap1    │ lap2    │ lap3    │
│     │ Int64 │ Int64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼───────┼───────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 2     │ 5     │ 0.1     │ 0.12    │ 0.18    │ 0.14    │ 0.15    │ 0.19    │ 0.2     │ 0.1     │ 0.21    │ 0.24    │ 0.15    │ 0.19    │ 0.2     │
│ 2   │ 3     │ 7     │ 0.03299 │ 0.05081 │ 0.0355  │ 0.02884 │ 0.03054 │ 0.0332  │ 0.03115 │ 0.02177 │ 0.04903 │ 0.04399 │ 0.03115 │ 0.02177 │ 0.04903 │
│ 3   │ 4     │ 3     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 5     │ 4     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 5   │ 6     │ 1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │

(I retain a wide output not to truncate any columns)

In the code I assume that you want to generate only :lap1, :lap2, and :lap3 columns, which means that anc should be allowed to take at most value 8 (if it will be 9 or more you will get an error in the code as there is no source coulmn to get data from).

The key to understand how this works is to parse:

[r"anc" => ByRow((x...) -> x[x[1]+i]) => "lap$i" for i in 1:3]

comprehension means that we will create three variables for i ranging from 1 to 3. So let us fix on e.g. i=1. Now:

  • r"anc" means that we will pass to our function all columns that have "anc" in their name in their order of appearance in a data frame (so first will be "anc" and then columns with suffixes from 1 to 10). These values will be passed as positional arguments
  • then ByRow((x...) -> x[x[1]+i]) means that we define a function that will be passed data row by row, (x...) -> part means that x will be a tuple containing the passed positional arguments, as we know "anc" is the fist column we refer to it as x[1] and then add i to it to get a column that is interesting for us;
  • finally "lap$i" gives us the name of the output variable

Another way to write it would be:

julia> transform(df, [AsTable(r"anc") => ByRow(x -> x[Symbol("anc", x.anc+i-1)]) => "lap$i" for i in 1:3])
5×15 DataFrame
│ Row │ grh   │ anc   │ anc1    │ anc2    │ anc3    │ anc4    │ anc5    │ anc6    │ anc7    │ anc8    │ anc9    │ anc10   │ lap1    │ lap2    │ lap3    │
│     │ Int64 │ Int64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼───────┼───────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1   │ 2     │ 5     │ 0.1     │ 0.12    │ 0.18    │ 0.14    │ 0.15    │ 0.19    │ 0.2     │ 0.1     │ 0.21    │ 0.24    │ 0.15    │ 0.19    │ 0.2     │
│ 2   │ 3     │ 7     │ 0.03299 │ 0.05081 │ 0.0355  │ 0.02884 │ 0.03054 │ 0.0332  │ 0.03115 │ 0.02177 │ 0.04903 │ 0.04399 │ 0.03115 │ 0.02177 │ 0.04903 │
│ 3   │ 4     │ 3     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 4   │ 5     │ 4     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │ 0.0     │
│ 5   │ 6     │ 1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │ 0.1     │

The difference is that AsTable makes x to be a NamedTuple so we can index into x using column names passed as x.anc or as Symbols as in x[Symbol("anc", x.anc+i-1)].

EDIT

If you want more speed at the expense of code complexity you can write:

m = Matrix(df[!, 3:end])
v = df.anc
insertcols!(df, ["lap$k" => getindex.(Ref(m), axes(df, 1), v .+ k .- 1) for k in 1:3]...)
user_12695670
Related