extract common words in the first column of multiple text files

vinsweet · 08-04-2017, 04:53 AM

Hi,
I am new to linux programming. I m attempting to come up with a simple script to extract words that appear in the first column of all the text files in a given folder.

I have multiple text files, each contain 3 columns. I want to extract only those words that are present in all the files.

For e.g.

file1.txt
rs12 band 0.003
rs43 band 0.044
rs67 band 0.011
etc.

file2.txt
rs33 naps 0.0045
rs98 naps 0.0004
rs12 naps 0.01
etc.

file3.txt
rs67 alle 0.003
rs12 alle 0.002
rs98 alle 0.00003
etc

I have many files like this and I want an output that contains common words in the first column of all files.

output.txt
rs12

Any help/suggestions are highly appreciated.
thank you

Turbocapitalist · 08-04-2017, 05:27 AM

Welcome to the forum. The programming you mention is common to all modern systems: in other words you'll find them on all systems except Windows.

In order to accomplish the task you describe, you could look at either perl or awk for your task. With awk, there are some built-in variables associated with the input files. Specifically look at FNR and FILENAME there. With perl, you have a lot of options but you might start with the -p or -n option and do a one-liner. In perl the equivalent of awk's FILENAME is $ARGV, if you are using the -p or -n loop for a one-liner.

Code:

man awk
man perlrun

Let us know which language you have chosen and your approach. Show us the code and we can help you over the hard parts.

ondoho · 08-04-2017, 06:07 AM

so first you need to extract the first word on each line, for each file.
easy with awk:

Code:

awk '{print $1}' file

then you need to compare your findings and find the one (or several) that occur(s) in every file.
not so easy; i'd use bash, because i'm familiar with it, but i guess it could be done with awk, too. or perl. or...

dejank · 08-04-2017, 06:43 AM

Code:

awk '{print $1}' file1 file2 file3 | sort | uniq -d

You would learn more that you did your best to find answer than to pick it up like this. Just saying, with no ill intention at all.

syg00 · 08-04-2017, 07:02 AM

How does that ensure the output is in all files, not just multiple files ?.

vinsweet · 08-04-2017, 07:16 AM

Thank you all for the suggestions. It seems to work fine.

I firstly made separate files by extracting first columns using
awk '{print $1}' file

then i used
awk 'END { for (r in _) if (_[r] == ARGC - 1) print r }
{ _[$0]++ }' filename1 filename2 filename3.... > output_common.txt

this is working perfectly fine. However, i sort of doing it not so efficiently as i am using 2 steps.

Turbocapitalist · 08-04-2017, 07:30 AM

You can do it in one step if you use only the first column as the key instead of the whole line.

Code:

awk '{ a[$1]++ } END { for (r in a ) if ( a[r] == ARGC - 1) print r }' file?.txt

However, if the key turns up more than once in the same file, it will throw the results off.

vinsweet · 08-04-2017, 07:40 AM

that was really cool!

Thanks.

syg00 · 08-04-2017, 08:00 AM

nm ...

Turbocapitalist · 08-04-2017, 11:46 PM

As an afterthought, one way to deal with possible duplicates within a single file is to use a second array and have it count the current file and clear that array whenever a new file is started:

Code:

awk '{ 
       if ( f != FILENAME ) { delete c; f=FILENAME }; 
       if ( ! c[$1]++) { a[$1]++ } 
     } 
     END { for (r in a ) if ( a[r] == ARGC - 1) print r }' file?.txt

AwesomeMachine · 08-05-2017, 07:52 PM

Deleted

Sefyir · 08-06-2017, 09:43 PM

Python has a object called a set. Set has a method (or function) called intersection.

What this does is take multiple sets and find the values that "intersect". That is, {1, 2, 3} {3, 4, 5} {3, 6, 12} will produce 3.
Sets can only have unique values. {1, 2, 3} is ok, but {1, 2, 2, 3} will result in {1, 2, 3}
Does this sound like your problem? I wrote a example program that does what you're looking for. Most of it is collecting each files first column into their own set. Then finally it runs set.intersection(other_sets) to produce the unique strings (or words).

Code:

$ ./similar_words.py file*.txt
rs12

Code:

#!/usr/bin/env python3                                                          
                                                                                
import csv                                                                      
import sys                                                                      
                                                                                
list_unique_words = list()                                                      
for _file in sys.argv[1:]:                                                      
    with open(_file) as f:                                                      
        unique_words = {row[0] for row in csv.reader(f, delimiter=' ')}         
        list_unique_words.append(unique_words)                                  
                                                                                
source_set = list_unique_words[0]                                               
other_sets = list_unique_words[1:]                                              
for word in source_set.intersection(*other_sets):                               
    print(word)

EDIT:
This seemed useful to me, so I made it a little more generic.

Code:

./similar_words.py -h
usage: Parse files for similar columns [-h] [-c COLUMN] [-d DELIMITER]
                                       [-f FILE]

optional arguments:
  -h, --help            show this help message and exit
  -c COLUMN, --column COLUMN
  -d DELIMITER, --delimiter DELIMITER
  -f FILE, --file FILE

This does the default behavior:

Code:

./similar_words.py -d ' ' -c 1 file*.txt
rs12

... But you can now use --delimiter (-d) to change the delimiter and --column (-c) to change which column. You can also use --file (-f) to specify a file, but it's useless unless you use it more then once (eg -f file1.txt -f file2.txt)

Code:

#!/usr/bin/env python3

import csv
import sys
import argparse

parser = argparse.ArgumentParser('Parse files for similar columns')
parser.add_argument('-c', '--column',
        type=int,
        default=1,
        )
parser.add_argument('-d', '--delimiter',
        type=str,
        default=' ',
        )
parser.add_argument('-f', '--file',
        type=str,
        action='append',
        )
args, other_args = parser.parse_known_args()
files = args.file if args.file else other_args

list_unique_words = list()
for _file in files:
    with open(_file) as f:
        unique_words = {row[args.column - 1] 
                for row in csv.reader(f, delimiter=args.delimiter)}
        list_unique_words.append(unique_words)

source_set = list_unique_words[0]
other_sets = list_unique_words[1:]
for word in source_set.intersection(*other_sets):
    print(word)