Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: left join using awk
Top Forums Shell Programming and Scripting left join using awk Post 302672497 by radoulov on Monday 16th of July 2012 12:09:26 PM
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

 

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. 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

5. 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

6. 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

7. 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

8. 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

9. 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

10. 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

LEARN ABOUT OSX

locale::codes::langfam

Locale::Codes::LangFam(3pm)				 Perl Programmers Reference Guide			       Locale::Codes::LangFam(3pm)

NAME

       Locale::Codes::LangFam - standard codes for language extension identification

SYNOPSIS

	  use Locale::Codes::LangFam;

	  $lext = code2langfam('apa');		       # $lext gets 'Apache languages'
	  $code = langfam2code('Apache languages');    # $code gets 'apa'

	  @codes   = all_langfam_codes();
	  @names   = all_langfam_names();

DESCRIPTION

       The "Locale::Codes::LangFam" module provides access to standard codes used for identifying language families, such as those as defined in
       ISO 639-5.

       Most of the routines take an optional additional argument which specifies the code set to use. If not specified, the default ISO 639-5
       language family codes will be used.

SUPPORTED CODE SETS

       There are several different code sets you can use for identifying language families. A code set may be specified using either a name, or a
       constant that is automatically exported by this module.

       For example, the two are equivalent:

	  $lext = code2langfam('apa','alpha');
	  $lext = code2langfam('apa',LOCALE_LANGFAM_ALPHA);

       The codesets currently supported are:

       alpha
	   This is the set of three-letter (lowercase) codes from ISO 639-5 such as 'apa' for Apache languages.

	   This is the default code set.

ROUTINES

       code2langfam ( CODE [,CODESET] )
       langfam2code ( NAME [,CODESET] )
       langfam_code2code ( CODE ,CODESET ,CODESET2 )
       all_langfam_codes ( [CODESET] )
       all_langfam_names ( [CODESET] )
       Locale::Codes::LangFam::rename_langfam  ( CODE ,NEW_NAME [,CODESET] )
       Locale::Codes::LangFam::add_langfam  ( CODE ,NAME [,CODESET] )
       Locale::Codes::LangFam::delete_langfam  ( CODE [,CODESET] )
       Locale::Codes::LangFam::add_langfam_alias  ( NAME ,NEW_NAME )
       Locale::Codes::LangFam::delete_langfam_alias  ( NAME )
       Locale::Codes::LangFam::rename_langfam_code  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Codes::LangFam::add_langfam_code_alias  ( CODE ,NEW_CODE [,CODESET] )
       Locale::Codes::LangFam::delete_langfam_code_alias  ( CODE [,CODESET] )
	   These routines are all documented in the Locale::Codes::API man page.

SEE ALSO

       Locale::Codes
	   The Locale-Codes distribution.

       Locale::Codes::API
	   The list of functions supported by this module.

       http://www.loc.gov/standards/iso639-5/id.php
	   ISO 639-5 .

AUTHOR

       See Locale::Codes for full author history.

       Currently maintained by Sullivan Beck (sbeck@cpan.org).

COPYRIGHT

	  Copyright (c) 2011-2012 Sullivan Beck

       This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.16.2							    2012-10-11					       Locale::Codes::LangFam(3pm)
All times are GMT -4. The time now is 09:08 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy