CSV file cleanup

Question

I'm fairly new to Python and am going to be parsing hundreds of thousands of names eventually. What I wanted to do was take individual CSV files, and create new CSV files with only the info I wanted. Then to take the whole lot and combine it into one big file.

This code does that, but I feel I have typed way too much of it.

import csv

csv1 = 'dictionary//first_names.csv'
csv2 = 'dictionary//last_names.csv'
csv3 = 'dictionary//last_name_prefixes.csv'
csv4 = 'dictionary//profanities.csv'
csv5 = 'dictionary//suffixs.csv'
csv6 = 'dictionary//titles.csv'
csv7 = 'dictionary//symbols.csv'
csv8 = 'dictionary//businesses.csv'
csv9 = 'dictionary//initials.csv'
csv10 = 'dictionary//numbers.csv'
csv11 = 'dictionary//removable.csv'

def cleanupCSVFiles(csvFile, uniqueName):
    with open(csvFile) as csvfile:
        reader = csv.reader(csvfile)
        names = [line[0] for line in reader]

    with open('MyOutput//'+uniqueName+'.csv', 'w') as f:
        for name in names:
            f.write('{0}\n'.format(name))

def mergeCSVFiles():
    fout=open('MyOutput//AllCSVFiles.csv', 'a')
    for num in range (1,11):
        for line in open('MyOutput//csv'+str(num)+'.csv'):
            fout.write(line)
    fout.close()

cleanupCSVFiles(csv1, "csv1")
cleanupCSVFiles(csv2, "csv2")
cleanupCSVFiles(csv3, "csv3")
cleanupCSVFiles(csv4, "csv4")
cleanupCSVFiles(csv5, "csv5")
cleanupCSVFiles(csv6, "csv6")
cleanupCSVFiles(csv7, "csv7")
cleanupCSVFiles(csv8, "csv8")
cleanupCSVFiles(csv9, "csv9")
cleanupCSVFiles(csv10, "csv10")
cleanupCSVFiles(csv11, "csv11")

mergeCSVFiles()

Ski · Accepted Answer · 2013-11-21 01:09:48Z

3

What is the purpose of those intermediate "output" csv files with a single column of name?

I don't see the purpose of it. So I removed it:

import csv

FILES = [
    'dictionary/first_names.csv',
    'dictionary/last_names.csv',
    'dictionary/last_name_prefixes.csv',
    'dictionary/profanities.csv',
    'dictionary/suffixs.csv',
    'dictionary/titles.csv',
    'dictionary/symbols.csv',
    'dictionary/businesses.csv',
    'dictionary/initials.csv',
    'dictionary/numbers.csv',
    'dictionary/removable.csv',
]

def read_names(filename):
    with open(filename) as csvfile:
        reader = csv.reader(csvfile)
        return [line[0] + '\n' for line in reader]

fout = open('MyOutput/AllCSVFiles.csv', 'a')

for f in FILES:
    fout.writelines(read_names(f))

fout.close()

If you don't want to type in all the files manually you can use os.listdir to find the files:

FILES = ['dictionary/' + filename
         for filename in os.listdir('dictionary/')
         if filename.endswith('.csv')]

edited Nov 21, 2013 at 1:09

answered Nov 21, 2013 at 1:01

Ski

2651 silver badge7 bronze badges

\$\begingroup\$ I also just made a function out of the call, but other than that it works great! \$\endgroup\$

Adam
– Adam

2013-11-22 02:11:00 +00:00
Commented Nov 22, 2013 at 2:11
\$\begingroup\$ I am not familiar with python coding structure. So a question - Shouldn't these file i/o operations be wrapped inside try/catch ? \$\endgroup\$

Ravi Kumar
– Ravi Kumar

2013-11-22 08:09:04 +00:00
Commented Nov 22, 2013 at 8:09
\$\begingroup\$ You can wrap anything with try/catch if you want to handle errors in certain way. What is common in python maybe is to wrap file operations using with open() as file:, which makes sure that file will be closed in the end of with statement. \$\endgroup\$

Ski
– Ski

2013-11-22 10:37:08 +00:00
Commented Nov 22, 2013 at 10:37

Add a comment |

Zoran Pavlovic · Accepted Answer · 2013-11-21 01:33:40Z

1

It's very important that you do not duplicate your logic, or your data. See my minor changes to the way you work with your data. You could even take it further seeing as you construct the filename using a sequence.

import csv

DICT_SUB_FOLDER = 'dictionary'
CSV_EXTENSION = 'csv'
OUTPUT_DIR = 'MyOutput'
FINAL_CSV_FILENAME = 'AllCSVFiles'

CSV_DATA_FILES = {
    'csv1': 'first_names',
    'csv2': 'last_names',
    'csv3': 'last_name_prefixes',
    'csv4': 'profanities',
    'csv5': 'suffixs',
    'csv6': 'titles',
    'csv7': 'symbols',
    'csv8': 'businesses',
    'csv9': 'initials',
    'csv10': 'numbers',
    'csv11': 'removable'
}


def GetNames(csvFile):
    with open(csvFile) as csvfile:
        reader = csv.reader(csvfile)
        names = [line[0] for line in reader]
        return names


