Find duplicates in 2 & 3rd column and their ID Post: 303000218

Sponsored Content

Top Forums Shell Programming and Scripting Find duplicates in 2 & 3rd column and their ID Post 303000218 by durden_tyler on Thursday 6th of July 2017 01:41:34 PM

07-06-2017

Registered User

Quote:

Originally Posted by busyboy

...
...
I have been trying to find out all IDs for those entries with duplicate names in 2nd and 3rd columns and their count like how many time duplication happened for any name if any,
...
...

If Perl is an option, then you could do something like this for a descriptive answer:

Code:

$
$ perl -lne 'm/^(\S+)\s+(.*)$/;
             $x{$2}->[0] += 1;
             push(@{$x{$2}->[1]}, $1) }{
             while (($k, $v) = each %x) {
                 printf("%-20s occurred %5d times with keys [%s]\n", $k, $v->[0], join(",",@{$v->[1]}))
                 if $v->[0] > 1
             }' data.txt
Aadrian Aadesh       occurred     3 times with keys [0.332158,0.173531,0.173545]
Aagneya Aadam        occurred     3 times with keys [0.854165,0.491736,0.491736]
Aahan Aabid          occurred     3 times with keys [0.0401409,0.910094,0.910094]
Aaditeya Aadhira     occurred     3 times with keys [0.631798,0.193475,0.193443]
Aahlaad Aabheer      occurred     3 times with keys [0.108022,0.265257,0.265232]
Aadir Aadidev        occurred     2 times with keys [0.893188,0.152208]
Aadinath Aadil       occurred     2 times with keys [0.265257,0.845814]
Aadit Aadi           occurred     2 times with keys [0.220351,0.585537]
Aaditya Aadhan       occurred     3 times with keys [0.571077,0.810623,0.810667]
Aagney Aadan         occurred     3 times with keys [0.567394,0.366957,0.366954]
Aadvik Aadavan       occurred     3 times with keys [0.104455,0.484983,0.484934]
Aahva Aaban          occurred     3 times with keys [0.639396,0.893155,0.893134]
Aadil Aadinath       occurred     2 times with keys [0.910094,0.291066]
Aagam Aadarsh        occurred     3 times with keys [0.502931,0.151863,0.151862]
$
$

From the output above, if you wanted to see only the key (1st column in your data file) and nothing else, then maybe something like this could work:

Code:

$
$ perl -lne 'm/^(\S+)\s+(.*)$/;
             $x{$2}->[0] += 1;
             push(@{$x{$2}->[1]}, $1) }{
             while (($k, $v) = each %x) {
                 print join("\n",@{$v->[1]}) if $v->[0] > 1
             }' data.txt
0.332158
0.173531
0.173545
0.854165
0.491736
0.491736
0.0401409
0.910094
0.910094
0.631798
0.193475
0.193443
0.108022
0.265257
0.265232
0.893188
0.152208
0.265257
0.845814
0.220351
0.585537
0.571077
0.810623
0.810667
0.567394
0.366957
0.366954
0.104455
0.484983
0.484934
0.639396
0.893155
0.893134
0.910094
0.291066
0.502931
0.151863
0.151862
$
$

If you post the output you want to see, then it should be helpful.

Last edited by durden_tyler; 07-06-2017 at 02:54 PM..

durden_tyler

View Public Profile for durden_tyler

Find all posts by durden_tyler

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find duplicates in the first column of text file

Hello, My text file has input of the form abc dft45.xml ert rt653.xml abc ert57.xml I need to write a perl script/shell script to find duplicates in the first column and write it into a text file of the form... abc dft45.xml abc ert57.xml Can some one help me plz?

2. Shell Programming and Scripting

need to remove duplicates based on key in first column and pattern in last column

Given a file such as this I need to remove the duplicates. 00060011 PAUL BOWSTEIN ad_waq3_921_20100826_010517.txt 00060011 PAUL BOWSTEIN ad_waq3_921_20100827_010528.txt 0624-01 RUT CORPORATION ad_sade3_10_20100827_010528.txt 0624-01 RUT CORPORATION ...

3. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Hi, I need an awk script (or whatever shell-construct) that would take data like below and get the max value of 3 column, when grouping by the 1st column. clientname,day-of-month,max-users ----------------------------------- client1,20120610,5 client2,20120610,2 client3,20120610,7...

4. Shell Programming and Scripting

Find duplicates in column 1 and merge their lines (awk?)

Hi, I have a file (sorted by sort) with 8 tab delimited columns. The first column contains duplicated fields and I need to merge all these identical lines. My input file: comp100002 aaa bbb ccc ddd eee fff ggg comp100003 aba aba aba aba aba aba aba comp100003 fff fff fff fff fff fff fff...

5. UNIX for Dummies Questions & Answers

Search word in 3rd column and move it to next column (4th)

Hi, I have a file with +/- 13000 lines and 4 column. I need to search the 3rd column for a word that begins with "SAP-" and move/skip it to the next column (4th). Because the 3rd column need to stay empty. Thanks in advance.:) 89653 36891 OTR-60 SAP-2 89653 36892 OTR-10 SAP-2...

