awk

• syntax

  • FR & FNR

  • FILENAME

• output

  • convert row to column

    • rs -T

    • datamash

    • tail -f file with timestamp

  • reverse words

  • align

    • merge every 2 lines

    • right/left alignment

    • alignment with fixed column

    • append space

    • convert csv format

  • unique

  • removal

    • remove non-duplicated lines

  • combination

    • git-st

  • show matched values

  • split

  • last n columns

    • last 2 columns

    • second-to-last column ( --NF )

    • last N columns

    • get major domain

    • trim

• calculate

  • length

    • longest line

    • longest filename

    • print chars and length

  • sum

    • base on other column

    • sum lines by extension

    • summary mem for all users (ps aux)

    • summary all in nth column

    • sum since nth line

    • sum for each column

  • calculate word count in a file

  • maximize & minimize

• field

  • multiple delimiters

  • field separator variable

  • FS/OFS

• parser

  • csv

• cheatsheet

  • print lines by defined line numbers

  • join and calculate

  • array diff

references:

• awk printf
• Gawk: Effective AWK Programming | pdf -gnu.org/manual
• 4.5 Specifying How Fields Are Separated
• awk裡好用的變數：FS, OFS, RS, ORS, NR, NF, FILENAME, FNR

syntax

FR & FNR

[!NOTE|label:references:]

• tips

  • NR == FNR: first file

  • NR != FNR: all files except the first one

• What are NR and FNR and what does "NR==FNR" imply?
• Explain the AWK syntax in detail(NR,FNR,NF)

  awk '                      # use awk
  (NR == FNR) {              # process 1st file
      s[$0]                  # hash the whole record to array s
      next                   # process the next record of the first file
  }
  {                          # process the second file
      for (i=1; i<=NF; i++)  # for each field in record
          if ($i in s)       # if field value found in the hash
              delete s[$i]   # delete the value from the hash
  }
  END {                      # after processing both files
      for (i in s)           # all leftover values in s
          print i            # are output
  }' tests1 tests2

• Explain the AWK syntax in detail(NR,FNR,NF)

$ cat a.txt b.txt
       File: a.txt
   1   a
   2   b
   3   c

       File: b.txt
   1   d
   2   e

$ awk '{print FILENAME, NR, FNR, $0}' a.txt b.txt
a.txt 1 1 a
a.txt 2 2 b
a.txt 3 3 c
b.txt 4 1 d
b.txt 5 2 e

# or
$ awk '{ printf "FILENAME:%s, Line:%s, NR:%d, FNR:%d, NF:%d\n", FILENAME, $0, NR, FNR, NF}'  a.txt b.txt
FILENAME:a.txt, Line:a, NR:1, FNR:1, NF:1
FILENAME:a.txt, Line:b, NR:2, FNR:2, NF:1
FILENAME:a.txt, Line:c, NR:3, FNR:3, NF:1
FILENAME:b.txt, Line:d, NR:4, FNR:1, NF:1
FILENAME:b.txt, Line:e, NR:5, FNR:2, NF:1

# or
$ awk 'NR==FNR { a[$0]; }
       { if($0 in a) print FILENAME " " NR " " FNR " " $0 }
      ' a.txt b.txt
a.txt 1 1 a
a.txt 2 2 b
a.txt 3 3 c

FILENAME

[!NOTE|label:references:]

• Input File Index vs. Filename

$ echo "a dummy line" > dummy.txt
$ awk '{print FILENAME}' dummy.txt <(cat dummy.txt)
dummy.txt
/dev/fd/63

output

[!NOTE]

• AWK: Formatted printing
• AWK Notebook: Index of /~berezin/330/N

convert row to column

[!TIP|label:original content]

$ cat sample.txt
job salary
c++ 13
java 14
php 12

• * An efficient way to transpose a file in Bash

$ awk '{
          for ( i=1; i<=NF; i++ ) arr[i] = (arr[i]? arr[i] FS $i: $i)
        } END {
          for ( i in arr ) print arr[i]
        }
      ' sample.txt

job c++ java php
salary 13 14 12

• or

  $ echo -e "job c++ java php\nsalary 13 14 12" |
    awk '{ for(i=1;i<=NF;i++) a[i][NR]=$i }
         END { for(i in a) for(j in a[i]) printf "%s"(j==NR?RS:FS), a[i][j] }
        ' "${1+FS=$1}" |
    column -t
  job   salary
  c++   13
  java  14
  php   12
  
  # or
  $ echo -e "job c++ java php\nsalary 13 14 12" |
    awk '{ for (i=1; i<=NF; i++) a[i,NR]=$i; max=(max<NF?NF:max) }
           END { for (i=1; i<=max; i++)
                 { for (j=1; j<=NR; j++) printf "%s%s", a[i,j], ( j==NR?RS:FS ) }
               }
        '
  job salary
  c++ 13
  java 14
  php 12

• or

  $ awk ' { for (i=1; i<=NF; i++)  {a[NR,i] = $i} }
            NF > p { p = NF }
            END { for(j=1; j<=p; j++) {
                    str=a[1,j]
                    for(i=2; i<=NR; i++){
                        str=str" "a[i,j];
                    }
                    print str
                  }
                }
        ' <<INPUT
  X column1 column2 column3
  row1 0 1 2
  row2 3 4 5
  row3 6 7 8
  row4 9 10 11
  INPUT
  
  X row1 row2 row3 row4
  column1 0 3 6 9
  column2 1 4 7 10
  column3 2 5 8 11

rs -T

