09-19-2018
Find the minimum value of the column with respect to other column
Hi All,
I would like get the minimum value in the certain column with respect to other column.
For example, I have a text file like this.
Code
ATOM 1 QSS SPH S 0 -2.790 -1.180 -2.282 2.28 2.28
ATOM 1 QSS SPH S 1 -2.915 -1.024 -2.032 2.31 2.31
ATOM 1 QSS SPH S 2 -2.946 -1.077 -1.782 2.21 2.21
ATOM 1 QSS SPH S 3 -2.974 -1.129 -1.532 2.12 2.12
ATOM 1 QSS SPH S 4 -3.018 -1.206 -1.282 2.05 2.05
ATOM 1 QSS SPH S 5 -3.053 -1.272 -1.032 2.00 2.00
ATOM 1 QSS SPH S 6 -3.086 -1.333 -0.782 1.96 1.96
ATOM 1 QSS SPH S 7 -3.121 -1.393 -0.532 1.94 1.94
ATOM 1 QSS SPH S 8 -3.150 -1.447 -0.282 1.94 1.94
ATOM 1 QSS SPH S 9 -3.175 -1.539 -0.032 1.94 1.94
ATOM 1 QSS SPH S 10 -3.184 -1.653 0.218 1.94 1.94
ATOM 1 QSS SPH S 11 -3.190 -1.762 0.468 1.96 1.96
ATOM 1 QSS SPH S 12 -3.200 -1.879 0.718 2.00 2.00
ATOM 1 QSS SPH S 13 -3.209 -1.989 0.968 2.06 2.06
ATOM 1 QSS SPH S 14 -3.150 -1.991 1.218 2.08 2.08
ATOM 1 QSS SPH S 15 -3.044 -1.900 1.468 2.08 2.08
ATOM 1 QSS SPH S 16 -2.895 -1.745 1.718 2.07 2.07
ATOM 1 QSS SPH S 17 -2.749 -1.589 1.968 2.10 2.10
ATOM 1 QSS SPH S 18 -2.656 -1.432 2.218 2.11 2.11
ATOM 1 QSS SPH S 19 -2.579 -1.282 2.468 2.14 2.14
ATOM 1 QSS SPH S 20 -2.499 -1.181 2.718 2.15 2.15
ATOM 1 QSS SPH S 21 -2.387 -1.226 2.968 2.12 2.12
ATOM 1 QSS SPH S 22 -2.273 -1.270 3.218 2.11 2.11
ATOM 1 QSS SPH S 23 -2.162 -1.311 3.468 2.12 2.12
ATOM 1 QSS SPH S 24 -2.049 -1.352 3.718 2.14 2.14
ATOM 1 QSS SPH S 25 -1.950 -1.384 3.968 2.18 2.18
ATOM 1 QSS SPH S 26 -1.836 -1.430 4.218 2.25 2.25
ATOM 1 QSS SPH S 27 -1.731 -1.459 4.468 2.33 2.33
ATOM 1 QSS SPH S 28 -1.619 -1.499 4.718 2.42 2.42
ATOM 1 QSS SPH S 29 -1.515 -1.530 4.968 2.54 2.54
ATOM 1 QSS SPH S 30 -1.410 -1.565 5.218 2.66 2.66
ATOM 1 QSS SPH S 31 -1.303 -1.594 5.468 2.80 2.80
ATOM 1 QSS SPH S 32 -1.251 -1.576 5.718 2.93 2.93
ATOM 1 QSS SPH S 33 -1.287 -1.476 5.968 3.03 3.03
ATOM 1 QSS SPH S 34 -1.324 -1.374 6.218 3.15 3.15
ATOM 1 QSS SPH S 35 -1.358 -1.272 6.468 3.28 3.28
ATOM 1 QSS SPH S 36 -1.403 -1.161 6.718 3.41 3.41
ATOM 1 QSS SPH S 37 -1.435 -1.063 6.968 3.56 3.56
ATOM 1 QSS SPH S 38 -1.628 -0.803 7.218 3.69 3.69
ATOM 1 QSS SPH S 39 -1.517 -0.809 7.468 3.65 3.65
ATOM 1 QSS SPH S 40 -1.473 -0.848 7.718 3.57 3.57
ATOM 1 QSS SPH S 41 -1.436 -0.888 7.968 3.50 3.50
ATOM 1 QSS SPH S 42 -1.377 -0.943 8.218 3.43 3.43
ATOM 1 QSS SPH S 43 -1.319 -1.004 8.468 3.37 3.37
ATOM 1 QSS SPH S 44 -1.255 -1.064 8.718 3.32 3.32
ATOM 1 QSS SPH S 45 -1.197 -1.126 8.968 3.29 3.29
ATOM 1 QSS SPH S 46 -1.137 -1.187 9.218 3.27 3.27
ATOM 1 QSS SPH S 47 -1.074 -1.248 9.468 3.26 3.26
ATOM 1 QSS SPH S 48 -1.015 -1.309 9.718 3.27 3.27
ATOM 1 QSS SPH S 49 -0.954 -1.371 9.968 3.29 3.29
ATOM 1 QSS SPH S 50 -0.893 -1.433 10.218 3.33 3.33
ATOM 1 QSS SPH S 51 -0.836 -1.491 10.468 3.38 3.38
ATOM 1 QSS SPH S 52 -0.783 -1.546 10.718 3.44 3.44
ATOM 1 QSS SPH S 53 -0.724 -1.605 10.968 3.51 3.51
ATOM 1 QSS SPH S 54 -0.664 -1.663 11.218 3.60 3.60
ATOM 1 QSS SPH S 55 -0.746 -1.707 11.468 3.63 3.63
ATOM 1 QSS SPH S 56 -0.852 -1.744 11.718 3.66 3.66
ATOM 1 QSS SPH S 57 -0.968 -1.785 11.968 3.71 3.71
ATOM 1 QSS SPH S 58 -1.086 -1.825 12.218 3.76 3.76
ATOM 1 QSS SPH S 59 -1.198 -1.865 12.468 3.84 3.84
ATOM 1 QSS SPH S 60 -1.232 -1.857 12.718 3.90 3.90
ATOM 1 QSS SPH S 61 -1.100 -1.750 12.968 3.92 3.92
ATOM 1 QSS SPH S 62 -0.972 -1.637 13.218 3.96 3.96
ATOM 1 QSS SPH S 63 -0.865 -1.531 13.468 4.01 4.01
ATOM 1 QSS SPH S 64 -0.782 -1.445 13.718 4.05 4.05
ATOM 1 QSS SPH S 65 -0.574 -1.306 13.968 4.17 4.17
ATOM 1 QSS SPH S 66 -0.682 -1.229 14.218 4.21 4.21
ATOM 1 QSS SPH S 67 -0.713 -1.189 14.468 4.22 4.22
ATOM 1 QSS SPH S 68 -0.556 -1.269 14.718 4.17 4.17
ATOM 1 QSS SPH S 69 -0.399 -1.342 14.968 4.12 4.12
ATOM 1 QSS SPH S 70 -0.268 -1.363 15.218 4.08 4.08
ATOM 1 QSS SPH S 71 -0.158 -1.328 15.468 4.03 4.03
ATOM 1 QSS SPH S 72 0.039 -1.341 15.718 3.96 3.96
ATOM 1 QSS SPH S 73 0.215 -1.366 15.968 3.88 3.88
ATOM 1 QSS SPH S 74 0.330 -1.395 16.218 3.81 3.81
ATOM 1 QSS SPH S 75 0.441 -1.425 16.468 3.75 3.75
ATOM 1 QSS SPH S 76 0.559 -1.456 16.718 3.71 3.71
ATOM 1 QSS SPH S 77 0.670 -1.484 16.968 3.68 3.68
ATOM 1 QSS SPH S 78 0.795 -1.488 17.218 3.64 3.64
ATOM 1 QSS SPH S 79 0.930 -1.488 17.468 3.61 3.61
ATOM 1 QSS SPH S 80 1.058 -1.478 17.718 3.59 3.59
ATOM 1 QSS SPH S 81 1.218 -1.504 17.968 3.58 3.58
ATOM 1 QSS SPH S 82 1.399 -1.554 18.218 3.58 3.58
ATOM 1 QSS SPH S 83 1.586 -1.606 18.468 3.60 3.60
ATOM 1 QSS SPH S 84 1.763 -1.650 18.718 3.64 3.64
ATOM 1 QSS SPH S 85 4.004 -3.386 18.968 3.92 3.92
ATOM 1 QSS SPH S 86 4.606 -3.652 19.218 4.17 4.17
ATOM 1 QSS SPH S 87 4.291 -3.332 19.468 4.12 4.12
ATOM 1 QSS SPH S 88 3.958 -2.987 19.718 4.12 4.12
ATOM 1 QSS SPH S 89 3.637 -2.644 19.968 4.16 4.16
ATOM 1 QSS SPH S 90 3.330 -2.306 20.218 4.24 4.24
ATOM 1 QSS SPH S 91 3.093 -1.847 20.468 4.28 4.28
ATOM 1 QSS SPH S 92 3.040 -1.811 20.718 4.24 4.24
ATOM 1 QSS SPH S 93 2.991 -1.772 20.968 4.22 4.22
ATOM 1 QSS SPH S 94 2.937 -1.740 21.218 4.21 4.21
ATOM 1 QSS SPH S 95 2.885 -1.703 21.468 4.21 4.21
ATOM 1 QSS SPH S 96 2.830 -1.659 21.718 4.22 4.22
ATOM 1 QSS SPH S 97 2.789 -1.605 21.968 4.24 4.24
ATOM 1 QSS SPH S 98 2.743 -1.555 22.218 4.26 4.26
ATOM 1 QSS SPH S 99 2.698 -1.504 22.468 4.30 4.30
ATOM 1 QSS SPH S 100 2.652 -1.453 22.718 4.34 4.34
ATOM 1 QSS SPH S 101 2.607 -1.402 22.968 4.40 4.40
ATOM 1 QSS SPH S 102 2.561 -1.351 23.218 4.47 4.47
ATOM 1 QSS SPH S 103 2.518 -1.299 23.468 4.54 4.54
ATOM 1 QSS SPH S 104 2.474 -1.247 23.718 4.63 4.63
ATOM 1 QSS SPH S 105 2.428 -1.196 23.968 4.73 4.73
ATOM 1 QSS SPH S 106 2.386 -1.144 24.218 4.83 4.83
ATOM 1 QSS SPH S 107 2.343 -1.091 24.468 4.94 4.94
ATOM 1 QSS SPH S 0 -2.790 -1.180 -2.282 2.28 2.28
ATOM 1 QSS SPH S -1 -2.677 -1.347 -2.532 2.25 2.25
ATOM 1 QSS SPH S -2 -2.565 -1.509 -2.782 2.25 2.25
ATOM 1 QSS SPH S -3 -2.448 -1.674 -3.032 2.28 2.28
ATOM 1 QSS SPH S -4 -2.340 -1.834 -3.282 2.33 2.33
ATOM 1 QSS SPH S -5 -2.215 -2.004 -3.532 2.40 2.40
ATOM 1 QSS SPH S -6 -2.078 -2.190 -3.782 2.50 2.50
ATOM 1 QSS SPH S -7 -1.986 -2.291 -4.032 2.59 2.59
ATOM 1 QSS SPH S -8 -1.893 -2.283 -4.282 2.54 2.54
ATOM 1 QSS SPH S -9 -1.770 -2.354 -4.532 2.47 2.47
ATOM 1 QSS SPH S -10 -1.654 -2.414 -4.782 2.41 2.41
ATOM 1 QSS SPH S -11 -1.539 -2.479 -5.032 2.38 2.38
ATOM 1 QSS SPH S -12 -1.421 -2.544 -5.282 2.37 2.37
ATOM 1 QSS SPH S -13 -1.304 -2.608 -5.532 2.37 2.37
ATOM 1 QSS SPH S -14 -1.192 -2.668 -5.782 2.39 2.39
ATOM 1 QSS SPH S -15 -1.071 -2.729 -6.032 2.43 2.43
ATOM 1 QSS SPH S -16 -1.129 -2.729 -6.282 2.39 2.39
ATOM 1 QSS SPH S -17 -1.189 -2.725 -6.532 2.36 2.36
ATOM 1 QSS SPH S -18 -1.262 -2.725 -6.782 2.34 2.34
ATOM 1 QSS SPH S -19 -0.471 -2.444 -7.032 2.33 2.33
ATOM 1 QSS SPH S -20 -0.315 -2.456 -7.282 2.28 2.28
ATOM 1 QSS SPH S -21 -0.069 -2.493 -7.532 2.21 2.21
ATOM 1 QSS SPH S -22 0.130 -2.547 -7.782 2.16 2.16
ATOM 1 QSS SPH S -23 0.332 -2.599 -8.032 2.13 2.13
ATOM 1 QSS SPH S -24 0.633 -2.513 -8.282 2.11 2.11
ATOM 1 QSS SPH S -25 1.104 -2.043 -8.532 2.04 2.04
ATOM 1 QSS SPH S -26 1.276 -1.840 -8.782 1.93 1.93
ATOM 1 QSS SPH S -27 3.055 -2.138 -9.032 1.96 1.96
ATOM 1 QSS SPH S -28 3.146 -1.917 -9.282 1.95 1.95
ATOM 1 QSS SPH S -29 2.929 -1.582 -9.532 1.89 1.89
ATOM 1 QSS SPH S -30 2.726 -1.258 -9.782 1.89 1.89
ATOM 1 QSS SPH S -31 2.913 -1.280 -10.032 1.81 1.81
ATOM 1 QSS SPH S -32 3.235 -1.415 -10.282 1.75 1.75
ATOM 1 QSS SPH S -33 3.796 -1.762 -10.532 1.75 1.75
ATOM 1 QSS SPH S -34 3.926 -1.732 -10.782 1.78 1.78
ATOM 1 QSS SPH S -35 4.171 -1.689 -11.032 1.76 1.76
ATOM 1 QSS SPH S -36 4.124 -1.792 -11.282 1.65 1.65
ATOM 1 QSS SPH S -37 4.586 -1.340 -11.532 1.56 1.56
ATOM 1 QSS SPH S -38 4.625 -1.359 -11.782 1.44 1.44
ATOM 1 QSS SPH S -39 4.817 -1.316 -12.032 1.29 1.29
ATOM 1 QSS SPH S -40 4.928 -1.367 -12.282 1.13 1.13
ATOM 1 QSS SPH S -41 3.836 -2.625 -12.532 1.06 1.06
ATOM 1 QSS SPH S -42 3.773 -2.795 -12.782 1.03 1.03
ATOM 1 QSS SPH S -43 3.730 -2.942 -13.032 1.02 1.02
ATOM 1 QSS SPH S -44 3.705 -3.069 -13.282 1.03 1.03
ATOM 1 QSS SPH S -45 3.895 -2.995 -13.532 0.91 0.91
ATOM 1 QSS SPH S -46 4.086 -2.977 -13.782 0.81 0.81
ATOM 1 QSS SPH S -47 4.274 -2.964 -14.032 0.75 0.75
ATOM 1 QSS SPH S -48 4.564 -2.974 -14.282 0.64 0.64
ATOM 1 QSS SPH S -49 5.917 -1.810 -14.532 0.74 0.74
ATOM 1 QSS SPH S -50 6.421 -1.439 -14.782 0.74 0.74
ATOM 1 QSS SPH S -51 6.886 -1.081 -15.032 0.89 0.89
ATOM 1 QSS SPH S -52 7.017 -0.959 -15.282 0.97 0.97
ATOM 1 QSS SPH S -53 6.894 -0.996 -15.532 0.97 0.97
ATOM 1 QSS SPH S -54 6.785 -1.033 -15.782 1.00 1.00
ATOM 1 QSS SPH S -55 6.680 -1.072 -16.032 1.05 1.05
ATOM 1 QSS SPH S -56 6.626 -1.112 -16.282 1.10 1.10
ATOM 1 QSS SPH S -57 6.642 -1.156 -16.532 1.15 1.15
ATOM 1 QSS SPH S -58 6.851 -1.213 -16.782 1.16 1.16
ATOM 1 QSS SPH S -59 6.785 -1.414 -17.032 1.15 1.15
ATOM 1 QSS SPH S -60 6.644 -1.658 -17.282 1.17 1.17
ATOM 1 QSS SPH S -61 6.266 -2.022 -17.532 1.26 1.26
ATOM 1 QSS SPH S -62 6.164 -2.240 -17.782 1.38 1.38
ATOM 1 QSS SPH S -63 5.492 -3.497 -18.032 1.61 1.61
ATOM 1 QSS SPH S -64 5.566 -3.685 -18.282 1.76 1.76
ATOM 1 QSS SPH S -65 5.641 -3.884 -18.532 1.94 1.94
ATOM 1 QSS SPH S -66 5.714 -4.076 -18.782 2.13 2.13
ATOM 1 QSS SPH S -67 6.247 -5.586 -19.032 2.48 2.48
ATOM 1 QSS SPH S -68 6.117 -5.371 -19.282 2.64 2.64
ATOM 1 QSS SPH S -69 5.989 -5.160 -19.532 2.81 2.81
ATOM 1 QSS SPH S -70 5.894 -4.977 -19.782 2.99 2.99
ATOM 1 QSS SPH S -71 5.968 -4.945 -20.032 3.14 3.14
ATOM 1 QSS SPH S -72 6.074 -4.934 -20.282 3.28 3.28
ATOM 1 QSS SPH S -73 6.193 -4.942 -20.532 3.43 3.43
ATOM 1 QSS SPH S -74 6.320 -4.951 -20.782 3.59 3.59
ATOM 1 QSS SPH S -75 6.437 -4.958 -21.032 3.76 3.76
ATOM 1 QSS SPH S -76 6.570 -4.972 -21.282 3.93 3.93
ATOM 1 QSS SPH S -77 6.718 -5.005 -21.532 4.12 4.12
ATOM 1 QSS SPH S -78 6.668 -5.035 -21.782 4.21 4.21
ATOM 1 QSS SPH S -79 6.633 -5.103 -22.032 4.30 4.30
ATOM 1 QSS SPH S -80 6.509 -5.078 -22.282 4.42 4.42
ATOM 1 QSS SPH S -81 6.469 -5.140 -22.532 4.53 4.53
ATOM 1 QSS SPH S -82 6.418 -5.194 -22.782 4.65 4.65
ATOM 1 QSS SPH S -83 6.378 -5.256 -23.032 4.78 4.78
Form this data I like get the minimum value of column 10 with respect to column 6.
I am expecting output as
Code
ATOM 1 QSS SPH S 7 -3.121 -1.393 -0.532 1.94 1.94
ATOM 1 QSS SPH S -48 4.564 -2.974 -14.282 0.64 0.64
I am looking for awk one liner that will help me to find the minimum values of the column 10.
Thank you very much for your kind help.
Balaji
Last edited by Scrutinizer; 09-19-2018 at 12:57 PM ..
Reason: code tags
10 More Discussions You Might Find Interesting
1. Shell Programming and ScriptingHi. How do I find an expression with awk in only one column, and if it fits, then print that whole column.
1 apple oranges
2 bannanas pears
3 cats dogs
4 hesaid shesaid
echo "which number:"
read NUMBER (user inputs number 2 for this example)
awk " /$NUMBER/ {field to search is field... (2 Replies)
Discussion started by: glev2005
2 Replies
2. Shell Programming and ScriptingI have a data file with two columns,
for the second column I want to find the minimum,
and subtract this minimum from each value in the second column,
how to realize this using C shell
For example, I have
1 -2.4
2 -4.8
3 7.9
I wanna output
1 2.4
2 0
3 12.7
Thanks! (4 Replies)
Discussion started by: rockytodd
4 Replies
3. Shell Programming and ScriptingHello All,
Please help me with this file.
My input file (Tab separated) is like:
Abc-01 pc1 -0.69
Abc-01 E2cR 0.459666666666667
Abc-01 5ez.2 1.2265625
Xyz-01 pc1 -0.153
Xyz-01 E2cR 1.7358
Xyz-01 5ez.2 2.0254
Ced-02 pc1 -0.5714
Ced-02 ... (7 Replies)
Discussion started by: mira
7 Replies
4. UNIX for Dummies Questions & AnswersHi,
I have a file like:
0.000000 124.085533 124.085533 124.085533 124.085533 124.085533 124.085533 124.085533 124.085533 124.085533 33.097845 33.363764
0.000000 266.483441 262.519130 266.380993 274.989622 289.594799 309.523518 336.124848 372.386124 413.522043 429.984825 421.621810... (6 Replies)
Discussion started by: cosmologist
6 Replies
5. UNIX for Dummies Questions & AnswersHi,
I have a wide and long dataset which looks as follows:
0 3 4 2 3 0 2 2 ...
3 2 4 0 2 2 2 3 ...
0 3 4 2 0 4 4 4 ...
3 0 4 2 2 4 2 4 ...
....
I would like to obtain the minimum of each column (ignoring zero values) so the output would look like:
3 2 4 2 2 2 2 2
I have the... (3 Replies)
Discussion started by: kasan0
3 Replies
6. Shell Programming and ScriptingInput file A.txt :-
C2062 -117.6 -118.5 -117.5
C5145 0 0 0
C5696 0 0 0
Output file B.txt
C2062 X -117.6
C2062 Y -118.5
C2062 Z -117.5... (4 Replies)
Discussion started by: asavaliya
4 Replies
7. Shell Programming and ScriptingI have a file like:
I would like to find lines lines with duplicate values in column 1, and retain only one based on two conditions: 1) keep line with highest value in column 3, 2) if column 3 values are equal, retain the line with the highest value in column 4.
Desired output:
I was able to... (3 Replies)
Discussion started by: pathunkathunk
3 Replies
8. Shell Programming and Scriptinghi all,
i want to get the most comon column w.r.t another
this is my file
Tom|london
Tom|london
Tom|Paris
Adam|Madrid
Adam|NY
the Output to get me :
Tom|london
Adamn|Madrid
ive tried (10 Replies)
Discussion started by: teefa
10 Replies
9. Shell Programming and ScriptingHello,
I need to find out the minimum and maximum values based on specific column, and then print out the entire row with the max value.
Infile.txt:
scf6 290173 290416 . + X_047241 T_00113118-1
scf6 290491 290957 . + X_047241 T_00113118-2
scf6 290898 290957 . + X_047241 T_00113119-3
scf6... (2 Replies)
Discussion started by: yifangt
2 Replies
10. Shell Programming and ScriptingNeed your support for below. Please help to get required output
If column 5 is INV then only consider column1 and take out duplicates/identical rows/values from column1 and then put minimum value of column6 in column7 and put maximum value in column 8 and then need to do subtract values of... (7 Replies)
Discussion started by: as7951
7 Replies
09-19-2018