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.
- We have your input in
- We run
sqlite3 people.sqlite, which gives us a neat little shell in which we can write the following commands:
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.
.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:
.mode csvEnter, which will set the output mode to CSV
.output oldpeople.csvEnter, which tells sqlite to write the output into the file "oldpeople.csv" (including the header you asked for)
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
SELECT * FROM "people" WHERE "age" < 60;Enter needs no further explanation
.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…