UNIX - command to split the file into multiple files with all the lines for every 3 unique values in a column

Question

Consider the below input file. Input file:

C1|C2|C3|C4|C5|C6
0|1|2|3|0-1-2-3|4
0|2|2|4|0-1-2-3|5
0|1|2|3|1-3-2-4|4
0|1|2|3|1-1-3-4|4
0|1|2|3|1-1-3-4|5
0|1|2|3|4-5-2-6|4
0|1|2|3|4-5-2-6|6
0|4|5|3|7-4-2-4|4
0|1|2|3|7-4-2-5|4
0|1|2|3|7-4-2-5|5
0|1|2|3|7-4-2-5|6
0|1|2|3|7-5-2-6|5

Based on field 5, for example 0-1-2-3 in the first record, output split files are expected as below Split file1:

C1|C2|C3|C4|C5|C6
0|1|2|3|0-1-2-3|4
0|2|2|4|0-1-2-3|5
0|1|2|3|1-3-2-4|4
0|1|2|3|1-1-3-4|4
0|1|2|3|1-1-3-4|5

Split file 2:

C1|C2|C3|C4|C5|C6
0|1|2|3|4-5-2-6|4
0|1|2|3|4-5-2-6|6
0|4|5|3|7-4-2-4|4
0|1|2|3|7-4-2-5|4
0|1|2|3|7-4-2-5|5
0|1|2|3|7-4-2-5|6

Split file 3:

C1|C2|C3|C4|C5|C6
0|1|2|3|7-5-2-6|5

Based on column 5, for every 3 unique values in the column the file should be split and should even have all the lines where the value of column 5 is repeated. Can someone help me with this?

Answer 1

A job for awk. Something like:

awk -F'|' -v fileformat="/abc/output/file_%04d.txt" -v max=3 -v field=5 '
  NR == 1 {header = $0; next}
  ! ($field in seen) {
    seen[$field]
    if (++n % max == 1) {
      close(out)
      out = sprintf(fileformat, ++f)
      print header > out
    }
  }
  {print > out}' < /abc/input/a.txt

Answer 2

awk solution:

awk -F'|' 'NR==1{ h=$0; f=0; c=1 }NR>1{ 
              a[$5]; if(length(a)>3) { f=0;c++; delete a }; 
              fn="file"c".txt"; if(!f) print h > fn; print > fn; f++ 
           }' file

• h=$0 - header line

• f=0 - flag pointing to the moment of printing header line into the next new file

• c=1 - filename suffix (incremented for each new file)

• a[$5] - indexing array a with unique values of the 5th field

• if(length(a)>3) { f=0;c++; delete a } - initiating next new filename (c++) when succeeding 3 unique values. (delete a - delete ll items from array a)

• fn="file"c".txt" - current filename

---

Viewing results:

for f in file[0-9]*.txt; do echo "$f"; cat "$f"; echo; done

The output:

file1.txt
C1|C2|C3|C4|C5|C6
0|1|2|3|0-1-2-3|4
0|2|2|4|0-1-2-3|5
0|1|2|3|1-3-2-4|4
0|1|2|3|1-1-3-4|4
0|1|2|3|1-1-3-4|5

file2.txt
C1|C2|C3|C4|C5|C6
0|1|2|3|4-5-2-6|4
0|1|2|3|4-5-2-6|6
0|4|5|3|7-4-2-4|4
0|1|2|3|7-4-2-5|4
0|1|2|3|7-4-2-5|5
0|1|2|3|7-4-2-5|6

file3.txt
C1|C2|C3|C4|C5|C6
0|1|2|3|7-5-2-6|5

Answer 3

#!/bin/bash

awk -F '|' '
    function print_to_file(str) {
        print str > "file_"file_num;    
    }
    NR == 1 {header = $0;}
    NR > 1 {
        if(!buf) {
            file_num = 1;
            print_to_file(header);
        }
        if(buf != $5) {
            buf = $5;
            cnt++;
        }
        if(cnt > 3) {
            cnt = 1;
            file_num++;
            print_to_file(header);
        }
        print_to_file($0);
    }
' input.txt

Output:

$ tail -n +1 -- file_* # display content of all files with their filenames

==> file_1 <==
C1|C2|C3|C4|C5|C6
0|1|2|3|0-1-2-3|4
0|2|2|4|0-1-2-3|5
0|1|2|3|1-3-2-4|4
0|1|2|3|1-1-3-4|4
0|1|2|3|1-1-3-4|5

==> file_2 <==
C1|C2|C3|C4|C5|C6
0|1|2|3|4-5-2-6|4
0|1|2|3|4-5-2-6|6
0|4|5|3|7-4-2-4|4
0|1|2|3|7-4-2-5|4
0|1|2|3|7-4-2-5|5
0|1|2|3|7-4-2-5|6

==> file_3 <==
C1|C2|C3|C4|C5|C6
0|1|2|3|7-5-2-6|5