Extract columns by matching, rename, and assign value using AWK

user_10084916 0
Viewed 240

I have a tab delimited csv file containing summary statistics for object lengths:

sampled. objs.  obj. min. len.  obj. mean. len. obj. max. len.  obj. std.
50  22  60  95  5

I want the information about minimum and maximum lengths by searching matching column headers obj. min. len. and obj. max. len.. I then want to create a new csv file, comma-delimited with new column headers to get the result

object_minimum,object_maximum
22,95

I first print the new headers. Then I tried retrieving the indices of the match and then extracting from the second row using these indices:

#!/bin/awk -f

BEGIN {
    cols="object_minimum:object_maximum"
    FS="\t"
    RS="\n"
    col_count=split(cols, col_arr, ":");
    for(i=1; i<=col_count; i++) printf col_arr[i] ((i==col_count) ? "\n" : ",");
}
{
    for (i=1; i<=NF; i++) {
        if(index($i,"obj. min. len.") !=0) {
        data["object_minimum"]=i;
        }
        if(index($i,"obj. max. len.") !=0) {
        data["object_maximum"]=i;
        }  
    }
}
END NR==1 {
    for (j=1; j<=col_count; j++) printf NF==data[j] ((i==col_count) ? "\n" : ",");
}

There could be more columns and in a different order so it is necessary to do the matching to find the position, and also I may have to select for more columns by changing cols and looking for more matches. I execute by running

awk -f awk_script.awk original.csv > new.csv
aggregate awk
3 Answers
user_37768580

here is one working prototype, add formatting and error checking...

$ awk -F'\t' -v OFS=, '
       NR==1 {for(i=1;i<=NF;i++) 
                if($i=="obj. min. len.") min=i; 
                else if($i=="obj. max. len.") max=i; 
              print "min","max"}
       NR==2 {print $min,$max; exit}' file

min,max
22,95
user_14358690

Could you please try following, completely based on your shown samples only, written and tested in GNU awk. Created an awk variable named sep="###" this could be changed as per need too.

awk -v sep="###" '
BEGIN{
  OFS=","
}
FNR==1{
  while(match($0,/ +obj\./)){
    val=substr($0,RSTART,RLENGTH)
    sub(/^ +/,"",val)
    line=(line?line:"")substr($0,1,RSTART-1)sep val
    $0=substr($0,RSTART+RLENGTH)
  }
  if(substr($0,RSTART+RLENGTH)!=""){
    line=line substr($0,RSTART+RLENGTH)
  }
  num=split(line,arr,sep)
  for(i=1;i<=num;i++){
    if(arr[i]=="obj. min. len."){ min=i }
    if(arr[i]=="obj. max. len."){ max=i }
  }
  print "object_minimum,object_maximum"
  next
}
{
  print $min,$max
}
'  Input_file

Logical explanation: Working on the very first line of Input_file. Then using awk's match function to look for matches +obj\. in current line. In this creating a variable which has values of matched and before matched values. Once all searching of specific regex is done(means all occurrences of matched regex are found). Then splitting newly created variable(which has value of first line with separators ### assuming these are NOT present in your Input_file else change them to something else) into array. Finally going through all elements of that array and putting condition if a column is obj. min. len. then setting min variable value to that specific index number(which is actually field number for rest of the lines) and if value is obj. max. len. then setting max variable. After processing first line simply printing corresponding fields by doing $min,$max.

user_58665800
Related