Transpose few columns from a csv file based on several columns using awk

Question

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?

Answer 1

$ 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