merge two column multiple files into one Post: 302589125

Sponsored Content

Top Forums UNIX for Advanced & Expert Users merge two column multiple files into one Post 302589125 by mary271 on Wednesday 11th of January 2012 12:30:24 AM

01-11-2012

Registered User

Quote:

Originally Posted by binlib

Code:

$ cat agg.awk
BEGIN { OFS = "\t" }
{
  if ($1 != last) {
    if (NR > 1) print last, a
    last = $1
    a = $2
  } else a = a ", " $2
}
END { print last, a }

$ join -t$'\t' -a1 -a2 -e- -j1 -o0,1.2,2.2 <(awk -f agg.awk f1) <(awk -f agg.awk f2) |
join -t$'\t' -a1 -a2 -e- -j1 -o0,1.2,1.3,2.2 - <(awk -f agg.awk f3)

This code is incredible.
This works exactly what i wanted. Awesome.
Can you please explain the switches you used here especially join. Also if I have variable number of input files then how can I extend this.

* I never did awk programming but this works for me. I was using matlab to do this and is damn slow.
Thanks again.

---------- Post updated at 12:30 AM ---------- Previous update was at 12:28 AM ----------

Quote:

Originally Posted by dmaharana

Try this out

Code:

 
#!/bin/ksh
cat f1 f2 f3 > tmp
_list=$(cat tmp | awk '{print $1}' | sort | uniq)
for i in ${_list}
do
        _inf1=$(cat f1 | grep $i | awk '{print $2}' | awk 'BEGIN{ORS="\t"}{print $0}')
        _inf2=$(cat f2 | grep $i | awk '{print $2}' | awk 'BEGIN{ORS="\t"}{print $0}')
        _inf3=$(cat f3 | grep $i | awk '{print $2}' | awk 'BEGIN{ORS="\t"}{print $0}')
        print "${i}\t${_inf1}${_inf2}${_inf3}"
done

This code is just concatenating one file after another. Did not work for me.
Thanks again

mary271

View Public Profile for mary271

Find all posts by mary271

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge Two Files based on First column

Hi, I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like: File one: 110001 abc efd 110002 fgh dfg 110003 ...

2. UNIX for Dummies Questions & Answers

Writing a loop to merge multiple files by common column

I have 100 data files labelled 250.1.txt through 250.100.txt. The second column of the data files partially match (there is about %90 overlap). Each data file has 4 columns. I want the merge all these text files by the matching values in the second column. In the output, the first column should...

3. Shell Programming and Scripting

Merge two files with similar column entries

Hi , I have few files which contains user name and data transfer rate in MBs and this data is collected for year and for each month report is saved in 12 different files I have to merge all the files to prepare the final report Files are as below Similarly I have 10 more files ...

4. Shell Programming and Scripting

How to merge multiple rows into single row if first column matches ?

Hi, Can anyone suggest quick way to get desired output? Sample input file content: A 12 9 A -0.3 2.3 B 1.0 -4 C 34 1000 C -111 900 C 99 0.09 Output required: A 12 9 -0.3 2.3 B 1.0 -4 C 34 1000 -111 900 99 0.09 Thanks

5. Shell Programming and Scripting

Merge two columns from two files into one if another column matches

I have two text files that look something like this: A:B:C 123 D:E:F 234 G:H:I 345 J:K:L 123 M:N:O 456 P:Q:R 567 A:B:C 456 D:E:F 567 G:H:I 678 J:K:L 456 M:N:O 789 P:Q:R 890 I want to find the line where the first column matches and then combine the second columns into a single...

6. UNIX for Dummies Questions & Answers

How do I merge multiple columns into one column?

Hi all, I'm looking for a way to merge multiple columns (from one file) into a single column in an output file. The file I have looks somewhat like this: @HWI-ST212 1:N:0 AGTCCTACCGGGAGT + @@@DDDDDHHHHHII @HWI-ST212 1:N:0 CGTTTAAAAATTTCT + @;@B;DDDDH?:F;F...

7. Shell Programming and Scripting

Merge files based on the column value

Hi Friends, I have a file file1.txt 1|ABC|3|jul|dhj 2|NHU|4|kil|eu 3|hjd|34|hfd|43 file2.txt 1||3|KING|dhj 2|NHU||k| 3|hjd|34|hd|43 i want to merge file1.txt file2.txt based on the column null values in file2.txif there are any nulls in column values ,

8. Shell Programming and Scripting

Multiple file merge by column

Hello all, I am quite new in linux shell scripting and I have this issue. I ve got some files including measurements taken every 10minutes for a whole day. File name format is: 00.00, 00.10, 00.20,....23.50 File structure is: x | y | temperature x and y is the same in all files (same...

9. Shell Programming and Scripting

How to merge two or more fields from two different files where there is non matching column?

Hi, Please excuse for often requesting queries and making R&D, I am trying to work out a possibility where i have two files field separated by pipe and another file containing only one field where there is no matching columns, Could you please advise how to merge two files. $more...

10. UNIX for Dummies Questions & Answers

Merge two csv files using column name

Hi all, I have two separate csv files(comma delimited) file 1 and file 2. File 1 contains PAN,NAME,Salary AAAAA5467D,Raj,50000 AAFAC5467D,Ram,60000 BDCFA5677D,Kumar,90000 File 2 contains PAN,NAME,Dept,Salary ASDFG6756T,Karthik,ABC,450000 QWERT8765Y,JAX,CDR,780000...

LEARN ABOUT OSF1

merge

merge(1)																  merge(1)

NAME

       merge - three-way file merge

SYNOPSIS

       merge [-Llabel1 [-Llabel3]] [-p] [-q] file1 file2 file3

DESCRIPTION

       merge  incorporates  all  changes that lead from file2 to file3 into file1. The result goes to standard output if -p is present, into file1
       otherwise.  merge is useful for combining separate changes to an original.  Suppose file2 is the original, and both  file1  and	file3  are
       modifications of file2. Then merge combines both changes.

       An  overlap occurs if both file1 and file3 have changes in a common segment of lines. On a few older hosts where diff3 does not support the
       -E option, merge does not detect overlaps, and merely supplies the changed lines from file3.  On most hosts, if overlaps occur, merge  out-
       puts a message (unless the -q option is given), and includes both alternatives in the result.  The alternatives are delimited as follows:

       <<<<<<< file1 lines in file1 ======= lines in file3 >>>>>>> file3

       If  there  are  overlaps,  the  user  should edit the result and delete one of the alternatives. If the -L label1 and -L label3 options are
       given, the labels are output in place of the names file1 and file3 in overlap reports.

DIAGNOSTICS

       Exit status is 0 for no overlaps, 1 for some overlaps, 2 for trouble.

IDENTIFICATION

       Author: Walter F. Tichy.
       Revision Number: 1.1.6.2; Release Date: 1993/10/07.
       Copyright (C) 1982, 1988, 1989 by Walter F. Tichy.
       Copyright (C) 1990, 1991 by Paul Eggert.

SEE ALSO

       diff3(1), diff(1), rcsmerge(1), co(1)

																	  merge(1)

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Merge Two Files based on First column

Discussion started by: apjneeraj

2. UNIX for Dummies Questions & Answers

Writing a loop to merge multiple files by common column

Discussion started by: evelibertine

3. Shell Programming and Scripting

Merge two files with similar column entries

Discussion started by: pratapsingh

4. Shell Programming and Scripting

How to merge multiple rows into single row if first column matches ?

Discussion started by: cbm_000