$ rs -T -c, -C, <<INPUT
  a,b,c
  ,x,y
  11,22,33
  INPUT
a,,11,
b,x,22,
c,y,33,

# with default `-C`
$ rs -T -c, <<INPUT
a,b,c
,x,y
11,22,33
INPUT
a                                                       11
b                           x                           22
c                           y                           33

datamash

[!NOTE|label:references:]

• GNU datamash
• GNU datamash - Examples

$ echo -e "job c++ java php\nsalary 13 14 12" |
  datamash transpose -t ' ' |
  column -t
job   salary
c++   13
java  14
php   12

tail -f file with timestamp

$ tail -f file | awk '{now=strftime("%F %T%z\t");sub(/^/, now);print}'

reverse words

[!NOTE]

• How to reverse a list of words in a shell string?
• NOTE:

  • rev not working since number will be reversed to different values

    $ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' | rev | column -t
    02  226  0  0  5  5
    8   934  0  2  3  6
    21  054  0  3  2  5

• How to reverse all the words in a file with bash in Ubuntu?
• Reversing the List of Words in a Bash String

$ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' | column -t
  5  5  0  0  622  20
  6  3  2  0  439  8
  5  2  3  0  450  12

$ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' |
  awk '{ for(i=NF;i>0;--i) printf "%s%s",$i,(i>1?OFS:ORS) }' |
  column -t
20  622  0  0  5  5
8   439  0  2  3  6
12  450  0  3  2  5

# or
$ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' |
  awk '{ do printf "%s" (NF>1?FS:RS),$NF; while(--NF) }' |
  column -t
20  622  0  0  5  5
8   439  0  2  3  6
12  450  0  3  2  5

# or
$ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' |
  awk '{ i=NF; while(i) printf "%s",$i (i-->1?FS:RS) }' |
  column -t
20  622  0  0  5  5
8   439  0  2  3  6
12  450  0  3  2  5

# or
$ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' |
  awk '{ for (i=NF; i>1; i--) printf("%s ",$i); print $1; }' |
  column -t
20  622  0  0  5  5
8   439  0  2  3  6
12  450  0  3  2  5

# or
$ echo -e '5 5 0 0 622 20\n6 3 2 0 439 8\n5 2 3 0 450 12' |
  awk '{ for (i=NF; i>0; i--) printf("%s ",$i); printf("\n") }' |
  column -t
20  622  0  0  5  5
8   439  0  2  3  6
12  450  0  3  2  5

align

[!NOTE]

• format the shell script output as a table

merge every 2 lines

$ echo {a..f} | fmt -1 | awk '{ x=$0; getline; print x " : " $0; }'
a : b
c : d
e : f

[!NOTE|label:references:]

• bash: how to add header to its corresponding lines
• How to add blank space in the middle of the line if the line is less than fixed length?
• Bash shell script output alignment

$ cat a.txt
CGRT,630,SC063P1
10001,X,6849
10003,X,6913
10005,X,6977
CGRT,631,SC063P2
10049,X,8481
10051,X,8545
10081,X,1185
CGRT,632,SC063P3
10110,X,1601
10111,X,1633

$ awk '!/^[0-9]/{h=$0;next}{print h,$0}' a.txt
CGRT,630,SC063P1 10001,X,6849
CGRT,630,SC063P1 10003,X,6913
CGRT,630,SC063P1 10005,X,6977
CGRT,631,SC063P2 10049,X,8481
CGRT,631,SC063P2 10051,X,8545
CGRT,631,SC063P2 10081,X,1185
CGRT,632,SC063P3 10110,X,1601
CGRT,632,SC063P3 10111,X,1633

# or
$ awk '/^[a-zA-Z]/{val=$0;next} {print val "\t" $0}' a.txt

# or via sed
$ sed '/^[^0-9]/h;//d;G;s/\(.*\)\n\(.*\)/\2 \1/'

right/left alignment

$ printf '|%-5s|\n' a ab abc abcd abcde
|a    |
|ab   |
|abc  |
|abcd |
|abcde|

$ printf '|%5s|\n' a ab abc abcd abcde
|    a|
|   ab|
|  abc|
| abcd|
|abcde|

$ printf '|%.5s|\n' a ab abc abcd abcde
|a|
|ab|
|abc|
|abcd|
|abcde|

alignment with fixed column

$ cat -pp a.txt
ABCEFGH  K
ABCDE  FGH
ABCD  EFG
ABCDE FGH
ABCDE

$ awk -v tgt=10 'length($0)<tgt{gsub(/ /,""); $0=substr($0,1,4) sprintf("%*s",tgt-length($0),"") substr($0,5)} 1' a.txt
ABCEFGH  K
ABCDE  FGH
ABCD   EFG
ABCD  EFGH
ABCD     E

# or
$ awk -v l=10 -v i=4 'length($0)>=l{print; next}
                      {
                      gsub(/ /,"")
                      s1=substr($0,1,i)
                      s2=substr($0,i+1,length($0))
                      printf "%s%*s%s\n",s1,l-length($0)," ",s2
                      }' a.txt

append space

[!NOTE]

• How to add 100 spaces at end of each line of a file in Unix
• Find Longest Line in a .txt File and fill all Lines to that Length with 'blank Spaces'?

# 2 extra empty space, 22 chars per line in linux
$ cal | wc -L
22
$ cal | command cat -e
    January 2024      $
Su Mo Tu We Th Fr Sa  $
    1  2  3  4  5  6  $
 7  8  9 10 11 12 13  $
14 15 16 17 18 19 20  $
21 22 23 24 25 26 27  $
28 29 30 31           $

