Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

simple code to collapse rows in bash

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers simple code to collapse rows in bash
# 1  
Old 08-23-2012
simple code to collapse rows in bash
Hello to the experts! I have a file that I'd like to collapse based on a common ID column, separated by a character delimiter.
example input
Code:
a 1 6 word1 uniq1
b 2 7 WORD2 uniq2
b 2 7 WORD2 uniq3
b 2 7 WORD2 uniq4
c 3 8 word4 uniq5
d 4 9 word5 uniq6
e 5 1 word6 uniq7

desired output
Code:
a 1 6 word1 uniq1
b 2 7 WORD2 uniq2;uniq3;uniq4
c 3 8 word4 uniq5
d 4 9 word5 uniq6
e 5 1 word6 uniq7

Note that column 4 contains the ID used to collapse, and column 5 are the strings to actually collapse with the delimter ";"
Help is sincerely appreciated!
Many thanks.
# 2  
Old 08-23-2012
Code:
awk '{idx=$1 OFS $2 OFS $3 OFS $4}{a[idx]=(idx in a)?a[idx]";"$NF:$NF}END{for(i in a) print i,a[i]}' myFile

This User Gave Thanks to vgersh99 For This Post:
torchij
# 3  
Old 08-23-2012
vgersh99, thank you so much that works exactly as I wanted. Is it easily adaptable to choose any two columns, one as the ID column as one as the column to collapse?
Thank you.
# 4  
Old 08-23-2012
you'll need to change the code for the 'idx' - right now I' taking columns/fields 1,2,3, and 4 as the unique key to 'merge/collapse' on.
 
Login or Register to Ask a Question

Previous Thread | Next Thread

7 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Collapse linked values

please help, I want to group together all linked data pairs. If I have 10 pairs, each row showing col2 and col3 are linked. R1 1 2 R2 1 3 R3 2 4 R4 3 4 R5 5 6 R6 8 1 R7 6 7 R8 9 10 Then I am looking to make R1 1 2 3 4 8 R5 5 6 7 R8 9 10 (2 Replies)
Discussion started by: sheetalk
2 Replies

2. Shell Programming and Scripting

Bash script help - removing certain rows from .csv file

Hello Everyone, I am trying to find a way to take a .csv file with 7 columns and a ton of rows (over 600,000) and remove the entire row if the cell in forth column is blank. Just to give you a little background on why I am doing this (just in case there is an easier way), I am pulling... (3 Replies)
Discussion started by: MrTuxor
3 Replies

3. Shell Programming and Scripting

Awk: group rows by id and simple conversion

Hi all, I am a newbie to awk and trying to learn by doing examples. I got stuck at this relatively simple conversion. The start file looks like: 1 2 "t1" 1 3 "h1" 2 1 "h1" 2 2 "h2" and I want to convert it into 1 t1:2, h1:3; 2 h1:1, h2:2; Thanks. (9 Replies)
Discussion started by: eagle_fly
9 Replies

4. Shell Programming and Scripting

Help making simple perl or bash script to create a simple matrix

Hello all! This is my first post and I'm very new to programming. I would like help creating a simple perl or bash script that I will be using in my work as a junior bioinformatician. Essentially, I would like to take a tab-delimted or .csv text with 3 columns and write them to a "3D" matrix: ... (16 Replies)
Discussion started by: torchij
16 Replies

5. Shell Programming and Scripting

AWK - calculating simple correlation of rows

Is there any way to calculate a simple correlation of few selected rows with all the rows in input ? In the below example I selected Row01,02,03 and correlated with all the rows. I was trying to run in R. But the this big data matrix is too much to handle for R and eventually my system is... (3 Replies)
Discussion started by: quincyjones
3 Replies

6. Shell Programming and Scripting

how to converting rows to columns, bash

I have in file these words: @fraza1 = rw @fraza2 = r @fraza3 = r @fraza4 = r @fraza5 = r @fraza1 = r @fraza6 = r @fraza7 = r @fraza2 = r @fraza8 = r @fraza9 = r ... I would like so that: ,rw,@fraza1 ,r,@fraza2 (2 Replies)
Discussion started by: patrykxes
2 Replies

7. UNIX for Advanced & Expert Users

block editing, collapse, comment.

any way i can block edit a program ? i wrote a macro to do it in emacs so it works like : void foo ( int ... ) ; // collapsed. it moves the body to temperary buffer, but i can't rely on this :( (2 Replies)
Discussion started by: max_475
2 Replies
Login or Register to Ask a Question