Sponsored Content
Top Forums Shell Programming and Scripting Compare 2 csv files by columns, then extract certain columns of matcing rows Post 302904043 by bkane3 on Sunday 1st of June 2014 12:35:08 PM
Old 06-01-2014
Compare 2 csv files by columns, then extract certain columns of matcing rows

Hi all, I'm pretty much a newbie to UNIX. I would appreciate any help with UNIX coding on comparing two large csv files (greater than 10 GB in size), and output a file with matching columns.

I want to compare file1 and file2 by 'id' and 'chain' columns, then extract exact matching rows' remaining columns from file2 and add them to file1's columns, and remove no match rows. Also create new entries in file1 for multiple row matches from file2

For example:

Code:
$ head file1
id,chain,offer,market,repeattrips,repeater,offerdate
86246,205,1208251,34,5,t,2013-04-24
86252,205,1197502,34,16,t,2013-03-27
12682470,18,1197502,11,0,f,2013-03-28
12996040,15,1197502,9,0,f,2013-03-25
13089312,15,1204821,9,0,f,2013-04-01


Code:
$ head file2
id,chain,dept,category,company,brand,date,productsize,productmeasure,purchasequantity,purchaseamount
86246,205,7,707,1078778070,12564,2012-03-02,12,OZ,1,7.59
86246,205,63,6319,107654575,17876,2012-03-02,64,OZ,1,1.59
86246,205,97,9753,1022027929,0,2012-03-02,1,CT,1,5.99
86976,205,25,2509,107996777,31373,2012-03-02,16,OZ,1,1.99
97646,206,55,5555,107684070,32094,2012-03-02,16,OZ,2,10.38



and the desired output would be:
Code:
id,chain,dept,category,company,brand,date,productsize,productmeasure,purchasequantity,purchaseamount,offer,market,repeattrips,repeater,offerdate
86246,205,7,707,1078778070,12564,2012-03-02,12,OZ,1,7.59,1208251,34,5,t,2013-04-24
86246,205,63,6319,107654575,17876,2012-03-02,64,OZ,1,1.59,1208251,34,5,t,2013-04-24
86246,205,97,9753,1022027929,0,2012-03-02,1,CT,1,5.99,1208251,34,5,t,2013-04-24

If you leave a code please explain them a little bit

Thanks

Last edited by Scrutinizer; 06-01-2014 at 02:07 PM.. Reason: code tags
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

deleting rows & columns form a csv file

Hi , I want to delete some rows & columns from file. can someone please help me on this? Regards. (2 Replies)
Discussion started by: code19
2 Replies

2. Shell Programming and Scripting

Extract difference of two columns from different rows

Hello guys, Please help me to solve this problem. I have tried some awk commands but couldn't succeed. I have a tab delimited file where each record is separated by ------ and 4th column of each record is same. <INPUT FILE> ------ peon 53931587 53931821 ... (12 Replies)
Discussion started by: sam_2921
12 Replies

3. Shell Programming and Scripting

How to compare the columns in two .csv files?

Hi I have to compare two .csv files which having 4 columns and i am expecting the output if there is difference in the 3,4columns in two files with respect to the first column. if my statement is not clear please refer the example. Input: ----- File 1 : hostname MAC SWITCH_IP SWITCH_PORT... (7 Replies)
Discussion started by: Kanchana
7 Replies

4. Shell Programming and Scripting

Extract values from a matrix given the rows and columns

Hi All, I have a huge (and its really huge!) matrix about 400GB in size (2 million rows by 1.5 million columns) . I am trying to optimize its space by creating a sparse representation of it. Miniature version of the matrix looks like this (matrix.mtx): 3.4543 65.7876 54.564 2.12344... (4 Replies)
Discussion started by: shoaibjameel123
4 Replies

5. Shell Programming and Scripting

How to change value in CSV columns and compare two files where Column1 is identical

Hi all, Could someone help me with the following issue: 1st I have an CSV file delimiter is ";" I I have a column 7 where I need to do some multiple mathem. operation, I need all values in this columns to be multiplied by 1.5 and create a new CSV file with the replaced values. 2nd. I... (3 Replies)
Discussion started by: kl1ngac1k
3 Replies

6. Shell Programming and Scripting

Extract several columns with few rows

Hello, I want to extract several columns and rows from a huge tab delimited file for example: I want to print from from column 3 to 68 till row number 30. I have tried using cut command but it was extracting whole 3rd and 68th column. Please suggest a solution. Ryan (8 Replies)
Discussion started by: ryan9011
8 Replies

7. Shell Programming and Scripting

Converting rows to columns in csv file

Hi, I have a requirement to convert rows into columns. data looks like: c1,c2,c3,.. r1,r2,r3,.. p1,p2,p3,.. and so on.. output shud be like this: c1,r1,p1,.. c2,r2,p2,.. c3,r3,p3,.. Thanks in advance, (12 Replies)
Discussion started by: Divya1987
12 Replies

8. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

9. Shell Programming and Scripting

Extract rows with different values at 2 columns

Hallo, I would need to extract only rows which has different value in the second and third column. Thank you very much for any advices Input: A 0 0 B 0 1 C 1 1 D 1 3 Output B 0 1 D 1 3 (4 Replies)
Discussion started by: kamcamonty
4 Replies

10. Shell Programming and Scripting

Extracting data from specific rows and columns from multiple csv files

I have a series of csv files in the following format eg file1 Experiment Name,XYZ_07/28/15, Specimen Name,Specimen_001, Tube Name, Control, Record Date,7/28/2015 14:50, $OP,XYZYZ, GUID,abc, Population,#Events,%Parent All Events,10500, P1,10071,95.9 Early Apoptosis,1113,11.1 Late... (6 Replies)
Discussion started by: pawannoel
6 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 RCS'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.8.1; Release Date: 2012-06-06. Copyright (C) 2010-2012 Thien-Thi Nguyen. Copyright (C) 1990, 1991, 1992, 1993, 1994, 1995 Paul Eggert. Copyright (C) 1982, 1988, 1989 Walter F. Tichy. 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 RCS 5.8.1 2012-06-06 MERGE(1)
All times are GMT -4. The time now is 07:27 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy