6

I have a large number of individual files that contain six columns each (number of rows can vary). As a simple example:

1   0   0   0   0   0

0   1   1   1   0   0

I am trying to identify how many unique columns I have (i.e. numbers and their order match), in this case it would be 3.

Is there a simple one-liner to do this? I know it is easy to compare one column with another column, but how to find identical columns?

8

You can count the unique columns with following pipe:

$ awk '{for (i=1; i<=NF; ++i) a[i]=a[i]$i; } END { for (i in a) print a[i] }' foo \
  | sort -u | wc -l

The awk command transposes your input, the resulting lines are sorted, only unique lines are kept (-u) and at the end all (unique) lines (i.e. the transposed columns) are counted (wc -l).

Note that NF is a builtin awk variable and is automatically set to the number of fields in the current record. $i references the i-th field and END guards the following block such that it is executed after all records are processed. Awk uses by default blank-non-blank field delimiting.

  • Thank you! This is just what I was looking for! Is there a simple way to adjust this so I can do this to several files at once? When adding multiple input files, it appears to read those all in at once before sorting and counting. – Sarah Oct 14 '14 at 13:24
3

(((...))), but how to find identical columns?

$ printf '%s\n' '1 0 0 0 0 0' '0 1 1 1 0 0' | awk -vSUBSEP='=' '
    { for (i=1; i<NF; i++)
        for (j=i+1; j<=NF; j++)
          if ($i==$j)
            M[i,j]++
    }
    END{ for (m in M) if (M[m]==NR) print m }'
5=6
2=3
2=4
3=4

For all columns i<j of each row, increment M[i,j] whenever the values of those colums are equal. So M[i,j]==NR after reading NR rows means, the values were identical for all rows read.

2

This question got me interesting and I wanted to follow an approach which I couldn't figure out exactly and got some wonderful help after I posted as a different question . You could understand the approach that am trying to follow from the question I have posted.

I got 2 more solutions for this problem (one from Gnouc's answer which is a perl solution and another from John's solution combined with my solution).

#The variable appended_input will remove spaces/tabs and just append the rows. 
#Modify the file name in this line. Here I use inputfile as the filename. 

appended_input=$(column -s '\t' inputfile | tr -d '[:space:]') ;

#The array variable will store each column-wise value as an array element.  
#I use sort to find the number of unique elements.

array=($(
    for ((i=0; i<6; i++))
    do
        new=${appended_input:$i:1}
        for ((j=i+6; j<${#appended_input}; j=j+6))
        do 
            new="$new${appended_input:$j:1}"
        done
        echo "$new"
    done
    )) | echo "${array[@]}" | tr ' ' '\n' | sort -u | tr '\n' ' '

Testing

My input file is as below.

1 0 0 1 0 0
0 1 1 0 0 0
1 1 1 1 1 0
1 0 0 1 0 1
1 0 0 1 0 1

After running the above script, I get the output as,

00011 00100 01100 10111

You could have a wc -w as a final pipe and you would get the output as just 4 instead of the unique column values as above.

1

Here's a little sed script that I actually wrote for myself not too long ago. I have just had a little fun updating it, though. It does the whole job by itself:

cdup() { _u= _d= 
         case "${1#-}" in (U) _u='\)\(';; (D) _d='\
';      _d="$_d\\2$_d";; (*) ! :;;esac && shift
sed 's/  */  /g;H;1h;1d;x;:t
     s/   *\(.*\(\n\)\)\([^ ]\{1,\}\) */\2\3 \1/;tt
     s/ /  /g;h;$!d;s/.*/  &  /;:n
     /\( \([^ ]\{1,\}\) \)\(.*'"$_u\1${_d:+.*}\)/{ 
         s//\3${_d:- }"'/;s/$\n*//;tn
};   s/.* \n\n*//;s/  *//;s// /g
     s/\n\n/ /g;y/ \n/\n /' "$@"                        
     unset -v _u _d
}  

sed works two lines at a time at rearranging the fields in its input to align by column - and stacks its work in the hold buffer between each line. It doesn't delimit on anything other than the original space delimiter in your sample (and I originally wrote it to handle $IFS separated arg arrays) - and so, provided that delimiter is solid, fields of any reasonable length containing most any character but the delimiter should all work just as well.

So it does (L1COL1\nL2COL1) (L1COL2\nL2COL2)...((L[12]C1)\nL3COL1)... for as long as it must until it encounters the last line. By the time it does it has already so neatly arranged all of the data in its memory that it is a trivial matter to check for duplicates - and so it prints columns only once no matter how many times they appear in input:

cdup <<\COLS
1 A 4 Z 1
2 B 3 Y 2
3 C 2 X 3
4 D 1 W 4  
5 E 0 U 5
COLS

OUTPUT

A B C D E
4 3 2 1 0
Z Y X W U
1 2 3 4 5

But with the -U flag it prints only unique items so...

cdup -U <<\COLS
1 A 4 Z 1
2 B 3 Y 2
3 C 2 X 3
4 D 1 W 4  
5 E 0 U 5
COLS

...gets...

A B C D E
4 3 2 1 0
Z Y X W U

Or -D for duplicates only, with an extra record per duplicate column appearance. Not so bad...

cdup -D <<\DATA
1 1 A A 4 Z 1
2 2 B B 3 Y 2
3 3 C C 2 X 3
4 4 D D 1 W 4
5 5 E E 0 U 5
DATA
1 2 3 4 5
1 2 3 4 5
A B C D E 
  • Phew, I salute your ability to come up with sed cryptomagic. – iruvar Oct 15 '14 at 1:16
  • @1_CR - thanks very much. I'm just stubborn is all. I was pretty pleased with the unique thing - I was already capturing a compare like \( [^ ]* \)\(.*\1\) then saving only \2 so it turned out all I had to do was u='\)\('...\( [^ ]* \)\(.*'"$u"'\1\) and suddenly I was saving all but the matches. Anyway, I see you've got one... a little crytpomagical, too, isn't it? – mikeserv Oct 15 '14 at 1:28
  • Mikeserv, lol, there's the small matter of degree! – iruvar Oct 15 '14 at 2:44
  • @1_CR - I meant literally crypto-magic - you're employing a cryptography engine to mathematically evaluate the likelihood of matches. That's awesome - it's not some obfuscated (if also awesome) regex. – mikeserv Oct 15 '14 at 2:53
1

Here's a gawk solution that uses coprocesses to feed each column to a separate instance of sha256sum and reports the total number of unique hashes (the number of unique hashes should coincide with the number of unique columns given that hash collision likelihood with sha256sum is statistically insignificant). While some may consider this an egregious hack, one advantage this approach has over some of the others is that it doesn't attempt to concatenate/transpose the data and is therefore relatively memory-efficient.

awk 'BEGIN{for(i=1; i<=6; ++i){s=sprintf("%*s", i+1, ""); a[i]="sha256sum"s}}
    {for (i=1; i<=6; ++i) print $i |& a[i]}
    END{com= "sort | uniq | wc -l"
    for (i=1; i<=6; ++i){close(a[i], "to"); a[i] |& getline x;
    close(a[i]); print x | com};
    close(com)}' file 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.