Visit Our UNIX and Linux User Community


how to combine fields from different files


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to combine fields from different files
# 1  
Old 10-29-2009
how to combine fields from different files

linus>cat data1
Code:
8119463 | 15000
8136789 | 50000
8152238 | 15000
8167890 | 15000
7155789 | 15000
8123334 | 30000
7166330 | 15000
8146683 | 15000
8152238    | 15000    
8167877    | 15000
7145389    | 15000
8178434    | 15000
7166222    | 50000
8167566    | 15000
8115410    | 45000
7100906    | 15000
7100893 | 30000
8104672    | 15000


linus>cat data2
Code:
8167877|CD9S|DOL1  
8136789|CD6S|DOL1
8152238|CD2S|DOL3
8104672|CD8S|DOL4
8152238|CD6S|DOL1
7155789|CD6S|DOL2
7100906|CD5S|DOL1
7166330|CDS|DOL7
8146683|PLUS|DOL4
8123334|PLUS|DOL3
7145389|PLUS|DOL9  
8178434|CD6S|DOL1
8167890|CD6S|DOL1
8167566|MAX1|DOL5
8119463|CD6S|DOL5
8115410|MAX9|DOL7
7100893|MAX+|DOL6
7166222|CD6S|DOL9



I need help on unix commands to combine the two tables together to form four fields and each row would be like below, then sum the total of field four for each unique field 3

OUTPUT FILE

Code:
8167877|DOL1|CD9S|15000
.....
.....

CD9S_total = xxxxxx
     CDS_total = xxxxxx
     DOL1_sum  = xxxxxx
     ....
     ....


Last edited by vgersh99; 10-29-2009 at 06:36 PM.. Reason: code tags, please!
# 2  
Old 10-29-2009
Code:
nawk '
  BEGIN {
    FS=OFS="|"
  }
  FNR==NR {f1[$1+0]+=$2;next}
  { print $0,f1[$1+0]; tot[$2]+=f1[$1+0]}
  END {
    for (i in tot)
      printf("%s_total=%s\n", i, tot[i])
  }' data1 data2

# 3  
Old 10-31-2009
Thanks a lot, it really works.

Previous Thread | Next Thread
Test Your Knowledge in Computers #201
Difficulty: Easy
Perl is implemented as a core interpreter, written in C, together with a large collection of modules, written in Perl and C.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to format file and combine two fields using comma

I am trying to use awk to format the file below, which is tab-delimited. The desired out is space delimited and is in the order of $9 $13 $2 $10-$11.$10 and $11 are often times multiple values separated by a comma, so the value in $10 is combined with the first value from $11 using the comma.... (5 Replies)
Discussion started by: cmccabe
5 Replies

2. Shell Programming and Scripting

awk to combine all matching fields in input but only print line with largest value in specific field

In the below I am trying to use awk to match all the $13 values in input, which is tab-delimited, that are in $1 of gene which is just a single column of text. However only the line with the greatest $9 value in input needs to be printed. So in the example below all the MECP2 and LTBP1... (0 Replies)
Discussion started by: cmccabe
0 Replies

3. Shell Programming and Scripting

awk to combine matches and use a field to adjust coordinates in other fields

Trying to output a result that uses the data from file to combine and subtract specific lines. If $4 matches in each line then the last $6 value is added to $2 and that becomes the new$3. Each matching line in combined into one with $1 then the original $2 then the new$3 then $5. For the cases... (4 Replies)
Discussion started by: cmccabe
4 Replies

4. Shell Programming and Scripting

Combine two Fields if there's a comma between

Hi All, Seeking for your assistance on how to combine two fields if there's a comma between them. What i did was, extract the records from database. file1.csv(extracted from db) 82|Supplies Station, Inc.|112012|KARISSA APPAREL, INC. - 112012|NON TRADE F/A AND... (2 Replies)
Discussion started by: znesotomayor
2 Replies

5. Shell Programming and Scripting

Awk: Combine multiple lines based on number of fields

If a file has following kind of data, comma delimited 1,2,3,4 1 1 1,2,3,4 1,2 2 2,3,4 My required output must have only 4 columns with comma delimited 1,2,3,4 111,2,3,4 1,222,3,4 I have tried many awk command using ORS="" but couldnt progress (10 Replies)
Discussion started by: mdkm
10 Replies

6. Shell Programming and Scripting

Trying to combine fields with sort/join

I have a file with two fields in it delimited by a comma. Some of the first fields are duplicates. I am trying to eliminate any duplicate records in the first field, and combine the second fields in the output file. For example, if the input is: Jane,group=A Bob,group=A Bob,group=D... (3 Replies)
Discussion started by: DJR
3 Replies

7. Shell Programming and Scripting

Search and combine fields

Hi all, 1. I have a log file 2011/11/14 00:42:50 | 38:guess pid=008499 opened Testing 0, 1, 2, 3 2011/11/14 11:43:42 | 38:guess pid=008499 closed 2011/11/14 11:47:08 | 39:guess pid=017567 opened Testing 0, 1, 2, 3 2011/11/14 11:47:08 | 40:guess pid=012780 opened Testing 0, 1,... (4 Replies)
Discussion started by: sabercats
4 Replies

8. Shell Programming and Scripting

How to combine two files based on fields?

I have two files which are as follows: File 1: 1 abc 250 2 pqr 300 3 xyz 100 File 2: 1 abc 230 2 pqr 700 3 xyz 500 Now I need output File, File 3as: S.No Name Count1 Count2 1 abc 250 230 2 pqr 300 700 3 xyz 100 500 NOTE: (13 Replies)
Discussion started by: karumudi7
13 Replies

9. Shell Programming and Scripting

How to combine first,second and third fields in a file

Hi Guys, I have a file as follows: 1 2 3 There are 5 spaces between 1 and 2 and one space between 2 and 3. I want: 123 How can I do this using awk? I tried using: (8 Replies)
Discussion started by: npatwardhan
8 Replies

10. UNIX for Dummies Questions & Answers

Combine fields and eliminate white space

Good Morning, Newbie here. Could someone help with shell scripting that will enable me to combine 2 fields into one eliminating the white space. The fields are fixed but the data of course varies. For example: First Name: "George " 20 positions" Last Name: "Washington " 30 positions" I need... (2 Replies)
Discussion started by: ski
2 Replies

Featured Tech Videos