Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Diff CSV files - But ignore certain fields

by tsk1979 (Scribe)
on Mar 04, 2013 at 10:59 UTC ( #1021626=perlquestion: print w/replies, xml ) Need Help??
tsk1979 has asked for the wisdom of the Perl Monks concerning the following question:

I have a script which runs over a data generated, and then creates a csv file Now I need to diff multiple csv files. But the diff as a twist Lets take a look at my sample csv file
Index1,Index_ID,Final_result,Method1,TimeGiven,TimeTaken,Result,Method +2,TimeGiven,TimeTaken,Result set1,541,PASSED,SOLVE12,13020,13481,UNDETERMINISTIC,Solve2,329614,683, +PASSED set1,542,FAIL,SOLVE12,13020,13531,UNDETERMINISTIC,Solve2,315259,84094, +FAIL set1,545,UNDETERMINISTIC,SOLVE12,13638,13403,UNDETERMINISTIC,Solve2,43 +6268,437526,UNDETERMINISTIC set1,546,UNDETERMINISTIC,SOLVE12,13638,13420,UNDETERMINISTIC + set1,547,UNDETERMINISTIC,SOLVE12,13638,13476,UNDETERMINISTIC + set1,548,UNDETERMINISTIC,SOLVE12,13638,13476,UNDETERMINISTIC + set1,549,UNDETERMINISTIC,SOLVE12,13638,13486,UNDETERMINISTIC + set1,550,UNDETERMINISTIC,SOLVE12,13638,13530,UNDETERMINISTIC + set1,551,UNDETERMINISTIC,SOLVE12,13638,13461,UNDETERMINISTIC + set1,552,UNDETERMINISTIC,SOLVE12,13638,13449,UNDETERMINISTIC +
The first two fields constitude the index. So if I am comparing 2 csv files, I will compare based on this index. eg set1.551
To my script I will give more than 1 generated csv files
I want to print all those lines in a new CSV where
1. Index is not present in all the csv files
2. Either Field 3, field 7, and field 12(corresponding to columns) is different
Any fast and easy way to do this.

I have tried some hash based methods, but once I go to more than 2 files, things get complicated.
Here is what I am kind of trying
1. Read all csv files, create a hash of index<nr> 2. Store all indexes in an array which are not present in all the csv files
3. Store all indexes in an array where the number of columns for that particular row are different
3. For each index, extract the required fields, and then see if they are same in all the csv files
As you can see, its getting fairly complicated and I am getting confused, any help on how to proceed.

Replies are listed 'Best First'.
Re: Diff CSV files - But ignore certain fields
by McA (Priest) on Mar 04, 2013 at 16:50 UTC


    I give that as a starting point. Nr. 1 should be solved. the rest is an execise for tsk1979.

    #!/usr/bin/perl use warnings; use strict; use Text::CSV; use Data::Dumper; die "ERROR: You have to provide at least one csv file." unless(@ARGV); my %RESULT; foreach my $filename (@ARGV) { if(grep { $_ eq $filename } keys %RESULT) { warn "WARN: Why dou you want to compare a file '$filename' whi +ch you have read already. Skipped."; next; } my $csv = Text::CSV->new( { binary => 1 } ) or die "Cannot use CSV: " . Text::CSV->error_diag (); open my $fh, "<", $filename or die "ERROR: Can't open '$filename': + $!"; my $counter = 0; while (my $row = $csv->getline($fh)) { $counter++; next if($counter < 2); unless(@$row <= 11) { die "ERROR: Line '$counter' of file '$filename' has wrong +format. Check your data crap."; } my $index = $row->[0]; my $index_id = $row->[1]; $RESULT{$filename}->{$index}->{$index_id} = { 'record' => $row + }; } $csv->eof or die "ERROR: Couldn't read csv file '$filename': " . $ +csv->error_diag(); close $fh; } # Missing keys my %FOUND_KEYS; my $files_loaded = scalar keys %RESULT; foreach my $filename (keys %RESULT) { foreach my $index (keys %{$RESULT{$filename}}) { foreach my $index_id (keys %{$RESULT{$filename}->{$index}}) { my $key = $index . '@' . $index_id; my $hash_ref = $FOUND_KEYS{$key} || {}; $hash_ref->{$filename} = 1; $FOUND_KEYS{$key} = $hash_ref; } } } foreach my $key (keys %FOUND_KEYS) { if(scalar keys %{$FOUND_KEYS{$key}} != $files_loaded) { print "INFO: Key combination '$key' only found in the followin +g files: " . join(', ', sort keys %{$FOUND_KEYS{$key}}) . "\n"; } }

    Best regards

Re: Diff CSV files - But ignore certain fields
by tmharish (Friar) on Mar 04, 2013 at 14:07 UTC
    I have a script ...

    I cant help with a script I cant see - please update with script?

Re: Diff CSV files - But ignore certain fields
by McA (Priest) on Mar 04, 2013 at 14:44 UTC


    IMHO the csv file format is not correct. The first line of the file lets the user assume you allways get 11 fields. Afterwards there are not enough commas to define 11 fields even if they are empty.

    Best regards

Re: Diff CSV files - But ignore certain fields
by stonecolddevin (Vicar) on Mar 07, 2013 at 22:05 UTC

    1. Use Text::CSV_XS to parse your CSV
    2. Read your files into two separate arrays of arrays, populating the arrays with only the fields you need
    3. Use Set::Scalar to do a difference operation to get your diff

    Three thousand years of beautiful tradition, from Moses to Sandy Koufax, you're god damn right I'm living in the fucking past

My solution
by tsk1979 (Scribe) on Mar 19, 2013 at 10:18 UTC
    I finally came up with a solution. you can choose which field to diff, and then print a diff report based on that field
    use strict; use warnings; if ($#ARGV < 1) { die "usage <csvoutout1> <csvout2> +<csvoutout3>.....\n"}; my $f=0; my %allhash; my @indexes_to_print; my %data; my @line; my $index; foreach my $files (@ARGV) { $f++; open FILE,$files or die "Cannot open $files\n"; print "Opened $files \n"; while (<FILE>) { next if (/csvout_ID/); chomp; @line = split(/,/,$_); $index = $line[0].".".$line[1]; $allhash{$index} = 1; $data{$f}{$index} = "$_"; } close FILE; } foreach $index (keys %allhash) { my @temparray; my $flag = 0; #First lets take all those indexes which are not in all files foreach my $dataset (keys %data) { if (!(exists $data{$dataset}{$index})) { push (@indexes_to_print,$index); #delete $allhash{$index}; $flag = 1; } } next if ($flag eq 1); #Now we take those indexes where the Final result is different for (my $i=1;$i<=$f;$i++) { my @temp = split(/,/,$data{$i}{$index}); $temparray[$i] = $temp[2]; if ($i>1) { if ($temparray[$i] !~ $temparray[$i-1]) { push (@indexes_to_print,$index); } } } } #uniquify @indexes_to_print # my %seen = map { $_, 1 } @indexes_to_print; my @unique_indexes = keys %seen; foreach my $index (@unique_indexes) { print "$index"; for (my $i=1;$i<=$f;$i++) { if (exists $data{$i}{$index}) { my @temp = split(/,/,$data{$i}{$index}); #print ",$i,$temp[2]"; print ",$i"; foreach (my $j=2;$j<=10;$j++) { if (exists ($temp[$j])) { print ",$temp[$j]"; } else { print ","; } } } else { print ",$i,NA,,,,,,,,"; } } print "\n"; }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1021626]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (10)
As of 2017-02-28 18:02 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (407 votes). Check out past polls.