# remove trailing space and empty lines
$ cal | sed 's/[ \t]*$//' | sed '/^[[:space:]]*$/d' | command cat -e
    January 2024$
Su Mo Tu We Th Fr Sa$
    1  2  3  4  5  6$
 7  8  9 10 11 12 13$
14 15 16 17 18 19 20$
21 22 23 24 25 26 27$
28 29 30 31$

# append spaces to the length of longest line ( 20 )
$ cal | sed 's/[ \t]*$//' | sed '/^[[:space:]]*$/d' | awk '{printf "%-20s\n", $0}' | command cat -e
    January 2024    $
Su Mo Tu We Th Fr Sa$
    1  2  3  4  5  6$
 7  8  9 10 11 12 13$
14 15 16 17 18 19 20$
21 22 23 24 25 26 27$
28 29 30 31         $

# tput cub 20 works for every single lines

• and more

  $ command cat file
  jlsf
  slf
  asdfasfs
  sd
  
  $ awk 'FNR==NR{ t=(length>=t)?length:t;next }length<t{ for(o=1;o<=t-length;o++)s=s "|";$0=$0s;s="" }1' file file
  jlsf||||
  slf|||||
  asdfasfs
  sd||||||

convert csv format

$ awk 'BEGIN {FS = ","; OFS = "\t"} {$1 = $1} 1' <<DATA
a,b,c
,x,y
dd,ee,ff
DATA
a   b   c
    x   y
dd  ee  ff

unique

$ awk '!seen[$0]++' file

# sample
$ awk '!a[$0]++' <<< 'a
b
a
b
d
'
a
b
d
# or
$ awk '!a[$0]++' < <(echo -e 'a\nb\na\nb\nc')
a
b
c

removal

remove non-duplicated lines

pre-condition

$ cat sample.txt | xargs
a a b c d e e e f

$ awk '{ print $1 }' sample.txt | sort | uniq -cd | sort -g

• or

  $ awk '{ arr[$1]++ } END {
           for (key in arr) {
             if ( arr[key] > 1 ){ print arr[key], key }
           }
         }' \
    sample.txt

• show only duplicated lines

  $ awk 'seen[$1]++' sample.txt

  • show only non-duplicated lines

    $ awk '!seen[$1]++' sample.txt

combination

[!NOTE|label:references:]

• Using AWK to Process Input from Multiple Files

  • FNR == NR : read 1st file

  • FNR != NR : read 2nd file

• AWK compare two columns form different files and merge outputs [duplicate]
• Awk asking combine two files
• How to combine output of git diff --name-status and git diff --stat commands?

git-st

[!NOTE] objective: git status + git diff --stat

• * awk union two columns from different output files

awk 'FNR==NR { map[$1]=$2" "$3" "$4 }
     FNR != NR { if ($2 in map) {print $0, map[$2]} else { print $0 } }
    ' <(git --no-pager diff --stat --relative | head -n-1 ) <(git status -sb)

## main
 M a | 2 ++
?? b

# better solution
declare gdiff
gdiff=$(git --no-pager diff --stat --relative | head -n-1)
awk 'FNR==NR { k=$1; $1=""; map[k]=$0; next } { print $0 map[$2] }' \
    <(echo -e "${gdiff}") <(git status -sb)

show matched values

• Comparison Operators
• Regular Expressions
• How to check the checksum through commandline?

• find distrib name from /etc/lsb-release

  $ awk -F= '$1 == "DISTRIB_ID" {print $2;}' /etc/lsb-release
  Ubuntu
  
  # or
  $ awk -F= '$1=="ID" {print $2;}' /etc/os-release
  ubuntu

• find multiple matches

  $ awk -F= '$1 ~ /DISTRIB_ID|DISTRIB_RELEASE/ {print $2;}' /etc/lsb-release
  Ubuntu
  18.04

• return true or false according to matches result

  $ standard='2cf1b1652a5b268ec80717ef33fef111'
  $ md5sum ~/.bashrc | awk '$1 != "${standard}" {exit 1}'
  
  # or
  $ md5sum ~/.bashrc | awk '$1 == "${standard}" {print "true"}'

split

$ echo "12:34:56" |
      awk '{
        len = split( $0, a, ":" ) ;
        for( i=1; i <=len; i++ ) {
          print "a["i"] = "a[i] ;
        }
        print "length = "len
      }'
a[1] = 12
a[2] = 34
a[3] = 56
length = 3

• split with result

[!NOTE|label:real situation]

• original string

  > git config --list --show-origin --name-only | head -3
  file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   diff.astextplain.textconv
  file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   filter.lfs.clean
  file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   filter.lfs.smudge
      +-------------------------------------------------------+
                        wanted string

• filter via :

  REM file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   filter.lfs.smudge
  REM +---+-+-------------------------------------------------------------------------+
  REM   |  |                                        |
  REM   v  v                                        v
  REM   $1 $2                                       $3
  
  > git config --list --show-origin --name-only | head -3 | awk -F: '{print $2}'
  C
  C
  C
  > git config --list --show-origin --name-only | head -3 | awk -F: '{print $3}'
  /Users/marslo/AppData/Local/Programs/Git/etc/gitconfig  diff.astextplain.textconv
  /Users/marslo/AppData/Local/Programs/Git/etc/gitconfig  filter.lfs.clean
  /Users/marslo/AppData/Local/Programs/Git/etc/gitconfig  filter.lfs.smudge

REM via split

