Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

I have file.csv that looks like this

4,6,18,23,26
5,12,19,29,31
2,5,13,16,30
9,10,24,27,32
4,5,10,19,22
4,6,8,10,25
2,3,4,25,11

I want to find some patterns and save them in another log file file.log and remove them from the first file. Perl or grep ideally

  • for instance, if x+1 = x2, in range of 3, remove the row and log its existence in another file and where it existed. So then 2,4,5,25,11 will be removed from file.csv and in file.log I would find something like row 7: 2,3,4,25,11 was removed from file.csv. I'm trying to find sequences
share|improve this question
1  
Do you have a list of the pattern you want to find or do you want to machine learn the patterns? –  don.joey Aug 8 at 13:39
    
Also, your title speaks about txt files, but your question about csv files. –  don.joey Aug 8 at 13:39
    
CSV is text. I wouldn't give it too much thought, I think he just mean this is stuff that can be readily processed; not binary. –  Oli Aug 8 at 13:41
    
@don.joey i have patterns i want to look for, if machine learning is complicated topic, i'll ask about it later, but for now i have my patterns. I edited the title –  Fischer Aug 8 at 13:45
    
@Oli yes true, i edited the title anyway –  Fischer Aug 8 at 13:45

3 Answers 3

up vote 5 down vote accepted

If we interpret your requirement to mean that the value of the third field (column) should be one more than that of the second field (column), then with awk you can do things like

awk -F, '
$3==$2+1 {print "row "NR": "$0" was removed from "FILENAME > "file.log"; next}1
' file.csv > newfile.csv

which will create your file.log as specified and write the remaining lines to newfile.csv. You can rename newfile.csv to file.csv after to simulate deletion.

share|improve this answer
    
This is quite smart. I'm usually all over awk but this just plain didn't occur to me today. Might be good to show how you could chain multiple patterns (with some containing multiple conditions) together into one beast. –  Oli Aug 8 at 19:39
    
What's the 1 doing on the end? –  Oli Aug 8 at 19:39
    
The 1 is a shorthand way to make it print everything else to newfile (every line that's not skipped owing to the next evaluates as 1 i.e. true, thus invoking awk's default action - which is to print the line) –  steeldriver Aug 8 at 20:01
    
although i'm a python guy, thumbs up for awk! i shouldve accepted the perl solution since i asked for it originally, but one has to admit this is the most beautiful script here –  Fischer Aug 13 at 18:54
    
There's something wrong, that script test if s3=s2+1.... but I want it to be tested until the third row. I mean if there's 3 consecutive numbers they should be removed not 2 –  Fischer Aug 13 at 22:29

I think you need a heavier programming language for this. Python is my language of choice so here's a simple script with a simple example of a test:

import sys

tests = [
    lambda a, b, c, d, e: a+1==b and b+1==c and c+1==d and d+1==e,
]

with open(sys.argv[1]) as f:
    for line in f:
        if any(t(*map(int, line.split(','))) for t in tests):
            sys.stderr.write('Line removed: %s\n' % line)
            continue
        print line

That's obviously only a skeleton example of tests but it should be usable. Run normally it will output just the lines that don't match to STDOUT and the ones that do to STDERR. This makes it useful for redirecting into a new file.

Here it is in action:

$ python patterns.py <(echo -n 1,2,3,4,5)
Line removed: 1,2,3,4,5

$ python patterns.py <(echo -n 1,2,4,4,5)
1,2,4,4,5

Once you've loaded it up with patterns, you can just pass it the csv: python patterns.py input.csv


In terms of performance, Python isn't always the fastest. I use it because it's more than fast enough for web development and the time to write is much faster (which is what costs me time/money).

You can speed things up with PyPy. This is an alternative Python runtime that benchmarks amazingly well. You might not need the PPA version (Trusty ships 2.2, PPA is 2.3.1) but here's how you would:

sudo add-apt-repository ppa:pypy/ppa
sudo apt-get update
sudo apt-get install pypy

You'd then launch your script with pypy script.py or if you're executing it directly change the opening shebang to #!/usr/bin/env pypy. I've done some very simple testing on a 350000-line input file (your example repeated 50000 times) with the above script.

python2 ran it in 1.417s and pypy ran it in 0.645s. In my experience, you're likely going to see an even bigger improvement with more complicated algos.

... But yeah, none of this is going to beat the C/C++ equivalent. If the time it takes to run is money, spend some time reimplementing it in a faster language.

share|improve this answer
    
can it be made faster? yesterday i was trying it on a big file, it's taking too much time, compared to perl which does it in 3 seconds or so –  Fischer Sep 8 at 10:08
    
Ordering your tests so the most likely is first will help, but you'll reach a point where Python does lag behind other languages... It's both slower and faster than Perl (on different things). You could try Pypy which tends to be much faster than the default Python runtime... But if you want to do this seriously, you'll do the whole thing in C/C++ which at this level, it's that hard to do at all. –  Oli Sep 8 at 10:16

perl:

$ perl -i.bak -F, -ane '
    if ($F[0]+1 == $F[1] and $F[1]+1 == $F[2]) {warn "row $.: $_"} else {print}
' file.csv 2>file.log
$ cat file.log
row 7: 2,3,4,25,11
$ cat file.csv
4,6,18,23,26
5,12,19,29,31
2,5,13,16,30
9,10,24,27,32
4,5,10,19,22
4,6,8,10,25
share|improve this answer
    
if i put it in script.pl it wont work, only in terminal –  Fischer Sep 8 at 9:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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