AWK - Split a file according to values in a field

Author

Split a file according to values in a field

Jonny

2005-04-30, 8:55 am

Hi,

I would like to be able to split a file according to values in a field.

For example, to split the following file by surname, into separate
files:

John Smith
Dave Green
Kim Brown
Steve West
Tom Brown
Dave West
Mary Green
Steve Smith
Jack Brown

giving separate files with contents:

John Smith
Steve Smith

Dave Green
Mary Green

Kim Brown
Tom Brown
Jack Brown

Steve West
Dave West

It doesn't matter how the resulting files are named, as long as I can
specify a directory in which they are to be created.

I'm asking in this group but I'm not sure if awk is the most appropriate
tool. I would post to comp.unix.shell also, but I'm not sure if I'm
supposed to do that, so I'll just post it here for the moment.

If anyone knows how to do this in awk, or of a more appropriate tool,
please could you let me know.

Thanks for your help.

Regards,
Jonny

Robert Katz

2005-04-30, 8:55 am

Janis Papanagnou

2005-04-30, 8:55 am

Jonny wrote:
> Hi,
>
> I would like to be able to split a file according to values in a field.
>
> For example, to split the following file by surname, into separate
> files:
>
> John Smith
> Dave Green
> Kim Brown
> Steve West
> Tom Brown
> Dave West
> Mary Green
> Steve Smith
> Jack Brown
>
> giving separate files with contents:
>
> John Smith
> Steve Smith
>
> Dave Green
> Mary Green
>
> Kim Brown
> Tom Brown
> Jack Brown
>
> Steve West
> Dave West
>
> It doesn't matter how the resulting files are named, as long as I can
> specify a directory in which they are to be created.
>
> I'm asking in this group but I'm not sure if awk is the most appropriate
> tool. I would post to comp.unix.shell also, but I'm not sure if I'm
> supposed to do that, so I'll just post it here for the moment.
>
> If anyone knows how to do this in awk, or of a more appropriate tool,
> please could you let me know.

Awk seems the most appropriate tool to me. The following program
will create files named West, Smith, Green, and Brown containing
the respective entries.

{ print > $2 }

Janis

Jonny

2005-04-30, 3:55 pm

Janis Papanagnou wrote:

> Jonny wrote:
>
> Awk seems the most appropriate tool to me. The following program
> will create files named West, Smith, Green, and Brown containing
> the respective entries.
>
> { print > $2 }

Thanks for the reply Janis.

I had no idea it was so simple. Because of the awk implemenation I'm
using, I had to use:

{ print >> $2; close($2) }

otherwise I got a "Too many open files error" when dealing with large
files containing many surnames.

However, since my initial post, I have found out that there also needs
to be a limit on the number of lines in each file produced. That is, in
my initial example, if there was a limit of 2 lines per file, then
instead of producing the files:

Smith
Green
Brown
West

the following files would need to be produced:

Smith
Green
Brown_1
Brown_2
West

which I suppose makes things not so simple. I appreciate that this
makes the problem more complex - or rather, completely different - but
if you have any ideas of how to use awk (or any other tool) to do this,
I would be very grateful.

Regards,
Jonny

Kenny McCormack

2005-04-30, 3:55 pm

In article <tkNce.11104$5A3.2716@newsfe4-win.ntli.net>,
Jonny <www.mail@ntlworld.com> wrote:
....
>Thanks for the reply Janis.
>
>I had no idea it was so simple. Because of the awk implemenation I'm
>using, I had to use:
>
>{ print >> $2; close($2) }

Get gawk. Then come back.

>However, since my initial post, I have found out that there also needs
>to be a limit on the number of lines in each file produced. That is, in
>my initial example, if there was a limit of 2 lines per file, then
>instead of producing the files:

Consider that your homework. We wouldn't to spoil you, now would we?

Robert Katz

2005-04-30, 3:55 pm

Jonny wrote:
> Janis Papanagnou wrote:
>
>
>
>
>
> Thanks for the reply Janis.
>
> However, since my initial post, I have found out that there also needs
> to be a limit on the number of lines in each file produced. That is, in
> my initial example, if there was a limit of 2 lines per file, then
> instead of producing the files:
>
> Smith
> Green
> Brown
> West
>
> the following files would need to be produced:
>
> Smith
> Green
> Brown_1
> Brown_2
> West
>
> which I suppose makes things not so simple.

Well, if you need to index file names *ONLY AFTER* the first two entries, then it's still simple.