REM  "FS"                                  $2
REM   |                                    |
REM   v                                    v
REM  ___ ___________________________________________________________________________
REM |   |                                                                           |
REM file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   filter.lfs.smudge
REM     +                                                       +   +               +
REM     .........................................................   .................
REM                              v                                          v
REM                           arr[1]                                     arr[2]

> git config --list --show-origin --name-only | head -3 | awk 'BEGIN { FS="file:" }; { print $2 }'
C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig        diff.astextplain.textconv
C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig        filter.lfs.clean
C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig        filter.lfs.smudge

> git config --list --show-origin --name-only | head -3 | awk 'BEGIN { FS="file:" }; { n=split($2, arr, " "); print arr[1] }'
C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig

last n columns

last 2 columns

$ echo {a..z} | xargs -n4
a b c d
e f g h
i j k l
m n o p
q r s t
u v w x
y z

$ echo {a..z} | xargs -n4 | awk '{ print $(NF-1), $NF }'
c d
g h
k l
o p
s t
w x
y z

# better solution
$ echo {a..z} | xargs -n5
a b c d e
f g h i j
k l m n o
p q r s t
u v w x y
z
$ echo {a..z} | xargs -n5 |
  awk 'NF == 1 { printf ("%3s", $NF) };
       NF >= 2 { print $(NF-1), $NF }
      '
d e
i j
n o
s t
x y
  z

# or `NF && NF-1`
$ echo {a..z} | xargs -n5 |
  awk 'NF == 1    { printf ("%3s", $NF) };
       NF && NF-1 { print $(NF-1), $NF }
      '

second-to-last column ( --NF )

$ seq 12 | xargs -n5 | awk '{ NF--; print $NF }'
4
9
11

last N columns

$ awk '{ print substr($0,index($0,$6)) }' <<INPUT
  f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
  c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
  INPUT
f6 f7 f8 f9 f10
c6 c7 c8 c9 c10

# or
$ awk '{for (i=6; i<=NF; i++) printf("%s%s", $i,(i==NF)? "\n" : OFS) }' <<INPUT
  f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
  c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
  INPUT
f6 f7 f8 f9 f10
c6 c7 c8 c9 c10

# or
$ awk '{ for(i=6;i<=NF;i++) printf $i" "; print "" }' <<INPUT
  f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
  c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
  INPUT
f6 f7 f8 f9 f10
c6 c7 c8 c9 c10

# or
$ awk '{ for(i=1;i<=5;i++) $i="";print }' <<INPUT
  f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
  c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
  INPUT
     f6 f7 f8 f9 f10
     c6 c7 c8 c9 c10

• via cut -f<n>-

  $ cut -d" " -f6- <<INPUT
    f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
    c1 c2 c3 c4 c5 c6 c7 c8 c9 c10
    INPUT
  f6 f7 f8 f9 f10
  c6 c7 c8 c9 c10

get major domain

$ echo -e 'a.domain.com\nb.domain.com\nc.domain.com' |
  awk -F. -v OFS='.' '{ print $(NF-1), $NF }'
domain.com
domain.com
domain.com

trim

[!NOTE|label:references:]

• How can I trim white space from a variable in awk?

  • andrewrcollins/trim.awk
• How can I keep the leading white spaces while removing last field in awk?

# original string
$ echo "man(1), apropos(1), whatis(1) - display online manual documentation pages" |
  awk -F"(\\\([0-9]\\\),?)" \
        '{ printf "|%s|", $NF }'
| - display online manual documentation pages|
#^ space here

# remove leading & trailing space
$ echo "man(1), apropos(1), whatis(1) - display online manual documentation pages" |
  awk -F"(\\\([0-9]\\\),?)" \
        '{ gsub(/^[ \t]+|[ \t]+$/, "", $NF); printf "|%s|", $NF}'
|- display online manual documentation pages|
#^ remove `-` and space after it

$ echo "man(1), apropos(1), whatis(1) - display online manual documentation pages" |
  awk -F"(\\\([0-9]\\\),?)" \
        '{ gsub(/^[ \t-?]+|[-? \t]+$/, "", $NF); printf "|%s|", $NF}'
|display online manual documentation pages|

calculate

length

longest line

[!NOTE] Longest line in a file

$ cat -pp /tmp/terminal-1
    January 2024
Su Mo Tu We Th Fr Sa
    1  2  3  4  5  6
 7  8  9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30 31

$ wc -L /tmp/terminal-1
20 /tmp/terminal-1

$ awk '{ if (length($0) > max) {max = length($0); maxline = $0} } END { print maxline }' /tmp/terminal-1
Su Mo Tu We Th Fr Sa

$ awk '{print length, $0}' /tmp/terminal-1  | sort -nr
20 Su Mo Tu We Th Fr Sa
20  7  8  9 10 11 12 13
20 21 22 23 24 25 26 27
20 14 15 16 17 18 19 20
20     1  2  3  4  5  6
16     January 2024
11 28 29 30 31

longest filename

# in repo
$ fd | awk '{ print length " | " $0 }' | sort -rn | head -3
98 | programming/archive/maven/hello-world/src/main/java/com/juvenxu/mvnbook/helloworld/HelloWorld.java
83 | programming/archive/maven/hello-world/src/main/java/com/juvenxu/mvnbook/helloworld/
72 | programming/archive/maven/hello-world/src/main/java/com/juvenxu/mvnbook/

$ fd | awk '{ if (length($0) > max){ max = length($0); line = $0} } END { print max " | " line }'
98 | programming/archive/maven/hello-world/src/main/java/com/juvenxu/mvnbook/helloworld/HelloWorld.java

print chars and length

$ awk '{ for(i=1; i<=NF; i++) { print $i, length($i) } }' sample.txt

• or using while

  $ while IFS= read -r line; do
    echo "${line}: ${#line}"
  done < <(cat sample.txt | xargs -n1 echo)

sum

[!NOTE|label:references:]

• Summing values of a column using awk command
• Using awk to sum the values of a column, based on the values of another column
• Using awk to sum the values of a column, based on the values of another column
• Using awk to sum the values of a column, based on the emptyness or not of another column

base on other column

[!NOTE]

• Sum First Column on basis of Second Column

#  `substr` to get 4-last chars of string `n--xxxx`
#                     v
$ awk 'NR > 1 { k = substr($2, 4); cnt[k] += $1 } { print }
       END
       { print "\nTOTAL:"; for (k in cnt) print cnt[k], k }
      ' <<INPUT
  No.ofRecord    FileName                   delimiter
  563394         1--UnixfileName.txt        28
  364794         2--UnixfileName.txt        28
  785895         3--UnixfileName.txt        28
  99778453       1--NextUnixFileName.txt    18
  95645453       2--NextUnixFileName.txt    18
  99745313       3--NextUnixFileName.txt    18
  INPUT

No.ofRecord    FileName                   delimiter
563394         1--UnixfileName.txt        28
364794         2--UnixfileName.txt        28
785895         3--UnixfileName.txt        28
99778453       1--NextUnixFileName.txt    18
95645453       2--NextUnixFileName.txt    18
99745313       3--NextUnixFileName.txt    18

TOTAL:
295169219 NextUnixFileName.txt
1714083 UnixfileName.txt

sum $4 base on $2 and print original

$ url='jira.sample.com'
$ project='MY_PROJECT'
$ sprint='SPRINT-1'
$ curl --silent --insecure --globoff -XGET "https://${url}/rest/api/2/search?jql=project%3D${project}%20AND%20Sprint%3D${sprint}&maxResults=100" |
  jq -r '.issues[] | [.key, .fields.status.name, .fields.created, .fields.customfield_10062] | join(" | ")' |
  sort -t'|' -k3 |
  column -t -s'|' -o'|' |
  awk -F'|' '{ map[$2]+=$4; print }; END { print "\nresult:"; for (key in map) { print key ": " map[key] } }'
JIRA-00 | Closed      | 2024-02-05T23:05:36.000-0800 | 1.0
JIRA-01 | Closed      | 2024-02-05T23:08:13.000-0800 | 1.0
JIRA-02 | Open        | 2024-02-06T19:35:12.000-0800 |
JIRA-05 | Open        | 2024-02-07T00:56:35.000-0800 | 1.0
JIRA-06 | Open        | 2024-02-07T00:56:50.000-0800 | 1.0
JIRA-07 | Open        | 2024-02-07T00:56:57.000-0800 | 1.0
JIRA-08 | Open        | 2024-02-07T00:57:06.000-0800 | 1.0
JIRA-09 | In Review   | 2024-02-07T00:57:21.000-0800 | 3.0
JIRA-10 | In Review   | 2024-02-07T00:57:29.000-0800 | 2.0
JIRA-11 | Open        | 2024-02-07T00:57:35.000-0800 | 2.0
JIRA-12 | Open        | 2024-02-07T00:57:42.000-0800 | 2.0
JIRA-13 | Open        | 2024-02-07T00:57:58.000-0800 | 2.0
JIRA-14 | Closed      | 2024-02-08T02:26:37.000-0800 | 0.5
JIRA-15 | Closed      | 2024-02-08T02:27:34.000-0800 | 0.5
JIRA-16 | In Progress | 2024-02-08T03:11:11.000-0800 | 1.5
JIRA-17 | In Progress | 2024-02-08T03:12:58.000-0800 | 0.5
JIRA-18 | In Progress | 2024-02-08T03:14:14.000-0800 | 1.5
JIRA-19 | In Progress | 2024-02-08T03:14:34.000-0800 | 0.5
JIRA-20 | In Progress | 2024-02-08T03:15:38.000-0800 | 1.5
JIRA-21 | In Progress | 2024-02-08T03:15:57.000-0800 | 0.5
JIRA-22 | Open        | 2024-02-08T03:18:30.000-0800 | 1.5
JIRA-23 | Open        | 2024-02-08T03:18:46.000-0800 | 0.5
JIRA-24 | Open        | 2024-02-09T00:11:31.000-0800 | 1.5
JIRA-25 | Open        | 2024-02-09T00:12:09.000-0800 | 0.5
JIRA-26 | Open        | 2024-02-09T00:12:45.000-0800 | 1.5
JIRA-27 | Open        | 2024-02-09T00:13:18.000-0800 | 0.5
JIRA-28 | Open        | 2024-02-09T00:14:03.000-0800 | 1.5
JIRA-29 | Open        | 2024-02-09T00:14:31.000-0800 | 0.5

result:
 Open        : 18
 In Progress : 6
 In Review   : 5
 Closed      : 3

sum before date

[!TIP|label:substr]

• references:

  • Use awk to print first 6 characters
  • comparing dates using awk in bash
• substr VS. original string:

  $ curl --silent --insecure --globoff -XGET "https://${url}/rest/api/2/search?jql=project%3D${project}%20AND%20Sprint%3D${sprint}&maxResults=100" |
    jq -r '.issues[] | [.key, .fields.status.name, .fields.created, .fields.customfield_10062] | join(" | ")' |
    sort -t'|' -k3 |
    column -t -s'|' -o'|' |
    awk -F '|' -v dateA="2024-02-08" '{ d=substr($3,2,10); if (dateA>d) {print d":"$3} }'
  2024-02-05: 2024-02-05T23:05:36.000-0800
  2024-02-05: 2024-02-05T23:08:13.000-0800
  2024-02-06: 2024-02-06T19:35:12.000-0800
  2024-02-07: 2024-02-07T00:56:35.000-0800
  2024-02-07: 2024-02-07T00:56:50.000-0800
  2024-02-07: 2024-02-07T00:56:57.000-0800
  2024-02-07: 2024-02-07T00:57:06.000-0800
  2024-02-07: 2024-02-07T00:57:21.000-0800
  2024-02-07: 2024-02-07T00:57:29.000-0800
  2024-02-07: 2024-02-07T00:57:35.000-0800
  2024-02-07: 2024-02-07T00:57:42.000-0800
  2024-02-07: 2024-02-07T00:57:58.000-0800

$ url='jira.sample.com'
$ project='MY_PROJECT'
$ sprint='SPRINT-1'
$ curl --silent --insecure --globoff -XGET "https://${url}/rest/api/2/search?jql=project%3D${project}%20AND%20Sprint%3D${sprint}&maxResults=100" |
  jq -r '.issues[] | [.key, .fields.status.name, .fields.created, .fields.customfield_10062] | join(" | ")' |
  sort -t'|' -k3 |
  column -t -s'|' -o'|' |
  awk -F '|'  -v dateA="2024-02-08" '{ d=substr($3,2,10); if (dateA>d) {print; sum+=$4} }; END { print "total: "sum }'
JIRA-00 | Closed      | 2024-02-05T23:05:36.000-0800 | 1.0
JIRA-01 | Closed      | 2024-02-05T23:08:13.000-0800 | 1.0
JIRA-02 | Open        | 2024-02-06T19:35:12.000-0800 |
JIRA-05 | Open        | 2024-02-07T00:56:35.000-0800 | 1.0
JIRA-06 | Open        | 2024-02-07T00:56:50.000-0800 | 1.0
JIRA-07 | Open        | 2024-02-07T00:56:57.000-0800 | 1.0
JIRA-08 | Open        | 2024-02-07T00:57:06.000-0800 | 1.0
JIRA-09 | In Review   | 2024-02-07T00:57:21.000-0800 | 3.0
JIRA-10 | In Review   | 2024-02-07T00:57:29.000-0800 | 2.0
JIRA-11 | Open        | 2024-02-07T00:57:35.000-0800 | 2.0
JIRA-12 | Open        | 2024-02-07T00:57:42.000-0800 | 2.0
JIRA-13 | Open        | 2024-02-07T00:57:58.000-0800 | 2.0
total: 17

sum $3 base on $1,$2

$ awk -F, '{array[$1","$2]+=$3} END { for (i in array) {print i"," array[i]}}' <<INPUT
  P1,gram,10
  P1,tree,12
  P1,gram,34
  P2,gram,23
  INPUT

P1,gram,44
P1,tree,12
P2,gram,23

sum $2 base on $1

$ awk '{array[$1]+=$2} END { for (i in array) {print i, "\t", array[i]} }' <<INPUT
 .sh 23
 .py 100
 .sh 10
 .py 5
 .c 43
 INPUT

.c   43
.sh  33
.py  105

# central alignment
$ awk '{array[$1]+=$2} END { for (i in array) {printf ("%10s | %s\n", i, array[i])} }' <<INPUT
 .sh 23
 .py 100
 .sh 10
 .py 5
 .c 43
 INPUT
        .c | 43
       .sh | 33
       .py | 105

sum lines by extension

$ fd --type f | xargs wc -l
  375 chars.md
  544 date.md
   25 date.sh
  255 params.md
   58 progressBar.sh
   15 progressBar2.sh
  205 tricky.md
  476 util.md
 1953 total

$ fd --type f |
  xargs wc -l |
  tac |
  sed -re 's/\s*([0-9]+)\s*[^\.]+(\.\w+)$/\1 \2/g' |
  awk 'NR == 1 { print }
       NR > 1  { array[$2]+=$1 } END { for (i in array) {printf ("%10s | %s\n", i, array[i])} }
      '
 1953 total
       .sh | 98
       .md | 1855

# another
$ fd --type f |
  xargs wc -l |
  tac |
  sed -re 's/\s*([0-9]+)\s*[^\.]+(\.\w+)$/\1 \2/g' |
  awk 'NR == 1 { print }
       NR > 1  { a[$NF]+=$1 } END { for(i in a){print i,a[i]} }
      '

summary mem for all users (ps aux)

$ awk '{sum += $1} END {print sum}' < <(ps -u marslo -o pmem)

• or

  $ ps -u marslo -o pcpu,pmem,pid,command |
       awk '{sum += $2} END {print sum}'

summary all in nth column

$ echo 'chr19   10 11
        chr19   12 15
        chr19   11 29
        chr19   a0 20
       ' |
  awk '{SUM+=$3}END{print SUM}'
75

# alternative
$ echo 'chr19   10 11
        chr19   12 15
        chr19   11 29
        chr19   a0 20
       ' |
  awk '{count=count+$NF}END{print count}'           # the last column
# or
$ echo 'chr19   10 11
        chr19   12 15
        chr19   11 29
        chr19   a0 20
       ' |
  awk '{count=count+$3}END{print count}'            # the 3rd column

sum since nth line

$ echo 'chr19   10 11
        chr19   12 15
        chr19   11 29
        chr19   a0 20
       ' |
  awk 'NR>1 {SUM+=$3}END{print SUM}
64

sum for each column

[!NOTE|label:references:]

• Calculate the sum

  $ cat budget.txt
  Budget Jan Feb Mar Apr May Jun Sun
  Mike   10  20  31  52  7   11
  Eggs   1   5   1   16  4   58
  Bread  22  36  17  8   21  16
  Butter 4   5   8   11  36  2
  Total

$ cat budget.txt |
  awk 'NR==1{print} NR>1 && $0!="Total" { rsum=0;for(i=2;i<=NF;i++){ a[i]+=$i;rsum+=$i }; printf "%s\t%3d\n",$0,rsum; fc=NF; a[NF+1]+=rsum } $1=="Total" { printf "Total\t";for(i=2;i<=fc;i++){ printf "%3d\t",a[i] }; printf "%3d\n",a[fc+1] }' |
  column -t
Budget  Jan  Feb  Mar  Apr  May  Jun  Sun
Mike    10   20   31   52   7    11   131
Eggs    1    5    1    16   4    58   85
Bread   22   36   17   8    21   16   120
Butter  4    5    8    11   36   2    66
Total   37   66   57   87   68   87   402

# better format
$ cat budget.txt |
  awk 'NR==1 { print }
       NR>1 && $0!="Total" { rsum=0; for(i=2;i<=NF;i++){ a[i]+=$i;rsum+=$i }; printf ( "%s\t%3d\n",$0,rsum ); fc=NF; a[NF+1]+=rsum }
       $1=="Total" { printf "Total\t"; for(i=2;i<=fc;i++){ printf "%3d\t",a[i] }; printf ( "%3d\n",a[fc+1] ) }
      ' |
  column -t

calculate word count in a file

$ < sample.txt \
    tr -s ' ' '\n' |
    sort |
    uniq -c |
    awk '{print $2,$1}' |
    sort -gk2

• or

  $ cat sample.txt |
        xargs -n1 echo |
        sort |
        uniq -c |
        awk '{print $2,$1}' |
        sort -gk2

• or

  $ awk '{ for(w=1;w<=NF;w++) print $w }' sample.txt |
        sort |
        uniq -c |
        awk '{print $2,$1}' |
        sort -gk2

maximize & minimize

• maximize and second maximize

  $ awk '{ for(i=1;i<=NF;i++) if($i>maxval) maxval=$i; }
           NR%4==0 { print maxval ; maxval= -1}' <<INPUT
      1 2 3
    4 5 6 7 7
    9 7 7 6 5
      4 3 2
      1 2 3
    4 6 7 7 7
    7 7 7 6 5
      4 3 2
  INPUT
  
  9
  7
  
  # alternative using array
  $ awk '{ x=split($0,a);asort(a);if(a[x]>maxval)maxval=a[x] }
           NR%4==0 { print maxval ; maxval= -1}'

field

multiple delimiters

[!NOTE]

• multiple Field Separators in awk
• Using Multiple Delimiters in Awk

• -F"[..]"

  $ echo "  man(1), apropos(1), whatis(1) - display online manual documentation pages" |
    awk -F"[,-]" '{print $1, "\n", $2, "\n", $3, "\n", $4}'
    man(1)
    apropos(1)
    whatis(1)
    display online manual documentation pages

• -F"(..)"

  [!TIP] the \ should be \\\ in "(..)"

  $ echo "man(1), apropos(1), whatis(1) - display online manual documentation pages" |
    awk -F"(\\\([0-9]\\\),?)" \
        '{ for(i=1;i<NF;i++) {
             sub(/ +/, "", $i);
             sub(/ +-? +/, "", $NF);
             if (length($i) != 0) printf ("%s - %s\n", $i, $NF)
         } }'
  man - display online manual documentation pages
  apropos - display online manual documentation pages
  whatis - display online manual documentation pages

  • simprude solution

    # remove everything between/include first `(` to last `)`
    $ echo "man(1), apropos(1), whatis(1) - display online manual documentation pages" |
      sed -r 's/(\(.+\))//g'
    man - display online manual documentation pages

• -F'..'

  $ echo '-foo { {0.000 0.000} {648.0 0.000} {648.0 1980.0} {0.000 1980.0} {0.000 0.000} }' |
    awk -F'}+|{+| ' '{for (i=1; i<=NF; i++) if ($i ~ "[0-9]") print $i}'
  0.000
  0.000
  648.0
  0.000
  648.0
  1980.0
  0.000
  1980.0
  0.000
  0.000

• BEGIN{ FS=".." }

  $ echo '-foo { {0.000 0.000} {648.0 0.000} {648.0 1980.0} {0.000 1980.0} {0.000 0.000} }' |
    awk 'BEGIN{FS="}+|{+| "} {for(i=1;i<=NF;i++) if($i ~ "[0-9]")print $i}'

field separator variable

