Find the minimum value of the column with respect to other column


 
Thread Tools Search this Thread
Top Forums Programming Find the minimum value of the column with respect to other column
# 1  
Old 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
# 2  
Old 09-19-2018
Hi, try:
Code:
awk '$6==0{if(p) print p; m=""} m==""||$10<m{m=$10; p=$0} END{print p}' file

# 3  
Old 09-19-2018
Hi Moderator,

Sorry for pasting the data as text file. I will copy and paste them in code tags hereafter.

I tried the awk one liner. It gets me only the first line of the output and does not show the second one in output.
Code:
ATOM      1  QSS SPH S   7      -3.121  -1.393  -0.532  1.94  1.94

I am also expecting second one also as output
Code:
ATOM      1  QSS SPH S -48       4.564  -2.974 -14.282  0.64  0.64

Thank You,
Balaji
# 4  
Old 09-19-2018
Hi, with the specific example in post#1 I get:
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

Dit you try it with that sample?
What is your OS and version?
# 5  
Old 09-19-2018
Hi Moderator,

I tried the example code and it outputs only the "S 7" not the other one "S -48".

I am using mac OS X Yosemite and version 10.10.5

Thank you sir.

------ Post updated at 05:18 PM ------

Hi Moderator,

I tried the same in linux and it works.

Sorry for the inconvenience sir.

Thank You very much for your help.

Balaji.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to find maximum and minimum from column and store in other column

Need 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

2. Shell Programming and Scripting

Find minimum and maximum values based on column with associative array

Hello, 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

3. Shell Programming and Scripting

Getting the most common column with respect another

hi 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

4. Shell Programming and Scripting

Find lines with matching column 1 value, retain only the one with highest value in column 2

I 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

5. Shell Programming and Scripting

convert row to column with respect of first column.

Input 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

6. UNIX for Dummies Questions & Answers

[Solved] Using awk to obtain minimum of each column (ignoring zeros)

Hi, 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

7. UNIX for Dummies Questions & Answers

Getting the minimum of each column in a file

Hi, 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

8. Shell Programming and Scripting

convert columns into rows with respect to first column

Hello 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

9. Shell Programming and Scripting

C shell--take the minimum of a column

I 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

10. Shell Programming and Scripting

find expression with awk in only one column, and if it fits, print whole column

Hi. 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
Login or Register to Ask a Question