# Text columns processing using awk

**URL:** <https://community.unix.com/t/text-columns-processing-using-awk/344555>\
**Category:** Shell Programming and Scripting\
**Created:** [July 17, 2014, 6:12am UTC](https://community.unix.com/t/text-columns-processing-using-awk/344555 "2014-07-17T06:12:10Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dovah](https://community.unix.com/letter_avatar/dovah/32/6_449c533f586939b6e5f246632a46421f.png) [@dovah](https://community.unix.com/u/dovah)\
**Post date:** [July 17, 2014, 6:12am UTC](https://community.unix.com/t/text-columns-processing-using-awk/344555/1 "2014-07-17T06:12:10Z")

</div>

P { margin-bottom: 0.25cm; line-height: 120%; }CODE.cjk { font-family: "WenQuanYi Micro Hei",monospace; }CODE.ctl { font-family: "Lohit Hindi",monospace; }A:link { } I'm trying to build an awk statement to print from a file (file1):

```nohighlight
A 1,2,3 *    
 A 4,5,6 **
 B 1    
 B 4,5 *

```

Another file like this:

```nohighlight
 A 1,2,3 * 3 1 0.333
 A 4,5,6 ** 3 2 0.666
 B 1 1 0 0
 B 4,5 * 2 1 0.5
 

```

In this new file, the first three columns are the same as in the original file. The forth column must contain the number of comma separated elements in column 2. The fifth column must contain the number of characters in column 3. The last column contains the proportion of column 5 on column 4.

I'm trying the following code:

```nohighlight
awk -F ',' '{print $1"\t"$2"\t"$3"\t"NF-1"\t"length($3)"\t"(length($3)/ NF-1)}' file1 > file2

```

But the output is unexpected (it seems that the second column is splitted, and thus all the calculations are wrong).

```nohighlight
  
 ~$ cat file2 
 A 1 2 3 * 2 4 0.333333 
 A 4 5 6 ** 2 5 0.666667 
 B 1 0 0 -1 
 B 4 5 * 1 0 -1 

```

Thank you for your help.

EDIT  
P { margin-bottom: 0.25cm; line-height: 120%; }CODE.cjk { font-family: "WenQuanYi Micro Hei",monospace; }CODE.ctl { font-family: "Lohit Hindi",monospace; }A:link { } I actually fixed one of my errors, but I still have no idea why the fourth column doesn't print correctly. Here's my new code and output:

```nohighlight
awk '{print $1"\t"$2"\t"$3"\t"(NF","$2 -1)"\t"length($3)"\t"(length($3)/(NF","$2-1))}' file1 > file2
 

```

```nohighlight
:~$ cat file2 
 A	1,2,3	*	3,0	1	0.333333 
 A	4,5,6	**	3,3	2	0.666667 
 B	1 2,0	0	0 
 B	4,5	*	3,3	1	0.333333 
  

```

---

<div class="post-metadata">

**Author:** ![Scrutinizer](https://community.unix.com/user_avatar/community.unix.com/scrutinizer/32/1216_2.png) [@Scrutinizer](https://community.unix.com/u/Scrutinizer)\
**Post date:** [July 17, 2014, 8:04am UTC](https://community.unix.com/t/text-columns-processing-using-awk/344555/2 "2014-07-17T08:04:08Z")

</div>

Since in the second example, where the `FS` is set to default, `NF` has no relation with the number of comma separated elements in `$2` and `$2-1` is unlikely to do what you want..

Compare:

```nohighlight
$ awk '{print $2, $2-1}' file
1,2,3 0
4,5,6 3
1 0
4,5 3

```

It is probably best to use `split()` with a comma as separator on `$2` to get the fields that you want..

---

<div class="post-metadata">

**Author:** ![dovah](https://community.unix.com/letter_avatar/dovah/32/6_449c533f586939b6e5f246632a46421f.png) [@dovah](https://community.unix.com/u/dovah)\
**Post date:** [July 17, 2014, 8:26am UTC](https://community.unix.com/t/text-columns-processing-using-awk/344555/3 "2014-07-17T08:26:05Z")

</div>

P { margin-bottom: 0.25cm; line-height: 120%; }CODE.cjk { font-family: "WenQuanYi Micro Hei",monospace; }CODE.ctl { font-family: "Lohit Hindi",monospace; }A:link { } P { margin-bottom: 0.25cm; line-height: 120%; }CODE.cjk { font-family: "WenQuanYi Micro Hei",monospace; }CODE.ctl { font-family: "Lohit Hindi",monospace; }A:link { }

```nohighlight
awk '{split($2,a,",")}' file1; awk '{OFS="\t"; print $1, $2, $3, length(a), length($3), length($3)/length($2)}' file1 > file2

```

I tried this but I get as output:

```nohighlight
:~$ cat file2
A 1,2,3 * 0 1 0.2
A 4,5,6 ** 0 2 0.4
B 1 0 0 0
B 4,5 * 0 1 0.333333

```

Close, but not correct. Column 4 has zeros.

---

<div class="post-metadata">

**Author:** ![Scrutinizer](https://community.unix.com/user_avatar/community.unix.com/scrutinizer/32/1216_2.png) [@Scrutinizer](https://community.unix.com/u/Scrutinizer)\
**Post date:** [July 17, 2014, 8:30am UTC](https://community.unix.com/t/text-columns-processing-using-awk/344555/4 "2014-07-17T08:30:19Z")

</div>

Why are you using two separate awk statements? The first awk has no output and thus no meaning. You should integrate the two..

---

<div class="post-metadata">

**Author:** ![dovah](https://community.unix.com/letter_avatar/dovah/32/6_449c533f586939b6e5f246632a46421f.png) [@dovah](https://community.unix.com/u/dovah)\
**Post date:** [July 17, 2014, 9:05am UTC](https://community.unix.com/t/text-columns-processing-using-awk/344555/5 "2014-07-17T09:05:48Z")

</div>

PRE.cjk { font-family: "WenQuanYi Micro Hei",monospace; }PRE.ctl { font-family: "Lohit Hindi",monospace; }P { margin-bottom: 0.25cm; line-height: 120%; }CODE.cjk { font-family: "WenQuanYi Micro Hei",monospace; }CODE.ctl { font-family: "Lohit Hindi",monospace; }A:link { }

```nohighlight
awk '{l2=split($2,a,","); OFS="\t"; print $1, $2, $3, l2, length($3), length($3)/l2}'

```

This works. Thanks for the tips!
