mjhjmtu

I have a 5000+ space separated lines as below:

Item_A: Acou#1 Bla#5

Item_B: Acou#1 Elfa#2 Flq#2

Item_C: Acou#1 Bla#4 Elfa#2 Flq#2

Item_D: Agly#3 Bla#4 Elfa#2

I want to make a table with common headers for all and quantity each in a table as below,

 Acou Agly Bla Elfa Flq

Item_A: 1 0 5 0 0

Item_B: 1 0 0 2 2

Item_C: 1 0 4 2 2

Item_D: 0 3 4 2 0

I used to grep lines containing "Acou", then, "Bla" and so on. Then edit in excel to quantify them and concatenate all separate files to one file. However, it took a lot of time.

If you don't mind throwing GNU datamash into the mix, then you could simply serialize the entries and then crosstabulate them:

awk '
 for (i=2;i<=NF;i++) split($i,a,"#"); print $1,a[1],a[2]' OFS='t' file | 
 datamash --filler=0 crosstab 1,2 count 3
 Acou Agly Bla Elfa Flq
Item_A: 1 0 1 0 0
Item_B: 1 0 0 1 1
Item_C: 1 0 1 1 1
Item_D: 0 1 1 1 0

Alternatively, with GNU awk (which allows multidimensional arrays):

gawk '
 BEGIN 
 OFS="t";
 PROCINFO["sorted_in"] = "@ind_str_asc";
 
 
 for (i=2;i<=NF;i++) 
 split($i,a,"#"); 
 h[a[1]] = 1;
 t[$1][a[1]] += a[2];
 
 
 END 
 for (j in h) printf("t%s", j);
 printf "n";
 for (i in t) 
 printf("%s",i);
 for (j in h) 
 printf("t%d", j in t[i] ? t[i][j] : 0);
 printf "n";
 
 ' file
 Acou Agly Bla Elfa Flq
Item_A: 1 0 5 0 0
Item_B: 1 0 0 2 2
Item_C: 1 0 4 2 2
Item_D: 0 3 4 2 0

BEGIN OFS = "t" 

# Collect headers from data
FNR == NR 
 for (i = 2; i <= NF; ++i)
 if (!($i in heads))
 heads[$i]
 next


# Output header
FNR == 1 
 line = "Items"
 for (j in heads)
 line = line OFS j
 print line



 line = $1
 # Iterate through the header items, testing each field against it
 for (j in heads) 
 found = 0 # assume not found
 for (i = 2; !found && i <= NF; ++i)
 if ($i == j)
 found = 1 # matches header
 line = line OFS found
 
 print line

Running this on your data (after having removed empty lines):

$ awk -f script.awk file file
Items Acou#1 Bla#4 Bla#5 Elfa#2 Agly#3 Flq#2
Item_A: 1 0 1 0 0 0
Item_B: 1 0 0 1 0 1
Item_C: 1 1 0 1 0 1
Item_D: 0 1 0 1 1 0

Note that you have to specify the input data file twice. This is because we're scanning it two times. In the first scan we're collecting the data items on each line (the FNR == NR block). On the second scan, we test each collected data item (the headers) against the data on each line.

The output is simply 0 if the field in the header is not present in the data on that line, and 1 if it is. This is not quite what you asked for, so...

A variation that truncates the headers at the # and uses the part after the # as the data to display:

BEGIN OFS = "t" 

# Collect headers from data
FNR == NR 
 for (i = 2; i <= NF; ++i) 
 split($i, h, "#")
 if (!(h[1] in heads))
 heads[h[1]]
 
 next


# Output header
FNR == 1 
 line = "Items"
 for (j in heads)
 line = line OFS j
 print line



 line = $1
 # Iterate through the header items, testing each field against it
 for (j in heads) 
 found = 0 # assume not found
 for (i = 2; !found && i <= NF; ++i) 
 split($i, h, "#")
 if (h[1] == j)
 found = h[2] # matches header
 
 line = line OFS found
 
 print line

Running it:

$ awk -f script.awk file file
Items Elfa Bla Acou Agly Flq
Item_A: 0 5 1 0 0
Item_B: 2 0 1 0 2
Item_C: 2 4 1 0 2
Item_D: 2 4 0 3 0

Note that the order of the columns is not necessarily sorted (since they are stored as keys in an associative array). I'm leaving it as an exercise to the reader to sort them.