Conditionally replace multiple values in multiple columns

Question

I have a coma delimited file where some values can be missing like

1,f,12,f,t,18
2,t,17,t, ,17
3,t,15, ,f,16

I want to change some of the columns to numeric; f to 0 and t to 1. Here, I want to change only columns 2 and 5 and don't want to change column 4. I my result file should look like

1,0,12,f,1,18
2,1,17,t, ,17
3,1,15, ,0,16

I can use the statement

awk -F, -v  OFS=',' '{ if ( $2 ~ /t/ ) { $2 = 1 } else if ( $2 ~ /f/ ) { $2 = 0 }; print}' test.csv

To change individual columns

I can also use a loop like

 awk -F, -v  OFS=',' 'BEGIN {
     IFS = OFS = ","
  }
  {     
    for (column = 1; column <= 4; ++column) {
        if ($column ~ /t/) {
          $column = 1
       }
        else if($column ~ /f/) {
           $column = 0
        }
     }    
     print 
   }         
' test.csv

to replace multiple columns if they are together. How do I change the for loop to specify only the specific columns? I know there is a for each loop to do the same but I couldn't get it to work. Also how can I assign multiple variables to the array in a single statement like

a =[1, 2, 3, 4]

anubhava · Accepted Answer · 2015-10-22 11:19:57Z

3

You can use this awk:

awk 'BEGIN{ FS=OFS=","; a[2]; a[5] }
          { for (i in a) if ($i=="f") $i=0; else if ($i=="t") $i=1 } 1' file
1,0,12,f,1,18
2,1,17,t, ,17
3,1,15, ,0,16

answered Oct 22, 2015 at 11:19

anubhava

790k67 gold badges603 silver badges671 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

discipulus Over a year ago

Just an additional query, how can I ignore the first row with this command.

anubhava Over a year ago

Yes sure: awk 'BEGIN{FS=OFS=","; a[2]; a[5]} NR>1{for (i in a) if ($i=="f") $i=0; else if ($i=="t") $i=1} 1' file

Collectives™ on Stack Overflow

Conditionally replace multiple values in multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related