Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

I have a CSV file users.csv with a list of userNames, userIDs, and other data:

username, userid, sidebar_side, sidebar_colour
"John Lennon", 90123412, "left", "blue"
"Paul McCartny", 30923833, "left", "black"
"Ringo Starr", 77392318, "right", "blue"
"George Harrison", 72349482, "left", "green"

In another file toremove.txt I have a list of userIDs:

30923833
77392318

Is there a clever, efficient way to remove all the rows from the users.csv file which contain the IDs in toremove.txt? I have written a simple Python app to parse the two files and write to a new file only those lines that are not found in toremove.txt, but it is extraordinarily slow. Perhaps some sed or awk magic can help here?

This is the desired result, considering the examples above:

username, userid, sidebar_side, sidebar_colour
"John Lennon", 90123412, "left", "blue"
"George Harrison", 72349482, "left", "green"
share|improve this question
    
Maybe you should share your python script. I suspect there's something wrong there, like being O(N²) Although if you are keeping and removing millions of records magic won't help too much. –  Ángel Jul 17 at 18:28
    
The script is in fact O(n<sup>2</sup>): n for the users.csv file's lines, and n for the lines of toremove.txt. I'm not really sure how to do it with lower complexity. The gist of it is: for u in users: if not any(toremove in u): outputfile.write(u). I can post it to Code Review. –  dotancohen Jul 17 at 19:21
    
I would read toremove.txt, saving the entries as keys. Iterate users.csv, printing those where the id is not in the dict. You get O(n) processing for both toremove.txt and users.csv, and O(n) memory usage for toremove.txt (which is probably relatively small) –  Ángel Jul 17 at 19:28
    
@Ángel: Yes, that is exactly how the script works! –  dotancohen Jul 17 at 19:32
1  
Checking if a key exists in a dictionary, equals to a hash table check, which is (almost) O(1). On the other hand, if it needs to iterate the items to remove, that's O(m) –  Ángel Jul 17 at 22:25

2 Answers 2

up vote 6 down vote accepted

With grep, you can do:

$ grep -vwF -f toremove.txt users.txt 
username, userid, sidebar_side, sidebar_colour
"John Lennon", 90123412, "left", "blue"
"George Harrison", 72349482, "left", "green"

With awk:

$ awk -F'[ ,]' 'FNR==NR{a[$1];next} !($4 in a)' toremove.txt users.txt 
username, userid, sidebar_side, sidebar_colour
"John Lennon", 90123412, "left", "blue"
"George Harrison", 72349482, "left", "green"
share|improve this answer
    
Perfect, thank you! –  dotancohen Jul 17 at 12:43
    
@terdon: Dang! I was going to say that. Note, though, that Gnouc’s answer (arguably) does what the question asks for, but it might not be what the user wants. –  Scott Jul 17 at 15:59
    
The awk solution is highly sensitive to the files’ being formatted exactly as shown in the question. Most glaringly, if a name is just one word/token (i.e., it contains no spaces; e.g., "Bono") or is more than two tokens (i.e., it contains more than one space; e.g., "Sir Paul McCartney"), it will go through even if the userid matches. Less obviously, the same thing happens if there is no space between the first comma and the userid, or if there is more than one space (e.g., "John Lennon", 90123412, …). –  Scott Jul 17 at 16:00
    
@Scott: Yes, it's the reason I put awk solution behind grep –  Gnouc Jul 17 at 16:14

Here’s Gnouc’s awk answer, modified to be space-blind:

awk -F, 'FNR==NR{a[$1];next} !(gensub("^ *","",1,$2) in a)' toremove.txt users.csv

Since it uses only commas (and not spaces) as delimiters, $1 is "John Lennon", $2 is  90123412 (with a leading space), etc.  So we use gensub to remove any number of leading spaces from $2 before we check whether it (the userid) was in the toremove.txt file.

share|improve this answer

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.