Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

[Solved] Sorting a column in a file based on a column in a second file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting [Solved] Sorting a column in a file based on a column in a second file
# 1  
Old 12-14-2012
[Solved] Sorting a column in a file based on a column in a second file
Hello,

I have two files as the following:

File1:
Code:
F0100020 A G 
F0100030 A T
F0100040 A G

File2:
Code:
F0100040 A G    BTA-28763-no-rs     77.2692
 F0100030 A T    BTA-29334-no-rs     11.4989
 F0100020 A G    BTA-29515-no-rs     127.006

I want to sort the second file based on the first column of the first file and then print in a File 3 this output:

File3:
Code:
F0100020 A G    BTA-29515-no-rs     127.006
 F0100030 A T    BTA-29334-no-rs     11.4989
F0100040 A G    BTA-28763-no-rs     77.2692

Thank you very much in advance!
# 2  
Old 12-14-2012
Do you mind showing what you have done so far?
# 3  
Old 12-14-2012
I tried this but it doesn't work:

Code:
awk -F" " -vOFS=" " 'FNR==NR{o[$1]=NR; next}{$1=o[$1]" "$1}1' first file second file |sort -k1,1 |cut -d' ' -f2 > new file

# 4  
Old 12-14-2012
Code:
awk 'FNR==NR{a[$1]=$0} NR>FNR && ($1 in a) {print a[$1]}' file2 file1 > file3

This User Gave Thanks to in2nix4life For This Post:
Homa
# 5  
Old 12-14-2012
That works very well, thank you!
# 6  
Old 12-14-2012
The code provided by in2nix4life is much better, but here is a corrected version of what Homa was trying to do:
Code:
awk -F ' ' -v OFS=' ' 'FNR == NR { o[$1] = NR;next }
{ $1 = o[$1]" "$1;print }' File1 File2 | sort -nk1,1 | cut -d' ' -f2- > File3

with the important changes being:
  1. Add a space between -v and OFS. (This is required by awk on OS X; other implementations of awk may work with or without this space.)
  2. Add an n to the sort key. (This is only required if File1 contains more than nine lines.)
  3. Add a - to the end of the cut field list.
Hopefully, seeing the changes will help Homa understand what went wrong.
This User Gave Thanks to Don Cragun For This Post:
Homa
# 7  
Old 12-14-2012
This was so nice of you to correct my code. Thank you very much for it.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Get maximum per column from CSV file, based on date column

Hello everyone, I am using ksh on Solaris 10 and I'm gathering data in a CSV file that looks like this: 20170628-23:25:01,1,0,0,1,1,1,1,55,55,1 20170628-23:30:01,1,0,0,1,1,1,1,56,56,1 20170628-23:35:00,1,0,0,1,1,2,1,57,57,2 20170628-23:40:00,1,0,0,1,1,1,1,58,58,2... (6 Replies)
Discussion started by: ejianu
6 Replies

2. Shell Programming and Scripting

Pick the column value based on another column from .csv file

My scenario is that I need to pick value from third column based on fourth column value, if fourth column value is 1 then first value of third column.Third column (2|3|4|6|1) values are cancatenated. Main imp point, in my .csv file, third column is having price value with comma (1,20,300), it has... (2 Replies)
Discussion started by: Ganesh L
2 Replies

3. Shell Programming and Scripting

[Solved] Sorting a column based on another column

hello, I have a file as follows: F0100010 A C F0100040 A G BTA-28763-no-rs 77.2692 F0100020 A G F0100030 A T BTA-29334-no-rs 11.4989 F0100030 A T F0100020 A G BTA-29515-no-rs 127.006 F0100040 A G F0100010 A C BTA-29644-no-rs 7.29827 F0100050 A... (9 Replies)
Discussion started by: Homa
9 Replies

4. Shell Programming and Scripting

Sorting file based on a numeric column

Hi, I use UBUNTU 12.04. I have a file with this structure: Name 2 1245787 A G 12 14 12 14 .... Name 1 1245789 C T 13 12 12 12..... I would like to sort my file based on the second column so to have this output for example: Name 1 1245789 C T 13 12 12 12..... Name 2 1245787 A G 12 14... (4 Replies)
Discussion started by: Homa
4 Replies

5. UNIX for Dummies Questions & Answers

Sorting a file based on the absolute value of a column

I would like to sort a tab delimited text file based on the absolute value of its second column. How do I go about doing that? Thanks! Example input: A -12 B 0 C -6 D 7 Output: A -12 D 7 C -6 B 0 (4 Replies)
Discussion started by: evelibertine
4 Replies

6. Shell Programming and Scripting

[SOLVED] Sorting into new files based on column

Hi, I have the following file, I need to sort it based on a column and write to different output files based on this column request_guid iso_country_cd address_data response_time 32895901-d17f-414c-ac93-3e7e0f5ec240 AND BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND;... (1 Reply)
Discussion started by: ramky79
1 Replies

7. Shell Programming and Scripting

sorting based on a specified column in a text file

I have a tab delimited file with 5 columns 79 A B 20.2340 6.1488 8.5086 1.3838 87 A B 0.1310 0.0382 0.0054 0.1413 88 A B 46.1651 99.0000 21.8107 0.2203 89 A B 0.1400 0.1132 0.0151 0.1334 114 A B 0.1088 0.0522 0.0057 0.1083 115 A B... (2 Replies)
Discussion started by: Lucky Ali
2 Replies

8. UNIX for Dummies Questions & Answers

Sorting a txt file that is a single column

How do you sort a text file that is made up of a single column? (sorting done in alphabetical order) Example input: MAP1S ISYNA1 STAT6 Example output: ISYNA1 MAP1S STAT6 Double post (0 Replies)
Discussion started by: evelibertine
0 Replies

9. UNIX for Dummies Questions & Answers

Sorting a txt file that is a single column

How do you sort a text file that is made up of a single column? (sorting done in alphabetical order) Example input: MAP1S ISYNA1 STAT6 Example output: ISYNA1 MAP1S STAT6 (1 Reply)
Discussion started by: evelibertine
1 Replies

10. Shell Programming and Scripting

sorting csv file based on column selected

Hi all, in my csv file it'll look like this, and of course it may have more columns US to UK;abc-hq-jcl;multimedia UK to CN;def-ny-jkl;standard DE to DM;abc-ab-klm;critical FD to YM;la-yr-tym;standard HY to MC;la-yr-ytm;multimedia GT to KJ;def-ny-jrt;critical I would like to group... (4 Replies)
Discussion started by: tententen
4 Replies
Login or Register to Ask a Question