Column to Row Data.

Shell Programming and Scripting
1

HI Guys,

I have below Input :-

X	L1	5
Y	L1	10
Z	L1	15
X	L2	20
Y	L2	12
Z	L2	15
X	L3	100
Y	L3	
Z	L3	300

Output:-

ID	L1	L2	L3
X	5	10	15
Y	20	12	15
Z	100	Null	300

I have used Below awk command but it massing my header.

nawk -v w=10 '{a[$1]=($1 in a)?a[$1] OFS sprintf("%-*s",w,$3):sprintf("%-*s",w,$3);h[$2]}END{for(i in h) printf("\t%-*s", w,i);print "";for(i in a) print i,a}' OFS='\t'
2

Your output does NOT reflect the input data; e.g. Z / L1 should be 15, not 100. Try

awk     '       {LN[$1]; HD[$2]; MX[$1,$2]=$3}
         END    {               printf "%10s", "ID"; for (i in HD) printf "%10s", i; print "";
                 for (j in LN) {printf "%10s",j;     for (i in HD) printf "%10s", MX[j,i]?MX[j,i]:"NULL"; print ""}
                }
        ' file
        ID        L1        L2        L3
         X         5        20       100
         Y        10        12      NULL
         Z        15        15       300
1 Like
3

Thanks a lot for Code

Yes You right and one more this ....

It Replace 0 to Null....I want to replace on "" to Null"

Can you try with This Input :

X	L1	5
Y	L1	10
Z	L1	15
X	L2	20
Y	L2	
Z	L2	15
X	L3	0
Y	L3	0
Z	L3	300
4

Can't you do it on your own?

1 Like
5

Thanks Buddy ....I will try from my End.

6

Just a hint - look at the MX[j,i]?MX[j,i]:"NULL" (conditional assinment) part; it now checks the array value if 0 or empty, make it check if empty only.

7

I tried But No Luck :frowning:

8

OK - replace the existing with (MX[j,i]!="")?MX[j,i]:"NULL";

9

You could have also fixed it by changing:

awk     '       {LN[$1]; HD[$2]; MX[$1,$2]=$3}

to:

awk     '       {LN[$1]; HD[$2]; MX[$1,$2]=((NF>2)?$3:"Null")}
10

Nice idea. In principle, yes. But, if one entry is missing entirely (Z / L1), post#8 would yield

        ID        L1        L2        L3
         X         5        20         0
         Y        10      NULL         0
         Z      NULL        15       300

, while post#9 would yield

        ID        L1        L2        L3
         X         5        20         0
         Y        10      Null         0
         Z                  15       300

Please note there's a closing parenthesis missing at the end of the posted statement.

1 Like
11

Thanks for noting the missing ")", post #9 has been updated to fix that.

It is true that my suggestion won't work if entries are missing, but the data samples provided in posts #1 and #3 don't have missing entries (even when the data presented for that entry is an empty field).

12

Thanks Guys!!!