How to convert a 3 column csv file into a table (or matrix)

Question

I have an CSV input file format like this, with a nucleotide sequence in field 1, text in field 2, and an integer in field 4:

ATGC,CD3,56
ATGC,CD4,67
ATGC,IgD,126
ATGC,IgM,127
AGTC,CD3,67
AGTC,CD4,78
AGTC,IgD,102
AGTC,IgM,89
TCGA,CD3,334
TCGA,CD4,123
TCGA,IgD,456
TCGA,IgM,80
CGTA,CD3,54
CGTA,CD4,32
CGTA,IgD,82
CGTA,IgM,117

I opened this CSV file using Numbers in Mac it's display as 3 columns format, however, I want to convert it to the table(or Matrix) format (also a CSV file), making the first column, the nucleotide sequences, into a header, and want the result to also look like a table (or Matrix):

     ATGC  AGTC  TCGA  CGTA
CD3  56    67    334   54
CD4  67    78    123   32
IgD  126   102   456   82
IgM  127   89    80    117

Below is a section from my real input CSV file (sample input.txt):

AGAATAGTCTGATTCT,-,,38
AGAATAGTCTGATTCT,AnnexinV,,51
AGAATAGTCTGATTCT,CD127,,39
AGAATAGTCTGATTCT,CD138,,3
AGAATAGTCTGATTCT,CD14,,2
AGAATAGTCTGATTCT,CD16,,4
AGAATAGTCTGATTCT,CD19,,10
AGAATAGTCTGATTCT,CD20,,6
AGAATAGTCTGATTCT,CD24,,21
AGAATAGTCTGATTCT,CD25,,4
AGAATAGTCTGATTCT,CD27,,87
AGAATAGTCTGATTCT,CD3,,235
AGAATAGTCTGATTCT,CD34,,5
AGAATAGTCTGATTCT,CD38,,18
AGAATAGTCTGATTCT,CD4,,412
AGAATAGTCTGATTCT,CD43,,99
AGAATAGTCTGATTCT,CD5,,430
AGAATAGTCTGATTCT,CD56,,3
AGAATAGTCTGATTCT,CD8,,7
AGAATAGTCTGATTCT,IgD,,4
AGAATAGTCTGATTCT,IgM,,2
TGTGGTAGTTCGTCTC,-,,9
TGTGGTAGTTCGTCTC,AnnexinV,,42
TGTGGTAGTTCGTCTC,CD127,,6
TGTGGTAGTTCGTCTC,CD138,,4
TGTGGTAGTTCGTCTC,CD16,,40
TGTGGTAGTTCGTCTC,CD19,,7
TGTGGTAGTTCGTCTC,CD20,,2
TGTGGTAGTTCGTCTC,CD24,,24
TGTGGTAGTTCGTCTC,CD25,,2

How can I do this using Linux text formatting commands?

Answer 1

Using awk:

{
    ks[$1 $2] = $3; # save the third column using the first and second as index
    k1[$1]++;       # save the first column
    k2[$2]++;       # save the second column
}
END {                                # After processing input
    for (j in k1) {                  # loop over the first column 
        printf "\t%s", j;            # and print column headers
    };
    print "";                        # newline
    for (i in k2) {                  # loop over the second 
        printf "%s", i;              # print it as row header
        for (j in k1) {              # loop over first again
            printf "\t%s", ks[j i];  # and print values
        }
        print "";                    # newline
    }
}

Output:

~ awk -F, -f foo.awk foo
        AGTC    ATGC    CGTA    TCGA
CD4     78      67      32      123
IgD     102     126     82      456
IgM     89      127     117     80
CD3     67      56      54      334

Answer 2

Using Miller (https://github.com/johnkerl/miller) with

mlr --n2p --ifs "," label key,property,emptyfield,value \
then reshape -s key,value \
then unsparsify \
then cut -x -f emptyfield input.csv

You will have

property AGAATAGTCTGATTCT TGTGGTAGTTCGTCTC
-        38               9
AnnexinV 51               42
CD127    39               6
CD138    3                4
CD14     2                -
CD16     4                40
CD19     10               7
CD20     6                2
CD24     21               24
CD25     4                2
CD27     87               -
CD3      235              -
CD34     5                -
CD38     18               -
CD4      412              -
CD43     99               -
CD5      430              -
CD56     3                -
CD8      7                -
IgD      4                -
IgM      2                -

Answer 3

An awk script solving your task:

script.awk

{
        arr[$1,$2] = $4; # read array values
        c1[$1] = 1;      # read row headers
        c2[$2] = 1;      # read row indexes
}
END {                # start fancy printing
        printf ("%-18s","");     # first line empty tab
        for (i1 in c1) printf("%-18s",i1); printf "\n";  # print headers
                     # print rows
        for (i2 in c2) {
                printf("%-18s",i2);  # print row index
                for (i1 in c1) {
                        printf("%-18d", arr[i1,i2]); # print row's values
                }
                printf "\n";          # terminat current row with newline
        }
}

runing:

awk -F "," -f script.awk input.txt

output:

                  TGTGGTAGTTCGTCTC  AGAATAGTCTGATTCT
CD4               0                 412
CD24              24                21
CD5               0                 430
CD43              0                 99
CD34              0                 5
CD25              2                 4
CD16              40                4
IgD               0                 4
CD27              0                 87
CD8               0                 7
CD19              7                 10
CD56              0                 3
CD38              0                 18
AnnexinV          42                51
-                 9                 38
CD127             6                 39
CD20              2                 6
CD138             4                 3
IgM               0                 2
CD3               0                 235
CD14              0                 2