1

I have a file with concatenated emails in it that looks like the following:

id  emails
1   jeff@email.com
2   larry@email.com
3   jprice@email.com,jeff@email.com,jeff@stacko.com

Each row only has distinct emails, but there might be duplicates from one row to another, as seen above in row 1 and row 3. I need to remove duplicates from the file so that the file looks like the following:

id  emails
1   jeff@email.com
2   larry@email.com
3   jprice@email.com,jeff@stacko.com

This means I need to check each row against all of the rows that follow it. This isn't feasible to do with any kind of iterative script given the amount of data I have. I feel like there is a simple (or at least viable) way to accomplish this with awk or sed but I haven't found any yet.

  • 3
    That’s kind of, not really, a CSV file. – Jeff Schaller Mar 22 '18 at 0:33
  • Email addresses, not emails, or do I miss something? Is the order relevant or can you sort it? Do lines with multiple entries have to kept together or are you allowed to split them to different lines? – user unknown Mar 23 '18 at 2:32
  • Has the id to be preserved? What is the amount of data you have? How long does it take with an iterative approach, and what might replace it, without iterating? How much time is acceptable? – user unknown Mar 23 '18 at 2:41
0

if your file is a real csv file (simple-csv) like below, you can use following awk command:

Input:

jeff@email.com
larry@email.com
jprice@email.com,jeff@email.com,jeff@stacko.com

The command:

awk -F, '{ COMMA="";i=0; while (++i<=NF) {
           $1=$i; printf (!seen[$1]++)?COMMA$i:""; COMMA=","}; print ""
}' infile.csv

Output:

jeff@email.com
larry@email.com
jprice@email.com,jeff@stacko.com

If not, and the input is like as giving in your question, you can use below instead:

awk  'NR==1; NR>1{id=$1"\t"; COMMA=$1="";split($0, ar, /,| /); 
    for(i in ar){if(ar[i]!=""){printf(!seen[ar[i]]++)?id""COMMA""ar[i]:""; COMMA=",";id=""}
} print ""}' infile

Output:

id  emails
1       jeff@email.com
2       larry@email.com
3       jprice@email.com,jeff@stacko.com
  • This did the trick! Thanks so much. I was getting some wonky behavior at first from the csvs I was using (slightly different than my abbreviated example above), so I moved that column to its own file, used your suggested command for the deduping, and then added it back in with awk -F, 'FNR==NR{a[NR]=$1;next}{$10=a[FNR]}1' OFS=, deduped_emails.csv original.csv. Worked like a charm! – greg118 Mar 22 '18 at 18:09
  • Except gawk 4+ with PROCINFO["SORTED_IN"], for(i in ar) is not guaranteed to be in numerical order (or any other fixed order) and thus the non-removed output values may be in a different order than the input. This may or may not be okay for this OP, and often will 'accidentally' work with only 3 fields as in the example data. Also if(ar[i]) will discard items like 0 or 0e3 but they aren't valid emailaddrs anyway. – dave_thompson_085 Mar 23 '18 at 4:30
  • @dave_thompson_085 in for(i in ar) we don't need if it's reading in numberical order or not, we just want to remove duplicates. for the if(ar[i]) I fixed, about often will 'accidentally' work with only 3 fields as in the example data I didn't get you mean, it's working just fine as I tested for more than 3 fields as well as single field. – αғsнιη Mar 23 '18 at 7:56
  • If you have a line with say 8 or 10 values (all not dupe) is the output in the same order? What awk (or awks) are you using? – dave_thompson_085 Mar 24 '18 at 1:45
  • @dave_thompson_085 Yes, of course it will/is keep/keeping the order, we don't keep the values all at once and print at the end, I'm checking if it was not seen in array called seen then I will print it out, otherwise it will print empty string. I don't think so it's related to awk version but here is mine GNU Awk 4.1.3 – αғsнιη Mar 24 '18 at 6:13
1

Here is a sed solution that works with your exact input format and hopefully runs fast as well.

sed -rz 's:[ \t]+:,:g;s:$:,:mg;:l;s:,([^,]+),(.*),\1,:,\1,\2,:;tl;s:,$::mg;s:^([^,]+),:\1\t:mg' file.csv

How it works:

The '-z' flag loads the entire file, so the following code is applied once, not on every line as it is by default.

#transform input format to actual CSV format
s:[ \t]+:,:g;s:$:,:mg;
#loop while the s command can still find and replace
:l;
    #main code: find two identical cell values anywhere and delete the latter
    #on a very big file this can suffer from backtracking nightmare
    s:,([^,]+),(.*),\1,:,\1,\2,:;
tl;
#transform format back
s:,$::mg;s:^([^,]+),:\1\t:mg
  • @greg118 I'm curious if the sed script works and especially in what amount of time, if you're willing to try it. I want to learn/optimize sed loops :) – seshoumara Mar 22 '18 at 23:03
  • this doesn't remove duplicated with each line (but yes, OP already stated all mails in each line are uniq), but should be mentioned. anther things is if there was a line let's say 4 jeff@email.com at the end of the file, then you will print 4 in last line. – αғsнιη Mar 23 '18 at 8:00
  • @αғsнιη You're right, since the OP mentioned line emails are unique, I didn't test for it. I'll fix that tomorrow. In my opinion leaving "empty" lines is ok, in browsing a large output they raise attention faster than non-sequential numbering. Thanks for the feedback. – seshoumara Mar 23 '18 at 8:49

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.