Reducing the complexity of the data in text file

Question

I have this file:

  1  2
  2  7
  3  4
  4  7
  5  3
  6  7
  7  1
  8  2
  9  4

And my desired output is

 1 13
 2 17
 3 7

In my input i have 9 rows and i want to reduce it to three rows while preserving the total amount of 2nd column. For example 1 in 1st column represnts 1,2,3 and 13 in 2nd column, 1st row represent addition (2+4+7) and so on.. Any idea? may be by using awk/ perl or any other linux tool.

Answer 1

Here is an awk solution:

awk '{ s+=$2; if (!(NR%3)) { k++; print k,s; s=0 } };
     END { if (NR%3) { k++; print k, s } }' file.txt

It ignores the first column, preferring to generate it in k as an output row number. The second column is summed in s, and every three lines ((NR % 3) == 0) it's output and the accumulator reset. Finally, if we have any left-over lines we output the remaining sum.

Output from the example file

1 13
2 17
3 7

---

Just for completeness, here is a DRY version that uses a function to handle the repeated code from the modulo-3 and END blocks:

awk 'function outsum() { print ++k,s; s=0 };
     { s+=$2; if (!(NR%3)) { outsum() } };
     END { if (NR%3) { outsum() } }' file.txt

Answer 2

Perl solution:

perl -lane '
    $s += $F[1];
    print(join "\t", ++$l, $s), $s = 0
        if 0 == $. % 3 || eof;
' input-file

• -n reads the input line by line
• -a splits each line on whitespace into the @F array
• $s is used as a variable keeping the sum
• $. is a special variable that contains the input line number
• $l is the output line number

Answer 3

This should maybe go to codegolf.SE. Here is a one liner without perl, awk or sed:

paste <(for i in $(seq 1 0.33333333334 $(A=$(wc -l input.dat | cut -d ' ' -f 1); echo $A/3+1 | bc)); do echo $i/1 | bc; done) <(tr -s ' ' < input.dat | cut -d ' ' -f 3) | datamash -g 1 sum 2

---

In detail

The left side

for i in $(seq 1 0.33333333334 $(A=$(wc -l input.dat | cut -d ' ' -f 1); echo $A/3+1 | bc)); do echo $i/1 | bc; done

Produces a list like (it accounts for the actual number of lines in the input file):

1
1
1
2
2
2
3
3
3

And the right side

tr -s ' ' < input.dat | cut -d ' ' -f 3

Chops first column of the input file leaving:

2
7
4
7
3
7
1
2
4

paste combines them back and datamash does the group by.

Answer 4

Yet another oneliner with sed and dc:

sed 's/ *[^ ]*//' < input.dat | tr "\n" " " | sed 's/\([^ ]\+\) *\([^ ]\+\) *\([^ ]\+\)/\1 \2 \3++p/g' | dc | cat -n

Explanation:

sed 's/ *[^ ]*//' < input.dat

Kills the first column; a bit more robust than cut against repeated spaces

tr "\n" " "

Transforms all the newlines in spaces, thus putting everything on one line

sed 's/\([^ ]\+\) *\([^ ]\+\) *\([^ ]\+\)/\0++p/g'

Replaces three space-delimited tokens with themselves followed by ++p.

dc

Feeds the output in dc, the RPN calculator; each number gets pushed on the stack, and every three you have +, + and p commands (+ means sum the two numbers on the top of the stack, p prints the stack). This gives us the second column of output.

cat -n

Rewrites everything adding the line numbers.

Answer 5

Here's a version that uses only shell commands. I've split it out across several lines but there's no reason why you couldn't roll it all together as a one-liner (that's how it started):

(
    s=0 k=1 n=0
    while read x v
    do
        s=$((s+v)) n=$((n+1))
        if [[ n -eq 3 ]]
        then
            echo $k $s
            k=$((k+1)) n=0 s=0
        fi
    done
    [[ s -gt 0 ]] && echo $k $s
) <file.txt

---

( s=0 k=1 n=0; while read x v; do s=$((s+v)) n=$((n+1)); if [[ n -eq 3 ]]; then echo $k $s; k=$((k+1)) n=0 s=0; fi; done; [[ s -gt 0 ]] && echo $k $s ) <file.txt