Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Match and retrieve information from file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Match and retrieve information from file
# 1  
Old 05-18-2015
Match and retrieve information from file
Hello

I just want to ask how to get the match of information column 2 file 1 and retrieve information from column 2 file 2. The column exon in file 1 and column color code in file 2.

File 1
Code:
//NODECOLORCODE         "Exon 1"        "ENST00000595813"
//NODECOLORCODE         "Exon 1"        "ENST00000596051"
//NODECOLORCODE         "Exon 2"        "ENST00000243643"
//NODECOLORCODE         "Exon 2"        "ENST00000421033"
//NODECOLORCODE         "Exon 3"        "ENST00000243643"
//NODECOLORCODE         "Exon 3"        "ENST00000421033"
//NODECOLORCODE         "Exon 4"        "ENST00000243643"
//NODECOLORCODE         "Exon 4"        "ENST00000421033"
//NODECOLORCODE         "Exon 5"        "ENST00000421033"
//NODECOLORCODE         "Exon 5"        "ENST00000440291"

File 2
Code:
"Exon 1"        "#FF0000"
"Exon 2"        "#FFD400"
"Exon 3"        "#FFff00"
"Exon 4"        "#2Bff00"
"Exon 5"        "#00ff00"

Desired output

Code:
//NODECOLORCODE         "Exon 1"        "ENST00000595813"        "#FF0000"
//NODECOLORCODE         "Exon 1"        "ENST00000596051"        "#FF0000"
//NODECOLORCODE         "Exon 2"        "ENST00000243643"        "#FFD400"
//NODECOLORCODE         "Exon 2"        "ENST00000421033"        "#FFD400"
//NODECOLORCODE         "Exon 3"        "ENST00000243643"        "#FFff00"
//NODECOLORCODE         "Exon 3"        "ENST00000421033"        "#FFff00"
//NODECOLORCODE         "Exon 4"        "ENST00000243643"        "#2Bff00"
//NODECOLORCODE         "Exon 4"        "ENST00000421033"        "#2Bff00"
//NODECOLORCODE         "Exon 5"        "ENST00000421033"        "#00ff00"
//NODECOLORCODE         "Exon 5"        "ENST00000440291"        "#00ff00"

I try using awk and it doesn't come out with the desired output I want. Thanks

Code:
awk 'FNR==NR {C[$2]=$1;next} FNR==1 {print "NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE"; next} $1 in C {print C[$1], $1, $4, $2, $3}'

# 2  
Old 05-18-2015
Are those tab separated? If not, you'll have to pretend they're quote separated and use some tricks to properly grab these fields.

Assuming not tab separated:
Code:
$ awk -F\" -v OFS='\t' 'FNR==NR{C[$2]=$4;next}FNR==1{print "NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE"; next} 1 {print $1,$2,$4,C[$2]}' file2 file1
NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE
//NODECOLORCODE                 Exon 1  ENST00000596051 #FF0000
//NODECOLORCODE                 Exon 2  ENST00000243643 #FFD400
//NODECOLORCODE                 Exon 2  ENST00000421033 #FFD400
//NODECOLORCODE                 Exon 3  ENST00000243643 #FFff00
//NODECOLORCODE                 Exon 3  ENST00000421033 #FFff00
//NODECOLORCODE                 Exon 4  ENST00000243643 #2Bff00
//NODECOLORCODE                 Exon 4  ENST00000421033 #2Bff00
//NODECOLORCODE                 Exon 5  ENST00000421033 #00ff00
//NODECOLORCODE                 Exon 5  ENST00000440291 #00ff00

# 3  
Old 05-18-2015
Quote:
Originally Posted by Wan Fahmi
Hello

I just want to ask how to get the match of information column 2 file 1 and retrieve information from column 2 file 2. The column exon in file 1 and column color code in file 2.

File 1
Code:
//NODECOLORCODE         "Exon 1"        "ENST00000595813"
//NODECOLORCODE         "Exon 1"        "ENST00000596051"
//NODECOLORCODE         "Exon 2"        "ENST00000243643"
//NODECOLORCODE         "Exon 2"        "ENST00000421033"
//NODECOLORCODE         "Exon 3"        "ENST00000243643"
//NODECOLORCODE         "Exon 3"        "ENST00000421033"
//NODECOLORCODE         "Exon 4"        "ENST00000243643"
//NODECOLORCODE         "Exon 4"        "ENST00000421033"
//NODECOLORCODE         "Exon 5"        "ENST00000421033"
//NODECOLORCODE         "Exon 5"        "ENST00000440291"

File 2
Code:
"Exon 1"        "#FF0000"
"Exon 2"        "#FFD400"
"Exon 3"        "#FFff00"
"Exon 4"        "#2Bff00"
"Exon 5"        "#00ff00"

Desired output

Code:
//NODECOLORCODE         "Exon 1"        "ENST00000595813"        "#FF0000"
//NODECOLORCODE         "Exon 1"        "ENST00000596051"        "#FF0000"
//NODECOLORCODE         "Exon 2"        "ENST00000243643"        "#FFD400"
//NODECOLORCODE         "Exon 2"        "ENST00000421033"        "#FFD400"
//NODECOLORCODE         "Exon 3"        "ENST00000243643"        "#FFff00"
//NODECOLORCODE         "Exon 3"        "ENST00000421033"        "#FFff00"
//NODECOLORCODE         "Exon 4"        "ENST00000243643"        "#2Bff00"
//NODECOLORCODE         "Exon 4"        "ENST00000421033"        "#2Bff00"
//NODECOLORCODE         "Exon 5"        "ENST00000421033"        "#00ff00"
//NODECOLORCODE         "Exon 5"        "ENST00000440291"        "#00ff00"

I try using awk and it doesn't come out with the desired output I want. Thanks

Code:
awk 'FNR==NR {C[$2]=$1;next} FNR==1 {print "NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE"; next} $1 in C {print C[$1], $1, $4, $2, $3}'

Hello Wan Fahmi,

Following may also help you in same.
Code:
awk 'FNR==NR{B=$NF;$NF="";gsub(/[[:space:]]+$/,X,$0);A[$0]=B;next} ($2" "$3 in A){print $0 OFS A[$2" "$3]}' File2 OFS="\t" File1

Thanks,
R. Singh

---------- Post updated at 11:59 AM ---------- Previous update was at 11:56 AM ----------

Quote:
Originally Posted by neutronscott
Are those tab separated? If not, you'll have to pretend they're quote separated and use some tricks to properly grab these fields.

Assuming not tab separated:
Code:
$ awk -F\" -v OFS='\t' 'FNR==NR{C[$2]=$4;next}FNR==1{print "NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE"; next} 1 {print $1,$2,$4,C[$2]}' file2 file1
NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE
//NODECOLORCODE                 Exon 1  ENST00000596051 #FF0000
//NODECOLORCODE                 Exon 2  ENST00000243643 #FFD400
//NODECOLORCODE                 Exon 2  ENST00000421033 #FFD400
//NODECOLORCODE                 Exon 3  ENST00000243643 #FFff00
//NODECOLORCODE                 Exon 3  ENST00000421033 #FFff00
//NODECOLORCODE                 Exon 4  ENST00000243643 #2Bff00
//NODECOLORCODE                 Exon 4  ENST00000421033 #2Bff00
//NODECOLORCODE                 Exon 5  ENST00000421033 #00ff00
//NODECOLORCODE                 Exon 5  ENST00000440291 #00ff00

Hello neutronscott,

OP needs to get quotes too in output so I am just adding a few addition to it, hope it will help us all.
Code:
awk -F\" -v OFS='\t' 'FNR==NR{C[$2]=$4;next}FNR==1{print "NODECLASSCOLOR EXON TRANSCRIPT_ID COLORCODE"; next} 1 {print $1,FS $2 FS,FS $4 FS ,FS C[$2] FS}' File2 File1

Thanks,
R. Singh
# 4  
Old 05-18-2015
Check this following code:

Code:
# cat MergeInfo.sh
#!/bin/ksh

[[ $# -ne 2 ]] && {
        echo "Usage:  $0 <File1> <File2>" 1>&2
        exit 1;
}

[ -e $1 ] || {
        echo "$1 does not exists!!!!!." 1>&2
        exit 1;
}

[ -e $2 ] || {
        echo "$2 does not exists!!!!!." 1>&2
        exit 1;
}

awk '{ if (( NF != "4" )) { print FILENAME " - Incorrect file format!!!!!."; exit 1; } }' $1

[[ `echo $?` -ne "0" ]] && {
        exit 1;
}

awk '{ if (( NF != "3" )) { print FILENAME " - Incorrect file format!!!!!."; exit 1; } }' $2

[[ `echo $?` -ne "0" ]] && {
        exit 1;
}

cat $1 | while read COLUMN1 COLUMN2 COLUMN3 COLUMN4;
do
        CODE=`grep -w ${COLUMN2} File2 | grep -w ${COLUMN3} | awk '{print $NF}'`;
        echo "${COLUMN1}  ${COLUMN2} ${COLUMN3}  ${COLUMN4}  ${CODE}";
done

# 5  
Old 05-18-2015
If your awk allows for multichar field separators, try
Code:
awk 'FNR==NR {T[$1]=$2; next} {print $0, T[$2]}' 'FS=  +' file2 file1
//NODECOLORCODE         "Exon 1"        "ENST00000595813" "#FF0000"
//NODECOLORCODE         "Exon 1"        "ENST00000596051" "#FF0000"
//NODECOLORCODE         "Exon 2"        "ENST00000243643" "#FFD400"
//NODECOLORCODE         "Exon 2"        "ENST00000421033" "#FFD400"
//NODECOLORCODE         "Exon 3"        "ENST00000243643" "#FFff00"
//NODECOLORCODE         "Exon 3"        "ENST00000421033" "#FFff00"
//NODECOLORCODE         "Exon 4"        "ENST00000243643" "#2Bff00"
//NODECOLORCODE         "Exon 4"        "ENST00000421033" "#2Bff00"
//NODECOLORCODE         "Exon 5"        "ENST00000421033" "#00ff00"
//NODECOLORCODE         "Exon 5"        "ENST00000440291" "#00ff00"

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to update file based on partial match in field1 and exact match in field2

I am trying to create a cronjob that will run on startup that will look at a list.txt file to see if there is a later version of a database using database.txt as the source. The matching lines are written to output. $1 in database.txt will be in list.txt as a partial match. $2 of database.txt... (2 Replies)
Discussion started by: cmccabe
2 Replies

2. Solaris

Solaris commands to retrieve chipset information

I need to know what are the commands in Solaris to retrieve the below information about the hardware platform. 1. Chipset information (information about various hardware controller cards on the mother boards, system BIOS versions, PCI firmware version etc..) 2. Serial number of the work... (2 Replies)
Discussion started by: rajujayanthy
2 Replies

3. Shell Programming and Scripting

Display match or no match and write a text file to a directory

The below bash connects to a site, downloads a file, searches that file based of user input - could be multiple (all that seems to work). What I am not able to figure out is how to display on the screen match found or no match found" and write a file to a directory (C:\Users\cmccabe\Desktop\wget)... (4 Replies)
Discussion started by: cmccabe
4 Replies

4. Shell Programming and Scripting

Retrieve information Text/Word from HTML code using awk/sed

awk/sed newbie here. I have a HTML file and from that file and I would like to retrieve a text word. <font face=arial size=-1><li><a href=/value_for_clients/Tokyo/abc_process.txt>abc</a> NDK Version: 4.0 </li> <font face=arial size=-1><li><a... (6 Replies)
Discussion started by: sk2code
6 Replies

5. Shell Programming and Scripting

Match pattern1 in file, match pattern2, substitute value1 in line

not getting anywhere with this an xml file contains multiple clients set up with same tags, different values. I need to parse the file for client foo, and change the value of tag "64bit" from false to true. cat clients.xml <Client type"FIX"> <ClientName>foo</ClientName>... (3 Replies)
Discussion started by: jack.bauer
3 Replies

6. Shell Programming and Scripting

Retrieve lines that match any occurence in a list of patterns

I have two files. The first containing a header and six columns of data. Example file 1: Number SNP ID dbSNP RS ID Chromosome Result_Call Physical Position 787066 SNP_A-8575395 RS6650104 1 NOCALL 564477 786872 SNP_A-8575125 RS10458597 1 AA ... (13 Replies)
Discussion started by: Selftaught
13 Replies

7. Shell Programming and Scripting

Execute a C program and retrieve information

Hi I have the following script: #!/bin/sh gcc -o program program.c ./program & PID=$! where i execute a C program and i get its pid. I want to retrieve information about this program (e.g memory consumption) using command top. So far i have: top -d 1.0 -p $PID But i dont know how to... (6 Replies)
Discussion started by: nteath
6 Replies

8. UNIX for Dummies Questions & Answers

retrieve lines that match a pattern

Hi, I would like to know how can I get lines from a text file that match no more than 2 '>'. Example: Input file: a >cr1 4 a>b b>c a >cr2 5 a>b Output file: a >cr2 5 a>b Thanks in advance (2 Replies)
Discussion started by: fadista
2 Replies

9. Solaris

command to retrieve user information

Hi, I want the command to retrieve the existing user information such as * authorization * Profile * role * exipre(expiration date of login) * inactive please tell me how to do that Thank you. (3 Replies)
Discussion started by: S_venkatesh
3 Replies

10. Shell Programming and Scripting

A script pls( To retrieve database information)

KSH - Unix -AIX - db2 ************** Get the input from the user say '123' (It varies) Then i want to connect to a database say "D1" then i want th extract from the database by giving "select * from tablename where input = '123' I also want to connect to another database "D2" then i... (3 Replies)
Discussion started by: rollthecoin
3 Replies
Login or Register to Ask a Question