Visit The New, Modern Unix Linux Community


Combine information from 2 files

 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Combine information from 2 files
# 1  
Combine information from 2 files

Hi there, I‘m a newbie in linux (ubuntu) working with several files, some of them containing hundred thousands of lines. I started to extract information out of 2 files, combining them by 1 column: I need a Vlookup-like command that reads sampleID (column 2)(line 2,..line by line) in file 1, looks up this sampleID in file 2 and writes all corresponding readIDs (e.g. 250000) that could be found to this sampleID to a new file (each in a new line). (new file: „sampleID“_readIDs.file) Try to do this for all sampleIDs in summary file1. In the end I have a number of .files as lines of sampleIDs in file 1.
file 1:
Code:
name    sampleID    nr 
Sample1    123    250000 
Sample2    345    200000 
Sample3    456    180000

file 2:
Code:
 readID    read_value    sampleID
15sj10n3-9372-9d73-i3i2-64b40faa330b    6000    123
19pe26j3-9372-9g22-i3i2-81f59a56d939    5900    123
93os17k5-9372-6k63-i3i2-b8b765b1a729    6050    456
49kk23o2-9372-9d73-i3i2-b09f4b1f0557    6080    123
09iy02p8-9372-9d73-i3i2-0d479e6fb751    5990    345

123_readID.file (output in new file):
Code:
15sj10n3-9372-9d73-i3i2-64b40faa330b
19pe26j3-9372-9g22-i3i2-81f59a56d939
49kk23o2-9372-9d73-i3i2-b09f4b1f0557

I am thankful for any help or suggestions. Nika

Last edited by RudiC; 04-03-2018 at 10:23 AM.. Reason: removed font info; Originator: format of data set file 1
# 2  
Welcome to the forum.

It usually helps to post your OS and shell versions as well as e.g. preferred tools, and to show your own attempts on a solution.

Howsoever, how far would this get you:
Code:
awk 'NR==FNR {SID[$2]; next} $3 in SID {FN = $3 "_readIDs.file"; print $1 >> FN; close (FN)}' file1 file2


Last edited by RudiC; 04-03-2018 at 10:19 AM.. Reason: Corrected typo & added two minor improvements of code

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #451
Difficulty: Easy
In a 2012 online poll at Lifehacker, Linux Mint was voted the second best Linux distribution, after Ubuntu.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Automate splitting of files , scp files as each split completes and combine files on target server

i use the split command to split a one terabyte backup file into 10 chunks of 100 GB each. The files are split one after the other. While the files is being split, I will like to scp the files one after the other as soon as the previous one completes, from server A to Server B. Then on server B ,... (2 Replies)
Discussion started by: malaika
2 Replies

2. Shell Programming and Scripting

Combine files

I have n of files with ending with _ZERO.txt need to combine all file ending with _ZERO.txt into 1 file ex: A_ZERO.txt 1 2 B_ZERO.txt 3 4 Output: FINAL.txt 1 2 (3 Replies)
Discussion started by: satish1222
3 Replies

3. UNIX for Dummies Questions & Answers

Combine files

i have 3 files: file1, file2 and file3 file1 has this content: #!/bin/ksh sqlplus username/password@Servername << EOF file2 has this content: drop table dropme cascade constraints; file3 has this content: EOF all said and done, file1, file2 and file2 will look like... (1 Reply)
Discussion started by: lawsongeek
1 Replies

4. Shell Programming and Scripting

combine multiple files by column into one files already sorted!

I have multiple files; each file contains a certain data in a column view simply i want to combine all those files into one file in columns example file1: a b c d file 2: 1 2 3 4 file 3: G (4 Replies)
Discussion started by: ahmedamro
4 Replies

5. Shell Programming and Scripting

3 files combine into one help please

Ok here is what I have file a {{BEGIN}} {{FAX File b 5555464584 5555465292 5555465828 5555485930 5555474939 File C }} ON ORDERS LESS THAN 70 LBS AND THE PACKAGE IS A COMBINED LENGTH AND GIRTH EQUAL TO OR LESS THAN 108" PLEASE UTILIZE UPS. ... (4 Replies)
Discussion started by: sctxms
4 Replies

6. HP-UX

How to combine 2 different files

Hi : I have a file containing the print queues with their IP address. I wanted to combine the 'lpstat' output with their respective IP address. For example : zebhtrmb-6078 lgonzale priority 0 Mar 17 11:50 on zebhtrmb with zebhtrmb-6078 lgonzale priority 0 ... (1 Reply)
Discussion started by: rdasari
1 Replies

7. Shell Programming and Scripting

Combine files with same name

I need a script that combines files with the same name. These files are on a windows directory but the PC has Cygwin so i have a limited unix command set. What I've got; WebData_9_2007-09-20.txt WebData_9_2007-09-20.txt WebData_9_2007-09-21.txt WebData_9_2007-09-20.txt... (4 Replies)
Discussion started by: jmwhitford
4 Replies

8. UNIX for Dummies Questions & Answers

Combine 2 files

Some one plz give me the answer we have 3 files 1 and 2 are given and we need to get 3 file by using some trick file_one.dat AMITH ARUN ARVIND file_two.dat (these are Variables) X Y Z and we need to write scirpt in file_two.data and get the answer in file_three.dat as fallows... (2 Replies)
Discussion started by: Nekki
2 Replies

9. Shell Programming and Scripting

How to combine 2 files

hi all i have 2 files f1 and f2 i have to combine these 2 files and make a new file f3 when i use paste f1 f2 >f3 its pasting vertically but i want to paste horizontally How to do .. pls let me know (2 Replies)
Discussion started by: ravi.sadani19
2 Replies

10. UNIX for Dummies Questions & Answers

how to combine two files

i need to combine two file. These two files have the same line number, and i need to combine each corresponding line. I tried the paste, but i need coma as the delimeter. are there anyway to do it? thanks. (4 Replies)
Discussion started by: tao
4 Replies

Featured Tech Videos