Comparing Columns and writing a new file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Comparing Columns and writing a new file
# 1  
Old 02-16-2013
Comparing Columns and writing a new file

I have a table with one column
File1.txt
Code:
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
Code:
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
Code:
1	a
2	d, f
3	
4	
5	
6	r,e,a,g
7	
8	
9	q,y
10

# 2  
Old 02-16-2013
Code:
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

This User Gave Thanks to radoulov For This Post:
# 3  
Old 02-16-2013
Code:
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";
} '

This User Gave Thanks to Yoda For This Post:
# 4  
Old 02-17-2013
Code:
$
$ 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
$
$

This User Gave Thanks to durden_tyler For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

Comparing two files and writing mismatched rows along with mismatched columns. Pointing out the mism

I got a requirement where I need to compare two files wrt to each columns and write the corresponding difference in another file along with some identification showing mismatched columns. Pointing out the mismatched columns is my main problem statement. For example we have files like: File 1 ... (8 Replies)
Discussion started by: piyush pankaj
8 Replies

2. Shell Programming and Scripting

Writing output to a file in columns

Hi I am trying to write output to a file in columns I have file in the follwoing: # cat file abc def # I am trying to write next output as like # cat file abc 123 def 345 # :mad: (6 Replies)
Discussion started by: Priya Amaresh
6 Replies

3. Shell Programming and Scripting

validating data in columns and writing them to new file

I have a file that contains records in the below format: 23857250998423948239482348239580923682396829682398094823049823948 23492780582305829852095820958293582093585823095892386293583203248 23482038509825098230958235234230502958205983958235820358205892095... (10 Replies)
Discussion started by: shellhelp
10 Replies

4. Shell Programming and Scripting

Comparing columns in a file

I have two files. One a small one and another one is big. The smaller one look like this: Filename: 1.tmp 3453 0 326543 1 2321 0 3212 1 The big file looks like this: Filename 1.res 0.3232 2321 9.2922 123 0.983 3212 8.373 326543 0.9 3453 1.098 3432 I want to extract those lines... (2 Replies)
Discussion started by: shoaibjameel123
2 Replies

5. UNIX for Dummies Questions & Answers

Comparing the 2nd column in two different files and printing corresponding 9th columns in new file

Dear Gurus, I am very new to UNIX. I appreciate your help to manage my files. I have 16 files with equal number of columns in it. Each file has 9 columns separated by space. I need to compare the values in the second column of first file and obtain the corresponding value in the 9th column... (12 Replies)
Discussion started by: Unilearn
12 Replies

6. UNIX Desktop Questions & Answers

COMPARING COLUMNS IN A TEXT FILE

Hi, Good day. I currently have this data called database.txt and I would like to check if there are no similar values (all unique) on an entire row considering the whole column data is unique. the data is as follows cL1 cL2 cL3 cL4 a12 c13 b13 c15 b11 a15 c19 b11 c15 c17 b13 f14 with... (1 Reply)
Discussion started by: whitecross
1 Replies

7. Shell Programming and Scripting

Comparing rows in same file and writing the result in new file

Help needed... Can you tell me how to compare the last two couple entries in a file and print their result in new file..:confused: I have one file Check1.txt \abc1 12345 \abc2 12327 \abc1 12345 \abc2 12330 I want to compare the entries in Check1 and write to... (1 Reply)
Discussion started by: kichu
1 Replies

8. Shell Programming and Scripting

Comparing Columns and printing the difference from a particular file

Gurus, I have one file which is having multiple columns and also this file is not always contain the exact columns; sometimes it contains 5 columns or 12 columns. Now, I need to find the difference from that particular file. Here is the sample file: param1 | 10 | 20 | 30 | param2 | 10 |... (6 Replies)
Discussion started by: buzzusa
6 Replies

9. Shell Programming and Scripting

Help with comparing columns from a csv file

Hi there, I have an csv file. I want to compare the 16th and 18th columns. They contain alpha numeric characters. Some are same and some are different. We have to pick the ones which are different. But with certain rules. 16th col. 18th col. ---------- ... (1 Reply)
Discussion started by: sickboy
1 Replies
Login or Register to Ask a Question