{ print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m : "") }

However that'll produce files

Brown
Brown_1
Green
Smith
West

--
Regards,

---Robert

Patrick TJ McPhee

2005-04-30, 3:55 pm

In article <tkNce.11104$5A3.2716@newsfe4-win.ntli.net>,
Jonny <www.mail@ntlworld.com> wrote:

% However, since my initial post, I have found out that there also needs
% to be a limit on the number of lines in each file produced. That is, in
% my initial example, if there was a limit of 2 lines per file, then
% instead of producing the files:

You could just keep counts of how many of each name you've spat out.

if (++ncount[$2] > limit) {
ncount[$2] = 1
fcount[$2]++
}
else if (! ($2 in fcount))
fcount[$2] = 1

fn = $2 "_" fcount[$2]
print >> fn
close(fn)

Depending on the size of your data, you could keep all the names in
memory then spit them out when you go over the limit. Something like

{
c[$2]++
if (c[$2] > l) {
f[$2]++
fn = $2 "_" f[$2]
for (i = 1; i <= l; i++) print n[$2,i] >> fn
c[$2] = 1
}
n[$2,c[$2]] = $0
}

END {
for (nm in c) {
if (nm in f) {
f[nm]++
for (i = 1; i <= c[nm]; i++) print n[nm,i] >> (nm "_" f[nm])
}
else {
for (i = 1; i <= c[nm]; i++) print n[nm,i] >> nm
}
}
}
--

Patrick TJ McPhee
North York Canada
ptjm@interlog.com

Robert Katz

2005-05-01, 3:55 am

Robert Katz wrote:
> Jonny wrote:
>
>
>
> Well, if you need to index file names *ONLY AFTER* the first two
> entries, then it's still simple.
>
> { print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m : "") }
>
> However that'll produce files
>
> Brown
> Brown_1
> Green
> Smith
> West
>

How about,

{ print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m + 1 : "") }
END {
for (x in a)
if (a[x] > 2)
system("mv " x " " x "_1")
}

--
Regards,

---Robert

Jonny

2005-05-01, 3:56 pm

Jonny wrote:

> However, since my initial post, I have found out that there also needs
> to be a limit on the number of lines in each file produced. That is, in
> my initial example, if there was a limit of 2 lines per file, then
> instead of producing the files:
>
> Smith
> Green
> Brown
> West
>
> the following files would need to be produced:
>
> Smith
> Green
> Brown_1
> Brown_2
> West

Thanks to Patrick and Robert for the additional replies. They all
produce the five files expected. I will have to test them on larger
files to determine which is the quickest or most feasible.

It's not going to be straightforward to test because I ran one of the
scripts on a file containing over a million names, and almost 300,000
files were produced, before I stopped awk. It then took around 2 hours
for the directory containing the files to be deleted. But that's NTFS
for you.

Thanks again for your help. I appreciate it.

Regards,
Jonny

Ed Morton

2005-05-01, 3:56 pm

Robert Katz wrote:

> Jonny wrote:
>
>
>
> Well, if you need to index file names *ONLY AFTER* the first two
> entries, then it's still simple.
>
> { print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m : "") }
>
> However that'll produce files
>
> Brown
> Brown_1
> Green
> Smith
> West
>

It might be more efficient to do:

$ awk -vCONVFMT="%d" '{print $2 > $2 "_" a[$2]++/2}' file

since on every line:

a) You don't have to evaluate NF (which I vaguely recall hearing took
some extra processing time compared to just using the field number)
b) You don't need to call the int() function
c) You don't need to test for a value befre appending the suffix

It will mean you end up with the first file names being having "_0"
appended but it seems more appropriate to have a common format for all
file names rather than having a special case for the first files anyway.

CONVFMT is specific to gawk. For other awks, setting OFMT should produce
the same result.

Ed.

Ed Morton

2005-05-01, 3:56 pm

Jonny wrote:
> Jonny wrote:
>
>
>
>
> Thanks to Patrick and Robert for the additional replies. They all
> produce the five files expected. I will have to test them on larger
> files to determine which is the quickest or most feasible.

See my other response (just posted). Note that string concatenation
(which produces your file names) is slow, so at some point depending on
how many lines you really want in each file, it'll probably be more
efficient to test for a count of lines before regenerating the file name
for that suffix.

> It's not going to be straightforward to test because I ran one of the
> scripts on a file containing over a million names, and almost 300,000
> files were produced, before I stopped awk. It then took around 2 hours
> for the directory containing the files to be deleted. But that's NTFS
> for you.

