Format with output

Shell Programming and Scripting
1

I have written some scripts that resulted in the table below (Column1 is ITEM, Column2 is Group, Column3 is Category and Column4 is Quantity) but I want the output in another format:

Input:

K123 X CATA 3
K123 Y CATA 4
K123 Z CATA 2
K123 X CATB 5
K123 Y CATB 2
K123 Z CATB 2
B65 M CATB 3
B65 N CATB 4
B85 X CATA 1
B85 Y CATA 4
TR4 X CATA 3
TR4 Y CATA 2
TR4 Z CATA 2
TR4 X CATB 2
TR4 Y CATB 3
TR4 Z CATB 2
U23 X CATA 3
U23 Y CATA 1
U23 Z CATA 2
U23 P CATA 2

Ouput:

ITEM CATA/CATB
K123 X3Y4Z2/X5Y2Z2
B65  ---/M3N4
B85 X1Y4/---
TR4 X3Y2Z2/X2Y3Z2
U23 X3Y1Z2P2/---
2

Any attempt of yours?

Are CATA nad CATB fixed categories, or do they change within the input file?

3

CATA and CATB are fixed.

4

OK.

Any attempt from your side?

5

I can do for each Category on different lines:

$ grep CATA ttt.txt | awk '{A[$1]=A[$1]$2$4}END{for (i in A) print i" "A}'
K123 X3Y4Z2
U23 X3Y1Z2P2
TR4 X3Y2Z2
B85 X1Y4


$ grep CATB ttt.txt | awk '{A[$1]=A[$1]$2$4}END{for (i in A) print i" "A}'
K123 X5Y2Z2
TR4 X2Y3Z2
B65 M3N4
6

Hello aydj,

Not with a single awk but following script may help you.

cat check_format1211.ksh 
awk '{V=$3;{if($3=="CATA"){A[$1 OFS $3]=A[$1 OFS $3]?A[$1 OFS $3] $2$4:$2$4} else {B[$1 OFS $3]=B[$1 OFS $3]?B[$1 OFS $3] $2$4:$2$4}}} END{for(i in A){print i OFS A >> "CATA_results"} for(j in B){print j OFS B[j] >> "CATB_results"}}' format_output121

awk 'BEGIN{print "ITEM CATA/CATB" >> "FINAL_OUTPUT"} FNR==NR{A[$1]=$3;next} ($1 in A){print $1 OFS $3"/"A[$1]} !($1 in A){print $1 OFS $3"/-----"}' CATB_results CATA_results  >> FINAL_OUTPUT

awk 'FNR==NR{A[$1]=$3;next} !($1 in A){print $1 OFS "-----/"$3}' CATA_results CATB_results >> FINAL_OUTPUT

Output file named FINAL_OUTPUT will be as follows.

cat FINAL_OUTPUT 
ITEM CATA/CATB
B85 X1Y4/-----
U23 X3Y1Z2P2/-----
K123 X3Y4Z2/X5Y2Z2
TR4 X3Y2Z2/X2Y3Z2
B65 -----/M3N4

Thanks,
R. Singh

7

Try

awk     '               {I[$1]
                         for (i=1; i<=Ccnt && $3 != C; i++);
                         if (i > Ccnt) C[++Ccnt]=$3
                         T[$1, $3]=T[$1,$3] $2 $4}
         END            {printf "ITEM\t"
                         for (j=1; j<=Ccnt; j++)
                                {printf "%s%s", DL, C[j]
                                 DL="/"
                                }
                         printf "\n"
                         for (i in I)   {DL=""
                                         printf "%s\t", i
                                         for (j=1; j<=Ccnt; j++)
                                                {X=T[i,C[j]]
                                                 printf "%s%s", DL, X?X:"---"
                                                 DL="/"
                                                }
                                         printf "\n"
                                        }
                        }
        ' file
ITEM    CATA/CATB
K123    X3Y4Z2/X5Y2Z2
U23     X3Y1Z2P2/---
B65     ---/M3N4
B85     X1Y4/---
TR4     X3Y2Z2/X2Y3Z2

If output order is important, try using an approach similar to the category capture...

2 Likes
8

Single awk can do your job, if order doesn't matter then you can process in END block also, current script reads same input twice and take care of output order.

Input

akshay@nio:/tmp$ cat infile
K123 X CATA 3
K123 Y CATA 4
K123 Z CATA 2
K123 X CATB 5
K123 Y CATB 2
K123 Z CATB 2
B65 M CATB 3
B65 N CATB 4
B85 X CATA 1
B85 Y CATA 4
TR4 X CATA 3
TR4 Y CATA 2
TR4 Z CATA 2
TR4 X CATB 2
TR4 Y CATB 3
TR4 Z CATB 2
U23 X CATA 3
U23 Y CATA 1
U23 Z CATA 2
U23 P CATA 2

Script

akshay@nio:/tmp$ cat test.awk 
       NR==1{
		key1 = "CATA"
		key2 = "CATB"
		IFS  = "/"		

		print "ITEM",key1 IFS key2
	    }

     FNR==NR{
		A[$1,$3] = sprintf("%s%s%s",A[$1,$3],$2,$4) 
		next 
	    }

	   (($1,key1) in A || ($1,key2) in A){

		print $1, \
		      (A[$1,key1] = ($1,key1) in A ? A[$1,key1] : "---") IFS \
		      (A[$1,key2] = ($1,key2) in A ? A[$1,key2] : "---")

		delete A[$1,key1] 
		delete A[$1,key2] 
	    }

Output

akshay@nio:/tmp$ awk -f test.awk infile infile
ITEM CATA/CATB
K123 X3Y4Z2/X5Y2Z2
B65 ---/M3N4
B85 X1Y4/---
TR4 X3Y2Z2/X2Y3Z2
U23 X3Y1Z2P2/---

---------- Post updated at 10:02 PM ---------- Previous update was at 09:44 PM ----------

---edit--

Processing the same in END block is as follows, which reads input file only once.

akshay@nio:/tmp$ cat test2.awk 
       NR==1{
		key1 = "CATA"
		key2 = "CATB"
		IFS  = "/"		

		print "ITEM",key1 IFS key2
	    }

            {
		A[$1,$3] = sprintf("%s%s%s",A[$1,$3],$2,$4) 
	    }

	 END{
		for(i in A)
		   {
		     split(i,I,SUBSEP)

		     if((I[1],key1) in A || (I[1],key2) in A)
		     {
			print I[1], \
		      	      (A[I[1],key1] = (I[1],key1) in A ? A[I[1],key1] : "---") IFS \
		      	      (A[I[1],key2] = (I[1],key2) in A ? A[I[1],key2] : "---")

			delete A[I[1],key1] 
			delete A[I[1],key2]
		     }

		  } 
	     }

Resulting

akshay@nio:/tmp$ awk -f test2.awk infile
ITEM CATA/CATB
TR4 X3Y2Z2/X2Y3Z2
B65 ---/M3N4
K123 X3Y4Z2/X5Y2Z2
B85 X1Y4/---
U23 X3Y1Z2P2/---
2 Likes