Creating text files with comma separated list of values from one column based on shared value in second column from the commandline

Question

I am hoping to create a series of text files each containing a comma separated list of values from column 1 of an input file based on their shared value in column 2 of the input file.

Input file format:

CB-03-01-01     CB-03.1
CB-03-01-02     CB-03.1
CB-03-01-03     CB-03.1
CB-03-02-01     CB-03.2
CB-03-02-02     CB-03.2
CB-03-02-03     CB-03.2
CB-08-01        CB-08
CB-08-02        CB-08
CB-08-03        CB-08

Desired outputs: CB-03.1.txt

CB-03-01-01,CB-03-01-02,CB-03-01-03

CB-03.2.txt

CB-03-02-01,CB-03-02-02,CB-03-02-03

CB-08.txt

CB-08-01,CB-08-02,CB-08-03

Thanks for any help you can provide!

Answer 1

The command you can use is:

awk '{a[$2]=a[$2]","$1} END {for(i in a) print substr(a[i],2) >i".txt"}' input_file

a is associative array and substr function remove the leading comma from first assignment of the array element.

As per comment you may need to replace >i".txt" with >(i".txt"); close(i".txt") with some versions of awk and to close open file handlers

Answer 2

Using Raku (formerly known as Perl_6)

~$ raku -e 'my %h; for lines() { %h.push: .[1] => .[0] given .split(/ \s+ /) };
            for %h.sort() { 
                spurt( ( .key ~ ".txt" ).IO, $_.value.join(",") ~ "\n", createonly => True);
            };'  file.txt

Above is a solution coded in Raku, a member of the Perl-family of programmming languages. An advantage of Raku is high-level support for Unicode.

Basically the problem as stated is a key/value problem, wherein all key/value pairs are stored in a %h hash. Every time you see a new second column (text) element you create a key, adding the corresponding first column (text) element as value. Since keys in a hash are maintained unique, subsequent values for the same key get pushed (or appended) to the same key.

Instead of taking input off the command line, you can write the code below which has the appropriate path hardcoded inside:

~$ raku -e 'my $in = "/path/to/file.txt".IO; my %h;
            for $in.lines() { %h.push: .[1] => .[0] given .split(/ \s+ /) };
            for %h.sort() { 
                my $out = IO::Spec::Unix.catpath($, $in.dirname, .key) ~ ".txt"; 
                spurt( $out,  $_.value.join(",") ~ "\n", createonly => True) };'

Other options include saving the code immediately above (inside the single quotes) as a script, and calling it at the command line with raku script.raku. Note above, the hardcoded script can be ported to other OSes, simply by changing the IO::Spec call as appropriate (to IO::Spec::Win32, IO::Spec::Cygwin, etc.).

https://docs.raku.org/language/hashmap#Mutable_hashes_and_immutable_maps
https://docs.raku.org/routine/split
https://raku.org

Answer 3

Using any awk (untested):

awk '
    $2 != prev {
        if ( out != "" ) {
            print "" > out
            close(out)
        }
        out = $2 ".txt"
        sep = ""
        prev = $2
    }
    {
        printf "%s%s", sep, $1 > out
        sep = ","
    }
    END {
        if ( out != "" ) {
            print "" > out
        }
    }
' file

The above only has 1 output file open at a time and so can't run into a "too many open files" error (or slowdown) and only stores 1 line at a time in memory so it'll continue to work no matter how large your input file is.

It does assume your input is grouped by the $2 values as in the example you provided but if that's not the case in your real data then just sort it by the 2nd field first, e.g. sort -k2,2 file | awk 'script'.