[Solved] Replace records according to reference


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [Solved] Replace records according to reference
# 1  
Old 06-26-2013
[Solved] Replace records according to reference

Dear All,

I am struggling with the following task and would appreciate some help. I have a large table (file1.txt). The first column of this table contains an ID. I would like to replace the ID with a label according to a reference file.

Here is an example:

Code:
cat infile.txt
0  AJ2312  310  3  34   ABT
0  BG231 310 2 55 ABG
1  GG342 305 5 77 AGG
2  HJ1231 404 6 99 ZTJ
...

Code:
cat reference.txt
0 LIB123
1 LIB223
2 LIB333
...

Code:
cat results.txt
LIB123  AJ2312  310  3  34   ABT
LIB123  BG231 310 2 55 ABG
LIB223  GG342 305 5 77 AGG
LIB333  HJ1231 404 6 99 ZTJ

I tried the following but it does only work for one record at the time:
Code:
for SEQN in 1
do
    LIB=`grep "^>${SEQN}" -w reference.txt | awk '{print $2}'`
    echo ${LIB}
    sed 's/'"$SEQN"'/'"$LIB"'/' infile.txt >> results.txt
done

Any better ideas or suggestions?
# 2  
Old 06-26-2013
join

Use the join command
Code:
join infile.txt reference.txt

Then you need a little manipulation to format the output.
# 3  
Old 06-26-2013
Perfect! Thank a lot krishmaths!!
# 4  
Old 06-26-2013
try

Code:
awk 'NR==FNR{A[$1]=$2;next}{sub($1,A[$1]);print}' reference.txt infile.txt

# 5  
Old 06-26-2013
Dear Pamu, would you be able to explain me you awk script idea?
# 6  
Old 06-26-2013
Trying to explain pamu's awk code here.

awk reads both the files.
Assigns the values of reference.txt in an array A[]. The beauty here is pamu makes use of the data in first column itself as an index to the array.
i.e.,
Code:
A[0]=LIB123, A[1]=LIB223, A[2]=LIB333

So, the code
Code:
{A[$1]=$2;next}

builds this array first.

In the next construct,
Code:
{sub($1,A[$1]);print}

pamu substitues the first field of infile.txt with the corresponding index's array value. i.e., if the first field of infile.txt is 0, it is substituted with A[0] which is LIB123 as the array is built above.

Let us know if you need more clarity. Happy learning Smilie
# 7  
Old 06-26-2013
Great - Thanks again for you help krishmaths!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Pattern match and replace indirect directory reference using sed

Hi, I need a ksh script to replace indirect directory references in an .ini file with a env variable using sed or awk. The .ini file is for example as such: A=.. B=../ C=../.. D=../../ E=../bin F=../../bin G=../../bin/xml H=../../bin/xml/ Need to replace an instance of .. or... (2 Replies)
Discussion started by: andyatit
2 Replies

2. Shell Programming and Scripting

Replace a value using a reference data from other file

Gents, Can you please help me to solve this case In my input file I have a values in column 49 which always need to be one, but sometimes the system create a value 2, in this case I need to go to search in the original file and replace the values in the row where the value 2 is and in the... (6 Replies)
Discussion started by: jiam912
6 Replies

3. Shell Programming and Scripting

Replace from reference file awk

Basically want to replace any field in input file from the refernce file ... for example. clar_2400:3113 in input file will be replaced by clar_2400:3113 Input file field seperator is "," Field which is not found in reference will stay as it is ... Input File ... (3 Replies)
Discussion started by: greycells
3 Replies

4. Emergency UNIX and Linux Support

[Solved] Extract records based on a repeated column value

Hi guys, I need help in making a command to find some data. I have multiple files in which multiple records are present.. Each record is separated with a carriage return and in each record there are multiple fields with each field separated by "|" what i want is that I want to extract... (1 Reply)
Discussion started by: m_usmanayub
1 Replies

5. Programming

[Solved] duplicate records

Hi I have a table which has 2 columns - id and amount. If there duplicate rows , as in id and amount are same , then i have to update the table in such away that only one row should contain amount and all rows should become zero for that id. eg id amount 1 100 1 100 2 200 1... (5 Replies)
Discussion started by: megha2525
5 Replies

6. Shell Programming and Scripting

replace numbers in records

hello every one I have file with following records begin ASX120016719 ASX190006729 ASX153406729 ASX190406759 ASX180006739 end for each record there is ASX word then 9 digits after it (NO spaces included) what i want is to : 1- skip ASX 2-skip first 2 digits after ASX word... (16 Replies)
Discussion started by: neemoze
16 Replies

7. Shell Programming and Scripting

Help with replace column one content based on reference file

Input file 1 testing 10 20 1 A testing 20 40 1 3 testing 23 232 2 1 testing 10 243 2 . . Reference file 1 final 3 used . . Output file (1 Reply)
Discussion started by: perl_beginner
1 Replies

8. Shell Programming and Scripting

Help with replace column one content based on reference file

Input file 1 testing 10 20 1 A testing 20 40 1 3 testing 23 232 2 1 testing 10 243 2 . . Reference file 1 final 3 used . . Output file (2 Replies)
Discussion started by: perl_beginner
2 Replies

9. Shell Programming and Scripting

Replace character based on reference file problem asking

I got two files right now, input file (target file), reference file 1 (query file) reference file 1 (long list of data) KOLOPWMOPOPO ADASDASD ADSASDASDAD . . target file (one long liner content) ADASDASDTYUKOKOLOPWMOPOPOOPLUAADSASDASDADPOPOUYADADASDASD desired output file content ... (1 Reply)
Discussion started by: patrick87
1 Replies

10. Shell Programming and Scripting

[Solved] Find Specific records from file and add totals into variables

Hi Eveyone, I am working on one shell script to find the specific records from data file and add the totals into variables and print them. you can find the sample data file below for more clarification. Sample Data File: PXSTYL00__20090803USA CHCART00__20090803IND... (7 Replies)
Discussion started by: veeru
7 Replies
Login or Register to Ask a Question