mjhjmtu

I'm looking for an awk solution to get from this table (with x number of names in column 1, and different lengths of column 2 which is ";" separated):

dataframe.txt:

name1 1;2;4;8
name2 4;5;7
name3 8
name4 11;12
namex 20;21

to this matrix with present 1 and non present 0:

matrix.txt:

 1 2 4 5 7 8 11 12 20 21
name1 1 1 1 0 0 1 0 0 0 0 
name2 0 0 1 1 1 0 0 0 0 0
name3 0 0 0 0 0 1 0 0 0 0
name4 0 0 0 0 0 0 1 1 0 0
namex 0 0 0 0 0 0 0 0 1 1

Awk solution:

awk 'BEGIN
 h = "1 2 3 4 5 6 7 8 11 12 20 21";
 len = split(h, head);
 print "tt" h
 
 
 printf "%st", $1;
 for (i = 1; i <= len; i++)
 printf "%s%d", (i == 1? "" : OFS), ($2 ~ "\<" head[i] "\>");
 print "" 
 ' file

• 
  h = "1 2 3 4 5 6 7 8 11 12 20 21" - header line


• 
  len = split(h, head) - split h line into array head where indices are ordered positions starting from 1 and values are crucial values obtained via splitting; len contains an array size


• 
  print "tt" h - print the header line with leading tab characters


• 
  printf "%st", $1; - print the 1st field $1
  


• 
  for (i = 1; i <= len; i++) - iterate through head items
  
  
  • 
    $2 ~ "\<" head[i] "\>" - check if the 2nd field $2 contains currently accessed item head[i]
    
  
  

The output:

 1 2 3 4 5 6 7 8 11 12 20 21
name1 1 1 0 1 0 0 0 1 0 0 0 0
name2 0 0 0 1 1 0 1 0 0 0 0 0
name3 0 0 0 0 0 0 0 1 0 0 0 0
name4 0 0 0 0 0 0 0 0 1 1 0 0
namex 0 0 0 0 0 0 0 0 0 0 1 1

Another awk approach which generate header first by itself and then fill the matrix with 1 for each key in array head exist in current input line otherwise with 0 if doesn't exist.

We used grep to fetch only -o numbers which matches the empty string at the left edge of a number b (same as < we used in awk for both left&right sides).

awk 'NR==FNR !head[$1]++; next 
 printf $1; for (x in head) printf (x?FS:"") ($0 ~ "\<" x "\>") ; print ""
' <(grep -o 'b[0-9]+' infile) infile

The output is:

name1 1 1 1 0 0 1 0 0 0 0
name2 0 0 1 1 1 0 0 0 0 0
name3 0 0 0 0 0 1 0 0 0 0
name4 0 0 0 0 0 0 1 1 0 0
namex 0 0 0 0 0 0 0 0 1 1

You could generate the header and insert in first line in input file, then the awk would be easy as:

awk 'NR==1 split($0, head); next 
 printf $1; for (x in head) printf (x?FS:"") ($2 ~ "\<" head[x] "\>" ) ; 
print "" ' infile