How to find DISTINCT rows and combine in one row?

Login or Register to Ask a Question and Join Our Community

How to find DISTINCT rows and combine in one row?

Tags

Login to Discuss or Reply to this Discussion in Our Community

Top Forums Shell Programming and Scripting How to find DISTINCT rows and combine in one row?

06-17-2013

Registered User

20, 0

Join Date: Jun 2013

Last Activity: 28 September 2013, 7:10 AM EDT

Posts: 20

Thanks Given: 10

Thanked 0 Times in 0 Posts

How to find DISTINCT rows and combine in one row?

Hi ,

i need to display only one of duplicated values and merged them in one record only when tag started with 3100.2.128.8

Code:

3100.2.97.1=192.168.0.12
3100.2.128.8=418/66/03e9/0044801
3100.2.128.8=418/66/03ea/0044601 
3100.2.128.8=418/66/03e9/0044801
3100.2.128.8=418/66/03ea/0044601
3100.2.128.8=418/66/03e9/0044801
3100.2.128.8=418/66/03ea/0044601
3100.2.128.8=418/66/03e9/0044801
3100.2.19.2=665000911

The Result should be like below

Code:

3100.2.97.1=192.168.0.12
3100.2.128.8=418/66/03e9/0044801 | 418/66/03ea/0044601 
3100.2.19.2=665000911

Last edited by OTNA; 06-17-2013 at 12:10 PM..

OTNA

View Public Profile for OTNA

Find all posts by OTNA

06-17-2013

Moderator

3,689, 1,352

Join Date: Jan 2012

Last Activity: 22 August 2020, 11:29 PM EDT

Location: Galactic Empire

Posts: 3,689

Thanks Given: 268

Thanked 1,352 Times in 1,258 Posts

An awk approach:

Code:

awk -F'=' '
        !/3100.2.128.8/ {
                T = H ? $0 : T
                H = H ? H : $0
        }
        /3100.2.128.8/ {
                A[$1] = $2 in R ? A[$1] : A[$1] ? A[$1] "|" $2 : $2
                R[$2] = $1
        }
        END {
                print H
                for ( k in A )
                        print k, A[k]
                print T
        }
' OFS='=' file

Yoda

View Public Profile for Yoda

Visit Yoda's homepage!

Find all posts by Yoda

06-17-2013

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

Try also

Code:

awk -F= 'Arr[$1] !~ $2  {Arr[$1]=Arr[$1] (Arr[$1]?" | ":"") $2}
         END {for (A in Arr) print A "="  Arr[A]}
        ' file
3100.2.97.1=192.168.0.12
3100.2.19.2=665000911
3100.2.128.8=418/66/03e9/0044801 | 418/66/03ea/0044601

RudiC

View Public Profile for RudiC

Find all posts by RudiC

06-18-2013

Registered User

20, 0

Join Date: Jun 2013

Last Activity: 28 September 2013, 7:10 AM EDT

Posts: 20

Thanks Given: 10

Thanked 0 Times in 0 Posts

udiC thanks , but this will distinct every thing even if there any duplicated tag except 3100.2.128.8 , i want to distinct only this tag 3100.2.128.8 , if any other duplicated do not do anything , it working fine thank you just please adding this tag only ,

OTNA

View Public Profile for OTNA

Find all posts by OTNA

06-18-2013

Registered User

5,690, 630

Join Date: Jan 2007

Last Activity: 9 January 2017, 4:40 AM EST

Location: Варна, България / Milano, Italia

Posts: 5,690

Thanks Given: 184

Thanked 630 Times in 587 Posts

Another one:

Code:

awk '$1 == s {
  t[$2]++ || r = r ? r q $2 : $0
  next
  }
  r {
    print r; r = x 
    split(x, t) # use delete x if available
    } 
  1' FS== q=\| s=3100.2.128.8 infile

This User Gave Thanks to radoulov For This Post:

radoulov

View Public Profile for radoulov

Find all posts by radoulov

06-18-2013

Registered User

15,129, 5,008

Join Date: Jul 2012

