|
|
Convert rows to column and add header
10-10-2014
10-10-2014
10-10-2014
awk 'NR==FNR{a[$(NF-1)]=$(NF-1);next}
{if (FNR==1) { asort(a) ; printf "Fam\t\tNo\t\tName\t" ; for ( j in a ) { printf a[j] FS }} if ( !b[$1,$2] ) { if ( FNR>1) { for(j in a) {if ( p[a[j]] ) { printf OFS p[a[j]] } else {printf OFS "NULL"} } delete p;} printf "\n"; b[$1,$2]++; printf $1 OFS $2 OFS ;for (i=3;i<=NF-2;i++) { printf $i FS } ; } if (FNR==1) { asort(a) } ; for ( j in a ) { if ( a[j] == $(NF-1) ) { p[a[j]]=$NF;} }} END { for(j in a) {if ( p[a[j]] ) { printf OFS p[a[j]] } else {printf OFS "NULL" } } printf "\n";}' OFS="|" testFile testFile
[akshay@nio tmp]$ cat file 192.98.1 192.98.192.98.17 VVC family Zorro 10 192.98.1 192.98.192.98.17 VVC family Ace 1 192.98.1 192.98.192.98.17 VVC family Bora 1 192.98.1 192.98.192.98.17 VVC family Sakura 5 12.A.4 12.A.4.10.30 channel2 family Usopun 1 7.A3.14 7.A3.14.3.1 DuanXon channel family T-Law 1 7.A3.14 7.A3.14.3.1 DuanXon channel family Robyn 1 7.A3.14 7.A3.14.3.1 DuanXon channel family Zorro 1 7.A3.14 7.A3.14.3.1 DuanXon channel family Ace 1 7.A3.14 7.A3.14.3.1 DuanXon channel family Bora 1 7.A3.14 7.A3.14.3.1 DuanXon channel family Sakura 1 7.A3.14 7.A3.14.3.1 DuanXon channel family Hashir 1 8.M.14 8.M.14.1 potential receptor, channel family Robyn 1 8.M.14 8.M.14.1.2 potential receptor, channel family Usopun 1 8.M.14 8.M.14.1.3 potential receptor, channel family T-Law 1 8.M.14 8.M.14.1.3 potential receptor, channel family Zorro 2 8.M.14 8.M.14.1.3 potential receptor, channel family Ace 4 8.M.14 8.M.14.1.3 potential receptor, channel family Bora 1 8.M.14 8.M.14.1.3 potential receptor, channel family Sakura 2 1.P.5 1.P.5.18.1 major intrinsic family Ace 8 1.P.5 1.P.5.18.3 major intrinsic family Sakura 1 1.P.5 1.P.5.6.4 major intrinsic family T-Law 1 1.P.5 1.P.5.6.4 major intrinsic family Robyn 6 1.P.5 1.P.5.6.4 major intrinsic family Sakura 1
awk '
{
# Field 1,2,4 and 5
f1 = $1
f2 = $2
f4 = $(NF -1)
f5 = $NF
# This is for f3
# We can also do this like $1 = $2 = $(NF -1) = $NF ="" ; f3 = $0
# But there will be OFS issue, you have set OFS in END block I guess for proper formating
split($0,d,/[^[:space:]]*/)
for(i=3; i<=NF-2; i++)
{
f3 = sprintf("%s%s%s",f3,d[i],$i)
}
# Remove Leading and trailing space in f3
gsub(/^[ \t]+|[ \t]+$/,"",f3)
# Index
key = f1 SUBSEP f2 SUBSEP f3
# Uniq index
D[key]
# Hash with value
N[key,f4] = f5
# Uniq User
U[f4]
# reset variable
key = f3 = ""
}
END{
# Sort User array
n = asorti(U,copy)
# Loop through uniq index
for(key in D)
{
# Split key
split(key,S,SUBSEP)
# Loop through uniq user
for(i=1;i<=n;i++)
{
# User
u = copy[i]
# If header not printed then
if(!header)
{
# Create header
hdr = sprintf("%s%s%s",hdr,OFS,u)
}
# index we are looking for
ind = S[1] SUBSEP S[2] SUBSEP S[3] SUBSEP u
# if key exists and use that value else string null
val = (ind in N)? N[ind] : "null"
# Save value in variable
str = sprintf("%s%s%s",str,OFS,val)
}
# write header if header not set
if(!header)
{
print "Fam","No","Name" hdr
# set flag header
header = 1
}
# Print values
# Quote is added incase if you want to open them in excel as
# there are comma inside the field
print S[1],S[2],"\""S[3]"\"" str
str = ""
}
}
' OFS="," fileFam,No,Name,Ace,Bora,Hashir,Robyn,Sakura,T-Law,Usopun,Zorro 12.A.4,12.A.4.10.30,"channel2 family",null,null,null,null,null,null,1,null 1.P.5,1.P.5.6.4,"major intrinsic family",null,null,null,6,1,1,null,null 1.P.5,1.P.5.18.3,"major intrinsic family",null,null,null,null,1,null,null,null 8.M.14,8.M.14.1.3,"potential receptor, channel family",4,1,null,null,2,1,null,2 8.M.14,8.M.14.1,"potential receptor, channel family",null,null,null,1,null,null,null,null 8.M.14,8.M.14.1.2,"potential receptor, channel family",null,null,null,null,null,null,1,null 7.A3.14,7.A3.14.3.1,"DuanXon channel family",1,1,1,1,1,1,null,1 1.P.5,1.P.5.18.1,"major intrinsic family",8,null,null,null,null,null,null,null 192.98.1,192.98.192.98.17,"VVC family",1,1,null,null,5,null,null,10
awk '
{
split($0,d,/[^[:space:]]*/)
for(i=1; i<=NF-2; i++)
{
key = sprintf("%s%s%s",key,d[i],$i)
}
D[key]
N[key,$(NF-1)] = $NF;
U[$(NF-1)]
key = ""
}
END{
n = asorti(U,copy)
for(key in D)
{
split(key,S,SUBSEP)
for(i=1;i<=n;i++)
{
u = copy[i]
if(!header)
{
hdr = sprintf("%3s%s%3s",hdr,"\t",u)
}
val = ((S[1],u) in N)? N[S[1],u] : "null"
str = sprintf("%3s%s%3s",str,"\t",val)
}
if(!header)
{
printf("%-100s%s\n",sprintf("%-15s%-25s%s","Fam","No","Name"),hdr);
header = 1
}
printf("%-100s%s\n", S[1],str)
str = ""
}
}
' fileFam No Name Ace Bora Hashir Robyn Sakura T-Law Usopun Zorro 1.P.5 1.P.5.18.3 major intrinsic family null null null null 1 null null null 8.M.14 8.M.14.1.3 potential receptor, channel family 4 1 null null 2 1 null 2 8.M.14 8.M.14.1 potential receptor, channel family null null null 1 null null null null 192.98.1 192.98.192.98.17 VVC family 1 1 null null 5 null null 10 1.P.5 1.P.5.18.1 major intrinsic family 8 null null null null null null null 1.P.5 1.P.5.6.4 major intrinsic family null null null 6 1 1 null null 12.A.4 12.A.4.10.30 channel2 family null null null null null null 1 null 8.M.14 8.M.14.1.2 potential receptor, channel family null null null null null null 1 null 7.A3.14 7.A3.14.3.1 DuanXon channel family 1 1 1 1 1 1 null 1
|
|