6. Shell Programming and Scripting

Find smallest & largest in every column

Dear All, I have input like this, J_15TEST_ASH05_33A22.13885.txt: $$ 1 MAKE SP1501 1 1 4 6101 7392 2 2442 2685 18 3201 4008 20 120 4158 J_15TEST_ASH05_33A22.13885.txt: $$ 1 MAKE SP1502 1 1 4 5125 6416 2 ...

7. Shell Programming and Scripting

Changing values only in 3rd column and 4th column

#cat file testing test! nipw asdkjasjdk ok! what !ok host server1 check_ssh_disk!102.56.1.101!30!50!/ other host server 2 des check_ssh_disk!192.6.1.10!40!30!/ #grep check file| awk -F! '{print $3,$4}'|awk '{gsub($1,"",$1)}1' 50 30 # Output:

8. Shell Programming and Scripting

Solution for replacement of 4th column with 3rd column in a file using awk/sed preserving delimters

input "A","B","C,D","E","F" "S","T","U,V","W","X" "AA","BB","CC,DD","EEEE","FFF" required output: "A","B","C,D","C,D","F" "S", T","U,V","U,V","X" "AA","BB","CC,DD","CC,DD","FFF" tried using awk but double quotes not preserving for every field. any help to solve this is much...

9. Shell Programming and Scripting

awk to Sum columns when other column has duplicates and append one column value to another with Care

Hi Experts, Please bear with me, i need help I am learning AWk and stuck up in one issue. First point : I want to sum up column value for column 7, 9, 11,13 and column15 if rows in column 5 are duplicates.No action to be taken for rows where value in column 5 is unique. Second point : For...

10. UNIX for Beginners Questions & Answers

UNIX script to compare 3rd column value with first column and display

Hello Team, My source data (INput) is like below EPIC1 router EPIC2 Targetdefinition Exp1 Expres rtr1 Router SQL SrcQual Exp1 Expres rtr1 Router EPIC1 Targetdefinition My output like SQL SrcQual Exp1 Expres Exp1 Expres rtr1 Router rtr1 Router EPIC1 Targetdefinition...

LEARN ABOUT POSIX

ypmatch

ypmatch(1)							   User Commands							ypmatch(1)

NAME

       ypmatch - print the value of one or more keys from a NIS map

SYNOPSIS

       ypmatch [-k] [-t] [-d domain] key [key...] mname

       ypmatch -x

DESCRIPTION

       ypmatch	prints	the values associated with one or more keys from the NIS's name services map specified by mname, which may be either a map
       name or a map nickname.

       Multiple keys can be specified; all keys will be searched for in the same map. The keys must be the same case and length. No pattern match-
       ing is available. If a key is not matched, a diagnostic message is produced.

OPTIONS

       The following options are supported:

       -k	       Before printing the value of a key, print the key itself, followed by a colon (:).

       -t	       Inhibit map nickname translation.

       -d domain       Specify a domain other than the default domain.

       -x	       Display	the  map nickname table. This lists the nicknames the command knows of, and indicates the map name associated with
		       each nickname.

OPERANDS

       The following operand is supported:

       mname	The NIS's name services map

EXIT STATUS

       The following exit values are returned:

       0	Successful operation.

       1	An error occurred.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +-----------------------------+-----------------------------+
       |      ATTRIBUTE TYPE	     |	    ATTRIBUTE VALUE	   |
       +-----------------------------+-----------------------------+
       |Availability		     |SUNWnisu			   |
       +-----------------------------+-----------------------------+

SEE ALSO

       ypcat(1), ypfiles(4), attributes(5)

NOTES

       ypmatch will fail with an RPC error message on yp operation if enough file descriptors are not available. The number  of  file  descriptors
       should be increased if this occurs.

SunOS 5.10							    22 Aug 1997 							ypmatch(1)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Find duplicates in the first column of text file

Discussion started by: gameboy87

2. Shell Programming and Scripting

need to remove duplicates based on key in first column and pattern in last column

Discussion started by: script_op2a

3. Shell Programming and Scripting

AWK script to create max value of 3rd column, grouping by first column

Discussion started by: ckmehta

4. Shell Programming and Scripting

Find duplicates in column 1 and merge their lines (awk?)

Discussion started by: falcox