Last Activity: 4 May 2020, 4:31 PM EDT

Location: Aachen, Germany

Posts: 15,129

Thanks Given: 735

Thanked 5,008 Times in 4,483 Posts

OK, try this:

Code:

awk -F= '!/^3100.2.128.8/       {print; next}
         Arr[$1] !~ $2          {Arr[$1]=Arr[$1] (Arr[$1]?" | ":"") $2}
         END                    {for (A in Arr) print A "="  Arr[A]}
        ' file

This User Gave Thanks to RudiC For This Post:

RudiC

View Public Profile for RudiC

Find all posts by RudiC

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Combine multi-row cell into a single line

My CSV file looks similar to this example (the K, L, and M are in the same cell as J but each on a new line within that cell): 1, A, B, C, D 2, E, F, G, H 3, I, J N, O, K L M, 4, P, Q, R, S I would like to have it look like this: 1, A,...

2. Shell Programming and Scripting

Splitting single row into multiple rows based on for every 10 digits of last field of the row

Hi ALL, We have requirement in a file, i have multiple rows. Example below: Input file rows 01,1,102319,0,0,70,26,U,1,331,000000113200000011920000001212 01,1,102319,0,1,80,20,U,1,241,00000059420000006021 I need my output file should be as mentioned below. Last field should split for...

3. Shell Programming and Scripting

Combine rows with awk

I have a file of 100,000 entries that look like: chr1 980547 980667 + chr1:980547-980667 chr1 980728 980848 + chr1:980728-980848 chr1 980793 980913 + chr1:980793-980913 I am trying to reformat them to into 5 columns that are tab delineated: chr1 980547 980667 + ...

4. Shell Programming and Scripting

Select distinct rows in a file by last column

Hi, I have the following file: LOG:015608::ERR:2310:map_spsrec:Invalid parameter LOG:015608::ERR:2471:map_dgdrec:Invalid parameter LOG:015608::ERR:2487:map_nnmrec:Invalid number LOG:015608::ERR:2310:map_nmrec:Invalid number LOG:015608::ERR:2438:map_nmrec:Invalid number As a delimiter I...

5. Shell Programming and Scripting

Combine rows from two files

I have two files, called "File A" and "File B". File A hdisk120: iprod_0000151 dprod_0000135 iprod_0000148 dprod_0000148 iprod_0000157 iprod_0000155 hdisk118: dprod_0000141 dprod_0000134 dprod_0000155 iprod_0000166 dprod_0000129 lprod_0000017 iprod_0000146

6. Shell Programming and Scripting

Find distinct values

Hi, I have two files of the following format file1 chr1:345-456 chr2:123-456 chr2:455-678 chr3:456-789 chr3:444-555 file2 chr1:345-456 chr2:123-456 chr3:456-789 output

7. Shell Programming and Scripting

Combine 2 files by rows

Hi, I have 2 files, each files contain about 1 millions lines and I want to joint them to build the new files which contain each 2 lines of file1 and 1 lines of file2. Newfiles: line1-file1 line2-file1 line1-file2 line3-file1 line4-file1 line2-file2 ..... Could someone help...

8. Shell Programming and Scripting

To count distinct fields in a row

I have . dat file which contains data in a specific format: 0 3 892 921 342 1 3 921 342 543 2 4 817 562 718 765 3 3 819 562 717 761 i need to compare each field in a row with another field of the same column but different row and cont the...

9. UNIX for Dummies Questions & Answers

how to combine two files together, not by row buy by column

let's say, we have: x1.txt 1 2 3 4 5 6 x2.txt 7 8 9 10 11 12 is there a very simple command like 'cat' that can makes them combine into x3.txt : 1 2 7 8 3 4 9 10 5 6 11 12 Thanks so much for help me with the dummiest questions:o

10. UNIX for Dummies Questions & Answers

select distinct row from a file

Hi, buddies out there. I have a text file ( only one column ) which I created using vi editor. The file contains duplicate rows and I would like to select distinct rows, how to go on it using unix command: file content = apple apple orange watermelon apple orange Can it be done...

Login or Register to Ask a Question