Copy values from columns matching in those in second file.

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Copy values from columns matching in those in second file.
# 1  
Old 09-10-2011
Copy values from columns matching in those in second file.

Hi All,

I have two sets of files.

Set 1: 100 text files with extension .txt with names like 1.txt, 2.txt, 3.txt until 100.txt

Set 2: One big file with extension .dat

The text files have some records in columns like this:

0.7316431 82628
0.7248189 82577
0.7248182 81369
0.7222999 83490
0.71819735 81613
0.7173147 83027
0.7161552 83105
0.7161268 83822
0.7161214 80988
0.7157952 83798
0.7155143 81649
0.7151216 83717

All the .TXT files look the same but with different numbers.

My big .DAT file looks like this:

0.047589 11
0.021992 12
0.029547 13
0.030269 14
0.022525 15
0.021238 16
0.023595 17
0.028851 18
0.731 82628
0.724 82577
0.724 81369
0.72 83490
0.79 81613
0.77 83027
0.74 83105
0.73 83822
0.714 80988
0.7952 83798
0.743 81649
0.7216 83717

Now, the important observation in the two files is that numbers in column 2 in 100 .TXT files are sure to be found in column 2 of .DAT file.

I want to:

1. Read the .TXT files one by one.
2. Extract the first column from .DAT when the number in the second column from .TXT matches with that in the .DAT file and write the result like this in 1.res file considering that I am reading 1.txt file and same goes for 2.txt where I create 2.res file until 100.res:

0.7316431 0.731 
0.7248189 0.724 
0.7248182 0.724 
0.7222999 0.72 
0.71819735 0.79 
0.7173147 0.77 
0.7161552 0.74 
0.7161268 0.73 
0.7161214 0.714 
0.7157952 0.7952 
0.7155143 0.743 
0.7151216 0.7216

As you can see above that the numbers in the second column of .TXT files that matched with the numbers in the second column in the big file (.DAT), I have extracted only the numbers from the first column of the matching second column numbers and wrote the result in 1.res file.

I am using Linux with BASH.

I've been trying with some codes that I could get in this forum but after failing turned back here. This is what I wrote but it does not do anything expect return blank.

for txt in *.txt
  num=`echo $txt | cut -f1 -d"."`
  awk 'NR==FNR{a[$1]=$1;next}{print a[$0]}' big_file.dat $num.txt >> $num.res

# 2  
Old 09-10-2011
for txt in *.txt; do                                                         
  awk '
     NR == FNR { a[$2]=$1 }
     NR != FNR { print $1, a[$2] }
  ' big_file.dat $txt >${txt%txt}res # bash/ksh/zsh

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

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Copy columns from one file into another and get sum of column values and row count

I have a file abc.csv, from which I need column 24(PurchaseOrder_TotalCost) to get the sum_of_amounts with date and row count into another file say output.csv abc.csv- UTF-8,,,,,,,,,,,,,,,,,,,,,,,,, ... (6 Replies)
Discussion started by: Tahir_M
6 Replies

2. Shell Programming and Scripting

Shell script to filter records in a zip file that contains matching columns from another file

Not sure if this is the correct forum for this question. I have two files., file2 Input: col1, col2 , col3 a , b , 0:0:0:0:0:c436:9346:d40b x, y, 0:0:0:0:0:880:39f9:c9a7 m, n , 0:0:0:0:0:80c7:9161:fe00 file2.txt col1 c4:36:93:46:d4:0b... (1 Reply)
Discussion started by: anil.v
1 Replies

3. Shell Programming and Scripting

awk to copy previous line matching a particular columns

Hello Help, 2356798 7689867 999 000 123678 20385907 9797 666 17978975 87468976 968978 98798 I am trying to have out put which actually look for the third column value of 9797 and then it insert line there after with first, second column value exactly as the previous line and replace the third... (3 Replies)
Discussion started by: Indra2011
3 Replies

4. Shell Programming and Scripting

Replace text in column1 of a file matching columns of another file

Hi all, I have 2 files: species-names.txt Abaca-bunchy-top-virus ((((Abaca-bunchy-top-virus((Babuvirus((Unassigned((Nanoviridae((Unassigned)))) Abutilon-mosaic-virus ((((Abutilon-mosaic-virus((Begomovirus((Unassigned((Geminiviridae((Unassigned))))... (2 Replies)
Discussion started by: thienxho
2 Replies

5. Shell Programming and Scripting

Print columns matching to specific values

Hello Friends, I have a CDR file and i need to print out 2 columns with their field position which matches to some constant values, a part of input file CZ=1|CZA=1|DIAL=415483420001|EE=13|ESF=1|ET=|FF=0|9|MNC=99|MNP=9041|MTC=0|NID=2|NOA=international|ON=1| OutPut ... (3 Replies)
Discussion started by: EAGL€
3 Replies

6. Shell Programming and Scripting

Compare values in two files. For matching rows print corresponding values from File 1 in File2.

- I have two files (File 1 and File 2) and the contents of the files are mentioned below. - I am trying to compare the values of Column1 of File1 with Column1 of File2. If a match is found, print the corresponding value from Column2 of File1 in Column5 of File2. - I tried to modify and use... (10 Replies)
Discussion started by: Santoshbn
10 Replies

7. UNIX for Dummies Questions & Answers

Removing columns from a text file that do not have any values in second and third columns

I have a text file that has three columns. But at the end of the text file, there are trailing lines that have missing second and third columns: 4 0.04972604 KLHL28 4 0.0497332 CSTB 4 0.04979822 AIF1 4 0.04983331 DECR2 4 0.04990344 KATNB1 4 4 4 4 How can I remove the trailing... (3 Replies)
Discussion started by: evelibertine
3 Replies

8. Shell Programming and Scripting

selecting record by matching in two columns values

Hi Guys ! i want to search a record in file by matching two values in a record in two different columns suppose i have 3 columns and i want to select all those values from col1 for which in col3 has a specific value e.g select all "john" from column1 where column 3 has a value of "20" ... (9 Replies)
Discussion started by: ourned
9 Replies

9. Shell Programming and Scripting

Perl matching values in a file

I am trying to match first 5 values of $passt variable in a file $chpdvlst and then verify that value in $grp parameter I want first 5 values which has $vaht - ship and $grp = N-grp. Somehow script below lists whole file. $memcnt = 5; $passt = ship; open (ADVOUT, "< $chpdvlst") || die... (5 Replies)
Discussion started by: dynamax
5 Replies

10. Shell Programming and Scripting

compare two columns of different files and print the matching second file..

Hi, I have two tab separated files; file1: S.No ddi fi cu o/l t+ t- 1 0.5 0.6 o 0.1 0.2 2 0.2 0.3 l 0.3 0.4 3 0.5 0.8 l 0.1 0.6 ... (5 Replies)
Discussion started by: vasanth.vadalur
5 Replies
Login or Register to Ask a Question