reformat data for PCA with AWK

I'd use perl instead:

$ perl -lane ' $f{$F[0]}->{$F[1]} = $F[2]; $d{$F[1]} = 0; END { $, = "\t"; @d = sort keys %d; print "", @d; for $f (sort keys %f) { print $f, map {$f{$f}->{$_} // 0} @d; } }' -- file1 file2 | mlr --t2p --barred cat +----------------+-------+-------+-------+-------+-------+-------+-------+ | | D1000 | D1001 | D1002 | D1003 | D1012 | D1013 | D1018 | +----------------+-------+-------+-------+-------+-------+-------+-------+ | HG00097#1_chr1 | 15 | 196 | 48 | 55 | 38 | 0 | 2 | | HG00099#2_chr2 | 6 | 36 | 5 | 22 | 1 | 1 | 0 | +----------------+-------+-------+-------+-------+-------+-------+-------+ 

Or an English version:

perl -MEnglish -lane ' ($sample, $value, $count) = @F; $count{$sample}->{$value} = $count; $all_known_values{$value} = undef; END { $OUTPUT_FIELD_SEPARATOR = "\N{CHARACTER TABULATION}"; @all_known_values = sort keys %all_known_values; print "Sample", @all_known_values; foreach $sample (sort keys %count) { print $sample, map {$count{$sample}->{$ARG} // 0} @all_known_values; } }' -- file1 file2 

perl generates tab-separated-values output. mlr is just to pretty print it. Change "\t" to "," and t2p to c2p for csv instead.

file1 and file2 here are two files with the examples you gave. You'd give the full list instead.

Using Miller (mlr) to read the input as whitespace-delimited fields, reshape it by using the values found in the 2nd field as column labels, with the corresponding values taken from the 3rd field:

$ cat file* | mlr --n2p --barred reshape -s 2,3 then unsparsify --fill-with 0 then label id +----------------+-------+-------+-------+-------+-------+-------+-------+ | id | D1000 | D1001 | D1002 | D1003 | D1012 | D1018 | D1013 | +----------------+-------+-------+-------+-------+-------+-------+-------+ | HG00097#1_chr1 | 15 | 196 | 48 | 55 | 38 | 2 | 0 | | HG00099#2_chr2 | 6 | 36 | 5 | 22 | 1 | 0 | 1 | +----------------+-------+-------+-------+-------+-------+-------+-------+ 

Or, for CSV output:

$ cat file* | mlr --n2c reshape -s 2,3 then unsparsify --fill-with 0 then label id id,D1000,D1001,D1002,D1003,D1012,D1018,D1013 HG00097#1_chr1,15,196,48,55,38,2,0 HG00099#2_chr2,6,36,5,22,1,0,1 

Since CSV (and the pretty-print format used in the first example) requires that every record has the same number of fields, we use the unsparsify operation to add zeros to the fields that don't have values. We also use the label operation to set the first field's label to id (its label would otherwise default to 1).

If you need to sort the fields lexicographically, then do so with sort-within-records before relabeling the first field (doing it after would make the first field be sorted to the end, in this case):

mlr --n2c reshape -s 2,3 then unsparsify --fill-with 0 then sort-within-records then label id 

Testing on 4.5 million lines of input, this runs in well under a minute (about 12s on my system, using about 2 GB of RAM).

if R fails, awk is unlikely to fare any better. Your problem is that you need to pivot multiple large matrices.

the massive size of this dataset (~4.5M rows).

That does not sound massive to me; that's just 4.5 million integers and their position in a table? One could argue that's actually not very much data, and could fit on a good computer screen as pixels. Your computer computes dozens of screens per second, so that's not too much for it :)

Also, applying regexes through 4.5 million lines of text is not taking much time on a modern computer. So, I think you'll be fine!

This becomes very much data, however, if you're not handling it compactly.

I'd do this: as preliminary step (because 4.5 million rows are not that much, we can stand going through all the files in a preliminary processing step)

• Determine the number of columns you need. It seems to me that they are all Dxxxx with xxxx being an integer smaller than 10,000? Either you can directly infer the unique values, or you need to count them.
• Determine the number of rows you need. That probably means putting every occurring value of your first column in a (reverse indexable) set data structure, and checking the size of that set in the end.

Then,

• allocate a matrix with that number of rows and columns (and have it be zerofilled, which is usually almost free)
• for each row in your input, look up the row index in your row set, and derive the column index from the D-value. Put the number from your last column at that position in your table.

This matrix is what you seem to do PCA on. Go wild!

I'd implement that in a few lines of C++ or Python, but I'm taking bets R also has functionality to read files row-wise, split them on a delimiter, and store the first value in a set-like data structure, and so on.

Using any POSIX awk plus sort:

$ cat tst.sh #!/usr/bin/env bash awk ' BEGIN { OFS="\t" } NR == FNR { haps[++numHaps] = $2 next } FNR == 1 { prt() sample = $1 } { cnts[$2] = $3 } END { prt() } function prt( hapNr) { if ( doneHdr++ ) { printf "%s%s", sample, OFS for ( hapNr=1; hapNr<=numHaps; hapNr++ ) { printf "%d%s", cnts[haps[hapNr]], (hapNr < numHaps ? OFS : ORS) } } else { printf "%s%s", "Sample", OFS for ( hapNr=1; hapNr<=numHaps; hapNr++ ) { printf "%s%s", haps[hapNr], (hapNr < numHaps ? OFS : ORS) } } delete cnts } ' <(sort -u -k2,2 "$@") "$@" 

$ ./tst.sh file1 file2 Sample D1000 D1001 D1002 D1003 D1012 D1013 D1018 HG00097#1_chr1 15 196 48 55 38 0 2 HG00099#2_chr2 6 36 5 22 1 1 0 

That reads all the unique haplotypes plus the haplotypes+counts from just 1 file at a time into memory in awk (as opposed to reading all of the values from all of the files into memory at once) and uses sort just to sort the haplotypes uniquely so it should have no problem with the amount of input you describe.

You can pipe the above tab-separated output to whatever you like to format it differently, e.g.:

$ ./tst.sh file1 file2 | column -t -s$'\t' -o' | ' -R0 Sample | D1000 | D1001 | D1002 | D1003 | D1012 | D1013 | D1018 HG00097#1_chr1 | 15 | 196 | 48 | 55 | 38 | 0 | 2 HG00099#2_chr2 | 6 | 36 | 5 | 22 | 1 | 1 | 0 

or you could add some formatting code to the awk script if you prefer.