Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Concatenating many files based on a specific column contents

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Concatenating many files based on a specific column contents
# 1  
Old 06-11-2012
Concatenating many files based on a specific column contents
Dear all,

I have many files(.csv) in a directory.
I want to concatenate the files which have similar entries in a particular column and save into a new file like result_datetime.csv etc.
One example file is like below.

Code:
Sno,Step,Data1,Data2,Data3 etc.
1,0,2,3,4
2,1,3,4,5
3,2,0,1,1

and one more example is like below.

Code:
Sno,Step,Data1,Data2,Data3 etc.
1,0,4,1,1
2,1,4,4,5
3,2,6,1,1

Here both files have similar entries for col2(Step).
So we need to concatenate these files.
Like this we need to scan all the files in the folder and look for specific column entries and concatenate all similar files.

Regards
Sid
# 2  
Old 06-11-2012
like this ?
Code:
# cat file1 file2 file3
Sno,Step,Data1,Data2,Data3 etc.
1,0,2,3,4
2,1,3,4,5
3,2,0,1,1
Sno,Step,Data1,Data2,Data3 etc.
1,0,4,1,1
2,1,4,4,5
3,2,6,1,1
Sno,Step,Data1,Data2,Data3 etc.
1,0,5,5,5
2,1,6,6,6
3,2,7,7,7

Code:
# awk -F, 'NR==FNR&&NR!=1{a[c++]=$2" "$0}NR!=FNR&&FNR!=1{b[x++]=$2" "$0}END{for(i=0;i<c;i++){w="ok";for(j=0;j<x;j++){
split(a[i],s," ");split(b[j],ss," ");if(ss[1]==s[1])if(w=="ok"){printf "%s\n%s\n",s[2],ss[2];w="nok"}else printf "%s\n",ss[2];}}
}' file1 file2 file3
1,0,2,3,4
1,0,4,1,1
1,0,5,5,5
2,1,3,4,5
2,1,4,4,5
2,1,6,6,6
3,2,0,1,1
3,2,6,1,1
3,2,7,7,7

# 3  
Old 06-11-2012
The solution is not complete ! Has some issue.
Hi ygemici,

Your script has some logical problem.
Suppose if I change my file3 contents like below, then the script should ignore this file(file3) as the 2nd column contents across file3 and other two files(file1 and file2 is not 1-to-1).
Code:
Sno,Step,Data1,Data2,Data3
1,1,5,5,5
2,2,6,6,6
3,3,7,7,7

But I am getting the output like below. When I run your script with file1 file2 file3 as puts.
Code:
1,0,2,3,4
1,0,4,1,1
2,1,3,4,5
2,1,4,4,5
1,1,5,5,5 # This line should not present in output.
3,2,0,1,1
3,2,6,1,1
2,2,6,6,6 # This line should not present in output.

Basically the script should print the output from all the files which has similar (exact) column values (i.e. it should treat whole column as the key not the individual rows).

Let me know if you still have problem in understanding my requirement.

Regards
Sidda
# 4  
Old 06-13-2012
Quote:
Originally Posted by ks_reddy
Hi ygemici,

Your script has some logical problem.
Suppose if I change my file3 contents like below, then the script should ignore this file(file3) as the 2nd column contents across file3 and other two files(file1 and file2 is not 1-to-1).
Code:
Sno,Step,Data1,Data2,Data3
1,1,5,5,5
2,2,6,6,6
3,3,7,7,7

But I am getting the output like below. When I run your script with file1 file2 file3 as puts.
Code:
1,0,2,3,4
1,0,4,1,1
2,1,3,4,5
2,1,4,4,5
1,1,5,5,5 # This line should not present in output.
3,2,0,1,1
3,2,6,1,1
2,2,6,6,6 # This line should not present in output.

Basically the script should print the output from all the files which has similar (exact) column values (i.e. it should treat whole column as the key not the individual rows).

Let me know if you still have problem in understanding my requirement.

Regards
Sidda
Code:
# awk -F, 'NR==FNR&&NR!=1{a[c++]=$0;}NR!=FNR&&FNR!=1{a[c++]=$0}END{for(i=0;i<c;i++){split(a[i],s,FS);if(i==0)min=s[2];if(max<s[2])max=s[2];
if(min>s[2])min=s[2];else min=min};for(j=min;j<=max;j++){split(a[x++],s,FS);f=s[1];for(i=0;i<c;i++){split(a[i],ss,FS);;
if(j==ss[2]&&f<=ss[1])print a[i];}}}' file1 file2 file3
1,0,2,3,4
1,0,4,1,1
2,1,3,4,5
2,1,4,4,5
3,2,0,1,1
3,2,6,1,1
3,3,7,7,7

# 5  
Old 06-13-2012
Data
Quote:
Originally Posted by ygemici
Code:
# awk -F, 'NR==FNR&&NR!=1{a[c++]=$0;}NR!=FNR&&FNR!=1{a[c++]=$0}END{for(i=0;i<c;i++){split(a[i],s,FS);if(i==0)min=s[2];if(max<s[2])max=s[2];
if(min>s[2])min=s[2];else min=min};for(j=min;j<=max;j++){split(a[x++],s,FS);f=s[1];for(i=0;i<c;i++){split(a[i],ss,FS);;
if(j==ss[2]&&f<=ss[1])print a[i];}}}' file1 file2 file3
1,0,2,3,4
1,0,4,1,1
2,1,3,4,5
2,1,4,4,5
3,2,0,1,1
3,2,6,1,1
3,3,7,7,7

