Understanding AWK and CSV files

user_12543092 0
Viewed 146

How can I write an AWK program that analyses a list of fields in CSV files, count the number of each different string in the specified field, and print out the count of each string that is found? I have only coded in C and Java, so I am completely confused on the syntax of AWK. I understand the simplest of concepts, however, AWK is structured much differently. Any time is appreciated, thank you!

BEGIN {
    FS = ""
}
{
    for(i = 1; i <= NF; i++) 
        freq[$i]++
    PROCINFO ["sorted_in"] = "@val_num_desc" #this got the desired result
}
END {
    for {this in freq) 
        printf "%s\t%d\n", this, freq[this]
}

On a CSV file containing:

Field1, Field2, Field3, Field4
A, B, C, D
A, E, F, G
Z, E, C, D
Z, W, C, Q

I am able to obtain the result:

A     2
B     1
C     3
Q     1
D     1
E     2
F     1
,     12
G     1
W     1
Field1,Field2,Field3,Field4     1
Z     2

This is the desired result:

A       10
C       7
D       2
E       2
Z       2
B       1
Q       1
Field1  1
Field2  1
F       1
Field3  1
G       1
Field4  1
W       1

There is an edit to my code which is commented.

csv awk bash
2 Answers

Fixed your code:

$ awk '
BEGIN {                                     # you need BEGIN block for FS
    FS = ", *"                              # your data had ", " and "," seps
}                                           # ... based on your sample output
{
    for(i = 1; i <= NF; i++)
        freq[$i]++
}
END {
    for(this in freq)                       # fixed a parenthesis
        printf "%s\t%d\n", this, freq[this]
}' file

Output (using GNU awk. Other awks displayed output in different order):

A       2
B       1
C       3
Q       1
D       2
Field1  1
E       2
Field2  1
F       1
Field3  1
G       1
Field4  1
W       1
Z       2
user_41623560

AWK really isn't the right tool for this job. While AWK can interpret Comma or Tab separated data, it has no concept of field enclosures or escapes. So it could handle a simple example like:

Month,Day
January,Sunday
February,Monday

but would fail with this valid example:

Month,Day
January,"Sunday"
February,"Monday"

Because of that, I would recommend considering another language. Something like Python:

import csv
o = open('a.csv')
for m in csv.DictReader(o):
   print(m)

https://docs.python.org/library/csv.html

Or Ruby:

require 'csv'
CSV.table('a.csv').each do |m|
   p m
end

https://ruby-doc.org/stdlib/libdoc/csv/rdoc/CSV.html

Or even PHP:

<?php
$r = fopen('a.csv', 'r');
$a_head = fgetcsv($r);
while (true) {
   $a_row = fgetcsv($r);
   if (feof($r)) {
      break;
   }
   $m_row = array_combine($a_head, $a_row);
   print_r($m_row);
}

https://php.net/function.fgetcsv

user_10022600
Related