Issue

I would like to evaluate each row in a column of values and create new columns that each have a single 1 flagging any rows in the initial column above a specific value and 0's for all remaining rows.

For example, starting with this column:

I would like to create new columns that flag each row above 0.5 as follows:

Is there a simple approach to make a script in unix to accomplish this? Thanks very much for any input!

Solution

This two pass awk script should do the job:

$ awk '
NR==FNR {                                             # 1st pass 
    n+=($1+0>0.5)                                     # count the # of values>0.5
    next
}
n {                                                   # 2nd pass
    for(i=(n+1);i>1;i--)                              # create cols
        $i=(FNR==1?"EV"(i-1):($1>0.5&&i==(s+2)&&++s)) # fill cols with 0s/1s
}1' file file                                         # output

Output:

FD EV1 EV2 EV3
0.1 0 0 0
0.4 0 0 0
0.6 1 0 0
0.2 0 0 0
0.9 0 1 0
1.2 0 0 1
0.3 0 0 0

Answered By - James Brown

Answer Checked By - Terry (WPSolving Volunteer)

Tuesday, January 30, 2024

[SOLVED] Create new columns with flag for each row of an initial reference column above a specific value

Issue

Solution