0

I have a txt file just like below (This picture is from the .csv version of it). What I like to do is take the average of the months (starting column 7 to 10) and export it to a new column. But it just give me one average number.

OBSERVATORY,Abbreviations,COUNTRY,ALTITUDE(m),LONGITUDE(deg),LATITUDE (deg),January,February,March,April,May,June,July,August,September,October,November,December
Beverly-Begg Observatory Dunedin,,New Zealand,140,170.49,-45.8644,89.93,86.84,85.26,88.22,89.36,89.8,88.52,90.42,88.74,89.06,91.16,91.36
Aorangi Iti Observatory Lake Tekapo,,New Zealand,718,170.473,-44.0082,63.92,60.44,58.63,65.68,75.97,85.7,84.85,83.7,76.34,70.56,70.2,70.4
Mount John Observatory Lake Tekapo,,New Zealand,945,170.465,-43.9874,62.4,60.91,58.98,67.15,79.45,85.24,86.93,84.96,77.1,72.0,70.9,71.85

and my code is this:

awk '{ sum += $5 + $6 + $7 + $8 + $9 + $10 + $11 + $12 + $13 + $14
+ $15 } END { print sum / (NR * 18) }' observatory_1.txt > observatory_3.txt


output: 0.104394

I want to create a txt folder something like this:

OBSERVATORY, Abbreviations, COUNTRY, ALTITUDE(m), LONGITUDE(deg), LATITUDE (deg), MEAN
Beverly-Begg Observatory Dunedin, , New Zealand,  140, 170, 490, -45,8644, 89,05583333

Any suggestion would be appreciated.

0

3 Answers 3

2

Your script is summing a bunch of columns from each input line and then, after it has read ALL of the input lines, the END block is printing one output line...so it's processing the entire file before producing any output.

What you should be doing is processing each input line separately.

Your column numbers seem off too - e.g. why would you want to include the altitude, longitude, and latitude in the mean calculation? I'm going to assume you actually want the mean of columns 7 to 19 (January to December).

Anyway, you probably want something more like this:

awk -F, -v OFS=, '
     NR == 1 { print $1, $2, $3, $4, $5, $6, "MEAN" }
     NR  > 1 {
       sum = 0;
       for (i=7; i<=19; i++) { sum += $i }
       print $1, $2, $3, $4, $5, $6, (sum / 12)
     }' observatory_1.txt > observatory_3.txt

This will produce output like this:

OBSERVATORY,Abbreviations,COUNTRY,ALTITUDE(m),LONGITUDE(deg),LATITUDE (deg),MEAN
Beverly-Begg Observatory Dunedin,,New Zealand,140,170.49,-45.8644,89.0558
Aorangi Iti Observatory Lake Tekapo,,New Zealand,718,170.473,-44.0082,72.1992
Mount John Observatory Lake Tekapo,,New Zealand,945,170.465,-43.9874,73.1558

This probably isn't exactly what you want, but it should be a step in the right direction.

1

Using Raku (formerly known as Perl_6)

raku -e 'put get.split(",")[0..5].join(",") ~ ",MEAN"; \
      for lines() {my @a = .split(","); \
      put (@a[0...5].join(",") ~ "," ~ @a.[6..*].sum / @a.[6..*].elems)};'  

OR

raku -ne 'state $i=0; ++$i; my @a = .split(","); $i == 1 \
      ?? put @a.[0..5].join(",") ~ ",MEAN" \
      !! put (@a[0...5].join(",") ~ "," ~ @a.[6..*].sum / @a.[6..*].elems);'  

Sample Input:

OBSERVATORY,Abbreviations,COUNTRY,ALTITUDE(m),LONGITUDE(deg),LATITUDE (deg),January,February,March,April,May,June,July,August,September,October,November,December
Beverly-Begg Observatory Dunedin,,New Zealand,140,170.49,-45.8644,89.93,86.84,85.26,88.22,89.36,89.8,88.52,90.42,88.74,89.06,91.16,91.36
Aorangi Iti Observatory Lake Tekapo,,New Zealand,718,170.473,-44.0082,63.92,60.44,58.63,65.68,75.97,85.7,84.85,83.7,76.34,70.56,70.2,70.4
Mount John Observatory Lake Tekapo,,New Zealand,945,170.465,-43.9874,62.4,60.91,58.98,67.15,79.45,85.24,86.93,84.96,77.1,72.0,70.9,71.85

Sample Output (for both code solutions, above):

OBSERVATORY,Abbreviations,COUNTRY,ALTITUDE(m),LONGITUDE(deg),LATITUDE (deg),MEAN
Beverly-Begg Observatory Dunedin,,New Zealand,140,170.49,-45.8644,89.055833
Aorangi Iti Observatory Lake Tekapo,,New Zealand,718,170.473,-44.0082,72.199167
Mount John Observatory Lake Tekapo,,New Zealand,945,170.465,-43.9874,73.155833

Explaining the first answer briefly: get the header line, split on commas and output the first 6 columns followed by MEAN using .split(",")[0..5].join(",") ~ ",MEAN". (ICYMI, ~ tilde is used to concatenate strings in Raku).

Now with the linewise read cursor at the second line (first data row), for lines() reads input (linewise), splits on "," commas and stores the elements in the @a array. The first six columns @a[0..5] are output followed by the mean computed with @a.[6..*].sum / @a.[6..*].elems.

Note, you probably could hard-code column indices with @a.[6..17].sum / @a.[6..17].elems, and if it's always 12 columns you could do @a.[6..17].sum / 12 instead. Finally, use @a.[6..17].map(*.chars > 0).sum as the denominator if you need to adjust for missing values.

https://raku.org

0

We can do this using the GNU dc RPN calculator. But before that we need these 3 transformations, which is done using Perl:

  • enclose non numeric fields in []
  • negative sign of a numeric field changed to _
  • change commas to spaces.
perl -F, -lane 'my $i;
  print join " ",  map {
    $i++ < ($.>1?3:@F) ? qq([$_]) : s/^-/_/r;
  } @F;
' file |
dc -e "
#### macros definitions
[q]sq [44an]sz # OFS set to  comma
[+z7<a]sa # aum last 12 entries
[SMz0<b]sb # shunt remaining to M
[LMnz6>zzz6>c]sc # print LIFO M
[?z0=q
  lax #sum last 12 cols
  lbx #store all in stack M
  lcx #print
  LM12.0/p #orint average
cz0=?]s?

###> header processing
?[
  [4k]s0 [XrX=0z6<1]s1
  l1x lbx lcx [MEAN]pc
]x

##> non header lines
l?x
"

OBSERVATORY,Abbreviations,COUNTRY,ALTITUDE(m),LONGITUDE(deg),LATITUDE (deg),MEAN
Beverly-Begg Observatory Dunedin,,New Zealand,140,170.49,-45.8644,89.0558
Aorangi Iti Observatory Lake Tekapo,,New Zealand,718,170.473,-44.0082,72.1991
Mount John Observatory Lake Tekapo,,New Zealand,945,170.465,-43.9874,73.1558

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.