SmilieSmilie
I am new to scripting could you please explain the execution flow of the above code.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need a Linux command for find/replace column based on specific criteria.

I'm new to shell programming, I have a huge text file in the following format, where columns are separated by single space: ACA MEX 4O_ $98.00 $127.40 $166.60 0:00 0:00 0 ; ACA YUL TS_ $300.00 $390.00 $510.00 0:00 0:00 0 ; ACA YYZ TS_ $300.00 $390.00 $510.00 0:00 0:00 0 ; ADZ YUL TS_ $300.00... (3 Replies)
Discussion started by: transat
3 Replies

2. Shell Programming and Scripting

How to remove a line based on contents of the first column?

Good day all. Using basic UNIX/Linux tools, how would you delete a line based on a character found in column 1? For example, if the CITY name contains an 'a' or 'A', delete the line: New York City; New York Los Angeles; California Chicago; Illinois Houston; Texas Philadelphia;... (3 Replies)
Discussion started by: BRH
3 Replies

3. UNIX for Advanced & Expert Users

Appending a files contents to the end of a specific file name in several directories

Here is my dir structure: /tmp/dave/myappend.txt /tmp/dave/dir1/test.txt /tmp/dave/dir2/test.txt /tmp/dave/dir3/test.txt /tmp/dave/dir4/test.txt I want to append the contents of myappend.txt to the end of each file with the name "test.txt" in all dirs in /tmp/dave/ I have tried this:... (2 Replies)
Discussion started by: bigd213
2 Replies

4. UNIX for Dummies Questions & Answers

How to cut from a text file based on value of a specific column?

Hi, I have a tab delimited text file from which I want to cut out specific columns. If the second column equals one, I want to cut out columns 1 and 5 and 6. If the second column equals two, I want to cut out columns 1 and 5 and 7. How do I go about doing that? Thanks! (4 Replies)
Discussion started by: evelibertine
4 Replies

5. Shell Programming and Scripting

Concatenating contents of a file with members in a directory

Hi, I have a unix file with the below structure - CustId1 CustName1 CustPhn1 /u/home/xmldata/A000001 CustId2 CustName2 CustPhn2 /u/home/xmldata/A000002 CustId3 CustName3 CustPhn3 /u/home/xmldata/A000003 Then I have another unix directory /u/home/xmldata This directory has... (3 Replies)
Discussion started by: Simanto
3 Replies

6. Shell Programming and Scripting

Renaming Files Based on Contents

Hello everyone, I currently have a situation which is causing me some issues for keeping up with certain files. I will explain this to the best of my abilities. I have a list of files as follows 50_REPORT_1111 - file contains the word Car 50_REPORT_2222 - file contains the word House... (15 Replies)
Discussion started by: DerangedNick
15 Replies

7. Shell Programming and Scripting

Concatenating and appending string based on specific pattern match

Input #GEO-1-type-1-fwd-Initial 890 1519 OPKHIJEFVTEFVHIJEFVOPKHIJTOPKEFVHIJTEFVOPKOPKHIJHIJHIJTTOPKHIJHIJEFVEFVOPKHIJOPKHIJOPKEFVEFVOPKHIJHIJEFVHIJHIJEFVTHIJOPKOPKTEFVEFVEFVOPKHIJOPKOPKHIJTTEFVEFVTEFV #GEO-1-type-2-fwd-Terminal 1572 2030... (7 Replies)
Discussion started by: patrick87
7 Replies

8. Shell Programming and Scripting

Merging files based on the contents

Hi, I have a file f1 having the contents as below select (<condn>) from dual I have another file f2 having the contents as below 1, 2, 3 I want to replace <condn> in f1 with the contents of f2 I tried using sed like this sed "s:<condn>:`cat f2`:g" f1 The above command resulted in sed:... (3 Replies)
Discussion started by: mr_manii
3 Replies

9. UNIX for Dummies Questions & Answers

(cont) Retrieve line from a file based on a value in specific column

HI, Your help was great: awk -F":" '$5 ~ /^P/{print }' file I would like to know what changes need to be done to this line code, so that I can put it in a shell script and call it as the example below. example: countries that start with chacater 'P' > country P Result: ... (0 Replies)
Discussion started by: efernandes
0 Replies

10. UNIX for Dummies Questions & Answers

Retrieve line from a file based on a value in specific column

Hi, I have a file that has several values seperated by ":" 2006:John:Student:Football:Portugal:Cinema 2006:James:Engineer:Basket:Poland:Theatre 2007:Lucy:Diver:Gymnastic:England:Music 2007:Smith:Plumber:Basket:Spain:Poker I need make a filter based on the 5th field to find countries that... (1 Reply)
Discussion started by: efernandes
1 Replies
Login or Register to Ask a Question