merging similar columns in a single line file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting merging similar columns in a single line file
# 1  
Old 05-27-2009
merging similar columns in a single line file

Hi Guys. I have tried the commands sort and join. But I couldn't able to to find the command for joining in a single line based on keys.My example inputs and outputs are like the following.
Help would be appreciated.Smilie


Input file

a1tabXXXXXXX
a2tabXXXXXXX
a6tabYYYYYYYYY
a71tabXXXXXXX
a62tabYYYYYYYYY
a30tabZZZZZ
a13tabZZZZZ
a43tabZZZZZ
a23tabZZZZZ

outputfile

XXXXXXXtaba1taba2taba71
YYYYYYYYYtaba6taba62tab
ZZZZZtaba30taba13taba43tab23
# 2  
Old 05-28-2009
Code:
awk '{k=$2;a[k]=a[k]"\t"$1}END{for(k in a)print k,a[k]}' OFS="\t" filename


-Devaraj Takhellambam
# 3  
Old 05-28-2009
you should put in some effort next time by showing what you have done

If you have Python
Code:
#!/usr/bin/env python
d={}
for line in open("file"):
    line=line.strip().split()
    d.setdefault(line[-1],[])
    d[line[-1]].append(line[0])
for i,j in d.iteritems():
    print i, '\t'.join(j)

output
Code:
YYYYYYYYY       a6      a62
ZZZZZ   a30     a13     a43     a23
XXXXXXX         a1      a2      a71

# 4  
Old 05-28-2009
Quote:
Originally Posted by devtakh
Code:
awk '{k=$2;a[k]=a[k]"\t"$1}END{for(k in a)print k,a[k]}' OFS="\t" filename


-Devaraj Takhellambam
great solution but that will place two tab after first column so making small change Smilie
Code:
 
awk '{k=$2;a[k]=a[k]"\t"$1}END{for(k in a)print k,a[k]}' filename

# 5  
Old 05-28-2009
Yep

Thanx Guys the both scripts working great.
Well ghost Dog I tried to write script in perl but the sript was giving funny values and wasted 3 hrs. I didn't want to put the scipt coz that sript doesn't make any sense.
But thanx for the adviceSmilie
# 6  
Old 05-28-2009
Quote:
Originally Posted by repinementer
... I tried to write script in perl but the sript was giving funny values and wasted 3 hrs. I didn't want to put the scipt coz that sript doesn't make any sense.
...
Ah, the glorious messiness of Perl:

Code:
$
$ perl -ne 'split; $x{$_[1]}=$x{$_[1]}."\t".$_[0]; END{foreach $k(keys %x){print $k,"\t",$x{$k},"\n"}}' input.txt
XXXXXXX         a1      a2      a71
ZZZZZ           a30     a13     a43     a23
YYYYYYYYY               a6      a62
$
$

As Larry Wall says - "Easy things should be easy, and hard things should be possible."
Amazon.com Message

Cheers,
tyler_durden Smilie
# 7  
Old 05-28-2009
cool

Awesome Tyler

Last edited by repinementer; 05-28-2009 at 05:26 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To group the text (rows) by similar columns-names in a file

As part of some report generation, I've written a script to fetch the values from DB. But, unluckily, for certain Time ranges(1-9.99,10-19.99 etc), I don't have data in DB. In such cases, I would like to write zero (0) instead of empty. The desired output will be exported to csv file. ... (1 Reply)
Discussion started by: kumar_karpuram
1 Replies

2. UNIX for Beginners Questions & Answers

Merging multiple lines into single line based on one column

I Want to merge multiple lines based on the 1st field and keep into single record. SRC File: AAA_POC_DB.TAB1 AAA_POC_DB.TAB2 AAA_POC_DB.TAB3 AAA_POC_DB.TAB4 BBB_POC_DB.TAB1 BBB_POC_DB.TAB2 CCC_POC_DB.TAB6 OUTPUT ----------------- 'AAA_POC_DB','TAB1','TAB2','TAB3','TAB4'... (10 Replies)
Discussion started by: raju2016
10 Replies

3. Shell Programming and Scripting

Merging two file based on comparison of first columns

Respected Members. Hello. This is my first post in the forum. I will try to follow all the rules as prescribed by the forum. In case of non-compliance, I request you to kindly give me some more time to understand and abide by them. I am working on two files. I wish to merge the two files... (6 Replies)
Discussion started by: manojmalhotra
6 Replies

4. UNIX for Dummies Questions & Answers

How to generate one long column by merging two separate two columns in a single file?

Dear all, I have a simple question. I have a file like below (separated by tab): col1 col2 col3 col4 col5 col6 col7 21 66745 rs1234 21 rs5678 23334 0.89 21 66745 rs2334 21 rs9978 23334 0.89 21 66745 ... (4 Replies)
Discussion started by: forevertl
4 Replies

5. Shell Programming and Scripting

Merging the flat files into a single file

Hi, My requirement is search for the flat files in the location that are generated in a day and merge them into a single flat file. In the merged file as well particular column value should go into particular column. awk 'NR=1 FNR>1' $(ls -rt flatfile1_v_*) when i use this command for... (4 Replies)
Discussion started by: srikanth_sagi
4 Replies

6. Shell Programming and Scripting

Merging two columns from two files with similar names into a loop

I have two files like this: fileA.net A B C fileA.dat 1 2 3 and I want the output output_expected A 1 B 2 C 3 I know that the easier way is to do a paste fileA.net fileA.dat, but the problem is that I have 10,000 couple of files (fileB.net with fileB.dat; fileC.net with... (3 Replies)
Discussion started by: valente
3 Replies

7. Shell Programming and Scripting

Help with merging data into single line.

Hi, My input is <message> looking for a big <message>Does fit my G74 laptop. Makes the 10 pound. <message> <message>This bag is the only one I could find to fit my awesome ASUS G74S. <message> <message> Great bag my only wish is that they had put a pocket in which to store and... (6 Replies)
Discussion started by: pamu
6 Replies

8. Shell Programming and Scripting

merging multiple lines into single line

Hi, 1. Each message starts with date 2. There is blank line between each message 3. Each message does not contain same number of lines. Any help in merging multiple lines in each message to a single line is much appreciated. AIX: Korn Shell Error log file looks like below. ... (5 Replies)
Discussion started by: bala123
5 Replies

9. Shell Programming and Scripting

Merging information from multiple files to a single file

Hello, I am new to unix and need help with a problem. I have 2 files each containing multiple columns of information ie; File 1 : A B C D E 1 2 3 4 5 File 2 : F G 6 7 I would like to merge the information from File 2 to File 1 so that the data reads as follows; File 1: A... (4 Replies)
Discussion started by: crunchie
4 Replies

10. UNIX for Advanced & Expert Users

Merging two command into single line

1. du -sch int* | grep "total" | awk '{print $1}' first command result is 17K 2. echo "B" Result B i want the output is 17KB (2 Replies)
Discussion started by: kingganesh04
2 Replies
Login or Register to Ask a Question