How to extract columns in which their name containing the word "chronic" from CSV file

Question

I have a big csv file ( around 1000 columns ) and I want to extract to a new file only columns that contain the word "chronic" in their header name. How can I do that ?

For example if I have:

gender,chronic_disease1,chronic_disease2
male,2008,2009

The desired output is:

chronic_disease1,chronic_disease2
2008,2009

Note: the column/field separators is comma ",". if there was no chronic match then no output at all.

Answer 1

Using Miller (available from the Ubuntu "universe" repository) whose cut verb has an option to match field names using a regular expression:

mlr --csv cut -r -f 'chronic' file.csv

(matches the substring chronic anywhere in the field name) or more specifically

mlr --csv cut -r -f '^chronic_' file.csv

(which anchors the substring to the start of the name, and adds the trailing underscore) or

mlr --csv cut -r -f '"^chronic_"i' file.csv

to make the latter match case-insensitively.

To invert the match i.e. select all columns not matching ^chronic_, add -x:

mlr --csv cut -x -r -f '"^chronic_"i' file.csv

Note: you may be able to use the more efficient --csvlite engine if your input file does not contain more advanced CSV features such as RFC-4180-style double-quoting. See File formats - CSV/TSV/ASV/USV/etc.

If there are no field names containing the string chronic and you want no output at all rather than empty records, pass the extracted data through Miller's skip-trivial-records sub-command.

mlr --csv cut -r -f 'chronic' then skip-trivial-records file.csv

Answer 2

Using awk:

awk '
  BEGIN{ FS=OFS="," }
  NR==1{
         for(i=1; i<=NF; i++)
             found+=col[i]=($i ~ /chronic/)
         if(!found) exit
       }
  {
    for(i=1; i<=NF; i++)
        printf ("%s", (col[i]? (c++?OFS:"")$i :"") )
    printf("%s", (c?"\n" : "") ); c=0
  }' infile.csv

We sets the Field Separator and the Output Field Separator to be a comma, indicating that the input file is a CSV file.

For the first input line (which it's assumed that's header row) we creates an array col[] that stores whether each field in the row contains the substring "chronic" then TRUE/1 (by matching each field against /chronic/ regexp) or FALSE/0 if not matching.

The if(!found) exit part of the code tells awk to exit the command and stop processing the input file if there is not any fields to output. Otherwise...

