reset row number count in awk

Question

I have a file like this

file.txt

0   1   a
1   1   b
2   1   d
3   1   d
4   2   g
5   2   a
6   3   b
7   3   d
8   4   d
9   5   g
10   5   g
.
.
.

I want reset row number count to 0 in first column $1 whenever value of field in second column $2 changes, using awk or bash script.

result

0   1   a
1   1   b
2   1   d
3   1   d
0   2   g
1   2   a
0   3   b
1   3   d
0   4   d
0   5   g
1   5   g
.
.
. 

Answer 1

As long as you don't mind a bit of excess memory usage, and the second column is sorted, I think this is the most fun:

awk '{$1=a[$2]+++0;print}' input.txt

Answer 2

This awk one-liner seems to work for me:

[ghoti@pc ~]$ awk 'prev!=$2{first=0;prev=$2} {$1=first;first++} 1' input.txt
0 1 a
1 1 b
2 1 d
3 1 d
0 2 g
1 2 a
0 3 b
1 3 d
0 4 d
0 5 g
1 5 g

Let's break apart the script and see what it does.

• prev!=$2 {first=0;prev=$2} -- This is what resets your counter. Since the initial state of prev is empty, we reset on the first line of input, which is fine.
• {$1=first;first++} -- For every line, set the first field, then increment variable we're using to set the first field.
• 1 -- this is awk short-hand for "print the line". It's really a condition that always evaluates to "true", and when a condition/statement pair is missing a statement, the statement defaults to "print".

Pretty basic, really.

The one catch of course is that when you change the value of any field in awk, it rewrites the line using whatever field separators are set, which by default is just a space. If you want to adjust this, you can set your OFS variable:

[ghoti@pc ~]$ awk -vOFS="   " 'p!=$2{f=0;p=$2}{$1=f;f++}1' input.txt | head -2
0   1   a
1   1   b

Salt to taste.