I have 32 CSV files containing fetched information from a database. I need to make a frequency table in TSV/CSV format, where the names of the rows are the name of each file, and the names of the columns are the unique names found throughout the files. The table then needs to be populated with the frequency count of each name for each file. The biggest problem is that not all files contain the same fetched names.

.csv input:

$cat file_1

name_of_sequence,C cc,'other_information'
name_of_sequence,C cc,'other_information'
name_of_sequence,C cc,'other_information'
name_of_sequence,D dd,'other_information'

$cat file_2

name_of_sequence,B bb,'other_information'
name_of_sequence,C cc,'other_information'
name_of_sequence,C cc,'other_information'
name_of_sequence,C cc,'other_information'

$cat file_3

name_of_sequence,A aa,'other_information'
name_of_sequence,A aa,'other_information'
name_of_sequence,A aa,'other_information'
name_of_sequence,A aa,'other_information'

$cat `.csv/.tsv` output:

taxa,A aa,B bb,C cc,D dd    

Using bash I know how to cut the second column, sort and uniq the names, and then obtain a count for each name in each file. I do not know how to make a table that will show all the names, counts and place a "0 when the name does not exist in the file". I normally sort data with Bash, but a python script can also work.

  • You probably want to use something like pandas. And this question would probably be a better fit on Stack Overflow. – Faheem Mitha Apr 29 '16 at 15:47
  • The counts at the end don't appear to match the files. Please clarify. In particular, the files don't contain any E's in the second column. – Faheem Mitha Apr 29 '16 at 16:09
  • I have corrected the counts and syntax for clarity. – Lucia O May 9 '16 at 5:29

The following should work with python 2 and 3, save as xyz.py and run with
python xyz.py file_1 file_2 file_3:

import sys
import csv

names = set()  # to keep track of all sequence names

files = {}  # map of file_name to dict of sequence_names mapped to counts
# counting
for file_name in sys.argv[1:]:
    # lookup the file_name create a new dict if not in the files dict
    b = files.setdefault(file_name, {})    
    with open(file_name) as fp:
        for line in fp:
            x = line.strip().split()  # split the line 
            names.add(x[1])  # might be a new sequence name
            # retrieve the sequence name or set it if not there yet
            # what would not work is "i += 1" as you would need to assign
            # that to b[x[1]] again. The list "[0]" however is a reference 
            b.setdefault(x[1], [0])[0] += 1  

# output
names = sorted(list(names))  # sort the unique sequence names for the columns
grid = []
# create top line
top_line = ['taxa']
for name in names:
# append each files values to the grid
for file_name in sys.argv[1:]:
    data = files[file_name]
    line = [file_name]
    for name in names:
        line.append(data.get(name, [0])[0])  # 0 if sequence name not in file
# dump the grid to CSV
with open('out.csv', 'w') as fp:
    writer = csv.writer(fp)

Using [0] for the counters makes it easier to update the value than using integers directly. If the input files are more complex it is better to read them in with Python's CSV library

  • Thanks for the script, it worked well. Do you mind annotating it a bit, just so I know what parts to modify for future partitioning? Thanks. – Lucia O May 9 '16 at 1:55
  • I added some comments – Anthon May 9 '16 at 5:14
  • I was checking the out.csv file, and although the script does a great job at displaying the frequencies, I just realized that it is quantifying for both column 2 and 3, not only 2, (the headers are not only, A, B, C etc, but also 'other information'). I realize that I did not write the .csv syntax correctly when I first wrote the question, so I think that might have thrown you off. I have corrected the syntax for clarity. Thanks! – Lucia O May 9 '16 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.