Create table with frequency of unique names retrieved from multiple .csv files

Question

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    
File_1,0,0,3,1    
File_2,0,1,3,0    
File_3,4,0,0,0    

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.

Answer 1

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']
grid.append(top_line)
for name in names:
    top_line.append(name)
# append each files values to the grid
for file_name in sys.argv[1:]:
    data = files[file_name]
    line = [file_name]
    grid.append(line)
    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)
    writer.writerows(grid)

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