Split a CSV file based on second column value

Question

I am using Ubuntu and I want to split my csv file into two csv files based on the value in the second column (age). The first file for patients under 60 (<60) and the second for patients over 60(>=). For example, if I have the following input:

id,age
1,65
2,63
3,5
4,55
5,78

The desired output is:

file_under:

id,age
3,5
4,55

file_over:

id,age
1,65
2,63
5,78

I have tried the following code but it removes the header (column names) how can I avoid this?

awk -F ',' '($2>=60){print}' file.csv > file_over.csv 

The input file is about 50k rows (lines).

Answer 1

I'd do all such filtering operations in an environment that already understands my data as contents of a table, and not just as rows of characters.

And since you seem to be doing filtering, that environment would ideally have some structured language with which you can query the table.

Enter SQL, structured query language, invented to deal with patient databases and the like.

A tool that offers such an SQL interface to databases which don't even have to exist on disk is sqlite. (Chances are it's even already installed on your ubuntu. Else, it's very very small, and can be installed using sudo apt install sqlite3.)

So, let's see.

1. We have your input in allpeople.csv
2. We run sqlite3 people.sqlite, which gives us a neat little shell in which we can write the following commands:
3. CREATE TABLE "people" ("id" INTEGER UNIQUE, "age" INTEGER);Enter, which creates a new table with two columns, "id" and "age", both of which are integer. "id" is even guaranteed to be unique – if we try to have two entries with the same "id", you will get complaints.
4. .import --csv "allpeople.csv" "people"Enter, which will read the CSV "allpeople" and load it into the table "people" that we've just created

Now we've prepared our data. (we only need to do that once, no matter how many selections we make from our database.)
The fun begins here:

5. .mode csvEnter, which will set the output mode to CSV
6. .output oldpeople.csvEnter, which tells sqlite to write the output into the file "oldpeople.csv" (including the header you asked for)
7. SELECT * FROM "people" WHERE "age" >= 60;Enter, which well, you guess it, selects all rows containing people whose age is at least 60, and puts the result in oldpeople.csv
8. .output youngpeople.csvEnterSELECT * FROM "people" WHERE "age" < 60;Enter needs no further explanation
9. .quitEnter to quit sqlite.

You can of course also write these commands above into a text file, "commands.sql", and run it with sqlite3 people.sqlite < commands.sql.

Note that "people.sqlite" now still contains a much faster readable, much more compact, and much more flexible database of what you have in "allpeople.csv". I usually avoid doing any statistic, mathematical or analytic work on CSV – it's just not the right format imho. SQL is very handy, and you can do much more interesting things, especially if you have more than one table, or you have more than two columns.

For example, if your data had another column "sex", and a column "weight", SQL makes it trivial to select all heavily overweight men between 18 and 20 years in one clean SELECT statement. If you have another table that maps diagnoses to "id"s from the people table, you can even find exclusively these 18 to 20 year old heavy men that have diabetes. (You can probably do the same in awk, but it does get cumbersome and slow at some point.)

I kind of like SQLite because it actually works very well as data exchange format – unlike CSV, which few tools agree upon how it's encoded, delimited, quoted, escaped, whitespaced, titled. SQLite actually is a defined storage format, CSV is more of the rough idea that often, things work if you just divide them with commas.

Common programming languages used in data analysis typically come with sqlite3 interfacing built in – python3's standard library contains the sqlite3 module, Perl has DBD::SQLite (amongst others), R has library(RSQLite), C/C++ has native interfaces…

Answer 2

Your approach with awk is basically sounds provided that the files do not contain advanced CSV features such as quoted commas within fields. You should probably change the tests to $2+0<60 and $2+0>=60 to ensure that the comparison is numerical rather than lexical even if the value of $2 is parsed as a string¹.

To emit the header row in both cases, you need to add a test that returns true for the first record. You can omit {print} altogether in this context because it is the default action. So

$ awk -F ',' 'NR==1 || $2+0<60' file.csv
id,age
3,5
4,55

and

$ awk -F ',' 'NR==1 || $2+0>=60' file.csv
id,age
1,65
2,63
5,78

If your files do not fit the simple CSV criterion, then some other options are csvsql from the Python-based csvkit:

$ csvsql --query 'SELECT * FROM file WHERE age >= 60' file.csv
id,age
1,65
2,63
5,78

or Miller:

$ mlr --csv filter '$age >= 60' file.csv
id,age
1,65
2,63
5,78

Both csvkit and miller packages are readily available from the Ubuntu universe repository.

---

