merging rows into new file based on rows and first column


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers merging rows into new file based on rows and first column
# 1  
Old 09-12-2012
merging rows into new file based on rows and first column

I have 2 files,
file01= 7 columns, row unknown (but few)
file02= 7 columns, row unknown (but many)
now I want to create an output with the first field that is shared in both of them and then subtract the results from the rest of the fields and print there
e.g.
file 01
James05025105030
Mark05025105030
file02
James5100501015060
Dean36030613070
Mark5100501015060
John36030613070
output
James55025010030
Mark05025105030
I know there is a command join to join them and then use awk but is there a better way to do it?
Code:
 join file01.txt file02.txt > file.txt
cat file.txt | awk '{a=$8-$2; b=$9-$3; c=$10-$4; d=$11-$5; e=$12-$6; f=$13-$7; print $1, a, b, c, d, e, f, g}' > test.txt


Last edited by A-V; 09-12-2012 at 03:10 PM..
# 2  
Old 09-12-2012
Using the input files you specified and the commands that you say you ran, I get:
Code:
James 5 50 25 0 100 30 
Mark 5 50 25 0 100 30

(note that there are trailing spaces on both lines above because you are printing the value of the undefined variable g) rather than the
Code:
James	5	50	25	0	100	30
Mark	0	50	25	10	50	30

which is the output you said you wanted.

Given that (except for the names in column one) both input records for James and Mark are identical in file01 AND (except for the names in column one) both input records for James and Mark are also identical in file02, why would you expect different values in columns two through seven in the output?

From you description, I had assumed you wanted something like
Code:
#!/bin/ksh
awk 'FNR==NR{f1[$1]
        f2[$1] = $2
        f3[$1] = $3
        f4[$1] = $4
        f5[$1] = $5
        f6[$1] = $6
        f7[$1] = $7
        next
}
$1 in f2{$2 = abs($2 - f2[$1])
        $3 = abs($3 - f3[$1])
        $4 = abs($4 - f4[$1])
        $5 = abs($5 - f5[$1])
        $6 = abs($6 - f6[$1])
        $7 = abs($7 - f7[$1])
        print
}
function abs(val) {
        return(val >= 0 ? val : -val)
}' OFS='\t' file01 file02

which given the input files you specified would produce
Code:
James	5	50	25	0	100	30
Mark	5	50	25	0	100	30

which matches what you were getting with your combination of join and awk (except for the trailing space), but uses tabs instead of spaces as the field separator.
This User Gave Thanks to Don Cragun For This Post:
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Merging rows based on same ID in First column.

Hellow, I have a tab-delimited file with 3 columns : BINPACKER.13259.1.p2 SSF48239 BINPACKER.13259.1.p2 PF13243 BINPACKER.13259.1.p2 G3DSA:1.50.10.20 BINPACKER.13259.2.p2 SSF48239 BINPACKER.13259.2.p2 PF13243 BINPACKER.13259.2.p2 G3DSA:1.50.10.20... (7 Replies)
Discussion started by: anjaliANJALI
7 Replies

2. Shell Programming and Scripting

UNIX command -Filter rows in fixed width file based on column values

Hi All, I am trying to select the rows in a fixed width file based on values in the columns. I want to select only the rows if column position 3-4 has the value AB I am using cut command to get the column values. Is it possible to check if cut -c3-4 = AB is true then select only that... (2 Replies)
Discussion started by: ashok.k
2 Replies

3. Shell Programming and Scripting

Merging rows using two common rows.

Hi.. My requirement is simple but unable to get that.. File 1 : 3 415 A G 4 421 G . 39 421 G A 2 421 G A,C 41 427 A . 4 427 A C 42 436 G . 3 436 G C 43 445 C . 2 445 C T 41 447 A . Output (4 Replies)
Discussion started by: empyrean
4 Replies

4. Shell Programming and Scripting

Merging rows with same column 1 value

I have the following space-delimited input: 1 11.785710 117.857100 1 15 150 1 20 200 1 25 250 3 2.142855 21.428550 3 25 250 22 1.071435 10.714350 The first field is the ID number, the second field is the percentage of the total points that the person has and the third column is the number... (3 Replies)
Discussion started by: mdlloyd7
3 Replies

5. UNIX for Dummies Questions & Answers

Extracting rows from a space delimited text file based on the values of a column

I have a space delimited text file. I want to extract rows where the third column has 0 as a value and write those rows into a new space delimited text file. How do I go about doing that? Thanks! (2 Replies)
Discussion started by: evelibertine
2 Replies

6. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on numerical values of a column

I have a text file where the second column is a list of numbers going from small to large. I want to extract the rows where the second column is smaller than or equal to 0.0001. My input: rs10082730 9e-08 12 46002702 rs2544081 1e-07 12 46015487 rs1425136 1e-06 7 35396742 rs2712590... (1 Reply)
Discussion started by: evelibertine
1 Replies

7. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the first column

I have a tab delimited text file where the first column can take on three different values : 100, 150, 250. I want to extract all the rows where the first column is 100 and put them into a separate text file and so on. This is what my text file looks like now: 100 rs3794811 0.01 0.3434 100... (1 Reply)
Discussion started by: evelibertine
1 Replies

8. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the first column

I have a tab delimited text file where the first column can take on three different values : 100, 150, 250. I want to extract all the rows where the first column is 100 and put them into a separate text file and so on. This is what my text file looks like now: 100 rs3794811 0.01 0.3434... (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Shell Programming and Scripting

delete rows in a file based on the rows of another file

I need to delete rows based on the number of lines in a different file, I have a piece of code with me working but when I merge with my C application, it doesnt work. sed '1,'\"`wc -l < /tmp/fileyyyy`\"'d' /tmp/fileA > /tmp/filexxxx Can anyone give me an alternate solution for the above (2 Replies)
Discussion started by: Muthuraj K
2 Replies

10. UNIX for Dummies Questions & Answers

Remove duplicate rows of a file based on a value of a column

Hi, I am processing a file and would like to delete duplicate records as indicated by one of its column. e.g. COL1 COL2 COL3 A 1234 1234 B 3k32 2322 C Xk32 TTT A NEW XX22 B 3k32 ... (7 Replies)
Discussion started by: risk_sly
7 Replies
Login or Register to Ask a Question