Pre-process csv files before using

DrAxeman has asked for the wisdom of the Perl Monks concerning the following question:

I have a a script that parses a csv file and calculates teh averages of the columns and then outputs it in csv format. During this process, I'd like to strip out certain columns (before processing the averages). I'm stumped on how to do this. I also need to strip periods (.) from the header row. How can I do this?

#!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;
    use Text::CSV;
    use Data::Types qw(:all);

    # Connect to the database, (the directory containing our csv file(
+s))

    my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;");

    # Associate our csv file with the table name 'results'

    $dbh->{'csv_tables'}->{'results'} = { 'file' => 'test.csv'};

    my $sth = $dbh->prepare("SELECT * FROM results WHERE 1=0");
    $sth->execute;
    my @cols = @{$sth->{NAME}};
    shift @cols;
    my $avgSQL =
      'SELECT '
      . join(', ', map { "avg($_) \n" } @cols )
      . ' FROM results';
    my @avgs = $dbh->selectrow_array($avgSQL);

    my %avgsHash;
    @avgsHash{ @cols } = @avgs;  # uses a hash slice to populate %avgs
+Hash
    print "Col,Avg\n";
    printf("%s,%f\n", $_, $avgsHash{$_}) for @cols;
[download]

Comment on Pre-process csv files before using Download Code

Replies are listed 'Best First'.
Re: Pre-process csv files before using by jZed (Prior) on Aug 06, 2005 at 18:12 UTC
To strip the periods, use a regex like s/\.//g;. Then save the file. Then open the file with DBD::CSV and use this kind of approach to create a new file that only has the columns you want: `# my $dbh = DBI->connect( ... ); my $wanted_cols = join ',',qw( colname1 colname2 ...); $dbh->do(" CREATE TABLE new_table AS SELECT ($wanted_cols) FROM old_table ");` [download] That will make a new CSV file that is an exact duplicate of the original except leaving out the columns you don't want. The "CREATE TABLE AS SELECT ..." syntax is only available in the newest version of SQL::Statement (the SQL engine for DBD::CSV).	[reply] [d/l]
Re: Pre-process csv files before using by sk (Curate) on Aug 06, 2005 at 18:19 UTC
Will you know which columns numbers you want to keep? If so, you can just return the required portion of the array and use it for average calculation. `perl -e '@array = qw (hello world 1 2 3); @req = qw (2 3 4); $str = pr +int join (",",@array[@req]), $/;' __END__ 1,2,3` [download] In the above code i have an array called `array` then i have a required columns array called `req`. `@array[@req]` returns a list with values from the required columns. Regarding the removal of "." from the header, at what point do you want to do it? Before you start reading the CSV into DBI or when you write out? Before you use it in DBI would require you to do an inplace edit. Writing out the correct header (without period) should be very easy. A simple regex on the your "variable column" array. `perl -e '@wperiod = qw (hi.1 hi.2 hi.3); print join (",", map {s/\.//g +; $_} @wperiod), $/;' __END__ hi1,hi2,hi3` [download] -SK	[reply] [d/l] [select]
Re^2: Pre-process csv files before using by DrAxeman (Scribe) on Aug 06, 2005 at 18:40 UTC
Is there any way that I can add a line after <code> shift @cols; <code> That will look at the @cols array and then strip column names that meet a specific regular expression?	[reply]
Re^3: Pre-process csv files before using by sk (Curate) on Aug 06, 2005 at 18:51 UTC
Would something like this do? `perl -e'@cols =qw (need1 dont1 need2 dont2 need3); for (@cols) { push( +@req,$_) unless /dont.*/;} print join (",",@req),$/;' __END__ need1,need2,need3` [download] You mentiioned regex so the above should help you. However if the names you want to choose are sent as input then you might want to construct a hash from the list and then drop the ones that should be excluded by checking the hash .	[reply] [d/l]
Re^4: Pre-process csv files before using by DrAxeman (Scribe) on Aug 06, 2005 at 18:58 UTC
Re^5: Pre-process csv files before using by davidrw (Prior) on Aug 06, 2005 at 19:26 UTC
Re^5: Pre-process csv files before using by sk (Curate) on Aug 06, 2005 at 20:34 UTC
Some notes below your chosen depth have not been shown here
Re^5: Pre-process csv files before using by sk (Curate) on Aug 06, 2005 at 19:20 UTC
Some notes below your chosen depth have not been shown here


Keep It Simple, Stupid
	PerlMonks