Split big file into separate files depending on column's distinct values

Question

I have a big file, which contains column names in the first row. The data is about 20GB and consists of 10s of millions of rows. Every other row contains data which I would like to append to other files depending on the distinct entries in a particular column (depending on index). When the program encounters a new distinct entry it creates the file other wise it should append the data. The following piece of code is my basic attempt to achieve this (I am Python newbie):

import re
import os

input_path_file1 = 'C:\InputData.txt'
output_path = r'C:\Output\Data'
unique_values = {}

# read in first line containing column names
with open(input_path_file1, 'r') as file:
    first_line = file.readline()

rows = 0
# 7 line_number
# 35 range
index = 35
with open(input_path_file1, mode='r') as file:

    # skip first row
    next(file)

    for line in file:
        splitted_data = re.split(r'\t+', line.rstrip('\t'))
        cell_data = splitted_data[index].replace('/', '')
        target = os.path.join(output_path, cell_data + '.txt')

        if cell_data in unique_values:
            unique_values[cell_data] += 1
        else:
            unique_values[cell_data] = 1
            with open(target, 'w') as outfile:
                outfile.write(first_line)

        with open(target, 'a') as outfile:
            outfile.write(line)

        rows += 1

Could this be made more efficient?

Answer 1

It would be more efficient to open each file only once, instead of reopening every time you need it. Two obvious approaches come to mind:

• Accumulate all the lines you want to write to files into a dictionary of lists, and then write to the files one by one. Given that your content is very large, this alternative may consume too much memory and therefore not suitable for your case.

• Keep a dictionary of open file handles to write to.

• If the lines are ordered in such a way that the lines that should go to the same file are grouped together, then you don't even need a dictionary, you could just keep track of the previous filename, so that if the filename is the same then append to the currently open file, otherwise close the current file and open a new one.

And most certainly you don't want to open input_path_file1 twice to read the first line and then again to process the rest of the file. Opening it once would suffice, and instead of next(file) to skip the first line, you could store it with first_line = next(file).

The variable rows is not used, so it could be removed.

The variable unique_values is a dictionary of counts, but the counts are not used for anything. So this could have been a set instead of a dict.

Answer 2

Your solution looks quite ok to me. unfortunately you do not provide all necessary parameters to give an exact review. I do miss the number of lines in the file, the number or percentage of unique values. also there is no hint about the frequency of calls to that function. the points to discuss when knowing the numbers are

file write

If the whole problem fits into memory and there is a performance problem because of frequent calls you could write each file in a single flush. If there is no performance problem stick to the KISS variant. If you are not sure if it will always fit into memory stick to the kiss variant.

file read

You do open the imput file twice. This look a little inefficient. If you do this by intention to get better separation between software modules this is a reasonable idea. If you have latencies on file fetch it may be a very bad idea. I this peticular case I would do header analysis and data processing in a single file open.

regex split

Your regex split can most probably be done by the onboard split(). Depending on your data line.split("\t") or [x for x in line.split("\t") if x] may fit. If this is a tab separated file the regex looks suspicous. the regex is missing documentation and/or examples.

algorithm over all

your solution is O(n), your memory footprint is low, you have a minimum of open files. all good. unless you have real performance problems this is fine.

other topics:

Counting keys

When you want to count keys in a dict() you always end up with special handling for the first appearence of the key. There is Counterexisting in collections which may be incremented even if the key is not exsting yet.

counting lines or other iterables you are iterating over

initializing a variable like rows outside a loop and increment inside is error prone. Use enumerate().

variable names

your names are mostly fine and explaining the code. the exceptions are index (for what?), cell_data (are we talking about spreadsheets? and what is the data in this column?) and unique_values (unique is fine but what values?)

cleaning up code

Whenever you think a program is done clean up. remove unnecessary code, clean/add comments and docstrings. you have unused counters for rows and unique values. you have comments raising questions, not answering them.

---

EDIT:

you may drop your unique_values completely if you test the opened file for emptyness

with open(target, 'a') as outfile:
    if f.tell() == 0:
        outfile.write(first_line)
    outfile.write(line)

Also, after you added some numbers, we know the problem does not fit into memory. So your algorithm is fine. If and only if you have performanceproblems you coud work on chunks (say 10.000 lines) of the inputfile buffering file writes and flush to disk at the end of every chunk only. you could change unique_values to a defaultdict(list) and append lines. at the end of the chunk you iterate over the unique_values, do path calculation from the key and write all lines. again you test for file emptyness to write the header.