... then for each subsequent row (as well as the first row), it loops over each field in the row and prints the field if the corresponding col[i] value is 1, and prints an empty string otherwise; After processing the row, it prints a newline character if there were any fields out putted (when c counter is non-zero; the counter is also used to add OFS between fields if it's not the first field when ouput), or print nothing otherwise, and reset c to 0.

Answer 3

Assuming that the field names are in the first line of the .csv file like this:

$ cat input.csv 
gender,chronic_disease1,chronic_disease2
male,2008,2009

The following perl one-liner will print the fields where the field names contain the string "chronic":

perl -F, -lane '
  if ($. == 1) {   # first line of input
    # get a list of field numbers & names matching "chronic"
    foreach my $f (0..$#F) {
      if ($F[$f] =~ /chronic/i) { # case-insensitive 
        push @out, $f;            # get the field numbers
        push @outnames, $F[$f];   # get the names too
      }
    };
    last unless (@out);           # exit early if there's nothing to print
  } else {
    print join(",", @outnames) if ($. == 2); # print the header only once
    print join(",", @F[@out])                # print the data
  }' input.csv 

Sample output:

chronic_disease1,chronic_disease2
2008,2009

Note: this works for simple comma-delimited files only. It doesn't work for CSV files that have quoted fields containing embedded commas or newlines. For that, you'd need to use a CSV parser - e.g. perl's Text::CSV, or even perl's DBD::CSV module for DBI which allows you to perform SQL queries on CSV files as if they were an SQL database. Or use miller

Answer 4

Here is a ruby with the built-in csv parser to do that. You can both parse and generate proper quoted csv thus:

ruby -r csv -e 'data_in=CSV.parse($<.read, **{:headers=>true})
cron=data_in.headers.select{|h| h[/chronic/i] }
puts CSV.generate{|csv|
    ([cron] + data_in.values_at(*cron)).each{|row| csv << row}
}
' file

With your example, prints:

chronic_disease1,chronic_disease2
2008,2009

Now suppose an example with quoted fields that would be harder to do with awk alone.

Given:

gender,chronic_disease1,chronic_disease2
male,2008,"2005, type c"
female,"2010, type b",2009

Prints:

chronic_disease1,chronic_disease2
2008,"2005, type c"
"2010, type b",2009

---

As an alternate, you can delete the columns the do not match the regex:

ruby -r csv -e 'data_in=CSV.parse($<.read, **{:headers=>true})
data_in.delete(*data_in.headers.select{|h| !h[/chronic/i] })
puts data_in
' file
# same output

Which may be more efficient depending if you have more matches or non-matches in your csv.

Or if you have a HUGE file, processing the csv line by line is certainly more efficient:

ruby -r csv -e '
    header=[]
    $<.each_with_index{|line,i|
        row=CSV.parse(line).flatten
        if i==0
            row.each_with_index{|e,i| if e[/chronic/i] then header << i end }
        end
    puts header.map{|i| row[i] }.to_csv
}' file

That version removes columns from a 400MB file into a second file in about 3 minutes.

Just for comparison, here is an awk:

awk '
BEGIN{FS=OFS=","}
NR==1{for(i=1;i<=NF;i++) if ($i~/chronic/) head[++cnt]=i}
{   
    for(i=1;i<=cnt;i++)
        printf "%s%s", $(head[i]), (i<cnt ? OFS : ORS)
}
' file

That processes the same 400MB in about 45 seconds. So with simple csv, awk is better, faster. Throw in a few complexities, Ruby is easier and more robust.

Answer 5

Using Raku (formerly known as Perl6)

~$ raku -MText::CSV -e '  \

  #read header into @hdr array
      my $csv1 = Text::CSV.new;
      my $fh1 = "chronic_test.txt".IO.open;
      my @hdr = $csv1.header($fh1, munge-column-names => "fc").column-names;
      close $fh1;

  #read full csv file into @whole array
      my $csv2 = Text::CSV.new;
      my $fh2 = "chronic_test.txt".IO.open; 
      my @whole; while $csv2.getline($fh2) -> $row {
      @whole.push: $row;
      }; close $fh2;

  #output array that has been @whole>>.[index] filtered for desired columns
     .join(",").put for @whole>>.[@hdr.grep(/chronic/, :k)];'

Sample Input:

gender,chronic_disease1,chronic_disease2
male,2008,2009

Sample Output:

chronic_disease1,chronic_disease2
2008,2009

Raku is a language in the Perl family of programming languages. It features high-level support for Unicode, and a robust Regex implementation.

Raku's Text::CSV module parses valid CSV, and can output valid CSV. Check the markdown document below if you need to accept alternate column separators (e.g. tabs), or how to handle quoted fields, blank fields, embedded newlines and/or commas, etc.

Above is a fairly robust (but verbose) method of reading/filtering a CSV file by column names. Briefly, the header gets read twice, and a Regex is used to grep out the matching columns. You can munge the column names, to switch them to other cases if desired (uc, lc, fc, etc.).

The markdown document at the bottom provides the following code to output a CSV file (modified to output only desired columns):

# and write CSV file, filtered as above
my $fh_out = open "new.csv", :w;
$csv.say($fh_out, $_) for @whole>>.[@hdr.grep(/chronic/, :k)];
$fh_out.close;

---

---

MORE EFFICIENTLY: Note, the code above actually reads the @whole csv file into memory, albeit line-by-line. The code below only reads @filtered csv columns into memory and therefore may be more memory efficient.

Caution: it is very important to "promote" a $-sigiled scalar to a @-sigiled array when using such an object as a "positional index". Promotion can either be of the form @($index) or more simply, @$index:

~ % raku -MText::CSV -e '  \

  #read header into @hdr array
      my $csv1 = Text::CSV.new;
      my $fh1 = "chronic_test.txt".IO.open;
      my @hdr = $csv1.header($fh1, munge-column-names => "fc").column-names;
      my $index = @hdr.grep(/chronic/, :k); close $fh1;

  #read filtered csv file into @filtered array
      my $csv2 = Text::CSV.new;
      my $fh2 = "chronic_test.txt".IO.open;
      my @filtered; while $csv2.getline($fh2) -> $row {
      @filtered.push: $row.[@$index];
      }; close $fh2;

     .join(",").put for @filtered;'

https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md
https://docs.raku.org
https://raku.org

Answer 6

Using any awk:

$ cat tst.awk
BEGIN { FS=OFS="," }
NR == 1 {
    for ( inFldNr=1; inFldNr<=NF; inFldNr++ ) {
        if ( $inFldNr ~ /chronic/ ) {
            out2inFldNrs[++numOutFlds] = inFldNr
        }
    }
}
{
    for ( outFldNr=1; outFldNr<=numOutFlds; outFldNr++ ) {
        inFldNr = out2inFldNrs[outFldNr]
        printf "%s%s", $inFldNr, (outFldNr<numOutFlds ? OFS : ORS)
    }
}

$ awk -f tst.awk file
chronic_disease1,chronic_disease2
2008,2009

Hopefully it's obvious from the variable names what that does. Aside from being clear, simple and portable (it'll work on all Unix boxes), the main functional benefit of this approach vs others is that after the first line you only loop as many times as you you have output fields to print, you don't have to loop through all of the input fields deciding which to print.

