Column manipulating using Bash & Awk

user_12174978 0
Viewed 63

Let's assume have an example1.txt file consisting of few rows. 

item item item  
 A    B    C      
100  20   2       
100  22   3
100  23   4
101  26   2
102  28   2
103  29   3
103  30   2
103  32   2
104  33   2
104  34   2
104  35   2
104  36   3

There are few commands I would like to perform to filter out the txt files and add a few more columns.

At first, I want to apply a condition when item C is equal to 2. Using awk command I can do that in the following way.

Therefore The return text file would be:

awk '$3 == 2 { print $1 "\t"  $2  "\t" $3} ' example1.txt > example2.txt

item item item
 A    B    C      
100  20   2       
101  26   2
102  28   2
103  30   2
103  32   2
104  33   2
104  34   2
104  35   2

Now I want to count two things:

I want to count the total unique number in column 1. 

For example, in the above case example2.txt, it would be:
(100,101,102,103,104) = 5

And I would like to add the repeating column A number and add that to a new column.

I would like to have like this: 

item item item  item
 A    B    C     D
100  20   2      1
101  26   2      1
102  28   2      1
103  30   2      2
103  32   2      2
104  33   2      3
104  34   2      3
104  35   2      3

~

Above Item D column (4th), 1st row is 1, because it did not have any repetitive. but in 4th row, it's 2 because 103 is repetitive twice. Therefore I have added 2 in the 4th and 5th columns. Similarly, the last three columns in Item 4 is 3, because item A is repetitive three times in these three columns.

awk
4 Answers

Could you please try following. In case you want to save output into same Input_file then append > temp && mv temp Input_file to following code.

awk '
FNR==NR{
  if($3==2){
    a[$1,$3]++
  }
  next
}
FNR==1{
  $(NF+1)="item"
  print
  next
}
FNR==2{
  $(NF+1)="D"
  print
  next
}
$3!=2{
  next
}
FNR>2{
  $(NF+1)=a[$1,$3]
}
1
' Input_file  Input_file | column -t

Output will be as follows.

item  item  item  item
A     B     C     D
100   20    2     1
101   26    2     1
102   28    2     1
103   30    2     2
103   32    2     2
104   33    2     3
104   34    2     3
104   35    2     3

Explanation: Adding detailed explanation for above code.

awk '                    ##Starting awk program fro here.
FNR==NR{                 ##Checking condition if FNR==NR which will  be TRUE when 1st time Input_file is being read.
  if($3==2){             ##Checking condition if 3rd field is 2 then do following.
    a[$1,$3]++           ##Creating an array a whose index is $1,$3 and keep adding its index with 1 here.
  }
  next                   ##next will skip further statements from here.
}
FNR==1{                  ##Checking condition if this is first line.
  $(NF+1)="item"         ##Adding a new field with string item in it.
  print                  ##Printing 1st line here.
  next                   ##next will skip further statements from here.
}
FNR==2{                  ##Checking condition if this is second line.
  $(NF+1)="D"            ##Adding a new field with string item in it.
  print                  ##Printing 1st line here.
  next                   ##next will skip further statements from here.
}
$3!=2{                   ##Checking condition if 3rd field is NOT equal to 2 then do following.
  next                   ##next will skip further statements from here.
}
FNR>2{                   ##Checking condition if line is greater than 2 then do following.
  $(NF+1)=a[$1,$3]       ##Creating new field with value of array a with index of $1,$3 here.
}
1                        ##1 will print edited/non-edited lines here.
' Input_file Input_file   ##Mentioning Input_file names 2 times here.
user_58665800

You may try this awk:

awk -v OFS='\t' 'NR <= 2 {
   print $0, (NR == 1 ? "item" : "D")
}
FNR == NR && $3 == 2 {
   ++freq[$1]
   next
}
$3 == 2 {
   print $0, freq[$1]
}' file{,}


item  item  item  item
A     B     C     D
100   20    2     1
101   26    2     1
102   28    2     1
103   30    2     2
103   32    2     2
104   33    2     3
104   34    2     3
104   35    2     3
user_5482250

Similar to the others, but using awk with a single-pass and storing the information in arrays regarding the records seen and the count for D with the arrays ord and Dcnt used to map the information for each, e.g.

awk '
    FNR == 1 { h1=$0"\titem" }      # header 1 with extra "\titem"
    FNR == 2 { h2=$0"\tD" }         # header 2 with exter "\tD"
    FNR > 2 && $3 == 2 {            # remaining rows with $3 == 2
        D[$1]++                     # for D colum times A seen
        seen[$1,$2] = $0            # save records seen
        ord[++n] = $1 SUBSEP $2     # save order all records appear
        Dcnt[n] = $1                # save order mapped to $1 for D
    }
END {
    printf "%s\n%s\n", h1, h2       # output headers
    for (i=1; i<=n; i++)            # loop outputing info with D column added
        print seen[ord[i]]"\t"D[Dcnt[i]]
    }
' example.txt

(note: SUBSEP is a built-in variable that corresponds to the substring separator used when using the comma to concatenate fields for an array index, e.g. seen[$1,$2] to allow comparison outside of an array. It is by default "\034")

Example Output

item item item  item
A    B    C    D
100  20   2     1
101  26   2     1
102  28   2     1
103  30   2     2
103  32   2     2
104  33   2     3
104  34   2     3
104  35   2     3

Always more than one way to skin-the-cat with awk.

user_34221020

Assuming the file is not a big file;

awk 'NR==FNR && $3 == 2{a[$1]++;next}$3==2{$4=a[$1];print;}' file.txt file.txt

You parse through the file twice. In the first iteration, you calculate the 4th column and have it in an array. In the second parsing, we set the count as 4th column,and get the whole line printed.

user_16848680
Related