0

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   
  • 1
    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
  • 1
    What parts of the problem are you having difficulty with? Generating random numbers? Sorting? – glenn jackman Jun 7 '19 at 18:39
  • 1
    Do you need the intermediate output (1000 x 10 line tables) or just the final summary table? – bu5hman Jun 7 '19 at 19:10
0

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? – Ed Morton Jun 10 '19 at 1:58
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.