How to randomly change the order of two columns simultaneously for 1000 times using simulation values?

I have a data looks like this:

ID    pheno

231   0.12
432   0.32
11    0.0003
134   0.33
2334  0.553
12    0.33
100   0.331
1008  1.6
223  -0.81
998  -3.001

I should create 2 columns named as "simulation1" and "simulation2" with random numbers within them like below:

ID    pheno  simulation1 simulation2

134   0.33     4.11   0.004
2334  0.553    83     0.44
12    0.33     87     0.55
100   0.331    88     12
231   0.12     0.1    2
432   0.32     3      30
11    0.0003   3.5    11
1008  1.6      89     23
223  -0.81     113    0.001
998  -3.001    220    982

after that I sort columns "ID" and "pheno" according to the value on column "simulation1", then I calculate the average of "pheno" for top 40% rows which will be 0.193 = (0.12+0.32+0.0003+0.33)/4

ID    pheno  simulation1 simulation2

231   0.12     0.1    2
432   0.32     3      30
11    0.0003   3.5    11
134   0.33     4.11   0.004
2334  0.553    83     0.44
12    0.33     87     0.55
100   0.331    88     12
1008  1.6      89     23
223  -0.81     113    0.001
998  -3.001    220    982

Then I sort columns "ID" and "pheno" according to the value on column "simulation2", then I calculate the average of "pheno" for top 40% rows which will be 0.1 = (-0.81+0.33+0.553+0.33)/4

ID    pheno  simulation1 simulation2

223  -0.81     113    0.001
134   0.33     4.11   0.004
2334  0.553    83     0.44
12    0.33     87     0.55
231   0.12     0.1    2
11    0.0003   3.5    11
100   0.331    88     12
1008  1.6      89     23
432   0.32     3      30
998  -3.001    220    980

Then the difference between the averages of top 40% using different sortings would be -0.093 = 0.1 - 0.193.

For the second round, the first two colomns (ID and pheno) will remain constant, but I should create new values in columns "simulation1" and "simulation2". I create values in "simulation1" and "simulation2" in excel using function RAND()*. But it is impossible for me to repeat it it 1000 times. I am wondering how can I creae a loop in linux for what I want.

The final output will be:

row_name top40%average_simulate1  top40%average_simulate2  difference

1          0.193                    0.1                    -0.093
2
.
.
.
1000
• It is not clear what you mean with "What I want is doing the same thing for 1000 times ...". Do you have 1000 files? Do you have 1000 columns where "pheno" is one of them?
– Bodo
Jun 7 '19 at 17:17
• I have only one file with columns "ID" and "pheno". I need to simulate the second and forth column by myself. the numbers with columns "simulation1" and "simulation2" should be random. However in each repeat I need new random values in columns "simulation1 and simulation2". Is that clear now?
– zara
Jun 7 '19 at 17:58
• I added more explanation in m the body of my question to make it clear. I appreciate if you have any suggestion.
– zara
Jun 7 '19 at 18:21
• What parts of the problem are you having difficulty with? Generating random numbers? Sorting? Jun 7 '19 at 18:39
• Do you need the intermediate output (1000 x 10 line tables) or just the final summary table? Jun 7 '19 at 19:10

In all cases below | column -t is just added to make the output visually aligned.

1) I should create 2 columns named as "simulation1" and "simulation2" with random numbers within them

\$ cat tst.awk
BEGIN { srand(seed) }
{ print \$0, r(), r() }
function r() { return rand() * 100001 / 1000 }

\$ awk -f tst.awk file | column -t
231   0.12    85.5574  23.7444
432   0.32    23.558   65.5853
11    0.0003  59.2486  50.3799
134   0.33    27.8248  45.7872
2334  0.553   45.7947  13.1887
12    0.33    51.6042  99.55
100   0.331   88.0281  17.4515
1008  1.6     1.37974  65.5945
223   -0.81   14.6773  97.6476
998   -3.001  87.888   31.97

2) after that I sort columns "ID" and "pheno" according to the value on column "simulation1"

\$ awk -f tst.awk file | sort -k3,3n | column -t
1008  1.6     1.37974  65.5945
223   -0.81   14.6773  97.6476
432   0.32    23.558   65.5853
134   0.33    27.8248  45.7872
2334  0.553   45.7947  13.1887
12    0.33    51.6042  99.55
11    0.0003  59.2486  50.3799
231   0.12    85.5574  23.7444
998   -3.001  87.888   31.97
100   0.331   88.0281  17.4515

3) then I calculate the average of "pheno" for top 40% rows

\$ cat tst2.awk
{ vals[NR] = \$2 }
END {
max = NR * 40 / 100
for (i=1; i<=max; i++) {
sum += vals[i]
}
print sum / max
}

\$ awk -f tst.awk file | sort -k3,3n | awk -f tst2.awk
0.36

I expect you can figure out the rest. In the above I gave awk the same seed on every call so that the output would remain constant for ease of tracking through the stages of comuptation. Change the calls for tst.awk to awk -v seed="\$RANDOM" -f tst.awk to get different random numbers generated on every invocation.

• Thanks, but how can I make loop so this process repeats 1000 times?
– zara
Jun 9 '19 at 18:18
• Depends what you mean by that but maybe put it in a shell loop that says for ((( i=1; i<=1000; i++ )); do awk '...'; done? Jun 10 '19 at 1:58

An updated script because bc didn't play well with the leading signs on the numbers, so changed to awk in the domath.

Also changed to using shuf to shuffle the array indices for each iteration as its just simpler with a fixed array.

#!/bin/bash

function domath {
#do the math using the 4 indices into the pheno array
awk '{print (\$1+\$2+\$3+\$4)/4}' <<<"\${ph[\$1]} \${ph[\$2]} \${ph[\$3]} \${ph[\$4]}"
}

function iterate {
#randomise the indices and get the first 4
shuf -e 0 1 2 3 4 5 6 7 8 9 | head -n 4
}

#number of iterations
nits=100

#read the pheno values into an array
ph=(\$(tail -n +3 data | awk '{print \$2}'))

echo -e row'\t'sim1'\t'sim2'\t'diff
for (( row=1; row<=\$nits; row++ )); do
#calculate simulation1
first=\$(printf "%+.3f" \$(domath \$(iterate)))
#calculate simulation 2
second=\$(printf "%+.3f" \$(domath \$(iterate)))
#calculate the difference
diff=\$(printf "%+.3f" \$(awk '{print \$2-\$1}' <<<"\$first \$second"))
#and print
echo -e \$row'\t'\$first'\t'\$second'\t'\$diff
done