How to split a CSV file per initial column (with headers)?

Question

This is a combination of two other questions (how to split a file by each line prefix and how to split a file according to a column, including the header). I want to go from this content in input.csv:

id,first,second,third
1,a,b,c
333,b,b,b
1,d,e,f
2,d,e,f
1,c,d,e
333,a,a,a
[more lines in the same format]

to this content in 1.csv:

id,first,second,third
1,a,b,c
1,d,e,f
1,c,d,e

, this content in 2.csv:

id,first,second,third
2,d,e,f

, and this content in 333.csv:

id,first,second,third
333,b,b,b
333,a,a,a

, that is:

1. Put all the lines with ID of N into N.csv.
2. Keep the sequence of lines as in the original.
3. Include the header from the original file in all the output files.

This must also be really fast, so a while read loop is not going to cut it.

Answer 1

This GNU awk command does the trick:

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

Caveat: This will not work if there are escaped commas in the first field. Commas in other fields should work fine.

Explanation:

• -F ',' (field separator) ensures that $1 etc. refer to the CSV columns rather than space separated values.
• NR==1{h=$0; next} treats the first line specially (NR==1), by storing the full header line in a variable h (h=$0) and skipping the line (next).
• !seen[$1]++{f=$1".csv"; print h > f} treats the first occurrence of any $1 specially (!seen[$1]) by storing $1 followed by .csv into a filename variable f and saving the header to that file (print h > f).
• {f=$1".csv"; print >> f; close(f)} adds the current line to the file (print >> f) and closes the file descriptor (close(f)) to avoid keeping it around once processing of all lines with a specific ID is done.

Bonus: If you replace $1 with another field it should do what you expect: Create a file per unique value in that column with the lines containing that value in the given column.

Answer 2

(Sorry to spam you all with another answer) For many situations, the elegant awk versions presented are perfect. But there is life outside one-liners -- we often need more:

• add extra code to cope with complex csv files;
• add extra steps for normalization, reformatting, processing.

In the following skeleton, we use a Parser of CSV files. This time we are avoiding one-ligners and even strictly declare the variables!

#!/usr/bin/perl

use strict;
use Parse::CSV;
my %dict=();

my $c = Parse::CSV->new(file => 'a1.csv');

while ( my $row = $c->fetch ) {                    ## for all records
   $dict{$row->[0]} .=   join(" :: ",@$row)."\n";  ## process and save
}

for my $k (keys %dict){                            ## create the cvs files
   open(F,">","$k.cvs") or die;
   print F $dict{$k};
   close F;
}

• The main advantage is that we can deal with more complex csv files; this time the csv input can have strings with ";", can include multiline fields (csv specification is complex!):

 1111,2,3
 "3,3,3",a,"b, c, and d"
 "a more, complex
        multiline record",3,4

• to exemplify a processing step, the field separator was changed to " :: "
• to exemplify extra steps we added some optimization: as we used a dict cache, this script runs 100 times faster than my other solution.

Answer 3

This is not an answer but just an avoid-scrolling variant of IObO's excellent answer...

awk -F, 'NR==1{h=$0; next} {print seen[$1]++ ? $0 : h "\n" $0 >$1 ".csv"}'

Answer 4

old school version using only pipes and no awk:

warning: it runs on average slower than above awk solutions by a factor of the number of keys in the input file

cut -d , -f 1 input.csv | fgrep -v id | sort | uniq | xargs -n1 sh -c '(head -n1 input.csv && egrep "^${0}," input.csv) > ${0}.csv'

which does:

• cut -d , -f 1 input.csv splits each line of the file by the , char and grab the first column (-f 1) to keep only the keys
• fgrep -v id skip the header
• sort | uniq sort and keep only one of each keys
• xargs -n1 sh -c '<sub shell>' for each key execute a sub shell
• head -n1 input.csv first part of the sub shell grabs the header of the input file
• then egrep "^${0}," input.csv grabs the lines matching the key, it might not be obvious, but that is a loop over each line, that's why it is slow
• and > ${0}.csv finally writes the output into a file named by the key