Sponsored Content
Top Forums UNIX for Dummies Questions & Answers Join 2 files based on certain column Post 302916138 by Don Cragun on Sunday 7th of September 2014 02:36:55 PM
Old 09-07-2014
Reformattig Franklin52's code and adding comments:
Code:
awk -F\| '		# Set input field separator to "|".
fNR==FNR{		# For all lines in the 1st file given...
	i=$1		# Set i to the contents of the 1st field.
	sub($1,x)	# Substitute the 1st occurrence in the current line of
			# the contents of the 1st field with the contents of the
			# variable x.  Since x has not been set, the replaceent
			# string is an empty string.  This is dangerous if the
			# contents of the first field could contain any ERE
			# special characters.  I would have written this as:
			#	$1=""
			# or:	$1=x
	a[i]=$0		# Set a[i] to the contents of the modified current line
	next		# Skip remaining steps for this input line; start next
			# cycle.
}
$1 in a{print $0 a[$1]	# For all lines in the second file that have a 1st field
			# that matches the original first field in the 1st file,
			# print the current input line with the contents of the
			# corresponding line (except the 1st field) from the 1st
			# file added to the end of the line.
}' input2.txt input1.txt # Specify names of files to be processed.

Does this answer your questions?
This User Gave Thanks to Don Cragun For This Post:
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

"Join" or "Merge" more than 2 files into single output based on common key (column)

Hi All, I have working (Perl) code to combine 2 input files into a single output file using the join function that works to a point, but has the following limitations: 1. I am restrained to 2 input files only. 2. Only the "matched" fields are written out to the "matched" output file and... (1 Reply)
Discussion started by: Katabatic
1 Replies

2. UNIX for Dummies Questions & Answers

Join 2 files using first column

Hi, I'm trying to compare the first column of two files (tab or whitespace delimited, either way's fine, I`ve got both) and print the lines that are identical for the first column of both files. Something like this: File1 AAA 26 49 7 27 36 33 46 75 73 69 AAAAA 4 10 4 7 10 18 21... (2 Replies)
Discussion started by: vanesa1230
2 Replies

3. Shell Programming and Scripting

Join multiple files based on 1 common column

I have n files (for ex:64 files) with one similar column. Is it possible to combine them all based on that column ? file1 ax100 20 30 40 ax200 22 33 44 file2 ax100 10 20 40 ax200 12 13 44 file2 ax100 0 0 4 ax200 2 3 4 (9 Replies)
Discussion started by: quincyjones
9 Replies

4. Shell Programming and Scripting

join files based on a common field

Hi experts, Would you please help me with this? I have several files and I need to join the forth field of them based on the common first field. here's an example... first file: 280346 39.88 -75.08 547.8 280690 39.23 -74.83 538.7 280729 40.83 -75.08 499.2 280907 40.9 -74.4 507.8... (5 Replies)
Discussion started by: GoldenFire
5 Replies

5. Shell Programming and Scripting

join rows based on the column values

Hi, Please help me to convert the input file to a new one. input file: -------- 1231231231 3 A 4561223343 0 D 1231231231 1 A 1231231231 2 A 1231231231 4 D 7654343444 2 A 4561223343 1 D 4561223343 2 D the output should be: -------------------- 1231231231 3#1#2 A 4561223343 0 D... (3 Replies)
Discussion started by: vsachan
3 Replies

6. Shell Programming and Scripting

join two files based on one column

Hi All, I am trying to join to files based on one common column. Cat File1 ID HID Ab_1 23 Cd 45 df 22 Vv 33 Cat File2 ID pval Ab_1 0.3 Cd 10 Vv 0.0444 (3 Replies)
Discussion started by: newpro
3 Replies

7. UNIX for Dummies Questions & Answers

How to use the the join command to join multiple files by a common column

Hi, I have 20 tab delimited text files that have a common column (column 1). The files are named GSM1.txt through GSM20.txt. Each file has 3 columns (2 other columns in addition to the first common column). I want to write a script to join the files by the first common column so that in the... (5 Replies)
Discussion started by: evelibertine
5 Replies

8. UNIX for Dummies Questions & Answers

How to join 2 .txt files based on a common column?

Hi all, I'm trying to join two .txt file tab delimitated based on a common column. File 1 transcript_id gene_id length effective_length expected_count TPM FPKM IsoPct comp1000201_c0_seq1 comp1000201_c0 337 183.51 0.00 0.00 0.00 0.00 comp1000297_c0_seq1 ... (1 Reply)
Discussion started by: alisrpp
1 Replies

9. UNIX for Dummies Questions & Answers

Join files by second column

I have file input file1 1/1/2013 A 553.0763397 96 16582 1/1/2013 B 459.8333588 195 11992 1/2/2013 A 844.2973022 306 19555 1/2/2013 B 833.9300537 457 20165 1/3/2013 A 563.6917419 396 13879 1/3/2013 B 632.0749969 169 ... (1 Reply)
Discussion started by: radius
1 Replies

10. Shell Programming and Scripting

Join columns across multiple lines in a Text based on common column using BASH

Hello, I have a file with 2 columns ( tableName , ColumnName) delimited by a Pipe like below . File is sorted by ColumnName. Table1|Column1 Table2|Column1 Table5|Column1 Table3|Column2 Table2|Column2 Table4|Column3 Table2|Column3 Table2|Column4 Table5|Column4 Table2|Column5 From... (6 Replies)
Discussion started by: nv186000
6 Replies
MRTG-LOGFILE(1) 						       mrtg							   MRTG-LOGFILE(1)

NAME
mrtg-logfile - description of the mrtg-2 logfile format SYNOPSIS
This document provides a description of the contents of the mrtg-2 logfile. OVERVIEW
The logfile consists of two main sections. The first Line It stores the traffic counters from the most recent run of mrtg. The rest of the File Stores past traffic rate averates and maxima at increassing intervals. The first number on each line is a unix time stamp. It represents the number of seconds since 1970. DETAILS
The first Line The first line has 3 numbers which are: A (1st column) A timestamp of when MRTG last ran for this interface. The timestamp is the number of non-skip seconds passed since the standard UNIX "epoch" of midnight on 1st of January 1970 GMT. B (2nd column) The "incoming bytes counter" value. C (3rd column) The "outgoing bytes counter" value. The rest of the File The second and remaining lines of the file contains 5 numbers which are: A (1st column) The Unix timestamp for the point in time the data on this line is relevant. Note that the interval between timestamps increases as you progress through the file. At first it is 5 minutes and at the end it is one day between two lines. This timestamp may be converted in OpenOffice Calc or MS Excel by using the following formula =(x+y)/86400+DATE(1970;1;1) (instead of ";" it may be that you have to use "," this depends on the context and your locale settings) you can also ask perl to help by typing perl -e 'print scalar localtime(x)," "' x is the unix timestamp and y is the offset in seconds from UTC. (Perl knows y). B (2nd column) The average incoming transfer rate in bytes per second. This is valid for the time between the A value of the current line and the A value of the previous line. C (3rd column) The average outgoing transfer rate in bytes per second since the previous measurement. D (4th column) The maximum incoming transfer rate in bytes per second for the current interval. This is calculated from all the updates which have occured in the current interval. If the current interval is 1 hour, and updates have occured every 5 minutes, it will be the biggest 5 minute transfer rate seen during the hour. E (5th column) The maximum outgoing transfer rate in bytes per second for the current interval. AUTHOR
Butch Kemper <kemper@bihs.net> and Tobias Oetiker <tobi@oetiker.ch> 2.17.4 2012-01-12 MRTG-LOGFILE(1)
All times are GMT -4. The time now is 05:16 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy