CODEWITHSUNDEEP
bashsedawk
Andy K
Andy K

Reputation: 5044

increment values in column within file with bash, sed and awk

Please find below an excerpt from one of my file.

1991;1;-7;-3;-9;-4;-7
1991;1;-7;-3;-9;-4;-7
1991;1;-7;-3;-9;-4;-7
1991;2;-14;-11;-14;-4;-14
1991;2;-14;-11;-14;-4;-14
1991;2;-14;-11;-14;-4;-14
1991;3;-7;-3;-15;5;-7
1991;3;-7;-3;-15;5;-7
1991;3;-7;-3;-15;5;-7
1991;4;-15;-9;-21;1;-16
1991;4;-15;-9;-21;1;-16
1991;4;-15;-9;-21;1;-16
1992;1;-12;-6;-19;-2;-12
1992;1;-12;-6;-19;-2;-12
1992;1;-12;-6;-19;-2;-12
1992;2;-16;-7;-22;-12;-15
1992;2;-16;-7;-22;-12;-15
1992;2;-16;-7;-22;-12;-15
1992;3;-22;-15;-25;-16;-24
1992;3;-22;-15;-25;-16;-24

I'm trying through sed or/and awk to add + 1 on the second column for the second row for the second row as long as the year in the first column remains the same.

The results would be the following:

1991;1;-7;-3;-9;-4;-7
1991;2;-7;-3;-9;-4;-7
1991;3;-7;-3;-9;-4;-7
1991;4;-14;-11;-14;-4;-14
1991;5;-14;-11;-14;-4;-14
1991;6;-14;-11;-14;-4;-14
1991;7;-7;-3;-15;5;-7
1991;8;-7;-3;-15;5;-7
1991;9;-7;-3;-15;5;-7
1991;10;-15;-9;-21;1;-16
1991;11;-15;-9;-21;1;-16
1991;12;-15;-9;-21;1;-16
1992;1;-12;-6;-19;-2;-12
1992;2;-12;-6;-19;-2;-12
1992;3;-12;-6;-19;-2;-12
1992;4;-16;-7;-22;-12;-15
1992;5;-16;-7;-22;-12;-15
1992;6;-16;-7;-22;-12;-15
1992;7;-22;-15;-25;-16;-24
1992;8;-22;-15;-25;-16;-24

I've seen countless examples on stackflow but nothing that can lead me close to a solution.

I welcome any suggestions.

Best,

Upvotes: 0

Views: 1995

Answers (4)

Charles Duffy
Charles Duffy

Reputation: 295403

This could be done more tersely with awk, but pure bash works fine:

last_year=
counter_val=
while IFS=';' read -r year old_counter rest; do
  if [[ $year = "$last_year" ]]; then
    (( ++counter_val ))
  else
    counter_val=1
    last_year=$year
  fi
  printf -v result '%s;' "$year" "$counter_val" "$rest"
  printf '%s\n' "${result%;}"
done <input.txt >output.txt

Upvotes: 4

suspectus
suspectus

Reputation: 17268

Using awk, set the FS (field separator) and OFS (output field separator) to ';' and for each new year record set the val counter to the start column 2 value. Increment val for each line with that year.

awk -F';' 'BEGIN{OFS=";";y=0} 
 { if (y!=$1) 
      {y=$1;val=$2;print} 
   else 
      {val++;print $1,val,$3,$4,$5,$6,$7}}' data_file

Upvotes: 1

William Pursell
William Pursell

Reputation: 212248

If you always want the 2nd column to be 1 for the line in which the year first appears in column 1, then: 

awk -F\; '$1!=l{c=0}{$2=++c}{l=$1}1' OFS=\; input

If you want to maintain whatever was in column 2:

awk -F\; '$1!=l{c=$2}{$2=c++}{l=$1}1' OFS=\; input

Upvotes: 7

David W.
David W.

Reputation: 107040

You simply want to increment your second column, and not add one to it? Do you want the second column to go from one onward no matter what the second column is?

awk -F\; '{
    if ( NR == 1 ) {
        year = $0
    }
    if ( year == $0 ) {
        for (count = 1; count < NF; count++) {
            if ( count == 2) {
                printf NR ";";
            }
            else {
                printf $count ";";
            }
        }
        print "";
    }
    else {
        print 
    }
}' test.txt

Awk is a natural program to use because it operates in assuming a loop. Plus, it's math is more natural than plain shell.

The NR means Number of Records and NF means Number of fields. A field is separated by my -F\; parameter, and the record is the line number in my file. The rest of the program is pretty obvious.

Upvotes: 1

Related Questions