Remove specific CSV columns from large CSV file and duplicated rows with Perl

Question

I have a large CSV file (300MB+) and I want to remove only columns 2,3 and 6-8 using Perl AND remove duplicated rows:

Note 1: all columns are separated by , (commas) but sometimes my cell values contains a , or multiple , and are separated by " (see last row, columns 9 and 10); thus I'd like still be able to process the input.csv file even if it has , inside the cells:

Note 2: I've added links to the input.csv and output.csv files:

input.csv

Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10
info 1,info 2,info 3,...,info 10
address 1,address 2,....,address 10
city 1,city 2,city 3,city 4,city 5,city 6,city 7,city 8,"city 9, extra","city 10, new"

output.csv

Col1,Col4,Col5,Col9,Col10
info 1,info 4,info 5,info 9,info 10
address 1,address 4,address 5,address 9,address 10
city 1,city 4,city 5,"city 9, extra","city 10, new"

I've found a Perl command that would remove the last column using regex, but don't know if it's good enough or how to tweak it to suit my case (any other suggestion is more than welcomed!):

perl -pe 's/.*\K,.*//'

Is it possible to remove only columns 2,3 and 6-8 using Perl and remove any duplicated rows?

PS: updated the input.csv file to include duplicate rows as well

Thank you!

Answer 1

The easiest way to do this would be to use miller aka mlr, which is a great tool for working with data in CSV, json, and a few other input or output formats. For example:

$ mlr --csv --implicit-csv-header --headerless-csv-output \
    cut -x -f 2,3,6,7,8 \
    then uniq -a input.csv  
Col1,Col4,Col5,Col9,Col10
info 1,info 4,5,9,info 10
address 1,4,5,9,address 10
city 1,4,5,9,city 10

Using both the --implicit-csv-header and --headerless-csv-output options effectively ignores the header line (i.e. treat it the same as the other data lines) and allow me to specify the fields to be cut by number rather than by name.

I had to edit your sample input.csv file to add some junk data in the missing fields. mlr would have complained otherwise. I also added a duplicate input line to test that the dupe elimination was working.

$ cat input.csv 
Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10
info 1,info 2,info 3,info 4,5,6,7,8,9,info 10
info 1,info 2,info 3,info 4,5,6,7,8,9,info 10
address 1,address 2,3,4,5,6,7,8,9,address 10
city 1, city 2,3,4,5,6,7,8,9,city 10

---

If you want to do it with perl:

1. If you only need to handle simple comma-delimited input:

$ perl -F, -lane '
  next if $seen{$_}++;
  splice @F,5,3;
  splice @F,1,2;
  print join ",", @F' input.csv
Col1,Col4,Col5,Col9,Col10
info 1,info 4,5,9,info 10
address 1,4,5,9,address 10
city 1,4,5,9,city 10

This uses perl's -a option to auto-split each input line into an array called @F. The -F option tells it what delimiter to use.

Note 1: perl arrays start from zero, not one...so array element 5 is column 6. splice @$row, 5, 3 removes three elements from the array starting from element 5 (i.e. columns 6,7,8). See perldoc -f splice for details.

Note 2: I am deleting the columns in reverse order here (i.e. higher-numbered columns before lower-numbered). Otherwise, if I deleted columns 2 & 3 before deleting columns 5,6,7, the first deletion would cause those columns to be renumbered (to 3,4,5)

2. Using Text::CSV to handle any valid CSV (including things like multi-line quoted columns containing commas):

$ perl -MText::CSV -e '
  my $csv = Text::CSV->new();
  while (my $row = $csv->getline(*ARGV)) {
    next if $seen{join ",", @$row}++;
    splice @$row, 5, 3;
    splice @$row, 1, 2;
    $csv->say(*STDOUT, $row);
  }' input.csv
Col1,Col4,Col5,Col9,Col10
"info 1","info 4",5,9,"info 10"
"address 1",4,5,9,"address 10"
"city 1",4,5,9,"city 10"

There are four things worth noting here:

1. Text::CSV is not a core perl module, so it needs to be installed. It's packaged for most, if not all, Linux distributions. e.g. on Debian, you can install it with sudo apt-get install libtext-csv-perl. Otherwise, you can install it with the cpan command which comes with perl.

2. Text::CSV's getline() method (as in $row = $csv->getline(*ARGV) above) returns a reference to an array, or arrayref. That's a scalar value that points to an entire array (see man perlref and man perldata for more info).

3. $row in the code above contains the arrayref. Using/manipulating $row works on the reference itself, not the data it is referencing. So, e.g., $row2 = $row makes a copy of the reference, not the data. Both refs point to the same data. @$row "de-references" the arrayref as an array, so that it can be used just like any other array.

4. The *ARGV in getline(*ARGV) is a special file handle that reads input from ALL filename arguments given on the command line (which are stored in an array called @ARGV in perl). It is assumed that non-filename arguments (e.g. options, if your script has code to handle options) have already been processed and removed from @ARGV. Filenames that don't exist or can't be opened (e.g. due to permissions) will produce an error message. In short, it reads from one or more filenames that you give it. An argument of - is treated as stdin, so it can read input from file(s), stdin, or both.

This is a really simple & primitive example of what Text::CSV is capable of and how it can be used. Read the man page for more details and examples.

As you can see in the sample output above, Text::CSV will, by default, quote text fields if they contain a space. If you don't want it to do that, you can override that by setting the quote_space attribute to zero....either when you create the $csv object with the new method:

my $csv = Text::CSV->new({ quote_space => 0 });

or afterwards:

my $csv = Text::CSV->new();
$csv->quote_space(0);

The output would then be like this:

Col1,Col4,Col5,Col9,Col10
info 1,info 4,5,9,info 10
address 1,4,5,9,address 10
city 1,4,5,9,city 10

Answer 2

Turn it into an array, think about, recreate it as a csv:

perl -pe '@c = split(","); splice(@c, 1, 2); splice(@c, 3, 3); $_ = join(", ", @c)

If you're fields are quoted, you can use Text::CSV:

$ cat in.csv 
Col1,Col2,Col3,Col4,Col5
one,two,three,four,five
six,"se,ven","ei,ght",nine,ten
$ perl -MText::CSV -e 'Text::CSV::csv( in => "in.csv", headers => false, on_in => sub { splice( @{@_[1]}, 1, 2) } )'
Col1,Col4,Col5
one,four,five
six,nine,ten

You ask about perl, but for the sake awareness also consider the cut tool: cut -f '1,4,5,9,10' -d ,