How to divide csv file with condition?

Question

I have this csv file:

89,Network activity,ip-dst,80.179.42.44,,1,20160929
89,Payload delivery,md5,4ad2924ced722ab65ff978f83a40448e,,1,20160929
89,Network activity,domain,alkamaihd.net,,1,20160929
90,Payload delivery,md5,197c018922237828683783654d3c632a,,1,20160929
90,Network activity,domain,dnsrecordsolver.tk,,1,20160929
90,Network activity,ip-dst,178.33.94.47,,1,20160929
90,Payload delivery,filename,Airline.xls,,1,20160929
91,Payload delivery,md5,23a9bbf8d64ae893db17777bedccdc05,,1,20160929
91,Payload delivery,md5,07e47f06c5ed05a062e674f8d11b01d8,,1,20160929
91,Payload delivery,md5,bd75af219f417413a4e0fae8cd89febd,,1,20160929
91,Payload delivery,md5,9f4023f2aefc8c4c261bfdd4bd911952,,1,20160929
91,Network activity,domain,mailsinfo.net,,1,20160929
91,Payload delivery,md5,1e4653631feebf507faeb9406664792f,,1,20160929
92,Payload delivery,md5,6fa869f17b703a1282b8f386d0d87bd4,,1,20160929
92,Payload delivery,md5,24befa319fd96dea587f82eb945f5d2a,,1,20160929

I need to divide this csv file to 4 csv files where as the condition is the event number at the beginning of every row. so far I created a set that includes al the event numbers {89,90,91,92}, and I know that I need to make loop in a loop and copy each row to its dedicated csv file.

Have a look at this similar question: http://stackoverflow.com/questions/40789383/python-split-csv-file-according-to-first-character-of-the-first-column/40790237#40790237 — chthonicdaemon, Nov 29 '16 at 15:35

sid-m · Answer 1 · 2016-11-29T17:24:20.697

2

data = {
        '89': [],
        '90': [],
        '91': [],
        '92': []
    }

with open('yourfile.csv') as infile:
    for line in infile:
        prefix = line[:2]
        data[prefix].append(line)

for prefix in data.keys():
    with open('csv' + prefix + '.csv', 'w') as csv:
        csv.writelines(''.join(data[prefix]))

However if your are open to solutions other than python then this can be easily accomplished by running four commands

grep ^89 file.csv > 89.csv
grep ^90 file.csv > 90.csv

Similarly for other values.

edited Nov 29 '16 at 17:24

answered Nov 29 '16 at 15:20

sid-m

1,504
11
19

I got the idea however I am getting an error : " File "C:/Users/oshamir/untitled2.py", line 34, in data[prefix].append(line) KeyError: 'uu'" – shamirs888 Nov 30 '16 at 07:32

score 0 · Answer 2 · answered Nov 29 '16 at 15:39

You can even dynamically create the resulting files if the first field has not been encountered by keeping a mapping of that id and the associated file:

files = {}
with open('file.csv') as fd:
    for line in fd:
        if 0 == len(line.strip()): continue  # skip empty lines
        try:
            id_field = line.split(',', 1)[0] # extract first field
            if not id in files.keys():       # if not encountered open a new result file
                files[id] = open(id + '.csv')
            files[id].write(line)            # write the line in proper file
        except Exception as e:
            print('ERR', line, e)            # catchall in case of problems...

martineau · Accepted Answer · 2016-11-30T16:30:18.780

It would be best to not hardcode the event numbers in your code so it's not dependent on the values of the data. I also prefer to use the csv module which has been optimized to read and write .csv files.

Here's a way to do that:

import csv

prefix = 'events'  # of output csv file names
data = {}

with open('conditions.csv', 'rb') as conditions:
    reader = csv.reader(conditions)
    for row in reader:
        data.setdefault(row[0], []).append(row)

for event in sorted(data):
    csv_filename = '{}_{}.csv'.format(prefix, event)
    print(csv_filename)
    with open(csv_filename, 'wb') as csvfile:
        writer = csv.writer(csvfile)
        writer.writerows(data[event])

Update

The approach implemented above first reads the entire csv file into memory, and then writes the all the rows associated with each event value into a separate output file, one at a time.

A more memory-efficient approach would be to open multiple output files simultaneously and write each row immediately after it has been read out to the proper destination file. Doing this requires keeping track of what files are already open. Something else the file managing code needs to do is make sure all the files are closed when processing is complete.

In the code below all of this has been accomplished by defining and using a Python Context Manager type to centralize the handling of all the csv output files that might be generated depending on how many different event values there are in the input file.

import csv
import sys
PY3 = sys.version_info.major > 2

class MultiCSVOutputFileManager(object):
    """Context manager to open and close multiple csv files and csv writers.
    """
    def __enter__(self):
        self.files = {}
        return self

    def __exit__(self, exc_type, exc_value, traceback):
        for file, csv_writer in self.files.values():
            print('closing file: {}'.format(file.name))
            file.close()
        self.files.clear()
        return None

    def get_csv_writer(self, filename):
        if filename not in self.files:  # new file?
            open_kwargs = dict(mode='w', newline='') if PY3 else dict(mode='wb')
            print('opening file: {}'.format(filename))
            file = open(filename, **open_kwargs)
            self.files[filename] = file, csv.writer(file)

        return self.files[filename][1]  # return associated csv.writer object

And here's how to use it:

prefix = 'events'  # to name of each csv output file

with open('conditions.csv', 'rb') as conditions:
    reader = csv.reader(conditions)
    with MultiCSVOutputFileManager() as file_manager:
        for row in reader:
            csv_filename = '{}_{}.csv'.format(prefix, row[0])  # row[0] is event
            writer = file_manager.get_csv_writer(csv_filename)
            writer.writerow(row)

How to divide csv file with condition?

3 Answers3