Count columns that are non-empty per line


 
Thread Tools Search this Thread
Top Forums Programming Count columns that are non-empty per line
# 1  
Old 09-19-2018
Count columns that are non-empty per line

The file is similar to the attached.
Thousands of columns, delimiter is tab, with many columns containing free text and space separated.
I want to get the count of columns with non-empty entries.

eg.Col1=10, Col6=5, Col8=1
awk preferred
# 2  
Old 09-19-2018
Any attempts / ideas / thoughts from your side?
# 3  
Old 09-19-2018
Code:
awk -F "\t" '{ for(N=1; N<=NF; N++) if($N==""); {print NF}}'  ex.txt

This gives me NF=14, but I want the each col. in the file as header and another line with the number of non-empty lines for each.
So, not sure I am approaching this right.

Last edited by RudiC; 09-19-2018 at 02:08 PM..
# 4  
Old 09-19-2018
How about

Code:
awk -F "\t" '
       {for (N=1; N<=NF; N++) if ($N!="") CNT[N]++}
END    {for (N=1; N<=NF; N++) print "col" N ":", CNT[N]
       }
' /tmp/ex.txt 
col1: 10
col2: 10
col3: 10
col4: 2
col5: 2
col6: 6
col7: 5
col8: 2
col9: 4
col10: 1
col11: 3
col12: 3
col13: 1
col14: 10

This User Gave Thanks to RudiC For This Post:
# 5  
Old 09-19-2018
Yes, that worked perfectly. Thank you!
So, let me understand your approach.
First, you are looking for Non-empty cells, and then making a count of such cells?
Code:
($N!="") CNT[N]++

Then you are printing
Code:
 print "col" N ":", CNT[N]

the col number and the count?




A request: Assuming the file has a header, how to modify such that instead of Col number, the ColHeader is printed?
many Thanks
# 6  
Old 09-19-2018
It always helps to post samples, so people don't have to dream up some but can start working on a solution.


Would that be a one line header? No empty fields in it?


And yes, I count up a counter (array) per field, and, in the END section, print those.
# 7  
Old 09-19-2018
The sample you posted earlier seems to have a header line. And DOS line terminators (<CR> = \015 = 0x0D = \r = ^M). Try
Code:
awk -F "\t" '
        {sub ("\r","")
        }
NR == 1 {N = split ($0, HD); next
        }
        {for (N=1; N<=NF; N++) if ($N!="") CNT[N]++}
END     {for (N=1; N<=NF; N++) print HD[N] ":", CNT[N]
        }
' /tmp/ex.txt 
ID1: 9
ID2: 9
ID3: 9
Status1: 1
Status2: 1
Status3: 5
Status4: 4
Status5: 1
Status6: 3
..: 
..: 2
..: 2
..: 
StatusX: 2

This User Gave Thanks to RudiC For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Count the pipes "|" in line and delete line if count greter then number.

Hello, I have been working on Awk/sed one liner which counts the number of occurrences of '|' in pipe separated lines of file and delete the line from files if count exceeds "17". i.e need to get records having exact 17 pipe separated fields(no more or less) currently i have below : awk... (1 Reply)
Discussion started by: ketanraut
1 Replies

2. Shell Programming and Scripting

Need a program that read a file line by line and prints out lines 1, 2 & 3 after an empty line...

Hello, I need a program that read a file line by line and prints out lines 1, 2 & 3 after an empty line... An example of entries in the file would be: SRVXPAPI001 ERRO JUN24 07:28:34 1775 REASON= 0000, PROCID= #E506 #1065: TPCIPPR, INDEX= 003F ... (8 Replies)
Discussion started by: Ferocci
8 Replies

3. UNIX for Dummies Questions & Answers

Filling in the empty columns with rows above them

Hi, I have a text file where some of the values in columns are missing: Input: QhFudW3dKSYL2NTQUA 1 11133999-11134048 uhIp9KxB6USIy16CEY 1 11126760-11126774 11126775-11126805 11128065-11128068 6epeZ6CTNW4Au8uXys 1 24480823-24480872 Tp3nvutucBZ13CjuXc 1 155204870-155204891... (3 Replies)
Discussion started by: evelibertine
3 Replies

4. UNIX for Dummies Questions & Answers

Filling in the empty columns with the rows above them

I have a text file that looks like the following: 5.644 39.2% 0.00 0.50 rs1695626 4 -0.003 0.21% 0.03 0.73 rs1763326 3 -0.001 0.03% 0.00 0.89 5.645 39.2% 0.00 0.50 rs770721 2 -0.002 0.07% 0.01 0.84... (7 Replies)
Discussion started by: evelibertine
7 Replies

5. UNIX for Dummies Questions & Answers

Filling in the empty columns using the rows above them

I have a text file that looks like the following: rs529715 CFD rs1550758 CIDEB Magmas rs12542019 CPNE1 RBM12 rs10515181 CPNE1 RBM12 rs4411112 CPT1A Some rows have 1 column, whereas some have 2. The ones that have 2 columns have one column (1st column) that start with rs. I need to... (6 Replies)
Discussion started by: evelibertine
6 Replies

6. UNIX for Dummies Questions & Answers

Deleting all rows with empty columns

I have a text file that looks like this: 1 rs523634 8.22486 1 1 rs585160 8.22488 1 rs497228 8.2249 1 1 rs600933 8.225 1 rs480106 8.22531 1 rs600199 8.22533 1 rs529015 8.22534 1 rs598894 8.22534 I want to delete the rows with empty... (2 Replies)
Discussion started by: evelibertine
2 Replies

7. Shell Programming and Scripting

Fill the empty line by adding line before blank line

FIle A "A" 2 aa 34 3 ac 5 cd "B" 3 hu 67 4 fg 5 gy output shud be A"" 2 aa 34 "A" 3 ac 34 "A" 5 cd 34 "B" 3 hu 67 "B" 4 fg 67 "B" 5 gy 67 (6 Replies)
Discussion started by: cdfd123
6 Replies

8. Shell Programming and Scripting

count words and empty files

Hello, I will count words in a file (or more files) and count (if given up) empty files (test -z?), how can I do this? Like this: There are "108" words in "3" files There are "2" empty files Thanks for your reaction. Regards, Arjan Engbers (My English is not good, I hope you... (4 Replies)
Discussion started by: arjanengbers
4 Replies

9. UNIX for Dummies Questions & Answers

Merging Non-Empty Columns within a CSV

I am trying to place all my data in a single row (order doesn't matter). Note I am a Unix novice, so please go easy on me. Here is an example Raw data: row# (1) 45 64 23 (2) 32 1 6 56 (3) 32 45 Needs to be like this: row# (1) 45 (2) 32 (3) 32 ... (2 Replies)
Discussion started by: mmann1123
2 Replies

10. UNIX for Dummies Questions & Answers

Count of Field for Non-Empty

Hi Guys, I wanted to count the number of records for a particular field of a file. whose fields are separated by comma"," I fI use this command. cat "filename" cut -sd "," -f13 | wc -l This shows all the lines count including the blank values for the field number 13. I wanted to count... (2 Replies)
Discussion started by: Swapna173
2 Replies
Login or Register to Ask a Question