Comparing Columns and writing a new file

Shell Programming and Scripting
1

I have a table with one column
File1.txt

1
2
3
4
5
6
7
8
9
10

Another table with two columns; This has got a subset of entries from File 1 but not unique because they have differing values in col 2.
File2.txt

1	a
2	d
2	f
6	r
6	e
6	a
6	g
9	q
9	y

I want to compare Column 1 in File1 with Column 1 in File2 and write a new file (file3) with all entries of file1 (col1) with the corresponding entries from file2 (col2) entered in file 3 col 2 as follows.

File3.txt

1	a
2	d, f
3	
4	
5	
6	r,e,a,g
7	
8	
9	q,y
10
2 
awk > file3 'NR == FNR {
  f2[$1] = $1 in f2 ? f2[$1] s $2 : $2
  next
  }
$1 in f2 {
  print $1, f2[$1]
  next
  }1' s=, OFS='\t' file2 file1
1 Like
3 
join -a 1 -1 1 -2 1 file1.txt file2.txt | awk ' NR==1 {
                printf "%s\t%s", $1, $2;
        } p==$1 {
                printf ",%s", $2;
        } p!=$1 && NR!=1 {
                printf "\n%s\t%s", $1, $2;
        } {
                p=$1;
        } END {
                printf "\n";
} '
1 Like
4 
$
$ cat file1.txt
1
2
3
4
5
6
7
8
9
10
$
$ cat file2.txt
1       a
2       d
2       f
6       r
6       e
6       a
6       g
9       q
9       y
$
$
$ perl -lane '$ARGV eq "file1.txt"
                ? do {push @x, [$F[0]]; $y{$F[0]} = $#x}
                : do {push @{$x[$y{$F[0]}]}, $F[1] if defined $y{$F[0]}}
              }{ printf("%s\t%s\n", $_->[0], join(",", @{$_}[1..$#{$_}])) for (@x)
             ' file1.txt file2.txt
1       a
2       d,f
3
4
5
6       r,e,a,g
7
8
9       q,y
10
$
$
1 Like