☰

Как преобразовать CSV-файл из 3 столбцов в таблицу (или матрицу)

tag-icon • tag-icon linux command-line csv text-formatting file-format
Как преобразовать CSV-файл из 3 столбцов в таблицу (или матрицу)

У меня есть входной файл CSV следующего формата: с последовательностью нуклеотидов в поле 1, текстом в поле 2 и целым числом в поле 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

Я открыл этот CSV-файл с помощью Numbers на Mac, он отображается в формате из 3 столбцов, однако я хочу преобразовать его в формат таблицы (или матрицы) (тоже CSV-файл), сделав первый столбец, последовательности нуклеотидов, заголовком, и хочу, чтобы результат также выглядел как таблица (или матрица):

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

Ниже приведен фрагмент моего реального входного CSV-файла (пример 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

Как это сделать с помощью команд форматирования текста Linux?

решение1

Использование 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
    }
}

Выход:

~ 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

решение2

Используя Миллера (https://github.com/johnkerl/miller) с

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

У вас будет

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                -

решение3

Скрипт awk, решающий вашу задачу:

скрипт.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
        }
}

бег:

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

выход:

                  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

Связанный контент