1. In the C locale for example, a is lexicographically greater than 6 so you will likely observe that ($2>=60){print} includes the header line, while ($2<60){print} doesn't

Answer 3

Using awk's built-in output redirection to split the file in one pass:

$ awk -F, -v over=file_over.csv \
          -v under=file_under.csv \
    'NR==1 { print > over; print > under ; next };
    $2 < 60 { print > under ; next };
    { print > over }' file.csv

Redirection in awk works similarly to redirection in shell - the main difference is that awk's > truncates an output file only on the first write in the script (so there's no need for >> for subsequent output lines, unless you want your script to append to an existing file).

The one-liner above lets you set the output filenames with awk variables over and under. If you prefer to hard-code the output filenames, you can just embed them directly in the script as double-quoted strings (but note that repeating yourself in code is a common source of bugs due to typos, it's generally better to use a variable or constant for values that get re-used a lot):

$ awk -F, 'NR==1 {
    print > "file_over.csv";
    print > "file_under.csv" ;
    next
  };
  $2 < 60 { print > "file_under.csv" ; next };
  { print > "file_over.csv" }' file.csv```

or set them in a BEGIN block:

$ awk -F, '
    BEGIN {
      over  = "file_over.csv";
      under = "file_under.csv";
    };
    NR==1 { print > over; print > under ; next };
    $2 < 60 { print > under ; next };
    { print > over }' file.csv

Input & Output files:

$ head file*.csv
==> file.csv <==
id,age
1,65
2,63
3,5
4,55
5,78

==> file_over.csv <==
id,age
1,65
2,63
5,78

==> file_under.csv <==
id,age
3,5
4,55

Answer 4

You can also use grep with an REGEX:

grep -P "(,1\d\d|,[6-9]\d$)" file.csv > file_over
grep -vP "(,1\d\d|,[6-9]\d$)" file.csv > file_under

grep -P uses the Perl-style.

If the age is over 199 years you should change the REGEX :)

The complete commands in a one liner would be:

head -n1 file.csv > file_over && grep -P "(,1\d\d|,[6-9]\d]$)" file.csv >> file_over 
grep -vP "(,1\d\d|,[6-9]\d]$)" file.csv > file_under

Answer 5

Using any awk:

$ awk -F',' '
    NR==1 { print > "file_under"; print > "file_over"; next }
    { print > ( "file_" ($2 < 60 ? "under" : "over") ) }
' file

$ head file_under file_over
==> file_under <==
id,age
3,5
4,55

==> file_over <==
id,age
1,65
2,63
5,78

or, if you prefer, this would produce the same output without having to repeat the output file names in the code:

awk -F',' '
    BEGIN { split("file_over,file_under",out) }
    NR==1 { for (i in out) print > out[i]; next }
    { print > out[($2 < 60)+1] }
' file

Answer 6

Using Raku (formerly known as Perl_6)

#output rows with age < 60

~$ raku -e 'put get(); for lines() { .split(",").[1] < 60 ?? .put !! next};'  file.csv
id,age
3,5
4,55

#output rows with age >= 60

~$ raku -e 'put get(); for lines() { .split(",").[1] >= 60 ?? .put !! next};'  file.csv
id,age
1,65
2,63
5,78

The answer above is useful for "simple-csv" files. Briefly ( < 60 answer above), Raku gets the first (header) line and puts it. Then lines() (i.e. data rows) are read into Raku's ternary operator. If--when split on commas--values in the second column (zero-index == 1) are ?? True for an age < 60 years, then the entire row is output. If--when split on commas--values in the second column (zero-index == 1) are !! False for an age < 60 years, then the code moves onto the next line.

---

For more complicated CSV files (quoted/missing fields, embedded commas/newlines, etc.) you can use Raku's Text::CSV module. Code generally from the markdown document (at bottom):

#output rows with age < 60

~$ raku -MText::CSV -e ' my @hdr = < id age >;
        my @rows = csv(in => "file.csv", headers => "skip", filter => { $^row.[1] < 60 } );
        @rows.=unshift: @hdr; csv(in => @rows, out => $*OUT);'
id,age
3,5
4,55

#output rows with age >= 60

~$ raku -MText::CSV -e ' my @hdr = < id age >;
        my @rows = csv(in => "file.csv", headers => "skip", filter => { $^row.[1] >= 60 } );
        @rows.=unshift: @hdr; csv(in => @rows, out => $*OUT);'
id,age
1,65
2,63
5,78

Sample Input (from OP's post):

id,age
1,65
2,63
3,5
4,55
5,78

https://docs.raku.org/language/operators.html#infix_%3F%3F_!!
https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://docs.raku.org
https://raku.org