Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Combine columns - awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Combine columns - awk
# 1  
Old 04-09-2015
Combine columns - awk
Need some help with this ... please
Code:
 
60644,NJ090237_0263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,,06E:0_08E:0_09E:0_11E:0,0CE5,TDEV,34,VP_TIER
60644,NJ090237_0263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,,06E:0_08E:0_09E:0_11E:0,0CE6,TDEV,34,VP_TIER
60644,NJ090237_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,2100001b328d19d0_2100001b328dc3d2,06E:0_07E:0_08E:0_09E:0_10E:0_11E:0,0CE5,TDEV,34,VP_TIER
60644,NJ090237_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,2100001b328d19d0_2100001b328dc3d2,06E:0_07E:0_08E:0_09E:0_10E:0_11E:0,0CE6,TDEV,34,VP_TIER
60644,NJ090263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,2100001b329a4903_2100001b329a1710,06E:0_07E:0_08E:0_09E:0_10E:0_11E:0,0CE5,TDEV,34,VP_TIER
60644,NJ090263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,2100001b329a4903_2100001b329a1710,06E:0_07E:0_08E:0_09E:0_10E:0_11E:0,0CE6,TDEV,34,VP_TIER

Code:
 
60644,NJ090237_0263_GRP;NJ090237_GRP;NJ090263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,2100001b328d19d0_2100001b328dc3d2;2100001b329a4903_2100001b329a1710;,06E:0_07E:0_08E:0_09E:0_10E:0_11E:0,0CE5,TDEV,34,VP_TIER
60644,NJ090237_0263_GRP;NJ090237_GRP;NJ090263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,2100001b328d19d0_2100001b328dc3d2;2100001b329a4903_2100001b329a1710;,06E:0_07E:0_08E:0_09E:0_10E:0_11E:0,0CE6,TDEV,34,VP_TIER

WHERE combination of columns $3 and $8 is unique , combine $2 and $6 with a ";" seperator
As a result 6 lines become 2 unique lines with $2 and $6 columns combined


Thanks
# 2  
Old 04-09-2015
In your sample output, $2 and $6 do not seem to be combined..
# 3  
Old 04-09-2015
Code:
NJ090237_0263_GRP;NJ090237_GRP;NJ090263_GRP

= unique $2 combined with ";" separator

Code:
2100001b328d19d0_2100001b328dc3d2;2100001b329a4903_2100001b329a1710;

= unique $6 combined with ";" separator , $6 column is empty in one scenario so just a ; in that case
Last edited by Scrutinizer; 04-09-2015 at 03:39 PM.. Reason: code tags
# 4  
Old 04-09-2015
I see, ok, try:
Code:
awk '
  {
    i=$3 FS $8
    p=(i in A)
  } 
  NR==FNR {
    A[i]=A[i] (p?";":x) $2
    B[i]=B[i] (p?";":x) $6
    next
  } 
  p {
    $2=A[i]
    $8=B[i]
    delete A[i]
    delete B[i]
    print
  }
' FS=, OFS=, file file

Note: the input file is specified twice.

Output:
Code:
60644,NJ090237_0263_GRP;NJ090237_GRP;NJ090263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,,06E:0_08E:0_09E:0_11E:0,;2100001b328d19d0_2100001b328dc3d2;2100001b329a4903_2100001b329a1710,TDEV,34,VP_TIER
60644,NJ090237_0263_GRP;NJ090237_GRP;NJ090263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,,06E:0_08E:0_09E:0_11E:0,;2100001b328d19d0_2100001b328dc3d2;2100001b329a4903_2100001b329a1710,TDEV,34,VP_TIER

Last edited by Scrutinizer; 04-09-2015 at 11:33 PM..
This User Gave Thanks to Scrutinizer For This Post:
greycells
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Combine Columns

Input NJ090237_0263_GRP,NJ090237_0263_VIEW,NJ090237_0263_PSGRP,NJ090237_0263_GOLD_CSGRP,06E:0_08E:0_09E:0_11E:0,0CE5 NJ090237_0264_GRP,NJ090237_0263_VIEW,NJ090237_0264_PSGRP,NJ090237_0263_GOLD_CSGRP,06E:0_08E:0_09E:0_11E:0,0CE5... (7 Replies)
Discussion started by: greycells
7 Replies

2. 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 + ... (3 Replies)
Discussion started by: cmccabe
3 Replies

3. Shell Programming and Scripting

Combine columns from many files but keep them aligned in columns-shorter left column issue

Hello everyone, I searched the forum looking for answers to this but I could not pinpoint exactly what I need as I keep having trouble. I have many files each having two columns and hundreds of rows. first column is a string (can have many words) and the second column is a number.The files are... (5 Replies)
Discussion started by: isildur1234
5 Replies

4. Shell Programming and Scripting

Combine these two into one liner awk?

ignore the simplicity of the foo file, my actual file is much more hardcore but this should give you the jist of it. need to combine the two awks into one liner. essentially, need to return the value of one particular field in a file that has multiple comma separated fields. thanks guys cat foo... (1 Reply)
Discussion started by: jack.bauer
1 Replies

5. UNIX for Dummies Questions & Answers

Combine columns from 100 files with same structure

Hi, I have a bunch of files with the following format. PUR.1.9 30910 0.024 0.926 0.050 36587 0.024 0.927 0.049 91857 0.023 0.928 0.049 105797 0.024 0.927 0.049 146659 0.024 0.927 0.049 152695 0.024 0.927 0.049 192118 0.022 0.930 0.048 193310 0.018 0.936 0.046 PUR.2.9 30910 0.028... (6 Replies)
Discussion started by: genehunter
6 Replies

6. Shell Programming and Scripting

Combine columns from multiple files

Can anybody help on the script to combine/concatenate columns from multiple files input1 4 135 5 185 6 85 11 30 16 72 17 30 21 52 22 76 input2 2 50 4 50 6 33 8 62 10 25 12 46 14 42 15 46output (2 Replies)
Discussion started by: sdf
2 Replies

7. UNIX for Dummies Questions & Answers

How to combine 2 files with 6 columns?

This may seem obvious but I am having problems doing this as columns get converted to rows when i try to write a script. I have 2 files text1.txt and text2.txt each of which have 6 columns of numbers separated by a space. I need to combine the 2 files so that the output file text3.txt maintains... (2 Replies)
Discussion started by: tgoldstone
2 Replies

8. Shell Programming and Scripting

How to combine 2 files into 1 file with 2 columns

Hi Guys, I want to combine 2 files and and put together in 1 file and make two columns out it. See below desired output. Any help will be much appreciated. inputfile1.txt 12345 67890 24580 inputfile2.txt AAAAA BBBBB CCCCC (11 Replies)
Discussion started by: pinpe
11 Replies

9. UNIX for Dummies Questions & Answers

combine the values from the first two columns within a file

Hello everybody, I have a text file containing 10,000 rows and 5000 columns. The values are separated by a tab. Ex. file_ex.ped 1 mike 0 0 2 1 A A G G C T A G 1 jack 0 0 2 2 T A G T C A A C 1 Mary 0 0 1 2 A T G C A T G C ... I would like a out put file 1 mike 0 0 2 1 AA GG CT AG 1... (7 Replies)
Discussion started by: Unilearn
7 Replies

10. Shell Programming and Scripting

combine awk and tr -d

Hi Everyone, awk 'BEGIN{print strftime("%c",1272814948)}' | tr -d '\n' how to change tr -d '\n' to be part of the awk? means awk this pchoh time, and awk also remove '\n', instead of using "|" to combine "tr" command. Thanks (2 Replies)
Discussion started by: jimmy_y
2 Replies
Login or Register to Ask a Question