[SOLVED] [bash] group without sorting

hashbang#! · 02-18-2014, 05:47 PM

I have a tab-separated file of the following format:

Code:

<company-id> <employee-id> <name> <company> <phone>

example

Code:

123 11 "John MacDonald" "ABC Ltd" 01234814215
124 11 "Mike Smith"     "ABC Ltd" 01234814333
136 09 "Jane Doe"       "XYZ Ltd" 01234814444
135 09 "Peter Miller"   "XYZ Ltd" 01234814888

I'd like to create a tab-separated output grouping employees by company without changing the order of companies and employees:

Code:

"ABC Ltd"
 "John MacDonald" 01234814215 123
 "Mike Smith"     01234814333 124
"XYZ Ltd"
 "Jane Doe"       01234814444 136
 "Peter Miller"   01234814888 135

Speed is not of the essence. The input files are never more than 100 rows.

harryhaller · 02-18-2014, 06:22 PM

Try using Awk. Here is a tutorial.
Don't worry, your solution is one of the most basic functions of awk - so you won't have to read very far before you have your solution.

danielbmartin · 02-18-2014, 07:43 PM

With this InFile ...

Code:

123 11 "John MacDonald" "ABC Ltd" 01234814215
124 11 "Mike Smith"     "ABC Ltd" 01234814333
136 09 "Jane Doe"       "XYZ Ltd" 01234814444
135 09 "Peter Miller"   "XYZ Ltd" 01234814888

... this awk ...

Code:

awk -F \" '{if (CoName!=$4) {CoName=$4; print "\""CoName"\""}
      print " "$1"\""$2"\""$3$5}' $InFile >$OutFile

... produced this OutFile ...

Code:

"ABC Ltd"
 123 11 "John MacDonald"  01234814215
 124 11 "Mike Smith"      01234814333
"XYZ Ltd"
 136 09 "Jane Doe"        01234814444
 135 09 "Peter Miller"    01234814888

Daniel B. Martin

hashbang#! · 02-18-2014, 07:47 PM

Solved:

Code:

awk '
    BEGIN {
        FS  = "\t";
        OFS = "\t";
    }
    {
        if (r != $2)
            print $4;
        r = $2; 
        print "\t" $3, $5 ,$1;
    }
    ' $INFILE

hashbang#! · 02-18-2014, 07:49 PM

Oops, daniel, our posts crossed!

grail · 02-19-2014, 10:57 PM

@OP - seems to me when I run your code I receive the following output:

Code:

			123 11 "John MacDonald" "ABC Ltd" 01234814215
			124 11 "Mike Smith"     "ABC Ltd" 01234814333
			136 09 "Jane Doe"       "XYZ Ltd" 01234814444
			135 09 "Peter Miller"   "XYZ Ltd" 01234814888

You may need to check this unless your requirement has changed

**** Hold the phone ****

Turns out the data provided is not tab separated which I just noticed you have said it is in your code although not in the original problem.
So after fixing the input file and using your code I now get:

Code:

"ABC Ltd"
	"John MacDonald"	01234814215	123
	"Mike Smith"	"ABC Ltd"	124

	"Jane Doe"		136
	"Peter Miller"	"XYZ Ltd"	135

So a bit closer, but would still need some work

grail · 02-19-2014, 11:06 PM

forgot to put in a suggested solution for you:

Code:

awk -F'\t+' 'x!=$(NF-1){print $(NF-1);x=$(NF-1)}{print $3,$NF,$1}' OFS="|" infile | column -t -s '|'

This displays your originally requested output

hashbang#! · 02-20-2014, 06:42 AM

grail, which Perth, by the way?

Input + output are tab-separated as described in the OP. But the example I gave was typed up by hand using quote marks around names containing spaces and spaces instead of tabs. (I didn't think I code post tabs into a code block.) The original input/output has no quotes.

I am a bit puzzled as to why you found my script was not working. I have been using it for days and I can't see where I might have introduced a mistake in my posts.

I just ran a test and this is what I'm getting:

input

Code:

123	11	JohnMacDonald	ABCLtd	01234814215
124	11	MikeSmith	ABCLtd	01234814333
136	09	JaneDoe	XYZLtd	01234814444
135	09	PeterMiller	XYZLtd	01234814888

output

Code:

ABCLtd
	JohnMacDonald	01234814215	123
	MikeSmith	01234814333	124
XYZLtd
	JaneDoe	01234814444	136
	PeterMiller	01234814888	135

Your script works fine. I just need an empty first column in the employee rows (i.e. a tab preceding <name>) and output needs to be tab-separated.

I didn't realize that you can use expressions like $(NF-1) with awk. Learn something new every day!

grail · 02-20-2014, 09:28 AM

So I had a bit of look and based on how you formatted the input data, it appeared that there would be the necessary number of tabs (ie 1 or more) to make the original
data line up. Since your code set the FS value to a single tab, awk says that after each tab there must be a field, even if it is empty.
Hence, Mike Smith is shorter than the previous name and required an extra tab to line the data up, hence bogus output.

This is why you will notice that my FS value uses the '+' after the tab to indicate the possibility of 1 or more.

Also the addition of the column command nicely tidies up the output for you without worrying about too much formatting yourself.
For your requirement to have an additional space at the front:

Code:

awk -F'\t+' 'x!=$(NF-1){print $(NF-1);x=$(NF-1)}{print "",$3,$NF,$1}' OFS="|" infile | column -t -s '|'