Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Merge files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Merge files
# 1  
Old 05-02-2013
Merge files
Code:
a.txt

id 	name 	subject
12	aaa	History
23	bbb	Science
45	ccc	Zoology

Code:
b.txt
id	layer	LayerNo
12	xxx12	1
23	yyy23	2
23	lll23	3
45	xxx45	1
45	yyy45	2
45	lll45	3

i have file a.txt which is parent file and another children file b.txt . Both files are linked together by common field called "id".

Interesting part child file have multiple layers name associated with ids. (we are only aware that in b.txt for each id there could be max 3 layers)
Now I need to join both file a.txt and b.txt as below
*since we know ids could have max 3layers*
*Layerno ranks/evaluate each layer position/value*

e.g. for id 12 layer no 1 is only specified, that means out of max allowed 3 layers, id 12 got only 1st layer value and rest of layers as zero.

So final file should look like
Code:
id 	name 	subject		layer1		Layer2		Layer3
12	aaa	History		        xxx12		0		0
23	bbb	Science		0		yyy23		lll23
45	ccc	Zoology		xxx45		yyy45		lll45

In my actual scenario file a.txt contains 600,000records. So my script taking bit long time than expected to merge. Therefore, can someone suggest how to carry this same operation in awk in easy way.
# 2  
Old 05-02-2013
Here is an awk program that might work!
Code:
awk '
        NR == FNR {
                if ( NR == 1 )
                        n = split ( $0, H )
                if ( NR > 1 )
                        A[$1] = $2 OFS $3
                next
        }
        {
                if ( FNR == 1 )
                        m = split ( $0, F )
                if ( FNR > 1 )
                {
                        B[$1","$3] = $2
                        !C[$3]++
                }
        }
        END {
                for ( i = 1; i <= n; i++ )
                        printf H[i] OFS
                for ( k in C )
                        printf F[2] C[k] OFS
                printf "\n"
                for ( k in A )
                {
                        printf k OFS A[k] OFS
                        for ( l in C )
                                printf ( B[k","l] ? B[k","l] OFS : "0" OFS )
                        printf "\n"
                }
        }
' OFS='\t' a.txt b.txt

# 3  
Old 05-03-2013
Here is another awk program that may work. (Note however that it doesn't produce any spaces in the heading and Layer1 is capitalized like Layer2 and Layer3)
Code:
awk '
FNR == 1 {
        if(NR == 1)
                printf("id\tname\tsubject\t\tLayer1\t\tLayer2\t\tLayer3\n")
        next
}
FNR == NR {
        l[$1,$3] = $2
        next
}
{       printf("%s\t\t%s\t\t%s\t\t%s\n", $0, l[$1,1] ? l[$1,1] : "0",
                l[$1,2] ? l[$1,2] : "0", l[$1,3] ? l[$1,3] : "0")
}' b.txt a.txt

As always, if you're using a Solaris/SunOS system, use /usr/xpg4/bin/awk, /usr/xpg6/bin/awk, or nawk instead of awk. With the sample input given, this script produces the output:
Code:
id	name	subject		Layer1		Layer2		Layer3
12	aaa	History		xxx12		0		0
23	bbb	Science		0		yyy23		lll23
45	ccc	Zoology		xxx45		yyy45		lll45

You said a.txt contains 600,000 lines, but you didn't say how big b.txt is. This version doesn't need to store the contents of a.txt before producing the output. It reads b.txt and creates the layers before reading a.txt and then outputs each line from a.txt with its associated layers as it reads those lines from a.txt.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to merge two files?

Dear Frens, I have two files and need to merge into one file. Like File_1: Field1 Field2 1 4 File_2: Field1 Field2 3 5 I need one single output as File_1: Field1 Field2 1 4 3 5 This means taking header from either file. (8 Replies)
Discussion started by: manisha_singh
8 Replies

2. Shell Programming and Scripting

Merge files and generate a resume in two files

Dear Gents, Please I need your help... I need small script :) to do the following. I have a thousand of files in a folder produced daily. I need first to merge all files called. txt (0009.txt, 0010.txt, 0011.txt) and and to output a resume of all information on 2 separate files in csv... (14 Replies)
Discussion started by: jiam912
14 Replies

3. Shell Programming and Scripting

Checking in a directory how many files are present and basing on that merge all the files

Hi, My requirement is,there is a directory location like: :camp/current/ In this location there can be different flat files that are generated in a single day with same header and the data will be different, differentiated by timestamp, so i need to verify how many files are generated... (10 Replies)
Discussion started by: srikanth_sagi
10 Replies

4. UNIX for Dummies Questions & Answers

Merge files

Hi, I would like to know how can I merge files based on their coordinates, but mantaining the score of each file in the output file like: Note: 1st column is for chromosome, 2nd for start, 3rd for end of segment, 4th for score file1: 1 200 300 20 1 400 500 30 file2: 1 200 350 30 1... (1 Reply)
Discussion started by: fadista
1 Replies

5. Shell Programming and Scripting

MERGE FILES

Hi all! How could I merge all the text files (in format xml) of a single folder, after having deleted from each of them all the text from its beginning up to a specific string: "<body>" ? Thanks a lot! mjomba (4 Replies)
Discussion started by: mjomba
4 Replies

6. Shell Programming and Scripting

Merge 2 files

Hi , This might be the stupidest question I am asking, but I am struck with this problem. I am trying to merge 2 files, file1 has header and file2 has contents. while I merge them , it merges from the 1st line of file1. for ex: file1 col1|col2|col3| file2 123|234|456|... (2 Replies)
Discussion started by: rashmisb
2 Replies

7. Shell Programming and Scripting

How can i merge these two files into several...

Given are File A and File B File A has for example 5 lines: AAA BBB CCC DDD EEE File B has 3 lines: 111 222 333 How can i merge A and B into: 111 222 333 AAA (first line from A) then a new file: (4 Replies)
Discussion started by: Y-T
4 Replies

8. Shell Programming and Scripting

Merge files of differrent size with one field common in both files using awk

hi, i am facing a problem in merging two files using awk, the problem is as stated below, file1: A|B|C|D|E|F|G|H|I|1 M|N|O|P|Q|R|S|T|U|2 AA|BB|CC|DD|EE|FF|GG|HH|II|1 .... .... .... file2 : 1|Mn|op|qr (2 Replies)
Discussion started by: shashi1982
2 Replies

9. Shell Programming and Scripting

merge files

Hi, i have the files f1 and f2 like: files f1: c1 a1 c2 a2 c3 a3 file f2: c1 b1 c2 b2 c3 b3 i want merge the f1 and f2 file to f3 file like: c1 a1 b1 c2 a2 b3 c3 a3 b3........ .... . . please help me onthis..... (5 Replies)
Discussion started by: koti_rama
5 Replies

10. Shell Programming and Scripting

How to merge files

Hello guys, I gotta question, i have a lot of log files (simple text) and i need to merge them in group of 10 files, one next to the other, that have sense? For example, i have the files: File1 File2 File3 File4 . . File100 I need to merge the contents of each file into a new file... (3 Replies)
Discussion started by: lestat_ecuador
3 Replies
Login or Register to Ask a Question