3

I am trying to write an awk program on Linux, where if the string in the first column is different to the string in the first column of the previous line, it will print the entire previous line.

Another way to put this is whenever the first column is equal, print the entire last line of matched columns and discard the previous one that is equal.

I used this code:

awk 'BEGIN { FS=OFS=";" } $1==last{next} {last=$1} {print last}' test.txt

But I guess it is only printing the previous line's first column. How can I print the entire previous line?

My input file, test.txt, looks like this:

818522;"Joey";
817399;"john";
817399;"CCE";
817399;"smith";
817399;"Ron";
817400;
817400;
817400;
818000;"ODC";
890021;
890021;
890021;"rachel";
890021;"monica"

Desired Output:

818522;"Joey";
817399;"Ron";
817400;
818000;"ODC";
890021;"monica"
3
  • 1
    Save the last field and the last line...
    – RudiC
    Commented Oct 22, 2022 at 12:03
  • Can we assume the file will be sorted on the first column?
    – terdon
    Commented Oct 22, 2022 at 12:06
  • 1
    Please don't multi-post - stackoverflow.com/q/74164469/1745001
    – Ed Morton
    Commented Oct 23, 2022 at 3:33

5 Answers 5

4

Your description doesn't match your output, so I am a bit confused. Based on your description, the expected output should be:

818522;"Joey";
817399;"Ron";
817400;
818000;"ODC";

You wouldn't print any of the 890021 lines because they are last, so they will never have a different first field than the next line. If this is indeed what you want, you can do this:

$ awk -F';' '{ 
                if($1!=last && prevLine){ print prevLine } 
                { last=$1; prevLine=$0 }
             }' file
818522;"Joey";
817399;"Ron";
817400;
818000;"ODC";

If you also want to add an exception for the last bunch of lines, try something like this:

$ awk -F';' '{ 
              if($1!=last && prevLine){
                print prevLine; 
                lastPrinted=last
              } 
              {
                last=$1; 
                prevLine=$0
              }
             }
             END{ 
                if($1 != lastPrinted){ print }
             }' file 
818522;"Joey";
817399;"Ron";
817400;
818000;"ODC";
890021;"monica"

The idea is quite straightforward: if the first field is not the same as last and the prevLine variable is defined (so we don't print the first line), then we print the previous line (prevLine) and save the first field of the previous line (last) in the variable lastPrinted.

Then, for all lines, we set last to the 1st field and prevLine to the current line. Finally, when we reach the end of the file (END{}) we print the line if its 1st field is different from the 1st field we last printed for (lastPrinted).

0
3
$ awk -F';' '$1 != l1 && l1 != "" { print l0 };
             {l1 = $1; l0 = $0};
             END {if ($1 != $l1) {print}}' test.txt 
818522;"Joey";
817399;"Ron";
817400;
818000;"ODC";
890021;"monica"

The -F';' option sets awk's input field separator (FS) to a semi-colon. awk automatically splits each input line on FS and assigns the fields to $1, $2, $3, ....., $n.

Variables l1 and l0 are used to hold the first field ($1) and the entire line ($0).

For the most part, awk scripts are a series of PATTERN { ACTION } rules - if PATTERN evaluates as true, the ACTION is executed. PATTERN can be anything that evaluates as either true or false (regex matches, variable comparisons, calculations, etc). ACTION can be any awk code statement(s). These rules are repeated for each line of input. Note that either PATTERN or ACTION can be optional - if PATTERN is missing, it is treated as evaluating to true and the ACTION is always executed. If ACTION is missing, the default action is print (i.e. print the current input line). This is only a very brief and simplified summary, for more details read the awk documentation (e.g. man awk or, if you are using GNU awk, info awk. There's also the O'Reilly sed & awk book by Dale Dougherty & Arnold Robbins).

The first line of the awk script tests if the current line's $1 is not equal to both l1 and the empty string. If both tests are true, it prints the last input line, l0. On the first line of input, l1 will always be empty (because the 2nd line of the script hasn't executed yet, so no value has been assigned to it), so nothing will be printed (l0 would also be empty, anyway, so printing it would just output an empty line).

The second line of the awk script unconditionally sets l1 and l0 from the current input line.

The script repeats those two lines of code for each line of input.

When there is no more input, the main script loop ends and the END {...} block is executed. It prints the current input line (i.e. the last line of input) - currently only if $1 != l1 but on reflection (and some brief testing), it would probably work just as well without that test, just END {print}.

1
  • your answer works. thank you for fast reply. If it's not too much to ask, could you explain each step of the awk command? Commented Oct 22, 2022 at 15:12
2
$ mlr --csv --fs ';' -N --ragged unsparsify then tail -n 1 -g 1 file
818522;Joey;
817399;Ron;
817400;;
818000;ODC;
890021;monica;

This uses Miller (mlr) to read the data as header-less CSV with ; as the field separator. The input is allowed to have a varying number of fields per record.

We start by filling the non-existent fields in each record with empty values using the unsparsify operation, and then we use tail to get the last value of each group when grouping by the first field.

The output will be quoted if needed, or you may add --quote-all to quote all fields.

4
  • 1
    Miller's a good tool for this kind of thing, but what version are you running? With miller 6.4.0-1 on debian sid, I'm getting only an error message: mlr: mlr: CSV header/data length mismatch 3 != 2 at filename test.txt row 6.
    – cas
    Commented Oct 23, 2022 at 0:36
  • 1
    @cas Thanks for that! I'm using my system's default Miller 5.10.2, but forgot to test with later versions. Adding --ragged makes it work universally. I'll update the answer.
    – Kusalananda
    Commented Oct 23, 2022 at 5:32
  • The above command gives mlr: empty CSV key at file and mlr: option --ragged not recognised. Now I've changed it to mlr --nidx --fs ';' unsparsify then tail -n 1 -g 1 file in Miller 5.3.0. This works. Therefore upvoted. Commented Oct 31, 2022 at 19:00
  • @PrabhjotSingh Yes, there has definitely been a number of changes in Miller relating to how CSV data with variable number of fields per records are handled and accepted (or not accepted). Version 5.3.0 is quite old though and any current Linux should have a newer version by now (note that Ubuntu 18 has soon reached its end of life).
    – Kusalananda
    Commented Oct 31, 2022 at 19:32
1

GNU sed will cope.

sed -r 'N;/^([^;]+;).*\1/!P;D' file
1

Using datamash:

$ datamash -t ';' -g 1 last 2 <file

-t ; sets semicolon as field separator.

last 2 prints last value of the field 2.

-g 1 is short form of groupby 1.

This command is taken from datamash alternative one-liners. See 'Last value of each group'.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .