AWK command of add column to count of grouped column

user_10510908 0
Viewed 80

I have a data set tab separated like this: (file.txt)

A  B
1  111
1  111
1  112
1  113
1  113
1  113
1  113
2  113
2  113
2  113

I want to add a new C column to show count of grouped A and B

Desired output:

A  B    C
1  111  2
1  111  2
1  112  1
1  113  4
1  113  4
1  113  4
1  113  4
2  113  3
2  113  3
2  113  3

I have tried this:

awk 'BEGIN{ FS=OFS="\t" }
  NR==FNR{ 
    if (FNR>1) a[$2]+=$3
    next
  }
  { $(NF+1)=(FNR==1 ? "C" : a[$2]) }
  1
' file.txt file.txt > file2.txt
awk
2 Answers

Could you please try following, With shown samples.

awk '
FNR==NR{
  count[$1,$2]++
  next
} 
FNR==1{
  print $0,"C"
  next
}
{
  print $0,count[$1,$2]
}
' Input_file  Input_file

Add BEGIN{FS=OFS="\t"} in above code in case your data is tab delimited.

Explanation: Adding detailed explanation for above.

awk '                      ##Starting awk program from here.
FNR==NR{                   ##Checking condition if FNR==NR which will be TRUE when first time Input_file being read.
  count[$1,$2]++           ##Creating count with index of 1st and 2nd field and increasing its count.
  next                     ##next will skip further statements from here.
} 
FNR==1{                    ##Checking condition if this is 1st line then do following.
  print $0,"C"             ##Printing current line with C heading here.
  next                     ##next will skip further statements from here.
}
{
  print $0,count[$1,$2]    ##Printing current line along with count with index of 1st and 2nd field.
}
' Input_file  Input_file   ##Mentioning Input_file(s) here.

Problem in OP's attempt: OP was adding $3 in values(though logic looked ok) but there is NO 3rd field present in Input_file so that's why it was not working. Also OP was using index as 2nd field but as per OP's comments it should be 1st and 2nd fields.

user_58665800

You might consider using GNU Datamash, e.g.:

datamash -HW groupby 1,2 count 1 < file.txt | column -t

Output:

GroupBy(A)  GroupBy(B)  count(A)
1           111         2
1           112         1
1           113         4
2           113         3
user_13313990
Related