0

I have a file with the following structure:

Numero_di_conferimento  Numero_Campione Identificazione Valore Data_del_prelievo Data Analisi
358172 1 80_T1_Glu_Gln 0,14 17/10/2019 Granulociti_basofili_(K/µL)
358172 1 80_T1_Glu_Gln 0,23 17/10/2019 Granulociti_eosinofili_(K/µL)
358172 1 80_T1_Glu_Gln 0,42 17/10/2019 Monociti_(K/µL)
358172 1 80_T1_Glu_Gln 0,60 17/10/2019 Granulociti_basofili_%_(%)
358172 1 80_T1_Glu_Gln 2,02 17/10/2019 Granulociti_eosinofili_%_(%)
358172 1 80_T1_Glu_Gln 2,21 17/10/2019 Linfociti_(K/µL)
358172 1 80_T1_Glu_Gln 1,67 17/10/2019 Granulociti_neutrofili_(K/µL)
358172 1 80_T1_Glu_Gln 2,96 17/10/2019 Monociti_%_(%)
358172 1 80_T1_Glu_Gln 2,4 17/10/2019 Leucociti_(WBC)_(K/µL)
358172 1 80_T1_Glu_Gln 5,74 17/10/2019 Eritrociti_(RBC)_(M/µL)
358172 1 80_T1_Glu_Gln 10,2 17/10/2019 Emoglobina_(g/dL)
358172 1 80_T1_Glu_Gln 12,6 17/10/2019 Emoglobina_globulare_media_(MCH)_(pg)
358172 1 80_T1_Glu_Gln 40,2 17/10/2019 Ampiezza_distribuzione_eritrocitaria_(RDW)_(%)
358172 1 80_T1_Glu_Gln 40,9 17/10/2019 Concentrazione_media_emoglobina_globulare_(MCHC)_(g/dL)
358172 1 80_T1_Glu_Gln 15,1 17/10/2019 Linfociti_%_(%)
358172 1 80_T1_Glu_Gln 32,2 17/10/2019 Ematocrito_(HCT)_(%)
358172 1 80_T1_Glu_Gln 63,7 17/10/2019 Volume_globulare_medio_(MCV)_(fl)
358172 1 80_T1_Glu_Gln 27,3 17/10/2019 Granulociti_neutrofili_%_(%)
358172 1 80_T1_Glu_Gln 453 17/10/2019 Piastrine_(PLT)_(K/µL)
358172 2 97_T1_Glu_Gln 2,16 17/10/2019 Granulociti_basofili_(K/µL)
358172 2 97_T1_Glu_Gln 3,47 17/10/2019 Granulociti_eosinofili_(K/µL)
358172 2 97_T1_Glu_Gln 2,79 17/10/2019 Monociti_(K/µL)
358172 2 97_T1_Glu_Gln 1,1 17/10/2019 Granulociti_basofili_%_(%)
358172 2 97_T1_Glu_Gln 4 17/10/2019 Granulociti_eosinofili_%_(%)
358172 2 97_T1_Glu_Gln 5,63 17/10/2019 Granulociti_neutrofili_(K/µL)
...

Basically, the content of the last column repeats itself every 19 rows. In those 19 rows, the columns are always the same except for $4. The whole file is a couple thousand lines long.

I want to transform this into this:

Numero_di_conferimento  Numero_Campione Identificazione Data_del_prelievo    Ampiezza_distribuzione_eritrocitaria_(RDW)_(%) Concentrazione_media_emoglobina_globulare_(MCHC)_(g/dL) Ematocrito_(HCT)_(%)    Emoglobina_(g/dL)   Emoglobina_globulare_media_(MCH)_(pg)    Eritrociti_(RBC)   Granulociti_basofili_%_(%)  Granulociti_basofili_(K/µL) Granulociti_eosinofili_%_(%)    Granulociti_eosinofili_(K/µL)   Granulociti_neutrofili_%_(%)    Granulociti_neutrofili_(K/µL)   Leucociti_(WBC)_(K/µL)  Linfociti_%_(%)  Linfociti_(K/µL)   Monociti_%_(%)  Monociti_(K/µL) Piastrine_(PLT)_(K/µL)  Volume_globulare_medio_(MCV)_(fl)
358172 1 86_T1_Glu_Gln 17/10/2019   40,2    40,9    32,2    11,2    12,6    6,74    0,6 0,14    2,08    0,23    57,3    3,67    6,4 35,1    2,24    4,96    0,42    453 63,7
358172 2 98_T1_Glu_Gln 17/10/2019   23,5    32,3    30,4    9,82    18,3    5,36    1   0,16    3   0,47    23  3,63    15,8    68  10,74   5   0,79    420 56,8

