Identify duplicate values at first column in csv file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Identify duplicate values at first column in csv file
# 1  
Old 10-16-2015
Identify duplicate values at first column in csv file

Input
Code:
1,ABCD,no 
2,system,yes 
3,ABCD,yes 
4,XYZ,no 
5,XYZ,yes
6,pc,no

Code used to find duplicate with regard to 2nd column
Code:
awk 'NR == 1 {p=$2; next} p == $2 { print "Line" NR "$2 is duplicated"} {p=$2}' FS="," ./input.csv

Now is there a wise way to de-duplicate the entire line (remove the duplicate) based on the criteria found within this one liner or wrapped around additional logic?

Last edited by deadyetagain; 10-16-2015 at 05:57 PM.. Reason: Add CODE tags, change ICODE tags to CODE tags.
# 2  
Old 10-16-2015
depending on what your desired output should be:
Code:
awk -F, '!a[$2]++{next} {print "Line " NR " " $2 " is duplicated"}' myFile
OR
awk -F, '!a[$2]++' myFile

This User Gave Thanks to vgersh99 For This Post:
# 3  
Old 10-16-2015
Quote:
Originally Posted by deadyetagain
Input
Code:
1,ABCD,no 
2,system,yes 
3,ABCD,yes 
4,XYZ,no 
5,XYZ,yes
6,pc,no

Code used to find duplicate with regard to 2nd column
Code:
awk 'NR == 1 {p=$2; next} p == $2 { print "Line" NR "$2 is duplicated"} {p=$2}' FS="," ./input.csv

Now is there a wise way to de-duplicate the entire line (remove the duplicate) based on the criteria found within this one liner or wrapped around additional logic?

Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) for full-line or multi-line code segments AND for sample input and sample output.
Your thread title says you are trying to find duplicates in the 1st field; your code prints lines in which the 2nd field on the line has been seen before. Note that it prints duplicates; it does not remove duplicates.

And, since there are no lines in your sample input where the 1st field is duplicated on any other line, I have no idea what you are trying to do. What additional logic are you talking about? What output are you hoping to produce from this sample input?
# 4  
Old 10-16-2015
Quote:
Originally Posted by vgersh99
depending on what your desired output should be:
Code:
awk -F, '!a[$2]++{next} {print "Line " NR " " $2 " is duplicated"}' myFile
OR
awk -F, '!a[$2]++' myFile

Does this remove the line when $2 is found to have been a duplicate?

---------- Post updated at 05:19 PM ---------- Previous update was at 05:17 PM ----------

Quote:
Originally Posted by Don Cragun
Your thread title says you are trying to find duplicates in the 1st field; your code prints lines in which the 2nd field on the line has been seen before. Note that it prints duplicates; it does not remove duplicates.

And, since there are no lines in your sample input where the 1st field is duplicated on any other line, I have no idea what you are trying to do. What additional logic are you talking about? What output are you hoping to produce from this sample input?
Thank you for the suggestion.
I tried to change the title but that doesn't seem to be an option once the submission is made...

---------- Post updated at 05:19 PM ---------- Previous update was at 05:19 PM ----------

Quote:
Originally Posted by Don Cragun
Your thread title says you are trying to find duplicates in the 1st field; your code prints lines in which the 2nd field on the line has been seen before. Note that it prints duplicates; it does not remove duplicates.

And, since there are no lines in your sample input where the 1st field is duplicated on any other line, I have no idea what you are trying to do. What additional logic are you talking about? What output are you hoping to produce from this sample input?
Thank you for the suggestion.
I tried to change the title but that doesn't seem to be an option once the submission is made...
# 5  
Old 10-16-2015
Quote:
Originally Posted by deadyetagain
Does this remove the line when $2 is found to have been a duplicate?
I'd say it's for YOU to find out...
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

CSV File:Filter duplicate records from column1 & another column having unique record

Hi Experts, I have csv file with 30, 40 columns Pasting just 2 column for problem description. Need to print error if below combination is not present in file check for column-1 (DocumentNumber) and filter columns where value in DocumentNumber field is same. For all such rows, the field... (7 Replies)
Discussion started by: as7951
7 Replies

2. Shell Programming and Scripting

Filter duplicate records from csv file with condition on one column

I have csv file with 30, 40 columns Pasting just three column for problem description I want to filter record if column 1 matches CN or DN then, check for values in column 2 if column contain 1235, 1235 then in column 3 values must be sequence of 2345, 2345 and if column 2 contains 6789, 6789... (5 Replies)
Discussion started by: as7951
5 Replies

3. Shell Programming and Scripting

Find duplicate values in specific column and delete all the duplicate values

Dear folks I have a map file of around 54K lines and some of the values in the second column have the same value and I want to find them and delete all of the same values. I looked over duplicate commands but my case is not to keep one of the duplicate values. I want to remove all of the same... (4 Replies)
Discussion started by: sajmar
4 Replies

4. Shell Programming and Scripting

Remove duplicate values in a column(not in the file)

Hi Gurus, I have a file(weblog) as below abc|xyz|123|agentcode=sample code abcdeeess,agentcode=sample code abcdeeess,agentcode=sample code abcdeeess|agentadd=abcd stereet 23343,agentadd=abcd stereet 23343 sss|wwq|999|agentcode=sample1 code wqwdeeess,gentcode=sample1 code... (4 Replies)
Discussion started by: ratheeshjulk
4 Replies

5. Shell Programming and Scripting

Filter file to remove duplicate values in first column

Hello, I have a script that is generating a tab delimited output file. num Name PCA_A1 PCA_A2 PCA_A3 0 compound_00 -3.5054 -1.1207 -2.4372 1 compound_01 -2.2641 0.4287 -1.6120 3 compound_03 -1.3053 1.8495 ... (3 Replies)
Discussion started by: LMHmedchem
3 Replies

6. Shell Programming and Scripting

Remove the values from a certain column without deleting the Column name in a .CSV file

(14 Replies)
Discussion started by: dhruuv369
14 Replies

7. Shell Programming and Scripting

Fetching values in CSV file based on column name

input.csv: Field1,Field2,Field3,Field4,Field4 abc ,123 ,xyz ,000 ,pqr mno ,123 ,dfr ,111 ,bbb output: Field2,Field4 123 ,000 123 ,111 how to fetch the values of Field4 where Field2='123' I don't want to fetch the values based on column position. Instead want to... (10 Replies)
Discussion started by: bharathbangalor
10 Replies

8. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

9. Shell Programming and Scripting

Check to identify duplicate values at first column in csv file

Hello experts, I have a requirement where I have to implement two checks on a csv file: 1. Check to see if the value in first column is duplicate, if any value is duplicate script should exit. 2. Check to verify if the value at second column is between "yes" or "no", if it is anything else... (4 Replies)
Discussion started by: avikaljain
4 Replies
Login or Register to Ask a Question