Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

left join using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting left join using awk
# 1  
Old 07-16-2012
left join using awk
Hi guys,

I need AWK to merge the following 2 files:

file1
Code:
1 a 1 1
2 b 2 2
3 c 3 3
4 d 4 4

file2
Code:
a a/a c/c a/c c/c a/a c/t
c c/t c/c a/t g/g c/c c/t

desired output:
Code:
1 a 1 1 a/a c/c a/c c/c a/a c/t
2 b 2 2 x x x x x x
3 c 3 3 c/t c/c a/t g/g c/c c/t
4 d 4 4 x x x x x x

The awk command should merge the files and put many "x" as the number of columns - 1 from file 2

Thanks!!

Moderator's Comments:
Mod Comment Please use code tags next time for your code and data.
# 2  
Old 07-16-2012
Code:
awk 'NR == FNR {
  r = null
  for (i = 1; ++i <= NF;) 
    r = r ? r OFS $i : $i
  f2[$1] = r; nf = NF; next
  }
FNR == 1 {
  for (i = 1; ++i <= nf;) 
    x = x ? x OFS c : c
  }
{ 
  print $0, $2 in f2 ? f2[$2] : x 
  }' c=x file2 file1

If you want to be able to use an arbitrary OFS:

Code:
awk 'NR == FNR {
  r = null
  for (i = 1; ++i <= NF;) 
    r = r ? r OFS $i : $i
  f2[$1] = r; nf = NF; next
  }
FNR == 1 {
  for (i = 1; ++i <= nf;) 
    x = x ? x OFS c : c
  }
{ 
  $1 = $1
  print $0, $2 in f2 ? f2[$2] : x 
  }' OFS=, c=x file2 file1

# 3  
Old 07-16-2012
If the files are already sorted on the join field, and if you don't actually have an awk restriction (but merely mentioned it because you thought it was the best tool for the task):
Code:
join -1 2 -2 1 -a 1 -e x -o 1.1,1.2,1.3,1.4,2.2,2.3,2.4,2.5,2.6,2.7 file1 file2

Regards,
Alister
Last edited by alister; 07-16-2012 at 02:05 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Join, merge, fill NULL the void columns of multiples files like sql "LEFT JOIN" by using awk

Hello, This post is already here but want to do this with another way Merge multiples files with multiples duplicates keys by filling "NULL" the void columns for anothers joinning files file1.csv: 1|abc 1|def 2|ghi 2|jkl 3|mno 3|pqr file2.csv: 1|123|jojo 1|NULL|bibi... (2 Replies)
Discussion started by: yjacknewton
2 Replies

2. Shell Programming and Scripting

Difficulties in matching left bracket as literal in awk

I need to work with records having #AX in the EXP1 , please see my data sample and my attempt below: $ cat xx 08:30:33 KEY1 (1255) EXP1 VAL:20AX0030006 08:30:33 KEY1 (1255) EXP1 VAL:20AX0030006 08:30:33 KEY1 (1255) EXP1 VAL:20AW0030006 08:30:33 KEY1 (1255) EXP1 VAL:20AW0030006 $ gawk '{... (1 Reply)
Discussion started by: migurus
1 Replies

3. Shell Programming and Scripting

awk to substitute ip without zero left padding

Hello All, I have this script to awk IP to new file. #awk '/myip|yourip/ {sub(/...\....\....\..../, newip)}1' newip=$IP existing.txt > new.txt When existing.txt has myip=192.168.123.123 and $IP has 192.168.12.12, the awk script is not working. But while I add zero left padding to $IP i.e,... (3 Replies)
Discussion started by: Shaan_Shaan
3 Replies

4. Shell Programming and Scripting

Left pad spaces using awk or sed

Hi,I've a unix pipe delimited file as below f1|f2|f3|f4|f5|f6 My requirement is to pad spaces on the left to fields f2, f3 and f5. Field Lengths according to file layout f2 - 4 char f3 - 5 char f5 - 3 char If my record is as below 1|43|bc|h0|34|a Output record should be as below 1| 43| bc|h0|... (4 Replies)
Discussion started by: Soujanya_K
4 Replies

5. Shell Programming and Scripting

left join using awk

Hi guys, I need to use awk to join 2 files file_1 A 001 B 002 C 003 file_2 A XX1 B XX2 output desired A 001 XX1 B 002 missing C 003 XX2 thank you! (2 Replies)
Discussion started by: g1org1o
2 Replies

6. Programming

LEFT JOIN issue in Mysql

I have a data table as follows: mysql> select * from validations where source = "a03"; +------------+-------+--------+ | date | price | source | +------------+-------+--------+ | 2001-01-03 | 80 | a03 | | 2001-01-04 | 82 | a03 | | 2001-01-05 | 84 | a03 | | 2001-01-06... (2 Replies)
Discussion started by: figaro
2 Replies

7. Shell Programming and Scripting

Left Join in Unix based on Key?

So I have 2 files: File 1: 111,Mike,Stipe 222,Peter,Buck 333,Mike,Mills File 2: 222,Mr,Bono 444,Mr,Edge I want output to be below, where 222 records joined and all none joined records still in output 111,Mike,Stipe 222,Peter,Buck,Mr,Bono 333,Mike,Mills 444,Mr,Edge (4 Replies)
Discussion started by: stack
4 Replies

8. Shell Programming and Scripting

Unix Cut or Awk from 'Right TO Left'

Hello, I want to get the User Name details of a user from a file list. This list can be in the format: FirstName_MiddleName1_LastName_ID FirstName_LastName_ID FirstName_MiddleName1_MiddleName2_LastName_ID What i want it to return is FirstName_MiddleName1_LastName of a user. I... (6 Replies)
Discussion started by: limamichelle
6 Replies

9. UNIX for Dummies Questions & Answers

Join 2 files with multiple columns: awk/grep/join?

Hello, My apologies if this has been posted elsewhere, I have had a look at several threads but I am still confused how to use these functions. I have two files, each with 5 columns: File A: (tab-delimited) PDB CHAIN Start End Fragment 1avq A 171 176 awyfan 1avq A 172 177 wyfany 1c7k A 2 7... (3 Replies)
Discussion started by: InfoSeeker
3 Replies

10. Shell Programming and Scripting

Left join on files using awk

nawk 'NR==FNR{a;next} {if($1 in a) print $1,"Found" else print}' OFS="," File_B File_A The above code is not working help is appreciated (6 Replies)
Discussion started by: pinnacle
6 Replies
Login or Register to Ask a Question