2

Given a simplified CSV (max one line per row) with many data fields (>50), how can I count the maximum character length for each data field and then export all the counts to a txt file? BTW, I want to ignore the first line of the file which contains the column headings.

For example, given the input

These,are,the,column_headings_which_may_be_very_long_but_they_don't_count
abcdefghij,abcdefghijk,abcdefghijkl,abc
aardvark,bat,cat,dog
ant,bee,cow,abcdefghijklm

The end result could be something like the following, where the first column indicates the data fields in the original file and the second column indicates the maximum length of the field:

1 | 10
2 | 11
3 | 12
4 | 13

i.e., the length of the longest value in column 1 is 10 (abcdefghij), the length of the longest value in column 2 is 11 (abcdefghijk), etc.

I have researched on the site a little bit and found couple ways that can count maximum length in a fairly straightforward manner when a certain data field is specified. For example, use cut and wc commands to count maximum length of the second field in the file:

cut -d, -f2 test.csv | wc -L  

But how can I take the command and loop it over to all the data fields and then output?

5
  • Can you give some sample input?
    – cuonglm
    Commented Sep 15, 2014 at 16:45
  • Sure. You can download the sample file via this link: drive.google.com/file/d/0B3-WolEQUW6yM3NZUGhHaVFmdFk/… the sample file has 10 columns. thanks
    – QY Luo
    Commented Sep 15, 2014 at 17:01
  • I had a problem with your link. Can you please just post a small example in your question? Commented Sep 15, 2014 at 17:24
  • sorry guys, just fixed the link. please try again. thx!
    – QY Luo
    Commented Sep 15, 2014 at 17:26
  • guys, since HalosGhost edited the question with a perfect input example, I am going to take the link down. thank.
    – QY Luo
    Commented Sep 15, 2014 at 20:42

4 Answers 4

2

If I understand your question correctly, this will do what you want:

awk -F, 'NR!=1 { if (max_NF < NF) max_NF = NF;
                 for (i=1; i<=NF; i++) if (max[i] < length($i)) max[i] = length($i) }
         END   { for (i=1; i<=max_NF; i++) printf "%-2d | %d\n", i, max[i] }'
5
  • this works! G-man, you are the man. thx
    – QY Luo
    Commented Sep 15, 2014 at 18:04
  • @StéphaneChazelas: (1) “Note that you've got no guarantee of order with for (i in max) …” Good point. (2) “That would also cover for columns with only empty fields.” Huh? My current solution handles columns with only empty fields. Commented Sep 15, 2014 at 20:22
  • @StéphaneChazelas: I applied your suggestion, however there is nothing returned. Here is the code, am I making any mistake? awk -F, 'NR!=1 { for (i=1; i<=NF; i++) if (max[i] < length($i)) max[i] = length($i) } END { for (i=1; i<=max_NF; i++) printf "%-2d| %d\n", i, max[i] }' 2014-07-08.csv
    – QY Luo
    Commented Sep 15, 2014 at 21:09
  • @QYLuo: You need to add code to set max_NF; see my edit. Commented Sep 15, 2014 at 21:16
  • regarding empty columns, yes, sorry, I had overlooked the fact that accessing max[i] instantiates the array element. Note that length(max) will give you max_NF in GNU awk (but GNU awk only). Commented Sep 15, 2014 at 21:43
0

I don't see your link for the sample file, but you can do this by using awk command.

If you can specify what ever delimiter you may have and exactly what field you need to count.

awk '{ FS = "," } ; { if(NR!=1) gsub(/"/, "", $2) ; print NR "|" length($2) } ' test.csv

You can redirect this output to any file you want.

0

Using Miller (mlr) to calculate the max length of each field's values. The input is read as CSV, and the output is produced as an "xtab" file (one key+value pair on each):

$ mlr --c2x stats1 -a maxlen --fr . file
These_maxlen                                                       10
are_maxlen                                                         11
the_maxlen                                                         12
column_headings_which_may_be_very_long_but_they_don't_count_maxlen 13

The --fr . arguments to the stats1 operation is to calculate the maximum length of all fields with names that match the regular expression . (i.e. every field that is named).

As you can see, Miller retains the field names and adds a _maxlen suffix to each.

To read the CSV file as if its first line was a record rather than the headers, then remove that first line and do the same max calculation:

$ mlr --c2x -N filter -x 'NR == 1' then stats1 -a maxlen --fr . file
1_maxlen 10
2_maxlen 11
3_maxlen 12
4_maxlen 13

With an additional rename operation, we can remove the _maxlen suffix from the names of all fields:

$ mlr --c2x -N filter -x 'NR == 1' then stats1 -a maxlen --fr . then rename -r '(.*)_maxlen$,\1' file
1 10
2 11
3 12
4 13
0

Using Raku (formerly known as Perl_6)

~$ raku -ne 'BEGIN my @a;  
               unless ++$ == 1 { 
                   @a.push: $_.split(",").map: *.chars; 
               };  
             END say( ++$ ~ " | " ~ $_ ) for ([Z] @a).map: *.max;'  file

OR:

~$ raku -ne 'BEGIN my @a; 
               once next; 
               @a.push: $_.split(",").map: *.chars;  
             END say( ++$ ~ " | " ~ $_ ) for ([Z] @a).map: *.max;'  file

Here's an answer coded in Raku, a member of the Perl-family of programming languages. Raku features high level support for Unicode, so that character-counting is accurate.

We start by using the (awk-like) -ne linewise non-autoprinting command line flags:

  1. An array is declared in a BEGIN block,
  2. To remove the header-line (first answer), an anonymous counter (++$) is used to skip the first line. Alternatively (second answer), once next can be used,
  3. Within the body of the block/loop, each line is read-in, split on commas, and each resultant element is mapped into to obtain the number of chars (characters). These are pushed onto @a array,
  4. After all lines are read-in, the END block executes. The @a array is [Z] transformed such that rows and columns are interchanged. Once this happens we can then map into elements at each array position, and obtain the max. Finally the data is output, using an ++$ anonymous counter to provide line-numbers (string-concatenation is accomplished with ~ tilde).

Sample Input:

These,are,the,column_headings_which_may_be_very_long_but_they_don't_count
abcdefghij,abcdefghijk,abcdefghijkl,abc
aardvark,bat,cat,dog
ant,bee,cow,abcdefghijklm

Sample Output:

1 | 10
2 | 11
3 | 12
4 | 13

Note: There's no error-checking on number-of-columns per line: the [Z] transform will simply truncate rows with an excessive number to the common (i.e. to 4 columns in the example). See the first link below to accomplish this task in Raku regardless of the number of columns per line.

https://unix.stackexchange.com/a/774828/227738
https://docs.raku.org/language/unicode
https://raku.org

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .