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
Hi Pravin27,
Thanks a lot! It really works perfectly on my real data. New things in the codes that interest me like asort function. I am wondering about the input file being written twice there too. Anyways, i will study your code first and come back if i don't understand it.
---------- Post updated at 11:08 AM ---------- Previous update was at 11:02 AM ----------
Quote:
Originally Posted by Akshay Hegde
Try
Input
Code:
[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
Excel version
Code:
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="," file
Hi Gurus,
I have a requirement like this and have to create a UX shell scripts. Thanks in advance.
File-in:
------
Header2007-12-012007-11-21
100|xyz|was
101|wsa|qws
......
.......
Output should be:
-------------------
2007-12-01|100|xyz|was
2007-12-01|101|wsa|qws
......
.......... (7 Replies)
Hi,
I have a file with below contents.
Heading1 Heading2 Heading3 Heading4
Value1 Value2 Value3 Value4
The file has only 2 rows and is tab separated
The desired output is :
Heading1 Value1
Heading2 Value2
Heading3 Value3
Heading4 Value4
CAn you please help? (5 Replies)
Hi I have the text file like this
"A"
"AA Info"
"AA Text"
"AAA"
"ABC"
"ABC Info"
"ABC Tech"
"AGH"
"SYN"
"SYMBony"
"SYN BEREN"
Like about 2000 lines
Output would be in Column with groups like following
"A"
"AA Info", "AA Text"
"AAA"
"ABC","ABC Info","ABC Tech" (0 Replies)
Hi,
I have an input like this
1 2 3 4
2 3 4 5
4 5 6 7
I would like to count the no. of columns and print a header with a prefix "Col".
I would also like to count the no. of rows and print as first column with each line number with a prefix "Row"
So, my output would be
... (2 Replies)
Hi Experts,
I have a requirement to convert rows into columns.
For e.g.
Input File:
Output File should be like
Appreciate if you could suggest code snippet(may be awk) for above requirement...
Thanks in Advance for your help... (3 Replies)
I want to put the 3 first lines into a single line separated by ;
I've tried to use Sed and Awk but without success. I'm new to Shell scripting.
Thanks in advance!
Input
112
DESAC_201309_OR_DJ10
DJ10
1234567890123;8
1234567890124;20
1234567890125;3
expected Output... (8 Replies)
Hi all ,
I have a file with the below content
Header Section
employee|employee name||Job description|Job code|Unitcode|Account|geography|C1|C2|C3|C4|C5|C6|C7|C8|C9|Csource|Oct|Nov|Dec|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep
Data section ... (1 Reply)
Hi,
I have a requirement to format the data in a new order. Here is my source format :
ppp ***Wed Dec 16 10:32:30 GMT 2015
header1 header2 header3 header4 header5
server1 0.00 0.02 0.07 0.98
server2 0.01 0.00 0.08 0.79
server3 0.05 0.82 0.77 0.86
... (18 Replies)
10-10-2014
