06-17-2013
[Solved] Merging tables: identifiying common and unique elements
Hi all,
I know how to merge two tables and to remove the duplicated lines based on a field (Column 2) . My next challenge is to be able to identify in a new column those common elements between table A & B, those elements in table A not present in table B and vice versa. A simple count would be enough.
Here is a sample of my tables:
TABLE A:METHOD1
Method Chr:Start-End Gene_refgene
METHOD1 chr1:111111111-22222222 MUTYH
METHOD1 chr1:45794863-45794863 MUTYH
METHOD1 chr1:45794873-45794873 MUTYH
METHOD1 chr1:45794876-45794877 MUTYH
TABLE B:METHOD2
Method Chr:Start-End Gene_refgene
METHOD2 chr1:33333333-44444444 MUTYH
METHOD2 chr1:45794863-45794863 MUTYH
METHOD2 chr1:45794873-45794873 MUTYH
METHOD2 chr1:45794876-45794877 MUTYH
EXPECTED OUTPUT:
Method Chr:Start-End Gene_refgene Count
METHOD1 chr1:111111111-22222222 MUTYH 1
METHOD2 chr1:33333333-44444444 MUTYH 1
METHOD1 chr1:45794863-45794863 MUTYH 2
METHOD1 chr1:45794873-45794873 MUTYH 2
METODO1 chr1:45794876-45794877 MUTYH 2
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Hi,
i'm new here (and to scripting too).
I was hoping for some help in comparing two files.
i have a file called 'file1' with a list of names in the following format:
adam jones
paul higgins
kelly lowe
i also have another file which may contain some of the names but with a lot of... (4 Replies)
Discussion started by: ibking
4 Replies
2. Shell Programming and Scripting
Hi, I have two files file1 and file2. I have to merge the columns of those two files into file3 based on common column of two files. To be simple.
file1:
Row-id name1
13456 Rahul
16789 Vishal
18901 Karan
file2 :
Row-id place
18901 Mumbai
... (2 Replies)
Discussion started by: manneni prakash
2 Replies
3. UNIX for Dummies Questions & Answers
Dear Friends,
I really do not know Linux and I really would like to understand it because it does help to work with large data. I am reading this forum for 1 week to try a solution for my problem. I think that, using others post informations, I was almost there...
I have 2 big tables... (4 Replies)
Discussion started by: lColli
4 Replies
4. Shell Programming and Scripting
Hi All,
I do have 2 files
file 1 has 4 tab delimited columns
234 a c dfgyu
294 b g fih
302 c h jzh
328 z c san
597 f g son
File 2 has 2 tab delimted columns
234 23
302 24
597 24
I want to merge file 2 with file 1 based on the data common in both files which is the first column so... (6 Replies)
Discussion started by: Lucky Ali
6 Replies
5. Shell Programming and Scripting
Hi all,
I have created 3 arrays which can have common elements in each like-
arr_a contains str1 str2 str3 str4 str5
arr_b contains str3 str6 str7 str1 str8
arr_c contains str4 str9 str10 str2
each array is created with "set -A arr_name values" command. I want to create a resultant array-say... (1 Reply)
Discussion started by: sanzee007
1 Replies
6. Shell Programming and Scripting
Hi, I am trying to merge information across 2 files. The first file is a "master" file, with all IDS. File 2 contains a subset of IDs of those in File 1.
I would like to match up individuals in File 1 and File 2, and add information in File 2 to that of File 1 if they appear. However, if an... (3 Replies)
Discussion started by: hubleo
3 Replies
7. Shell Programming and Scripting
Hi
I have file1 as
IJU_NSOMOW;
SOWWOD_TWUIQ;
and file2 as
how are you?;
fine there;
Now my problem is i need the output file as
IJU_NSOMOW; how are you?;
SOWWOD_TWUIQ; fine there; (2 Replies)
Discussion started by: Priya Amaresh
2 Replies
8. Shell Programming and Scripting
HI, I have a 3-column tab separated column (approx 1GB) in which I would like to count and output the frequency of all of the common elements in the 1st column.
For instance:
If my input was the following:
dot is-big 2
dot is-round 3
dot is-gray 4
cat is-big 3
hot in-summer 5
My... (4 Replies)
Discussion started by: owwow14
4 Replies
9. Shell Programming and Scripting
I would like to merge two tables based on column 1:
File 1:
1 today
1 green
2 tomorrow
3 red
File 2:
1 a lot
1 sometimes
2 at work
2 at home
2 sometimes
3 new
4 a lot
5 sometimes
6 at work (4 Replies)
Discussion started by: BSP
4 Replies
10. Shell Programming and Scripting
I have an array code and output is below:
echo $1
while read -r fline; do
echo "%%%%%%$fline%%%%%"
fmy_array+=("$fline")
done <<< "$1"
Output:
CR30903 YU0007 SRIL CR30903 Yogesh SRIL
%%%%%%CR30903 YU0007 SRIL%%%%%
%%%%%%CR30903 Yogesh SRIL%%%%%
... (8 Replies)
Discussion started by: mohtashims
8 Replies
MERGE(1) General Commands Manual MERGE(1)
NAME
merge - three-way file merge
SYNOPSIS
merge [ options ] file1 file2 file3
DESCRIPTION
merge incorporates all changes that lead from file2 to file3 into file1. The result ordinarily goes into file1. merge is useful for com-
bining separate changes to an original. Suppose file2 is the original, and both file1 and file3 are modifications of file2. Then merge
combines both changes.
A conflict occurs if both file1 and file3 have changes in a common segment of lines. If a conflict is found, merge normally outputs a
warning and brackets the conflict with <<<<<<< and >>>>>>> lines. A typical conflict will look like this:
<<<<<<< file A
lines in file A
=======
lines in file B
>>>>>>> file B
If there are conflicts, the user should edit the result and delete one of the alternatives.
OPTIONS
-A Output conflicts using the -A style of diff3(1), if supported by diff3. This merges all changes leading from file2 to file3 into
file1, and generates the most verbose output.
-E, -e These options specify conflict styles that generate less information than -A. See diff3(1) for details. The default is -E. With
-e, merge does not warn about conflicts.
-L label
This option may be given up to three times, and specifies labels to be used in place of the corresponding file names in conflict
reports. That is, merge -L x -L y -L z a b c generates output that looks like it came from files x, y and z instead of from files
a, b and c.
-p Send results to standard output instead of overwriting file1.
-q Quiet; do not warn about conflicts. -V Print 's version number.
DIAGNOSTICS
Exit status is 0 for no conflicts, 1 for some conflicts, 2 for trouble.
IDENTIFICATION
Author: Walter F. Tichy.
Manual Page Revision: 5.7; Release Date: 1995/06/01.
Copyright (C) 1982, 1988, 1989 Walter F. Tichy.
Copyright (C) 1990, 1991, 1992, 1993, 1994, 1995 Paul Eggert.
SEE ALSO
diff3(1), diff(1), rcsmerge(1), co(1).
BUGS
It normally does not make sense to merge binary files as if they were text, but merge tries to do it anyway.
GNU
1995/06/01 MERGE(1)