Sponsored Content
Top Forums Shell Programming and Scripting Join multiple files by column with awk Post 302427649 by ikki on Monday 7th of June 2010 10:58:23 AM
Old 06-07-2010
So, I pondered your problem a bit. Your task isn't one that requires much processing power, instead the most likely bottleneck is file I/O. If you need to generate this kind of report rarely (say, once a month), then six hours doesn't seem too long.

If it's daily task, or more importantly if you need to generate multiple types of reports often, I'd consider importing the data into a real database. This assumes the data is somewhat static (and even if it isn't.. it could be written directly into the db, depending from the source of your data).

If a database's a no-go and performance has to be acquired through optimizing the code, I think one obvious place to optimize is reading in. Perhaps you could read in "bursts" filling a file specific buffer in one read. However I don't know anything about R-scripts, so you're on your own there.

I did a mock-up of the data (4 files with 360000 lines each) and wrote a perl script to do the heavy lifting. On my 500Mhz pentium it performed this way: processing of only one input file took 70 seconds and processing of 4 input files took 236 seconds. If we use these files for basis of how much time 1700 files would take (we really can't reliably) 33 hours and 27.9 hours, respectively.

I'll paste the code here if you want to play with it. It takes the filenames as input. It ignores a line if there's no values in it, and it doesn't get confused if some records are missing.

Code:
#!/usr/bin/perl

use strict;
use warnings;

my @if = ();    # array of input files
my $ignore_first_line = 1; #

# open all files
while ( <STDIN> ) {
        chomp;
        if ( -r $_ ) {
                my $index = @if;
                open( $if[ $index ]->{ handle }, "<", $_) or die "Couldn't open file $_: $!";
                $if[ $index ]->{ name } = $_; # save the filename
                $if[ $index ]->{ F }[0] = -1; # set default pos value for this file to "unread"
                if ( $ignore_first_line ) {
                        my $dummy_fh = $if[ $index ]->{ handle };
                        my $dummy = < $dummy_fh >;
                }
        }
}

# print the header
print "chr\tPosition";
for ( 0 .. $#if ) {
        print "\t$if[$_]->{name}";
}
print "\n";

my $pos = 0;    # pos indicates which record we're dealing with

# let's loop the files until all are read thru
while ( 1 ) {
        my $ofc = 0;    # open filehandle count
        my $str = "";   # build the infoline here
        my $ref = undef;
        ++$pos;                 # increase the line position

        # loop thru all files
        for my $index ( 0 .. $#if ) {
                if ( defined ( $if[$index]->{handle} ) ) { # check if the file is open and we can read from it
                        ++$ofc;
                        if ( $if[$index]->{F}[0] < $pos ) {
                                my $handle = $if[$index]->{handle}; # save filehandle to a temp variable
                                if ( defined ( $if[$index]->{line} = <$handle> ) ) {
                                        @{$if[$index]->{F}} = split(/\s/, $if[$index]->{line});
                                        $if[$index]->{F}[0] =~ s/.*?(\d+)/$1/; # save only the number, eg. from cnvi0000003
                                }
                                else {
                                        $if[$index]->{handle} = undef; # close filehandle
                                }
                        }

                        if ( defined ( $if[$index]->{handle} ) and $if[$index]->{F}[0] == $pos ) {
                                # according to position we'll print this data now
                                # also save a reference to the data so we can print
                                # character and position later
                                $ref = $if[$index]->{F};
                                $str .= "\t" . $if[$index]->{F}[3];
                        }
                        else {
                                $str .= "\t"; # empty record
                        }

                }
                else {
                        $str .= "\t"; # empty record
                }
        }

        if ( defined ( $ref ) ) {
                print "$$ref[1]\t$$ref[2]$str\n";
        }

        last unless $ofc;
}

 

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

2. Shell Programming and Scripting

Join multiple files based on 1 common column

I have n files (for ex:64 files) with one similar column. Is it possible to combine them all based on that column ? file1 ax100 20 30 40 ax200 22 33 44 file2 ax100 10 20 40 ax200 12 13 44 file2 ax100 0 0 4 ax200 2 3 4 (9 Replies)
Discussion started by: quincyjones
9 Replies

3. Shell Programming and Scripting

Join and awk max column

Hi Friends, I have a file1 with 3400 records that are tab separated and I have a file2 with 6220 records. I want to merge both these files. I tried using join file1 and file2 after sorting. But, the records should be (3400*6220 = 21148000). Instead, I get only around 11133567. Is there anything... (13 Replies)
Discussion started by: jacobs.smith
13 Replies

4. Shell Programming and Scripting

Awk - join multiple files

Is it possible to join all the files with input1 based on 1st column? input1 a b c d e f input2 a b input3 a e input4 c (2 Replies)
Discussion started by: quincyjones
2 Replies

5. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

6. Shell Programming and Scripting

Awk: Multiple Replace In Column From Two Different Files

Master_1.txt 2372,MTS,AP 919821,Airtel,DL 0819,MTS,MUM 919849788001,Airtel,AP 1430,Aircel MP,20 405899143999999,MTS,KRL USSDLIKE,MTS,DEL Master_2.txt 919136,DL 9664,RAJ 919143,KOL 9888,PUN Input File: (4 Replies)
Discussion started by: siramitsharma
4 Replies

7. UNIX for Dummies Questions & Answers

Join with awk different column

hi guys, i need help I need to join file2 to file1 when column 3 in my file1 and column 1 in my file2 in the same string file1 AA|RR|ESKIM RE|DD|RED WE|WW|SUPSS file2 ESKIM|ES SUPSS|SS Output AA|RR|ESKIM|ES RE|DD|RED| WE|WW|SUPSS|SS (3 Replies)
Discussion started by: radius
3 Replies

8. Shell Programming and Scripting

Join 2nd column of multiple files

Dear All, I have many files formatted like this: file1.txt: 1/2-SBSRNA4 18 A1BG 3 A1BG-AS1 6 A1CF 0 A2LD1 1 A2M 1160 file2.txt 1/2-SBSRNA4 53 A1BG 1 A1BG-AS1 7 A1CF 0 A2LD1 3 A2M 2780 (5 Replies)
Discussion started by: paolo.kunder
5 Replies

9. Shell Programming and Scripting

Join columns across multiple lines in a Text based on common column using BASH

Hello, I have a file with 2 columns ( tableName , ColumnName) delimited by a Pipe like below . File is sorted by ColumnName. Table1|Column1 Table2|Column1 Table5|Column1 Table3|Column2 Table2|Column2 Table4|Column3 Table2|Column3 Table2|Column4 Table5|Column4 Table2|Column5 From... (6 Replies)
Discussion started by: nv186000
6 Replies

10. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies
All times are GMT -4. The time now is 05:16 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy