Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Paste columns based on common column: multiple files

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Paste columns based on common column: multiple files
# 8  
Old 12-15-2017
@RudiC:
Yup, I saw that just now. I can't get my head around it. TF, FC I'm absolutely stumped by it. Please explain.
# 9  
Old 12-15-2017
Code:
awk '
BEGIN           {TF = ARGC - 1                                  # save initial total file count (minus 1 for $0) 
                }

FNR == 1        {FC++                                           # count files opened
                 if (FC <= TF) ARGV[ARGC++] = FILENAME          # if still in the original files 
                }                                               # list, append file name to argument list  
                                                                # so every file will be read twice
FC <= TF        {CNT[$2]++                                      # if still in org. files, count $2 occurrences
                 next                                           # and don''t proceed in script
                }

CNT[$2] == TF   {if (!LINE[$2]) SEQ[++SN] = $2                  # only for $2 that occurred file count times, increment
                                                                # sequence counter if 1. occurrence of $2 in 2. round
                 LINE[$2] = LINE[$2] $0 " "                     # collect lines of every file into a string array
                }

END             {for (s=1; s<=SN; s++) print LINE[SEQ[s]]       # print all collected (5 fold) lines in correct order
                }
'  HGWAS?/merged_info_CHR1.info

I modified this a bit to account for varying file argument counts. Please be aware that one of your input files has DOS <CR> (\r, ^M 0x0D) line terminators, distorting the screen output / result.
Last edited by RudiC; 12-15-2017 at 01:54 PM..
# 10  
Old 12-15-2017
Sorry, rudic I can't understand now too.

Code:
FNR == 1

How does this help?

If occurrence of $2 is equal to file count:
Code:
CNT[$2] == TF   {if (!LINE[$2]) SEQ[++SN] = $2                  # only for $2 that occurred file count times, increment
                                                                # sequence counter if 1. occurrence of $2 in 2. round
                 LINE[$2] = LINE[$2] $0 " "                     # collect lines of every file into a string array
                }

if (!LINE[$2]) SEQ[++SN] = $2

What is this line doing?
LINE, SEQ, SN Smilie

Edited: added occurrence
Last edited by genome; 12-15-2017 at 02:07 PM..
# 11  
Old 12-15-2017
In awk, input lines are counted in the (internal) NR variable across ALL files, while FNR does the same but is reset for every new file. So FNR==1 indicates the begin of a new file, and the (user defined) file count FC is incremented.
I highly recommend to do some reading on awk, e.g. man awk. There, all system variables are listed and you can tell them from user vars like TF and SEQ that are declared and allocated when needed.
The square brackets [...] enclose array indices, so LINE and SEQ are arrays, holding the output lines and the order in which to print them. SN is a scalar serial number.
# 12  
Old 12-16-2017
Well, not one of my magic moments. Try instead
Code:
awk '
BEGIN           {TF = ARGC - 1
                }

                {if (!LINE[$2]) SEQ[++SN] = $2
                 LINE[$2] = LINE[$2] $0 " "
                 CNT[$2]++
                }
END             {for (s=1; s<=SN; s++) if (CNT[SEQ[s]] == TF) print LINE[SEQ[s]]
                }
'  HGWAS?/merged_info_CHR1.info

Only if you're running out of memory with too large or too many files, you might want to fall back to the post#9 version reading files twice but saving some memory.
# 13  
Old 12-29-2017
Hi Rudic

Sorry for my delay in writing back.
I am still going through your code and am struck with it.
I'm copying few lines from #9:

Code:
awk '
BEGIN           {TF = ARGC - 1                                  # save initial total file count (minus 1 for $0) 
                }
#------

FNR == 1 
#-------
FC <= TF
        
#---------
CNT[$2] == TF

I'm surprised you've not used if condition for these lines. What I mean is:
Code:

awk '
BEGIN           {TF = ARGC - 1                                  # save initial total file count (minus 1 for $0) 
                }

#-----
if (FNR == 1 )

#----
if (FC <= TF        )

#-----
if (CNT[$2] == TF )

The code doesn't work too if I use if with these checks. My usual mind set would put if to confirm on these and then proceed ahead. I'm slow with catching up on awk. Sorry. Smilie

---------- Post updated at 04:51 PM ---------- Previous update was at 04:21 PM ----------

The difficult parts for me to get hold of me are:

1- The files are read twice. I cannot even think/read how in the code that's been taken care of.
2- The variables, SN, TF they are internal variables but they have their presence all the way in the code. I mean they don't need to be initiated before using them as in other languages (C, etc. )
# 14  
Old 12-30-2017
A reflection of awk basics might help. man awk:
Quote:
THE AWK LANGUAGE
1. Program structure
An AWK program is a sequence of pattern {action} pairs and user function definitions.
A pattern can be:
BEGIN
END
expression
expression , expression
FNR == 1 is such an expression; if its evaluation yields TRUE, the action following will be executed. So, NO if needed in this awk construct (although the if statement is available for flow control in the action parts).

1- Reading files twice has been eliminated in post#12. Nevertheless, the trick was to append the actual file name to the awk script's parameter list as described in post#9's comments.
2- Yes, awk variables are being created / initiated the first time they're referenced.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to copy a column of multiple files and paste into new excel file (next to column)?

I have data of an excel files as given below, file1 org1_1 1 1 2.5 100 org1_2 1 2 5.5 98 org1_3 1 3 7.2 88 file2 org2_1 1 1 2.5 100 org2_2 1 2 5.5 56 org2_3 1 3 7.2 70 I have multiple excel files as above shown. I have to copy column 1, column 4 and paste into a new excel file as... (26 Replies)
Discussion started by: dineshkumarsrk
26 Replies

2. 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

3. UNIX for Dummies Questions & Answers

Merge selective columns from files based on common key

Hi, I am trying to selectively merge two files based on keys reported in the 1st column. File1: #file1-header1 file1-header2 111 qwe rtz uio 198 asd fgh jkl 165 yxc 789 poi uzt rew 89 lkj File2: #file2-header2 file2-header2 165 ghz nko2 ... (2 Replies)
Discussion started by: dovah
2 Replies

4. 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

5. Shell Programming and Scripting

common entries between files based on 1st column

Hi, I am trying to get the common entries from 2 files based on 1st field.. However when I try to do in perl I am getting blank output.. How can I do this in awk? open(BUFF1, "my_genes"); open(BUFF3, "rawcounts"); #open(WRBUFF,">result_rawcounts"); while($line =<BUFF1>) { ... (3 Replies)
Discussion started by: Diya123
3 Replies

6. 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... (1 Reply)
Discussion started by: evelibertine
1 Replies

7. 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

8. Shell Programming and Scripting

Merging 2 files based on a common column

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

9. Shell Programming and Scripting

sum multiple columns based on column value

i have a file - it will be in sorted order on column 1 abc 0 1 abc 2 3 abc 3 5 def 1 7 def 0 1 -------- i'd like (awk maybe?) to get the results (any ideas)??? abc 5 9 def 1 8 (2 Replies)
Discussion started by: jjoe
2 Replies

10. Shell Programming and Scripting

How to convert 2 column data into multiple columns based on a keyword in a row??

Hi Friends I have the following input data in 2 columns. SNo 1 I1 Value I2 Value I3 Value SNo 2 I4 Value I5 Value I6 Value I7 Value SNo 3 I8 Value I9 Value ............... ................ SNo N (1 Reply)
Discussion started by: ks_reddy
1 Replies
Login or Register to Ask a Question