# How can I calculate the average of multiple columns with the same value for the first two columns?

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. Aug 14, 2017 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, 2017 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? Aug 14, 2017 at 21:07
• maybe this is clearer? Aug 14, 2017 at 21:49

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:
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 Aug 16, 2017 at 10:09