0

I have large csv file that looks like this:

1 , aaa, bbb, ...

1 , ccc, ddd, ...

2 , aaa, bbb, ...

..

10 , aaa, bbb, ...

..

1000 , aaa, bbb, ...

I would like to split it into separate csv files based on the first column being up to a multiple of n, eg. for n=10:

0.csv will contain:

1 , aaa, bbb, ...

1 , ccc, ddd, ...

2 , aaa, bbb, ...

..

10 , aaa, bbb, ...

and 1.csv will contain:

11 , ccc, ddd, ...

12 , aaa, bbb, ...

12 , aaa, bbb, ...

..

20 , aaa, bbb, ...

Problem is, I don't know in advance how many lines should be allocated to each file, and it's not simply matching the first column to an integer as you can with awk.

I know this comes closest to what I want:

awk -F ',' '!seen[$1]++{f=$1".csv"; print h > f};{f=$1".csv"; print >> f; close(f)}' file.csv

However, this only splits by first column value, which creates far too many files, whereas I would like to group them up to multiples on n.

  • 2
    Do you want the same number of lines in each output file? In that case, the split utility may be easier to use than awk. – Kusalananda Aug 19 '19 at 15:17
  • Unfortunately, number of lines per file will be different for each file since there can be any number of lines with first column = 1 etc. – dani_kasi Aug 19 '19 at 16:07
0

You need to use awk's modulus or % operator.

e.g.

$ awk -F, 'BEGIN { group=0; f="0.csv"};
           $1 % 10 == 0 && !seen[$1]++ {group++; f=group".csv"};
           { print >> f}' input.txt

Whenever $1 is evenly divisible by 10, it increments variable group and updates the output filename to match.

Using the following input file:

$ cat input.txt 
1 , aaa, bbb, ...
1 , ccc, ddd, ...
2 , aaa, bbb, ...
10 , aaa, bbb, ...
11 , aaa, bbb, ...
12 , aaa, bbb, ...
20 , aaa, bbb, ...
21 , aaa, bbb, ...
22 , aaa, bbb, ...
30 , aaa, bbb, ...
31 , aaa, bbb, ...
32 , aaa, bbb, ...
40 , aaa, bbb, ...
41 , aaa, bbb, ...
42 , aaa, bbb, ...
50 , aaa, bbb, ...
51 , aaa, bbb, ...
52 , aaa, bbb, ...
60 , aaa, bbb, ...
61 , aaa, bbb, ...
62 , aaa, bbb, ...
70 , aaa, bbb, ...
71 , aaa, bbb, ...
72 , aaa, bbb, ...
1000 , aaa, bbb, ...

It produces the following output files:

$ head *.csv
==> 0.csv <==
1 , aaa, bbb, ...
1 , ccc, ddd, ...
2 , aaa, bbb, ...

==> 1.csv <==
10 , aaa, bbb, ...
11 , aaa, bbb, ...
12 , aaa, bbb, ...

==> 2.csv <==
20 , aaa, bbb, ...
21 , aaa, bbb, ...
22 , aaa, bbb, ...

==> 3.csv <==
30 , aaa, bbb, ...
31 , aaa, bbb, ...
32 , aaa, bbb, ...

==> 4.csv <==
40 , aaa, bbb, ...
41 , aaa, bbb, ...
42 , aaa, bbb, ...

==> 5.csv <==
50 , aaa, bbb, ...
51 , aaa, bbb, ...
52 , aaa, bbb, ...

==> 6.csv <==
60 , aaa, bbb, ...
61 , aaa, bbb, ...
62 , aaa, bbb, ...

==> 7.csv <==
70 , aaa, bbb, ...
71 , aaa, bbb, ...
72 , aaa, bbb, ...

==> 8.csv <==
1000 , aaa, bbb, ...

An alternative to simply incrementing group is to make it equal to $1 / 10. e.g.

$ awk -F, '{ group = int($1 / 10); f=group".csv" ; print >> f }' input.txt

if you want to be able to specify n on the command line instead of hard-coding it:

$ awk -F, 'BEGIN { group=0; f="0.csv"};
           $1 % n == 0 && !seen[$1]++ {group++; f=group".csv"};
           { print >> f}' n=5 input.txt
  • I've tried all, but having the same problem with each: only the first file created is correct, every subsequent file only contains the line where $1==0 and splits them all into separate files like so: 1.csv: 10, aaa, bbb, ... 2.csv: 10, ccc, ddd, ... – dani_kasi Aug 19 '19 at 16:02
  • 1
    Then there's something about your input file that you haven't told us. If you give us a bogus sample, you'll get an answer that works with that bogus data. All three of the above work as expected on my sample input file (copied from your sample, then cut and pasted and edited multiple times so that there were entries with values for $1 being 1,2,3,10,11,12,20,21,22,...,70,71,72). The only difference between your desired output and the output of my scripts is that you said you wanted 1..10, 1..20, 21..30, etc while my scripts output groups of 0..9, 10..19, 20..29, etc. – cas Aug 19 '19 at 16:12
  • You could probably just write straight to int($1/10) ".csv". – Kusalananda Aug 19 '19 at 16:22
  • there is one fairly major bug in the scripts - if a matching first field (i.e. where $1%10==0) is repeated (e.g. 10, 10, 10, 10, ...) then the group will be incremented each time it occurs. that can be fixed by keeping track of the last seen matching event. – cas Aug 19 '19 at 16:22
  • @Kusalananda yeah, that would work too. – cas Aug 19 '19 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.