I did it by doing the following two commands:

awk -F"\t" '{print $1, $2, $3, $5}' Original_file | uniq > Intermediate_file

for i in `sed 's\/\-\g' Original_file | awk '{print $6}' | sort -u`; do sed 's\/\-\g' Original_file | grep -w "$i" | awk '{print $4}' > tmp${i}; done; paste Intermediate_file tmp* > File_endproduct # the sed 's\/\-\g' was because of the / giving problems when grepping

and copypasting the header by hand afterwards.

However, even if this home-made solution created the file I wanted, I feel that this is not the best way to go. How can I create the file I want in other, safer ways?

Improve this question

1 Answer 1

Reset to default
2

It's not awk, but I'm adding here because I think it could be useful for your goal.

Starting from

Numero_di_conferimento  Numero_Campione Identificazione Valore  Data_del_prelievo   Analisi
358172  1   80_T1_Glu_Gln   0,14    17/10/2019  Granulociti_basofili_(K/µL)
358172  1   80_T1_Glu_Gln   0,23    17/10/2019  Granulociti_eosinofili_(K/µL)
358172  1   80_T1_Glu_Gln   0,42    17/10/2019  Monociti_(K/µL)
358172  1   80_T1_Glu_Gln   0,60    17/10/2019  Granulociti_basofili_%_(%)
358172  1   80_T1_Glu_Gln   2,02    17/10/2019  Granulociti_eosinofili_%_(%)
358172  1   80_T1_Glu_Gln   2,21    17/10/2019  Linfociti_(K/µL)
358172  1   80_T1_Glu_Gln   1,67    17/10/2019  Granulociti_neutrofili_(K/µL)
358172  1   80_T1_Glu_Gln   2,96    17/10/2019  Monociti_%_(%)
358172  1   80_T1_Glu_Gln   2,4 17/10/2019  Leucociti_(WBC)_(K/µL)
358172  1   80_T1_Glu_Gln   5,74    17/10/2019  Eritrociti_(RBC)_(M/µL)
358172  1   80_T1_Glu_Gln   10,2    17/10/2019  Emoglobina_(g/dL)
358172  1   80_T1_Glu_Gln   12,6    17/10/2019  Emoglobina_globulare_media_(MCH)_(pg)
358172  1   80_T1_Glu_Gln   40,2    17/10/2019  Ampiezza_distribuzione_eritrocitaria_(RDW)_(%)
358172  1   80_T1_Glu_Gln   40,9    17/10/2019  Concentrazione_media_emoglobina_globulare_(MCHC)_(g/dL)
358172  1   80_T1_Glu_Gln   15,1    17/10/2019  Linfociti_%_(%)
358172  1   80_T1_Glu_Gln   32,2    17/10/2019  Ematocrito_(HCT)_(%)
358172  1   80_T1_Glu_Gln   63,7    17/10/2019  Volume_globulare_medio_(MCV)_(fl)
358172  1   80_T1_Glu_Gln   27,3    17/10/2019  Granulociti_neutrofili_%_(%)
358172  1   80_T1_Glu_Gln   453 17/10/2019  Piastrine_(PLT)_(K/µL)
358172  2   97_T1_Glu_Gln   2,16    17/10/2019  Granulociti_basofili_(K/µL)
358172  2   97_T1_Glu_Gln   3,47    17/10/2019  Granulociti_eosinofili_(K/µL)
358172  2   97_T1_Glu_Gln   2,79    17/10/2019  Monociti_(K/µL)
358172  2   97_T1_Glu_Gln   1,1 17/10/2019  Granulociti_basofili_%_(%)
358172  2   97_T1_Glu_Gln   4   17/10/2019  Granulociti_eosinofili_%_(%)
358172  2   97_T1_Glu_Gln   5,63    17/10/2019  Granulociti_neutrofili_(K/µL)

