3

I have two files. One is a CSV of data with 60490 lines. Each line is a set of values such as a customer name, service dates, etc.

One of these values in the first file is a VIN value.

I have a second large file that contains a list of 92809 VINs.

I need to find a way to delete all lines in the first file that have a VIN listed in the second file.

I've tried the grep below. This does do what I expect it to do but it's also extremely slow and is killed by the OS after outputting about 50 lines.

$ grep -v -f vinlist data.csv > output.csv

What is the fastest way to achieve this? Everything I've found while researching seems to require fully sorted data with lines in both files being identical or takes an extremely long time/gets killed by OS

data.csv

123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1C4NJPBB4DD122174,2014-01-20  
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1GMDV33179D147281,2014-01-20  
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1FUYDCYB7WP879651,2014-01-20  
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1FM5K8D8XFGA82149,2014-01-20  
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,5TDBT48A72S003496,2014-01-20  

vinlist:

JF1VA1E6XH9812361  
1HGCP26369A103521  
3N1CN7AP0CL810631  
5XYZK3AB7BG089758  
1FM5K8D8XFGA82149  
4S3BMBG61C3019520  
1FTNE24LX4HA22330  
1N4AL3AP8FC420210  
2GTEC19C491123429  
3N1CN7AP5FL944233  
  • What does the vin file look like? Is there any reasonable formatting to the first file? – Stephen Rauch Jun 18 '17 at 18:36
  • 1
    Your grep attempt seems good . You could also combine with -F (fixed string match instead of regex match). Is your grep not working or it is too slow? – George Vasiliou Jun 18 '17 at 18:40
  • @GeorgeVasiliou The grep is working, it's giving me the output I want but the OS kills it after it has given me about 40 lines matching my critera. It also takes about 10 minutes to do that so just increasing memory/swap wouldn't work due to the time it would take. – Arcana Jun 18 '17 at 18:52
  • @StephenRauch I've edited my original question to include the formatting and some anonymized data – Arcana Jun 18 '17 at 18:59
4

Since we know where the vin is in the line, we do not need to do a generic search. Instead we can read the vins into a data structure that is indexed to allow fast look ups.

Using Python you can do:

Code:

# read the vins into a set to allow fast lookup
with open('file3', 'rU') as f:
    vins = {vin.strip() for vin in f.readlines()}

# go through the data file one line at a time
with open('file2', 'rU') as f:
    for line in f.readlines():

        # get the vin in the line
        vin = line.split(',')[8]

        # if the vin is not in our set, print out the line
        if vin not in vins:
            print(line.strip())

Results:

123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1C4NJPBB4DD122174,2014-01-20
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1GMDV33179D147281,2014-01-20
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,1FUYDCYB7WP879651,2014-01-20
123,email@example.com,JOE,BLOGGS,123456789,12345-123,"Place Name",12345,5TDBT48A72S003496,2014-01-20
  • Thank you very much! This worked perfectly and very quickly! – Arcana Jun 18 '17 at 19:24
2

You can use unix command cut to pick the vin in the data.csv. Then use sort and uniq -d to find common vins in both files.

In bash:

cut -d',' -f9 > vin_data
sort vin_data vinlist | uniq -d > vin_to_delete

You can then use python or your fav scripting lang to create a new file. My python script.

f=open('data_vin_removed.csv','w')
v=[i.strip() for i in open('vin_to_delete')]
for i in open('data.csv'):
    if any([j in i for j in v]):
        continue
    else:
        f.write(i)
f.close() 

This will create a file with data.csv lines which don't have the vins in vinlist.

2

Using awk:

Code:

awk -F, 'FNR==NR{a[$1]=1;next} !a[$9] {print}' vin_file data_file

And as suggested from comments a more memory efficient way:

awk -F, 'FNR==NR{a[$1];next} (!($9 in a))' vin_file data_file

How:

  1. Set the field separator to ,
  2. When FNR=NR the first file is being processed. In this case read the vin number into an associative with a value of 1. Then next to skip the rest of the code

  3. For all files after the first, if record number 9 (the vin) is not in the associative array then print the line.

  • @GeorgeVasiliou, awk is at best a second language for me. Thanks for the help. – Stephen Rauch Jun 18 '17 at 19:37
  • Welcome. There is one more point to consider. Using !a[$9] will work but it will create an extra memory position for the non found a[$9]. Using a form that says in pseudo code "$9 not in array a" will do the check but will not create an extra memory position (can be written as (!($9 in a)) in awk) – George Vasiliou Jun 18 '17 at 19:41
  • @Arcana can you time both solution (python vs awk) to see some time results? Just run both solutions using time in front , i.e$ time awk '....' – George Vasiliou Jun 18 '17 at 19:50
1

grep is sufficient for this task:

grep -vFf vinlist data.csv

However, in your example text, there are trailing spaces in the vinlist, so to trim those out: (assuming bash, or other shell with process substitution syntax)

grep -vFf <(sed -r 's/^[[:blank:]]+|[[:blank:]]+$//g' vinlist) data.csv

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.