0

In my previous tag, i would keeping csv columns using a awk generic code. The answer was given here by @steeldriver.

In the following, I want to transpose some columns from a csv file to get a new column.

The description of data is:

  1. In the input, the first line contains the countries(several fields are empty) and the second line contains the headers to be processed.

  2. A transposing columns start from the last empty column based on the countries. Not that the number of columns per country vary. There can be 2 or 20 countries columns.

Example:

  • Input: file.csv
    ,,,fr,ch
    num,nom,date reg,match flag_fr,match flag_ch
    0001,AA,2020-05-15,reg1_fr,reg2_ch
    0002,AAA,2020-05-20,,reg3_ch
    
  • Ouput: file1.csv
    num,nom,date reg,match flag,country
    0001,AA ,2020-05-15,reg1_fr,fr      
    0002,AAA,2020-05-20,   _fr,fr       
    0001,AA ,2020-05-15,reg2_ch,ch
    0002,AAA,2020-05-20,reg3_ch,ch
    

I used the following code, the result is correct, but it forces me to declare all the different fields manually. Example:

awk -F, '
    BEGIN{OFS=FS} 
    NR==2{n = split($0,a); print "num,nom,date reg,match country,flag"; next}
    {for(i=4;i<=NF;i++) if (a[i] !=""){ print $1,$2,$3,a[i],$i} }
' < file.csv>file1.csv

Resulting file1.csv

num,nom,date reg,match country,flag
0001,AA,2020-05-15,match flag_fr,reg1_fr
0001,AA,2020-05-15,match flag_ch,reg2_ch
0002,AAA,2020-05-20,match flag_fr,
0002,AAA,2020-05-20,match flag_ch,reg3_ch

How can I do this, using generic awk code if possible?

0
$ cat tst.awk
BEGIN { FS=OFS="," }
NR==1 {
    for (numHdrs=1; numHdrs<=NF; numHdrs++) {
        if ( $numHdrs != "" ) {
            break
        }
    }
    numHdrs--
    next
}
{
    hdr = ""
    for (i=1; i<=numHdrs; i++) {
        hdr = hdr $i OFS
    }
}
NR == 2 {
    for (i=numHdrs+1; i<=NF; i++) {
        country[i] = $i
    }
    print hdr "match flag", "country"
    next
}
{
    for (i=numHdrs+1; i<=NF; i++) {
        print hdr country[i], $i
    }
}

.

$ awk -f tst.awk file.csv
num,nom,date reg,match flag,country
0001,AA,2020-05-15,match flag_fr,reg1_fr
0001,AA,2020-05-15,match flag_ch,reg2_ch
0002,AAA,2020-05-20,match flag_fr,
0002,AAA,2020-05-20,match flag_ch,reg3_ch

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.