using Miller (https://github.com/johnkerl/miller) and running this long to wide command

mlr --tsv reshape -s Analisi,Valore then unsparsify input.tsv >output.tsv

you will have

+------------------------+-----------------+-----------------+-------------------+-----------------------------+-------------------------------+-----------------+----------------------------+------------------------------+------------------+-------------------------------+----------------+------------------------+-------------------------+-------------------+---------------------------------------+------------------------------------------------+---------------------------------------------------------+-----------------+----------------------+-----------------------------------+------------------------------+------------------------+
| Numero_di_conferimento | Numero_Campione | Identificazione | Data_del_prelievo | Granulociti_basofili_(K/µL) | Granulociti_eosinofili_(K/µL) | Monociti_(K/µL) | Granulociti_basofili_%_(%) | Granulociti_eosinofili_%_(%) | Linfociti_(K/µL) | Granulociti_neutrofili_(K/µL) | Monociti_%_(%) | Leucociti_(WBC)_(K/µL) | Eritrociti_(RBC)_(M/µL) | Emoglobina_(g/dL) | Emoglobina_globulare_media_(MCH)_(pg) | Ampiezza_distribuzione_eritrocitaria_(RDW)_(%) | Concentrazione_media_emoglobina_globulare_(MCHC)_(g/dL) | Linfociti_%_(%) | Ematocrito_(HCT)_(%) | Volume_globulare_medio_(MCV)_(fl) | Granulociti_neutrofili_%_(%) | Piastrine_(PLT)_(K/µL) |
+------------------------+-----------------+-----------------+-------------------+-----------------------------+-------------------------------+-----------------+----------------------------+------------------------------+------------------+-------------------------------+----------------+------------------------+-------------------------+-------------------+---------------------------------------+------------------------------------------------+---------------------------------------------------------+-----------------+----------------------+-----------------------------------+------------------------------+------------------------+
| 358172                 | 1               | 80_T1_Glu_Gln   | 17/10/2019        | 0,14                        | 0,23                          | 0,42            | 0,60                       | 2,02                         | 2,21             | 1,67                          | 2,96           | 2,4                    | 5,74                    | 10,2              | 12,6                                  | 40,2                                           | 40,9                                                    | 15,1            | 32,2                 | 63,7                              | 27,3                         | 453                    |
| 358172                 | 2               | 97_T1_Glu_Gln   | 17/10/2019        | 2,16                        | 3,47                          | 2,79            | 1,1                        | 4                            | -                | 5,63                          | -              | -                      | -                       | -                 | -                                     | -                                              | -                                                       | -               | -                    | -                                 | -                            | -                      |
+------------------------+-----------------+-----------------+-------------------+-----------------------------+-------------------------------+-----------------+----------------------------+------------------------------+------------------+-------------------------------+----------------+------------------------+-------------------------+-------------------+---------------------------------------+------------------------------------------------+---------------------------------------------------------+-----------------+----------------------+-----------------------------------+------------------------------+------------------------+
Improve this answer
5
  • 1
    Thank you, this looks like a very interesting tool. I'll study it a bit.
    – LinuxBlanket
    Feb 6, 2020 at 13:30
  • If it's a right tool for your question, please accept my answer as a right one
    – aborruso
    Feb 6, 2020 at 13:48
  • 1
    I'm interested in gathering more answers because I'd like to know how to do it without installing an additional tool, but if yours will be the only answer then I'll accept it.
    – LinuxBlanket
    Feb 6, 2020 at 14:04
  • 1
    I have accepted your answer as it solves my problem. This may be an important tool for my purposes, but I have a question: can Miller read stdin? I can't find it in documentation and I can't reproduce this command making it read from stdin.
    – LinuxBlanket
    Feb 7, 2020 at 11:36
  • @LinuxBlanket yes it reads stdin. Try yourself curl -L "https://gist.github.com/aborruso/bd1017a9cb9d765932b0bbb59617c069/raw/dc0c29d807a0b4d7686e92973c08c9a203917250/input.tsv" | mlr --tsv reshape -s key,value then unsparsify >./output.tsv
    – aborruso
    Feb 7, 2020 at 13:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .