0

File1: Excel file (.xls)

UN          ID    St      M1    M2       SE    DOF  PV        PA            FC
17127159    0   -5.9    297.3   765.7   0.22    4   0.003   0.00389231  2.57536
17127163    2   -3.87   189.914 492.307 0.3548  4   0.0179  0.01795     2.59226
17127167    4   -3.8908 339.136 855.276 0.3429  4   0.0176  0.017       2.52192
17127171    6   -3.922  390.44  986.365 0.340   4   0.0172179   0.01721 2.52627
17127175    8   -4.715  536.072 1210.65 0.2492  4   0.00920158  0.00920 2.258

File2: Text file (.txt)

UNIT_ID   UN      TID        X       E       GG7     J     O
0      17127159 16657436 353.568 335.295 221.717 815.654 684.85
1      17127161 16657436 11.0842 7.01459 7.33511 11.2121 12.6268
2      17127163 16657450 221.647 226.774 136.274 431.32  392.533
3      17127165 16657452 5.02182 3.41172 4.12834 6.90306 4.91183

If 1st column of 1st file matches with 2nd column of 2nd file extract the matched rows of 2nd file from column 3 to column 9 and save them in the first file.

Can anyone help me ?

Output should be saved in a new file

output:

UN        ID   St  M1    M2    SE   DOF PV    PA    FC    TID     X  E  GG7  J O
17127159  0   -5.9  297.3   765.7   0.22    4   0.003   0.00389231  2.57536  16657436 353.568 335.295 221.717 815.654 684.85
  • R isn't really on topic here. You might do better asking on Stack Overflow. Anyway, please edit your question and i) show us your desired output (use the formatting tools to make it look right); ii) explain what the 1st file is. Is it a text file, an Excel XML file or a binary Excel file? We can only help if it is simple text. – terdon Mar 31 '16 at 13:13
  • Yes I did @terdon – user3351523 Mar 31 '16 at 13:45
  • You still haven't explained what you mean by "Excel file" and your desired output seems to only be the headers, with no values for X,E,GG7,J or O. And why did 2.57536 become 2.57? Do you want rounding? And, if so, why not 2.58? You have to explain exactly what you need or we won't be able to help. – terdon Mar 31 '16 at 13:48
  • Excel file is .xls and Text is .txt files – user3351523 Mar 31 '16 at 13:52
  • I made the changes for my output. please let me know if u need any other info @terdon – user3351523 Mar 31 '16 at 13:58
0

An awk solution:

$ awk 'NR==FNR{a[$2]=$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8"\t"$9; next} 
              {
                if($1 in a){
                    print $0,a[$1]
                }
               }' file2 file1
UN          ID    St      M1    M2       SE    DOF  PV        PA            FC TID  X   E   GG7 J   O   
17127159    0   -5.9    297.3   765.7   0.22    4   0.003   0.00389231  2.57536 16657436    353.568 335.295 221.717 815.654 684.85  
17127163    2   -3.87   189.914 492.307 0.3548  4   0.0179  0.01795     2.59226 16657450    221.647 226.774 136.274 431.32  392.533 

Explanation

Awk splits each input line into fields (at whitespace, by default), making the 1st field $1 the 2nd $2 etc. The special variable NR is the current input line number and FNR is the current line number of the file being read. Therefore, when processing multiple files, the two are equal only while the first file is being read.

  • NR==FNR{a[$2]=$3"\t"$4"\t"$5"\t"$6"\t"$7"\t"$8"\t"$9; next} : if we're reading the first file, save fields 3 through 9 (joined by tabs) as the value in the array a whose key is the 2nd field. Then, skip to the next line.
  • The next ensures that the rest of the script will not be run for the first file (file2) but only the second (file1).

  • if($1 in a){ print $0,a[$1] } : we're now in the second file (file1). If the first field exists as a key in the a array (if($1 in a)), then print the current line $0 and the value stored in a for $1: fields 3 through 9 from file2.

  • Thank you very much. Could you please explain about the solution once like what it does? It will be useful for my other data too. – user3351523 Mar 31 '16 at 15:29
  • Could you please explain about the solution once like what it does? It will be useful for my other data too. – user3351523 Mar 31 '16 at 15:53
  • @user3351523 I added an explanation. If this answer solved your issue, please take a moment and accept it by clicking on the check mark to the left. That will mark the question as answered and is the way thanks are expressed on the Stack Exchange sites. Also, please don't post the same comment twice like that. We are all volunteers here, I had seen the first one and would have answered as soon as I could. – terdon Mar 31 '16 at 16:25
  • it worked but column names are not in one row. it looks like this. UNIT_ID UnitName TranscriptID X_8__HuGene_2_0_st_.mdp_LEVEL E__HuGene_2_0_st_.mdp_LEVEL GG7__HuGene_2_0_st_.mdp_LEVEL J__HuGene_2_0_st_.mdp_LEVEL O__HuGene_2_0_st_.mdp_LEVEL T__HuGene_2_0_st_.mdp_LEVEL Statistics Mean1 Mean2 StandardError DegreeOfFreedom P-Value P-Adjusted FoldChange NewFC – user3351523 Apr 1 '16 at 8:39
  • @user3351523 if it doesn't look like what I have shown you above, then your files are not what you show in your question. – terdon Apr 1 '16 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.