def cleanupCSVFiles(csvFile, uniqueName, mainFileFH):
    names = GetNames(csvFile)
    with open('%s//%s.%s' % (OUTPUT_DIR, uniqueName, CSV_EXTENSION), 'w') as fh:
        fh.writelines(names)
        mainFileFH.writelines(names)


def SplitAndConsolidate(outputFile):
    mainFileFH = open(outputFile, 'a')
    for csvFile in CSV_DATA_FILES:
        outputCSVFileName = "%s//%s.%s" % (DICT_SUB_FOLDER, csvFile, CSV_EXTENSION)
        cleanupCSVFiles(outputCSVFileName, csvFile, mainFileFH)
    mainFileFH.close()


SplitAndConsolidate('%s//%s.%s' % (OUTPUT_DIR, FINAL_CSV_FILENAME, CSV_EXTENSION))

Don't feel bad about typing out too much code, or too many lines. The only thing you should feel bad or dirty about when programming is if you duplicate things. Once you remove duplicates, you start seeing the common patterns in your code, and everything flows naturally from that.

edited Nov 21, 2013 at 1:33

answered Nov 21, 2013 at 1:11

Zoran Pavlovic

3231 silver badge10 bronze badges

\$\begingroup\$ I don't like the thing that you construct filenames. This kind of stuff makes it harder to look through the code. Lets say I have a big code base. I have a bug and I have a clue: a file name. Now with your kind of code it will be much harder to find it. And you don't really win anything apart from few characters by constructing the path of the files. Also if you type in full relative path to python file in many power editors and IDE's you can click on the file name to jump to the file. If you construct the file again you loose this ability. \$\endgroup\$

Ski
– Ski

2013-11-21 01:23:19 +00:00
Commented Nov 21, 2013 at 1:23
\$\begingroup\$ @Skirmantas I construct them because I have the future in mind. Unfortunately, I do succumb to the YAGNI problem, that's true. You could say it boils down to a matter of style. Personally, if I have a bug with a specific file as a clue, I will search for the important-seeming part of the filename. I won't copy-paste the whole path to look for it. E.g. I will search for "first_names" instead of "dictionary/first_names.csv". Common sense, because searching for "first_names" I could also find important functions/names/comments/etc. \$\endgroup\$

Zoran Pavlovic
– Zoran Pavlovic

2013-11-21 01:31:23 +00:00
Commented Nov 21, 2013 at 1:31
\$\begingroup\$ You'll get lots of matches instead of 1 then. \$\endgroup\$

Ski
– Ski

2013-11-21 01:34:09 +00:00
Commented Nov 21, 2013 at 1:34
\$\begingroup\$ This case is simple. But so many times I had a hard time finding the stuff I need from the debugging info simply because someone was too smart in inventing dynamic name constructions where simple static definitions would have been good enough. \$\endgroup\$

Ski
– Ski

2013-11-21 01:36:33 +00:00
Commented Nov 21, 2013 at 1:36
\$\begingroup\$ And if you were searching through my code, you would have found 0 matches, because you failed to anticipate that some people don't hard-code filenames into their code. I prefer false-positives over no results. \$\endgroup\$

Zoran Pavlovic
– Zoran Pavlovic

2013-11-21 01:36:36 +00:00
Commented Nov 21, 2013 at 1:36

| Show 2 more comments

Janne Karila · Accepted Answer · 2013-11-22 07:30:19Z

mergeCSVFiles() should take some parameters, such as the output filename.
It is a good practice to open files by with statements. In one function you do so already, in the other you don't.
If the output file is supposed to be a csv file, use csv.writer to ensure proper quoting. If on the other hand you actually need a plain text file, you should not name it .csv.
Why do you create the numbered intermediate files instead of writing directly to the final file?
A generator function is a nice way to process the input files.
Using an if __name__ == '__main__': guard makes possible to import this module in other script to reuse the functions.

My proposal:

import csv

def names_from_csv(filename):
    with open(filename, 'rb') as csvfile:
        reader = csv.reader(csvfile)
        for line in reader:
            yield line[:1]

def merge_csv_files(filenames, output_filename):
    with open(output_filename, 'ab') as csvfile:
        writer = csv.writer(csvfile)
        for filename in filenames:
             writer.writerows(names_from_csv(filename))

if __name__ == '__main__':
    FILES = [
        'dictionary/first_names.csv',
        'dictionary/last_names.csv',
        'dictionary/last_name_prefixes.csv',
        'dictionary/profanities.csv',
        'dictionary/suffixs.csv',
        'dictionary/titles.csv',
        'dictionary/symbols.csv',
        'dictionary/businesses.csv',
        'dictionary/initials.csv',
        'dictionary/numbers.csv',
        'dictionary/removable.csv',
    ]
    merge_csv_files(FILES, 'MyOutput/AllCSVFiles.csv')

Great solution, this does create an extra space between each line it looks like. — Adam
– Adam, Commented Nov 22, 2013 at 1:59
@Airborne Oh, yes, the csv module wants you to open the files in binary mode on Python 2, and with newline='' on Python 3. Otherwise the line endings go wrong on Windows. — Janne Karila
– Janne Karila, Commented Nov 22, 2013 at 7:35

Stack Exchange Network

CSV file cleanup

3 Answers 3

You must log in to answer this question.

Hot Network Questions

CSV file cleanup

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions