3

File1:

00:00 274
00:04 476
00:05 450
00:06 499
00:07 373
00:08 206
00:09 471
00:10 154

File2:

00:00 183
00:01 60
00:02 344
00:03 540
00:04 450
00:07 348
00:09 473
00:10 203

Desired output:

00:00,274,183
00:01,0,60
00:02,0,344
00:03,0,540
00:04,476,450
00:05,450,0
00:06,499,0
00:07,373,348
00:08,206,0
00:09,471,473
00:10,154,203

Column 1 of each file will be checked and if same, values will be joined to the output. Please note the "0" value for those contents not present on either file. Also, this will be used for combining contents of 6 files.

4
  • file1, file2 and output is arranged in columns e.g. Column1 is Time and Column2 is Count. For output, Column1 is Time Column 2 and 3 are values from file1 and file2. Not sure why it was displayed like that.
    – binoy_php
    Jan 28 '14 at 8:16
  • try listing the contents of file1 and file2 as code.
    – Fabricio
    Jan 28 '14 at 8:19
  • Did you try join?
    – Bernhard
    Jan 28 '14 at 8:27
  • 1
    you swapped values for 00:02 and 00:03, see my answer
    – Timo
    Jan 28 '14 at 9:27
5

Assuming the input files are alphabetically sorted on their join field (as they are in your sample):

join -e0 -a1 -a2 -o 0,1.2,2.2 file1 file2 | tr ' ' ,
1
  • |tr ' ' ',' to be more precise
    – user55518
    Jan 28 '14 at 19:07
3

Bash

#!/bin/bash 
file1=t1
file2=t2
while  read line
do
        v1=$(grep "${line}" $file1|| echo 0)
        v2=$(grep "${line}" $file2|| echo 0)
        echo ${line},${v1#* },${v2#* }

done < <(awk '!a[$1]++{print $1| "sort"}' $file1 $file2)

Output

00:00,274,183
00:01,0,60
00:02,0,344
00:03,0,540
00:04,476,450
00:05,450,0
00:06,499,0
00:07,373,348
00:08,206,0
00:09,471,473
00:10,154,203
3
  • ./test.sh ./test.sh: line 10: syntax error near unexpected token (' ./test.sh: line 10: done < ( awk '!a[$1]++{print $1| "sort"}' $file1 $file2 )'
    – rking
    Jan 29 '14 at 1:27
  • awk '!a[$1]++{print $1| "sort"}' 1.txt 2.txt | sh -c 'while read LINE; do v1=$(grep "${LINE}" 1. txt || echo 0);v2=$(grep "${LINE}" 2.txt|| echo 0);echo ${LINE},${v1#* },${v2#* };done'
    – rking
    Jan 29 '14 at 5:37
  • I could not get yours to work. The awk command kept causing syntax errors. I posted a turned around version that pipes to sh and it works. I really like your solution. I was not aware of the parameter expansion to remove prefixes. Thanks!
    – rking
    Jan 29 '14 at 5:43
2

Unfortunately join does not add the files that are missing lines. It's -e option only does add the argument to lines with the key. You can extract the keys, add them to each file if not there yet with standard tools and then use join, but by then you are better of writing a small program, for example in Python:

import sys

default = ['0'] * len(sys.argv[1:])
r = {}
for idx, fn in enumerate(sys.argv[1:]):
    for line in open(fn):
        c1, c2 = line.split()
        r.setdefault(c1, default[:])[idx] = c2
for c1 in sorted(r): # print output
    print("{},{}".format(c1, ','.join(r[c1])))

save as join.py and run with

python join.py file1 file2 [file3 ....]

i.e. you can add as many files as you have on the commandline

This gives exactly the output you requested (except that you swapped values for 00:02 and 00:03)

1
2

If the order of the lines is not important, or you don't mind to have the input and output sorted (which doesn't seem to pose a problem considering the input you have given), you can use join twice:

(
    join -a 1 -e "0" -o "1.1 1.2 2.2" file1 file2
    join -a 2 -e "0" -o "2.1 1.2 2.2" file1 file2
) | sort -u | sed "s/ /,/g"

The -a option reproduces unmatched lines from the first (-a1) or the second (-a2) file, -e "0" uses zero as a substitute for the missing lines from the other file, -o describes the format of the output lines as a list of FILE.FIELD values (see join(1) man page). sort -u removes duplicates of lines. The final sed replaces all spaces on each line with commas.

Or, if you are adventurous enough, you'll find out that once is enough with the right options. Thanks Stephane!

5
  • Wow. I like this one. Haven't used join in such a fashion before.
    – rking
    Jan 28 '14 at 12:03
  • 1
    You don't need the two join invocations, you can use -o 0 to output the join key Jan 28 '14 at 13:17
  • @rking no wonder - given Stephane's answer it is a rather silly solution. :)
    – peterph
    Jan 28 '14 at 16:54
  • It wasn't apparent from reading the man that you could combine both -a 1 and -a 2 in one join. Makes sense now!
    – rking
    Jan 29 '14 at 1:20
  • @rking Exactly my point - the man page could use some improvements indeed.
    – peterph
    Jan 29 '14 at 21:47
2

Edited

Simpler version.

Updated Script: test2.awk

FNR==NR{ a[$1]=$2;next; }
{ 
    if ($1 in a){ 
        a[$1] = ( $1 "," a[$1] "," $2 )
    }else{
        a[$1] = ( $1 ",0," $2 )
    }
} END {
    for ( x in a ){
        if ( match(a[x],x) ){print a[x]}else{ print x "," a[x] ",0"}
    }
}

Commandline

awk -f new.awk 1.txt 2.txt | sort

Original First Attempt

Here is a go with awk. Not sure how to quickly sort an associative array so just pipe it to sort. Works.

test.awk script

BEGIN{st=0}
{if(st==0){
    cur=FILENAME; st++} 
 if((st==1)&&(cur==FILENAME)){ 
     a[$1]=$2; 
 }
 else{ b[$1]=$2 } 
}END{ 
    for(i in b){ 
        if(a[i]){ 
            a[i]=a[i] "," b[i]; 
        }else{ a[i]="0," b[i] } }
    for(i in a){ 
        if (b[i]){
            print i "," a[i] 
        }else{ 
            print i "," a[i] ",0" 
        }
    }
}

cmdline

awk -f test.awk 1.txt 2.txt | sort

output

00:00,274,183
00:01,0,60
00:02,0,344
00:03,0,540
00:04,476,450
00:05,450,0
00:06,499,0
00:07,373,348
00:08,206,0
00:09,471,473
00:10,154,203
3
  • have you test this script ?? it's not giving desire output.. Jan 28 '14 at 11:31
  • Yes, I just copy pasted again and it gave the results as shown. What results are you getting?
    – rking
    Jan 28 '14 at 11:57
  • hmm, it's working now.. Jan 28 '14 at 12:45
0

Try the following Perl script

#!/usr/bin/perl

$filenum = $#ARGV;
if ($filenum < 0) {
    print "No arguments\n";
    exit(1);
}

for (my $i=0; $i<=$filenum; $i++) {
    open($fh,"<","$ARGV[$i]") || die "Could not open $ARGV[$i]\n";

    while (<$fh>) {
        ($a,$b) = split/\s/;
        @{$myhash{$a}}[$i]=$b;
    }
    close($fh);
}

foreach my $x ( sort keys %myhash) {
    print "$x";
    for (my $i=0; $i<=$filenum; $i++) {
        if (defined @{$myhash{$x}}[$i]) {
            print ",@{$myhash{$x}}[$i]";
        }
        else {
            print ",0";
        }
    }
    print "\n";
}

save it to a myscript.pl file and run it as:

perl myscript.pl file1 file2 ...
2
  • This does not get the output in the requested order, but even if you add | sort the 00:01 line reads 00:01,60,0 and not 00:01,0,60 as requested
    – Timo
    Jan 28 '14 at 9:32
  • @Timo: Initially the desired output was hard to read. The request was to have 0 if the value is not in one of the files, but not necessarily ordered. Jan 28 '14 at 9:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.