0

I wish to keep only certain columns of a CSV file, based on the structure of the header line.

Description of data:

  1. In the header line (fields of country), several fields are empty
  2. The number of columns per country vary. There can be 3 columns or 10 columns per country.
  3. The number of columns in the header line, which starting from the position where the fields are not empty is dynamic. It can have 2 columns or 100 columns.

The objective is to keep the first field of each country if countries exists on first line. How can I do this using awk please? The example is like this:

  • input: file.csv
    ,,,fr,fr,fr,ch,ch,ch
    num,nom,date reg,match flag,date1,date2,match flag,date1,date2
    0001,AA,2020-05-15,reg1,2019-02-03,2019-02-05,reg2,2019-05-06,2019-06-10
    0002,AAA,2020-05-20,,,,reg3,2020-05-06,2020-06-10
    
  • Desired output: file1.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
    

Thank four your help.

3
  • Can any of the header fields be empty after the country columns start? or are all the potentially empty header fields at the start? Jul 8 '20 at 12:39
  • No, the headers fields can only be empty before the country columns. There must be at least one non-empty header fields. Thanks
    – lindo
    Jul 8 '20 at 13:13
  • Can your data fields contain commas? This, for example, is three fields in a CVS file, but with naïve parsing might be four: abc,"def,ghi",jkl
    – roaima
    Jul 9 '20 at 8:23
0

You need to iterate over the fields of the first record, conditionally adding their indices to a list; then iterate over the fields of each record and print the fields whose indices are in that list.

Ex.

$ awk -F, '
  BEGIN {OFS=FS}
  NR==1 {
    for(i=1;i<=NF;i++) if($i=="" || !seen[$i]++){inds[i]=1; lasti = i}
  }
  {
    for(i=1;i<=NF;i++) if(inds[i]) printf "%s%s", $i, i==lasti ? ORS : OFS
  }
' file.csv
,,,fr,ch
num,nom,date reg,match flag,match flag
0001,AA,2020-05-15,reg1,reg2
0002,AAA,2020-05-20,,reg3
6
  • Thank you for your help. This gives exactly what it takes. Otherwise I have a another problem in the file: I have line feed characters that I can't delete with awk. The only way was to use sed (probably Windows line feed). Data example is the same like this: ,,,fr,fr,fr,ch,ch,ch num,nom,date reg,match flag,date1,date2,match flag,date1,date2 0001,AA,2020-05-15,reg1,2019-02-03,2019-02-05,reg2,2019-05-06,2019-06-10 0002,AAA,2020-05-20,,,,reg3,2020-05-06,2020-06-10 ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,, Do you have a solution? Thank you.
    – lindo
    Jul 8 '20 at 18:02
  • @lindo you can try setting the awk record separator to account for the Windows line endings (awk -vRS='\r\n' ... ). Otherwise, use one of the methods described here What is ^M and how do I get rid of it? Jul 8 '20 at 18:05
  • Hello @steeldriver, Thank you for your response. I'm use: awk -F, ' BEGIN {OFS=FS} NR==1 { for(i=1;i<=NF;i++) if($i=="" || !seen[$i]++){inds[i]=1; lasti = i} } { for(i=1;i<=NF;i++) if(inds[i]) printf "%s%s", $i, i==lasti ? ORS : OFS }'< $file.csv | awk 'BEGIN {OFS=",";FS=","}!/^,+$/{print}'>file1.csv The result is correct. How we can add the code directly in the first awk instead of doing a pipe? . Thanks
    – lindo
    Jul 13 '20 at 10:28
  • @lindo you should be able to apply your !/^,+$/ pattern directly to the main {...} action Jul 13 '20 at 12:56
  • Ok, I put it directly in th awk code and the result is correctly output: awk -F, ' BEGIN {OFS=FS} NR==3 { for(i=1;i<=NF;i++) if($i=="" || !seen[$i]++){inds[i]=1; lasti = i} } !/^,+$/{ for(i=1;i<=NF;i++) if(inds[i]) printf "%s%s", $i, i==lasti ? ORS : OFS }' <file.csv `. Thank you
    – lindo
    Jul 13 '20 at 14:00

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.