1

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

id  emails
1   [email protected]
2   [email protected]
3   [email protected],[email protected],[email protected]

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   [email protected]
2   [email protected]
3   [email protected],[email protected]

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.

Improve this question
3
  • 3
    That’s kind of, not really, a CSV file.
    – Jeff Schaller ♦
    Commented Mar 22, 2018 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
    Commented Mar 23, 2018 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
    Commented Mar 23, 2018 at 2:41

2 Answers 2

Reset to default
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
Improve this answer
3
  • @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
    Commented Mar 22, 2018 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 [email protected] at the end of the file, then you will print 4 in last line.
    – αғsнιη
    Commented Mar 23, 2018 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
    Commented Mar 23, 2018 at 8:49
0

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

Input:

[email protected]
[email protected]
[email protected],[email protected],[email protected]

The command:

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

Output:

[email protected]
[email protected]
[email protected],[email protected]

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       [email protected]
2       [email protected]
3       [email protected],[email protected]
Improve this answer
5
  • 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
    Commented Mar 22, 2018 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
    Commented Mar 23, 2018 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нιη
    Commented Mar 23, 2018 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
    Commented Mar 24, 2018 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нιη
    Commented Mar 24, 2018 at 6:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .