Combine data from two files base on uniq data


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Combine data from two files base on uniq data
# 1  
Old 01-13-2015
Combine data from two files base on uniq data

File 1

Code:
ID    Name       Po1  Po2 
DD134 DD134_4A_1 NN-1 L_0_1
DD134 DD134_4B_1 NN-2 L_1_1
DD134 DD134_4C_1 NN-3 L_2_1
DD142 DD142_4A_1 NN-1 L_0_1
DD142 DD142_4B_1 NN-2 L_1_1
DD142 DD142_4C_1 NN-3 L_2_1
DD142 DD142_3A_1 NN-41 L_3_1
DD142 DD142_3A_1 NN-42 L_3_2

File 2 ( Combination of ID and Po1 have Two Value )

Code:
ID    Po1  Value
DD134 NN-1  GOOD
DD134 NN-1  Bad
DD134 NN-2  GOOD
DD134 NN-2  Bad
DD134 NN-3  GOOD
DD134 NN-3  Bad
DD142 NN-1  GOOD
DD142 NN-1  Bad
DD142 NN-2  GOOD
DD142 NN-2  Bad
DD142 NN-3  GOOD
DD142 NN-3  Bad
DD142 NN-41  GOOD
DD142 NN-41  GOOD
DD142 NN-42  GOOD
DD142 NN-42  Bad


File 3 ( Combination of ID and Po2 have Two Value )

Code:
ID    Po2   Value
DD134 L_0_1 0x00000000
DD134 L_0_1 0x00001002
DD134 L_1_1 0x00000000
DD134 L_1_1 0x00001000
DD134 L_2_1 0x00000000
DD134 L_2_1 0x00001002
DD142 L_0_1 0x00000000
DD142 L_0_1 0x00001002
DD142 L_1_1 0x00000000
DD142 L_1_1 0x00001000
DD142 L_2_1 0x00000000
DD142 L_2_1 0x00001000
DD142 L_3_1 0x00000000
DD142 L_3_1 0x00000000
DD142 L_3_2 0x00000000
DD142 L_3_2 0x00001000


Combine all files Base on Uniq Data :-

File 2 and file 3 have two values so first value shoud me Value1 and Secound Value Should be Value2.

File 4 :-

Code:
ID	Name	         Po1	Po2	Value1   Value2	Value1     	Value2
DD134	DD134_4A_1	NN-1	L_0_1	GOOD	Bad	0x00000000	0x00001002
DD134	DD134_4B_1	NN-2	L_1_1	GOOD	Bad	0x00000000	0x00001000
DD134	DD134_4C_1	NN-3	L_2_1	GOOD	Bad	0x00000000	0x00001002
DD142	DD142_4A_1	NN-1	L_0_1	GOOD	Bad	0x00000000	0x00001002
DD142	DD142_4B_1	NN-2	L_1_1	GOOD	Bad	0x00000000	0x00001000
DD142	DD142_4C_1	NN-3	L_2_1	GOOD	Bad	0x00000000	0x00001002
DD142	DD142_3A_1	NN-41	L_3_1	GOOD	GOOD	0x00000000	0x00000000
DD142	DD142_3A_1	NN-42	L_3_2	GOOD	Bad	0x00000000	0x00001000

# 2  
Old 01-13-2015
First attempt:
Code:
awk     'FNR==1         {FL++}
         FL<3           {T[$1, $2, FL, FNR%2]=$3; next}
         FNR==1         {$1=$1; print $0, "Value1", "Value2", "Value1", "Value2"; next}
                        {$1=$1; print $0, T[$1, $3, 1, 0], T[$1, $3, 1, 1], T[$1, $4, 2, 0], T[$1, $4, 2, 1]}
        ' file2 file3 OFS="\t" file1
ID       Name   Po1    Po2    Value1    Value2    Value1    Value2
DD134    DD134_4A_1    NN-1    L_0_1    GOOD    Bad    0x00000000    0x00001002
DD134    DD134_4B_1    NN-2    L_1_1    GOOD    Bad    0x00000000    0x00001000
DD134    DD134_4C_1    NN-3    L_2_1    GOOD    Bad    0x00000000    0x00001002
DD142    DD142_4A_1    NN-1    L_0_1    GOOD    Bad    0x00000000    0x00001002
DD142    DD142_4B_1    NN-2    L_1_1    GOOD    Bad    0x00000000    0x00001000
DD142    DD142_4C_1    NN-3    L_2_1    GOOD    Bad    0x00000000    0x00001000
DD142    DD142_3A_1    NN-41   L_3_1    GOOD    GOOD   0x00000000    0x00000000
DD142    DD142_3A_1    NN-42   L_3_2    GOOD    Bad    0x00000000    0x00001000


Last edited by RudiC; 01-13-2015 at 02:23 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Combine data out of 3 files into one new file

Hi, How can I combine the data of of three files into one new file? I try to give as much informations as possible. The three existing files are called file1 file2 and file3 the new file should named output_combined. The size of the files will be around 900 words/lines each .. but always... (5 Replies)
Discussion started by: MyMemberName
5 Replies

2. Shell Programming and Scripting

Combine/omit data from 2 files

i made a script on my own. this is for the inventory to all of my AWS servers, and i run it to all of my servers to get the hostname, please look at file2. Then i need some data in file3 as well,. i need to combine them #cat file1 192.10.1.41 server.age.com ###### 192.10.0.40 ssh cant... (10 Replies)
Discussion started by: kenshinhimura
10 Replies

3. Shell Programming and Scripting

How to combine the data of files?

I have a main file as follows aaa 3/2 = 1.5 aba 55+6 = 61 aca 67+8 = 75 hjk 3+3 = 67 ghd 66+30 = 96 ghj 99-3 = 96 ffg 67+3 = 70 I have 4 sub files named sub1, sub2, sub3, sub4 content of sub1 aaa 23+5 = 28 hjk 45+6 = 51 ghd 40-20 = 20 ... (2 Replies)
Discussion started by: jackevan
2 Replies

4. UNIX for Dummies Questions & Answers

Finding and Extracting uniq data in multiple files

Hi, I have several files that look like this: File1.txt Data1 Data2 Data20 File2.txt Data1 Data5 Data10 File3.txt Data1 Data2 Data17 File4.txt (6 Replies)
Discussion started by: Fahmida
6 Replies

5. Shell Programming and Scripting

get data from files combine them to a file

hi people; this is my file1.txt:192.168.1.1 192.168.1.2 192.168.1.3 192.168.1.4 ... this is my file2.txt:portnames usernames maxusercap ... i want to write to file3.txt:l ./getports 192.168.1.1 'get all;l+;get . portnames;l-' l ./getports 192.168.1.1 'get all;l+;get . usernames;l-'... (4 Replies)
Discussion started by: gc_sw
4 Replies

6. Shell Programming and Scripting

Modify log files to get uniq data

Hello, I have a log file that has following output as below. LAP.sun5 CC LAP.sun5 CQ perl.sun5 CC perl.sun5 CQ TSLogger.sun5 CC TSLogger.sun5 CQ TSLogger.sun5 KR WAS.sun5 CC WAS.sun5 MT WAS.sun5 CQ I want to output to be in the way below, i tried using awk but could not do it. ... (12 Replies)
Discussion started by: asirohi
12 Replies

7. Shell Programming and Scripting

combine data of 2 files by variable

my first post ... please be gentle. I have been working on a script to get info out of mysql. Its a support ticket system database OTRS. I can write the subject of open tickets to a text file with a unique user id. I also have a text file with the unique user id, username and email adres. I... (11 Replies)
Discussion started by: dicenl
11 Replies

8. Shell Programming and Scripting

How to combine data files using for loop

Hi, I have 5 files basically;namely file1.txt situated each at folder A to E respectively. I would like to extract out third column from each of these file1.txt from folder A to folder E. Also, I wanted to extract the first and second column which are common. In other words, e.g ... (6 Replies)
Discussion started by: ahjiefreak
6 Replies

9. UNIX for Dummies Questions & Answers

Recover data from 2 files then combine

Using dd or similar tools to recover data from 2 damaged cdroms, I need a way to then combine the 2 files, 1 from each cd, and make a good file: this all result from finding that certain cd's tops scratch easily even when using the "proper" cd markers, hence making the file useless, however the... (1 Reply)
Discussion started by: saint65
1 Replies

10. Shell Programming and Scripting

Using loop reading a file,retrieving data from data base.

Hi All, I am having trouble through, I am reading the input from tab delimited file containing several records, e.g. line1 field1 field2 field3 so on.. line2 field1 field2 field3 so on.. .. .. on the basis of certain fields for each record in input file, I have to retrieve... (1 Reply)
Discussion started by: Sonu4lov
1 Replies
Login or Register to Ask a Question