Answer 7

Using Raku (formerly known as Perl_6)

...this time, using Raku's CSV::Parser module:

~$ raku -MCSV::Parser -e '  \

    my $fh = open "chronic_test.csv", :r;
    my $parser = CSV::Parser.new( file_handle => $fh, contains_header_row => False );

    #declare data structures and iterate over lines:
    my @data; my %header; my $index; my Int $i = 0;
    until $fh.eof { $_ = $parser.get_line();

    #read first line into %-sigiled hash, filter for `chronic`, and store as sorted $index:
    if $i++ == 0 { %header .= push: $_.pairs;
    $index = do for %header.kv -> $k,$v { $k if $v.grep: /chronic/ };
    $index .= sort() };

    #read all lines into @-sigiled array, keeping correct column order:
    @data .= push: $_.pairs.sort({.key.Int})>>.values ;
    }

    #use @data>>.[$index] idiom to filter for desired columns, and output:
    .join(",").put for @data>>.[$index];
    $fh.close;  

The Raku code above uses Raku' CSV::Parser module, which appears to be entirely hash-based. This may be more efficient for some purposes, however care has to be taken to return columns in their original order.

Briefly, a filehandle is opened and a new $parser object is created, telling Raku via the parameter contains_header_row => False that we'll handle the header ourselves.

The first line is read into %header which gets filtered for the desired /chronic/ Regex, and these keys (i.e. column numbers) are stored as a sorted $index. Note adding :i to the Regex (to make /:i chronic/) enables case-insensitive search.

All lines are then pushed onto the @data array, taking care that columns aren't scrambled. Finally, the array is filtered for the desired columns using the @data>>.[$index] idiom, and output.

Sample Input:

gender,chronic_disease1,chronic_disease2
male,2008,2009

Sample Output:

chronic_disease1,chronic_disease2
2008,2009

https://raku.land/zef:tony-o/CSV::Parser
https://github.com/tony-o/perl6-csv-parser
https://docs.raku.org
https://raku.org