3

I have a file of about 13 million lines like:

Lat Long    air_temp    sst wind_speed  wave_height wave_period
65.3    7.3 4.3 8.8 7.7 4   8
61.6    1.3 -9.99   8.8 9.8 4   7
61.2    1.1 -9.99   8.8 7.7 3   7
61.1    1   -9.99   8.8 8.7 3.5 7
61  1.7 -9.99   8.8 10.8    4   7
60.6    1.7 -9.99   8.8 8.2 4   10
60.6    3.7 -9.99   8.8 8.2 3.5 8
60.6    -4.9    4.7 8.8 10.3    3.5 7
60.4    1.2 5.1 7   15  2   4
59.6    2.2 2.3 7.7 4.6 3.5 9
59.5    1.6 -9.99   7.7 3.6 4   8

I have had 72 files with all of these variables. I have merged them in just a single one and deleted the duplicates. What I have to do is when lat and long is the same for 2 lines, I have to calculate the average of the columns. For example:

Lat Long    air_temp    sst wind_speed  wave_height wave_period
61.1    1   -9.99   8.8 8.7 3.5 7
61.6    1.3 -9.99   8.8 9.8 4   7
61.6    1.3 3   8.6 7.7 3   7
65.3    7.3 4.3 8.8 7.7 4   8
65.3    7.3 2   4   5   2   10

outputfile will look something like this:

Lat Long    air_temp    sst wind_speed  wave_height wave_period
61.1    1   -9.99   8.8 8.7 3.5 7
61.6    1.3 -9.99   8.7 8.75 3.5  7
65.3    7.3 3.15 6.4 6.35 3   9

and therefore:

  1. if air_temp=-9.99 the 'average' calculated will be -9.99 as this shows missing data
  2. the average is calculated over as many points there are with the same long and lat - in case there are 2 points with the coordinates 61.6 and 1.3, there will be just one line with the variables (air_temp, sst, wind_speed, wave_height and wave_period) calculated on an average.

the original file:

Lat Long    air_temp    sst wind_speed  wave_height wave_period 
0   0.1 22.9    22.5    7.7 1   5
0   0.2 24  26  4.6 2   6 
0   0   24.1    25.3    3   1.5 9
0   0   24.4    25.3    3   1.5 8
0   0   24.5    25.3    2   1.5 8
0   0   24.7    25.2    1   1.5 10
0   0   24.8    25.1    3   1.5 8
0   0   24.8    25.2    2   1.5 12
0   0   24.9    25.2    5   1.5 9
0   0   25.2    25.5    2   3.5 10
0   0   25  25.2    5   1.5 9
0   0   26.9    27.2    4   1.5 10
0   0   26.9    27.2    5   1.5 9
0   0   28.5    29.6    7.2 1.5 7
0   -0.2    -9.99   30.4    3.6 1.5 8
0   0.3 27  27  4.6 2   12
0   -0.3    27  27.5    5.7 1.5 8
0   0.4 23  23  8.2 1.5 3
0   0.5 24.6    25  10.3    2   6
0   0.6 26.7    27  5.1 1.5 10
0   -0.7    24  24.8    5.7 1   3
0   0.7 24  27  7.2 1.5 10
0   0.7 -9.99   28  6   1   8
0   0.8 27  28  7.2 1.5 9

I don't know why it doesn't sort completely (why there is 0.1 and 0.2 in front), but nonetheless the desired output is:

 Lat    Long    air_temp    sst     wind_speed  wave_height     wave_period
 0  0.1     22.9    22.5    7.7     1   5
 0  0.2     24  26  4.6     2   6
 0  0   25.3916666667   25.9416666667   3.5166666667    1.6666666667 9.0833333333
 0  -0.2    -9.99   30.4    3.6     1.5     8
 0  0.3     27  27  4.6     2   12
 0  -0.3    27  27.5    5.7     1.5     8
 0  0.4     23  23  8.2     1.5     3
 0  0.5     24.6    25  10.3    2   6
 0  0.6     26.7    27  5.1     1.5     10
 0  -0.7    24  24.8    5.7     1   3
 0  0.7     -9.99   27.5    6.6     1.25    9
 0  0.8     27  28  7.2     1.5     9
  • Hi, welcome to U&L. Normal form here is to post an example of the desired output, helps folk understand your requirement more clearly. – steve Aug 14 '17 at 19:43
  • Please take some time to put your thoughts together. It's difficult to figure what you are trying to ask. And in any case, a sample representative of all possible scenarios of inputs + expected output is a minimum. – user218374 Aug 14 '17 at 20:56
  • I don't see in your example five consecutive lines with identical Lat and Long. What are we supposed to do when there are less or more than 5 lines? – xhienne Aug 14 '17 at 21:07
  • 2
    maybe this is clearer? – Roxana Radu Aug 14 '17 at 21:49
2

This can be done with Python like:

Code:

#!/usr/bin/python
import re
import sys

SPACES = re.compile('\s+')

data_by_lat_long = {}

with open(sys.argv[1]) as f:
    # get and print header
    line = next(f)
    print(line.strip())

    for line in f:
        data = SPACES.split(line.strip())
        data_by_lat_long.setdefault((data[0], data[1]), []).append(data[2:])

for lat_long, data in data_by_lat_long.items():
    results = zip(*data)
    if '-9.99' in results[0]:
        results[0] = ('-9.99', )
    avg = tuple(str(sum(float(x) for x in d) / len(d)) for d in results)
    print('\t'.join(lat_long + avg))

Results:

Lat Long    air_temp    sst wind_speed  wave_height wave_period
65.3    7.3 3.15    6.4 6.35    3.0 9.0
61.1    1   -9.99   8.8 8.7 3.5 7.0
61.6    1.3 -9.99   8.7 8.75    3.5 7.0

and,

Lat Long    air_temp    sst wind_speed  wave_height wave_period
0   -0.7    24.0    24.8    5.7 1.0 3.0
0   0.1 22.9    22.5    7.7 1.0 5.0
0   0.3 27.0    27.0    4.6 2.0 12.0
0   0.2 24.0    26.0    4.6 2.0 6.0
0   0.8 27.0    28.0    7.2 1.5 9.0
0   -0.3    27.0    27.5    5.7 1.5 8.0
0   0.5 24.6    25.0    10.3    2.0 6.0
0   -0.2    -9.99   30.4    3.6 1.5 8.0
0   0.4 23.0    23.0    8.2 1.5 3.0
0   0.7 -9.99   27.5    6.6 1.25    9.0
0   0   25.3916666667   25.9416666667   3.51666666667   1.66666666667   9.08333333333
0   0.6 26.7    27.0    5.1 1.5 10.0
  • Thanks, works perfect! it should work with a one line too, with gnu datamash: datamash -H -s -g 1,2 $(printf 'mean %s ' {3..7}) < infile.txt , only I get this error which is definitely confusing as I don't see any differences in the wave_period column (it works without the last column): 'atamash: invalid numeric input in line 2 field 7: '3. The condition must be added on too – Roxana Radu Aug 16 '17 at 10:09

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.