0

I have a csv file with a bunch of numbers ranging from 500 to 500,000 and would like to replace the matches with 1 and mismatches by shifting the mismatched content to right simultaneously shifting the rest of the columns to right but leaving the top row untouched and then replacing the empty spaces in that column with 0 so that I have a fixed length matrix, for instance, I have the following:

12345,6457,789,21231,657
6457,21231,657
12345,789,21231

When I search for the string 12345 in the first column replace the matched cells (row 1 column 1 and row 3 column 1) with 1 and mismatched cell (row 2 column 1) with a 0 and comma after pushing the second row to the right..see below:

1,6457,789,21231,657
0,6457,21231,657
1,789,21231

Now when I search for the next string (6457) in the second column perform the same steps as above, that is, replace the matched cells (row 1 column 2 and row 2 column 2) with 1 and mismatched cell (row 3 column 2) with a 0 and comma after pushing the third row to the right ..see below:

1,1,789,21231,657
0,1,21231,657
1,0,789,21231

and so on until I have the desired output as follows:

1,1,1,1,1
0,1,0,1,1
1,0,1,1,0,

Below is a link to the sample file.

https://pastebin.com/AmbHYC9T

Thanks

  • 1
    Are you looking to do this with just regex or JavaScript but using regex within JavaScript? – MyWrathAcademia Aug 6 '19 at 19:35
  • Your original file has a variable number of columns, and floating point numbers. The number of columns also seems to be changing (on some lines) for each step that you present. – Kusalananda Aug 6 '19 at 19:51
  • Both suits for me @MyWrathAcademia. Thanks. – user3441801 Aug 6 '19 at 20:21
  • Yes that's right @Kusalananda – user3441801 Aug 6 '19 at 20:21
  • There is an inconsistency in your example. Do you mean to replace columns or insert a character before columns? Sometimes you replace a column but other times you insert a column. The decimal number in the 3rd row also seems like a mistake because you then replace the first column of that row with 1, resulting in 1,456,7892. Following your logic I'm arriving at a different output to yours: [1, 1, 1, 1] [0, 0, 0] [1, 0, 0] [0, 0, 0]. Also, is it absolutely necessary to involve regex or is doing this in pure JavaScript fine? – MyWrathAcademia Aug 7 '19 at 20:49
0

This Perl script will do what you want:

#!/usr/bin/perl

## Read the file's lines into the @lines aray. 
my @lines = <>;
## read the values from the first line and save them as @values
my @values = split(/,/, $lines[0]);

## Iterate over the values. $#values is the number of elements
## in the array.
for my $i (0..$#values){
  ## Remove trailing newlines and save this value as $value
  chomp(my $value = $values[$i]);
  ## Iterate over each line
  for my $k (0..$#lines ) {
    ## remove trailing newlines. 
    chomp($lines[$k]);
    ## Get this line's values
    my @lineValues = split(/,/, $lines[$k]);
    ## If the value at position $i on this line is the same
    ## as the value at position  $i of the first line.
    if ($lineValues[$i] == $value) {
      ## Set this value to 1
      $lineValues[$i] = 1
    }
    ## If they are not equal
    else {
      ## Prepend '0,' to this line's value
      $lineValues[$i] = '0,' . $lineValues[$i];
    }
    ## Save the modified line back in the @lines array
    $lines[$k] = join ',', @lineValues;
  }

}
## Print the final output
print join "\n", @lines;
print "\n";

Save that as foo.pl and run like this (showing the output if run on your example file from the question):

$ perl foo.pl foo.csv 
1,1,1,1,1
0,1,0,1,1
1,0,1,1,0,

And on the file you linked to:

$ perl foo.pl file.csv 
1,1,1,1,1,1
1,0,1,0,1,1
0,1,0,1,1,1
0,0,1,1,1,1
0,1,1,1,1,1
1,1,1,0,0,1
0,1,1,1,0,1
  • the perl script that you supplied worked like a charm and only took 20 secs to generate the output for a 346kb file. Thanks a lot!! – user3441801 Aug 9 '19 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.