• multiple separators

  [!NOTE|label:multiple separators]

  • Two field separators (colon and space) in awk
  • How to match space or \s via regex in awk
  • * 8 Powerful Awk Built-in Variables – FS, OFS, RS, ORS, NR, NF, FILENAME, FNR
  • How to split a delimited string into an array in awk?
  • Retrieve information Text/Word from HTML code using awk/sed
  • How to use a shell command to only show the first column and last column in a text file?

    • FS: Input field separator variable

    • OFS: Output Field Separator Variable

  • example target : set both : (colon) and (space/blank) as separators

  # orignal contents
  > git config --list --show-origin --name-only | head -3
  file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   diff.astextplain.textconv
  file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   filter.lfs.clean
  file:C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig   filter.lfs.smudge
  #    +......................................................+
  #                             v
  #                       wanted string
  
  # via `-F <regex>`
  > git config --list --show-origin --name-only | head -3 | awk -F"[: ]" '{print $2":"$3}'
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  
  # or via `FS=`
  > git config --list --show-origin --name-only | head -3 | awk -v FS='[:[:space:]]+' '{print $2":"$3}'   F
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  
  # `[[:blank:]]` or `[[:space:]]`
  > git config --list --show-origin --name-only | head -3 | awk -v FS='[:[:blank:]]+' '{print $2":"$3}'
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  
  > git config --list --show-origin --name-only | head -3 | awk 'BEGIN { FS="[:[:blank:]]+" }; { print $2":"$3 }'
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig
  C:/Users/marslo/AppData/Local/Programs/Git/etc/gitconfig

FS/OFS

$ echo 'foo|dog|cat|mouse|lion|ox|tiger|bar' | awk -v 'FS=|' -v 'OFS=,' '{print $1, $NF}'
foo,bar
# or
$ echo 'foo|dog|cat|mouse|lion|ox|tiger|bar' | awk 'BEGIN {FS="|"; OFS="\t"} {print $1, $NF}'
foo   bar

# FS same with OFS
$ echo 'foo|dog|cat|mouse|lion|ox|tiger|bar' | awk 'BEGIN {FS = OFS = "|"} {print $1, $NF}'
foo|bar

parser

csv

[!NOTE|label:references:]

• geoffroy-aubry/awk-csv-parser
• AWK CSV Parser
• Parse a csv using awk and ignoring commas inside a field
• Output in CSV format using AWK command

cheatsheet

print lines by defined line numbers

[!NOTE|label:references:]

• sample code

  $ head file1.txt file2.txt
  ==> file1.txt <==
  line-01
  line-02
  line-03
  line-04
  line-05
  line-06
  line-07
  line-08
  line-09
  line-10
  
  ==> file2.txt <==
  2
  3
  4
  5
  7

$ awk 'NR==FNR { out[$1]=1; next } { if (out[FNR]==1) print $0 }' file2.txt file1.txt
line-02
line-03
line-04
line-05
line-07

# or
$ awk 'NR==FNR { out[$1]=1; next } out[FNR]' file2.txt file1.txt
line-02
line-03
line-04
line-05
line-07

join and calculate

[!NOTE|label:references:]

• sample code:

  $ head price.txt purchasing.txt
  ==> price.txt <==
  Product Price(USD/Kg) Supplier
  Apple 3.20 Supplier_X
  Orange 3.00 Supplier_Y
  Peach 5.35 Supplier_Y
  Pear 5.00 Supplier_X
  Mango 12.00 Supplier_Y
  Pineapple 7.70 Supplier_X
  
  ==> purchasing.txt <==
  Product Volume(Kg) Date
  Orange 120 2020-04-02
  Apple 400 2020-04-03
  Peach 70 2020-04-05
  Pear 50 2020-04-17
  
  ==> discount.txt <==
  Supplier Discount
  Supplier_X 0.10
  Supplier_Y 0.20

• awk array explanation?

# FNR>1 to skip the header lines from input files
$ awk 'BEGIN { print "PRODUCT COST DATE" }
       FNR>1 && NR==FNR { price[$1]=$2; next }
       FNR>1 { printf "%s $%.2f %s\n",$1, price[$1]*$2, $3}' price.txt purchasing.txt |
  column -t
PRODUCT  COST      DATE
Orange   $360.00   2020-04-02
Apple    $1280.00  2020-04-03
Peach    $374.50   2020-04-05
Pear     $250.00   2020-04-17

# FNR>1 to skip the header lines from input files
$ awk 'fname != FILENAME { fname = FILENAME; idx++ }
        FNR > 1 && idx == 1 { discount[$1] = $2 }
        FNR > 1 && idx == 2 { price[$1] = $2 * ( 1 - discount[$3] ) }
        FNR > 1 && idx == 3 { printf "%s $%.2f %s\n",$1, price[$1]*$2, $3 }
       ' discount.txt price.txt purchasing.txt |
  column -t
Orange  $288.00   2020-04-02
Apple   $1152.00  2020-04-03
Peach   $299.60   2020-04-05
Pear    $225.00   2020-04-17

$ cat file{z,x}
       File: filez
   1   111,111
   2   112,114
   3   113,113

       File: filex
   1   A,bb,111,xxx,nnn
   2   A,cc,112,yyy,nnn
   3   A,dd,113,zzz,ppp

$ awk 'BEGIN { OFS=FS="," }                  # sets input and output field separators to `,`
       NR==FNR {                             # for 1st file only
         a[$1]=$2;next                       # put $1 as index and $2 as value in array a
       } $3 in a && $3 = a[$3]               # if the third field is a key of the array a, replace the third field with a[$3]
      ' filez filex
A,bb,111,xxx,nnn
A,cc,114,yyy,nnn
A,dd,113,zzz,ppp

array diff

diff(){
  awk 'BEGIN{RS=ORS=" "}
       {NR==FNR?a[$0]++:a[$0]--}
       END{for(k in a)if(a[k])print k}' <(echo -n "${!1}") <(echo -n "${!2}")
}

Array1=( "key1" "key2" "key3" "key4" "key5" "key6" "key7" "key8" "key9" "key10" )
Array2=( "key1" "key2" "key3" "key4" "key5" "key6" )
Array3=($(diff Array1[@] Array2[@]))
echo ${Array3[@]}