<OT>
If you're on UNIX, do it in a tmp directory (say /dir/tmp) and when
you're done do:

nohup rm -rf /dir/tmp &

and never look back. The above will quietly remove all files in /dir/tmp
and remove the directory itself, all in the background so you can
continue working in a different directory or even log off. Obviously,
make sure your only copy of the input file is not in that directory!
</OT>

Ed.

> Thanks again for your help. I appreciate it.
>
> Regards,
> Jonny

Kenny McCormack

2005-05-01, 3:56 pm

In article <JZednfcEOexOa-nfRVn-jQ@comcast.com>,
Ed Morton <morton@lsupcaemnt.com> wrote:
....
>CONVFMT is specific to gawk. For other awks, setting OFMT should produce
>the same result.

Wrong. CONVFMT is, I am pretty sure, POSIX (but I can't quote C&V).
In any case, every AWK I could find (except the one that we all know about)
has it (yes, including TAWK).

Robert Katz

2005-05-01, 8:55 pm

Ed Morton wrote:
>
>
> Robert Katz wrote:
>
>
> It might be more efficient to do:
>
> $ awk -vCONVFMT="%d" '{print $2 > $2 "_" a[$2]++/2}' file
>
> since on every line:
>
> a) You don't have to evaluate NF (which I vaguely recall hearing took
> some extra processing time compared to just using the field number)

I used $NF just in case there was a line with a middle name or initial;
but if we're sure there isn't one, it seems from what you say, especially
for a large amount of data, it'd be more efficient to use $2

> b) You don't need to call the int() function

That, a) and b), should make it certainly, not might be, more efficient.

> c) You don't need to test for a value befre appending the suffix
>
> It will mean you end up with the first file names being having "_0"
> appended but it seems more appropriate to have a common format for all
> file names rather than having a special case for the first files anyway.

That's where the difficulty arises, the asymmetry of the subscripts, but that's what the original request was. Otherwise the logic is straightforward.

>
> CONVFMT is specific to gawk. For other awks, setting OFMT should produce
> the same result.

--
Regards,

---Robert

Ed Morton

2005-05-01, 8:55 pm

Kenny McCormack wrote:

> In article <JZednfcEOexOa-nfRVn-jQ@comcast.com>,
> Ed Morton <morton@lsupcaemnt.com> wrote:
> ...
>
>
>
> Wrong. CONVFMT is, I am pretty sure, POSIX (but I can't quote C&V).

Right you are. See
http://www.opengroup.org/onlinepubs...ities/awk.html.

Thanks for catching that,

Ed.

Robert Katz

2005-05-02, 3:55 am

Ed Morton wrote:
>
>
> Robert Katz wrote:
>
>
> It might be more efficient to do:
>
> $ awk -vCONVFMT="%d" '{print $2 > $2 "_" a[$2]++/2}' file
>
> since on every line:
>
> a) You don't have to evaluate NF (which I vaguely recall hearing took
> some extra processing time compared to just using the field number)
> b) You don't need to call the int() function
> c) You don't need to test for a value befre appending the suffix
>
> It will mean you end up with the first file names being having "_0"
> appended but it seems more appropriate to have a common format for all
> file names rather than having a special case for the first files anyway.

Okay, making the routine more efficient, but keeping asymmetrical
subscripts, and assuming the system is UNIX, we can do

BEGIN { CONVFMT = "%d" }
{ print $2 > $2 "_" 1 + a[$2]++/2 }
END { for (x in a) if (a[x] < 3) system("mv " x "_1 " x) }

The asymmetry makes a relatively simple solution quite involved.
Patrick solved it without assuming any OS. I guess we also need mind
the number of open files.

>
> CONVFMT is specific to gawk. For other awks, setting OFMT should produce
> the same result.

Except for gawk, I haven't found any awks that can paste together

print $2 > $2 "_" 1 + a[$2]++/2

but they did better if I did

print $2 > ($2 "_" 1 + a[$2]++/2)

The awks I tried besides gawk, couldn't paste the result with the
correct subscript, although some had even defined CONVFMT.

--
Regards,

---Robert

Brian Inglis

2005-05-02, 3:55 am

fOn Sun, 01 May 2005 14:14:48 GMT in comp.lang.awk, Jonny
<www.mail@ntlworld.com> wrote:

>Jonny wrote:
>
>
>Thanks to Patrick and Robert for the additional replies. They all
>produce the five files expected. I will have to test them on larger
>files to determine which is the quickest or most feasible.
>
>It's not going to be straightforward to test because I ran one of the
>scripts on a file containing over a million names, and almost 300,000
>files were produced, before I stopped awk. It then took around 2 hours
>for the directory containing the files to be deleted. But that's NTFS
>for you.

Most FSs out there will have problems with 300K files; maybe organize
with directories using a variant of the _0 suffix approach: B/Brown/0.

--
Thanks. Take care, Brian Inglis Calgary, Alberta, Canada

Brian.Inglis@CSi.com (Brian[dot]Inglis{at}SystematicSW[dot]a
b[dot]ca)
fake address use address above to reply

Robert Katz

2005-05-03, 3:56 pm

Carl Lowenstein

2005-05-03, 3:56 pm

In article <zdOdnYvhPPEsZOnfRVn-3A@comcast.com>,
Ed Morton <morton@lsupcaemnt.com> wrote:
>
>
>Jonny wrote:
>
><OT>
>If you're on UNIX, do it in a tmp directory (say /dir/tmp) and when
>you're done do:
>
> nohup rm -rf /dir/tmp &
>
>and never look back. The above will quietly remove all files in /dir/tmp
>and remove the directory itself, all in the background so you can
>continue working in a different directory or even log off. Obviously,
>make sure your only copy of the input file is not in that directory!
></OT>
>
<OT more>
On a Unix-like system, do it in a mounted partition. When finished,
unount the partition and re-create the file system with newfs or mkfs.
Poof, all files are gone. But I guess you have to be root to do this.
</OT more>

carl

--
carl lowenstein marine physical lab u.c. san diego
clowenst@ucsd.edu

Ed Morton

2005-05-04, 3:56 am

Robert Katz wrote:

> Jonny wrote:
>
>
>
> Well, if you need to index file names *ONLY AFTER* the first two
> entries, then it's still simple.
>
> { print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m : "") }
>
> However that'll produce files
>
> Brown
> Brown_1
> Green
> Smith
> West
>

It might be more efficient to do:

$ awk -vCONVFMT="%d" '{print $2 > $2 "_" a[$2]++/2}' file

since on every line:

a) You don't have to evaluate NF (which I vaguely recall hearing took
some extra processing time compared to just using the field number)
b) You don't need to call the int() function
c) You don't need to test for a value befre appending the suffix

It will mean you end up with the first file names being having "_0"
appended but it seems more appropriate to have a common format for all
file names rather than having a special case for the first files anyway.

CONVFMT is specific to gawk. For other awks, setting OFMT should produce
the same result.

Ed.

Ed Morton

2005-05-04, 3:56 am

Kenny McCormack

2005-05-04, 3:56 am

Kenny McCormack

2005-05-04, 8:56 pm

Carl Lowenstein

2005-05-05, 3:56 pm

Robert Katz

2005-05-05, 3:56 pm

Robert Katz wrote:
> Jonny wrote:
>
>
>
> Well, if you need to index file names *ONLY AFTER* the first two
> entries, then it's still simple.
>
> { print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m : "") }
>
> However that'll produce files
>
> Brown
> Brown_1
> Green
> Smith
> West
>

How about,

{ print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m + 1 : "") }
END {
for (x in a)
if (a[x] > 2)
system("mv " x " " x "_1")
}

--
Regards,

---Robert

Ed Morton

2005-05-06, 3:56 am

Robert Katz wrote:

> Jonny wrote:
>
>
>
> Well, if you need to index file names *ONLY AFTER* the first two
> entries, then it's still simple.
>
> { print > $NF ((m = int(a[$NF]++/2)) > 0 ? "_" m : "") }
>
> However that'll produce files
>
> Brown
> Brown_1
> Green
> Smith
> West
>

It might be more efficient to do:

$ awk -vCONVFMT="%d" '{print $2 > $2 "_" a[$2]++/2}' file

since on every line:

a) You don't have to evaluate NF (which I vaguely recall hearing took
some extra processing time compared to just using the field number)
b) You don't need to call the int() function
c) You don't need to test for a value befre appending the suffix

It will mean you end up with the first file names being having "_0"
appended but it seems more appropriate to have a common format for all
file names rather than having a special case for the first files anyway.

CONVFMT is specific to gawk. For other awks, setting OFMT should produce
the same result.

Ed.

Robert Katz

2005-05-06, 3:56 am

Patrick TJ McPhee

2005-05-07, 3:55 pm

Sponsored Links