Split and Merge data in a bunch of csv-files in a directory
<p>I have many .csv files in a directory, coming from an energy measurement device that stores its files every two seconds.
Each file looks similar to that:</p>

<pre><code>Position,Date,Time,V12,Unit,V23,Unit,V31,Unit,A1,Unit,A2,Unit,A3,Unit,P(SUM),Unit,S(SUM),Unit,Q(SUM),Unit,PF(SUM),Unit,PFH,Unit,WH,Unit,SH,Unit,QH,Unit,FREQ,Unit
0,7/21/2016,23:59:56,392.5, ACV,394, ACV,393.2, ACV,1.053, ACA,1.045, ACA,0, ACA,0.367,KW  ,0.432,KVA ,0.229,KVAR,0.84,,0.85,,854.6,KWH   ,1,MVAH  ,516.8,KVARH ,50,Hz  
0,7/21/2016,23:59:58,392.6, ACV,394.1, ACV,392.9, ACV,1.053, ACA,1.048, ACA,0, ACA,0.368,KW  ,0.433,KVA ,0.229,KVAR,0.84,,0.85,,854.6,KWH   ,1,MVAH  ,516.8,KVARH ,50,Hz  
0,7/22/2016,0:00:00,392.5, ACV,394, ACV,392.5, ACV,1.049, ACA,1.042, ACA,0, ACA,0.366,KW  ,0.431,KVA ,0.228,KVAR,0.84,,0.85,,854.6,KWH   ,1,MVAH  ,516.8,KVARH ,49.9,Hz  
0,7/22/2016,0:00:02,392.1, ACV,393.5, ACV,392.1, ACV,1.047, ACA,1.039, ACA,0, ACA,0.363,KW  ,0.428,KVA ,0.226,KVAR,0.84,,0.85,,854.6,KWH   ,1,MVAH  ,516.8,KVARH ,50,Hz  
</code></pre>

<p>Sometimes there is just one day in a file, sometimes two or more days (if the measurement stopped). Sometimes there is a headline between the data (when the measurement stopped and started again).
Each file consists of 30000 rows, the data is sorted in ascending order.</p>

<p>I want to make one file for each day that is named via the date. In this example it would be <code>2016-07-21.csv</code> (you can find the date in the second column). The file should start with the headline row you find above.</p>

<p>Sample files can be found here:
<a href="http://jmp.sh/TefuAek" rel="nofollow">Sample files</a></p>

<p>Is there a script to get the job done?</p>

<p>The following approach should get you started:</p>

<pre><code>from datetime import datetime
from collections import defaultdict
import csv
import glob

days = defaultdict(list)

for filename in glob.glob('*.csv'):
    with open(filename, 'rb') as f_input:
        csv_input = csv.reader(f_input)
        header = next(csv_input)

        for row in csv_input:
            if row[0] != "Position":
                day = datetime.strptime('{} {}'.format(row[1], row[2]), '%m/%d/%Y %H:%M:%S')
                days[row[1]].append([day, row])

for day in sorted(days.keys()):
    with open('/myoutputfolder/{}.csv'.format(days[day][0][0].strftime('%Y-%m-%d')), 'wb') as f_output:
        csv_output = csv.writer(f_output)
        csv_output.writerow(header)
        csv_output.writerows(row for dt, row in sorted(days[day], key=lambda x: x[0]))
</code></pre>

<p>This reads all of the csv files, sorts the entries, and writes each day out into a separate csv file. It uses a Python <code>defaultdict</code> to keep a list of entries for each day. It also converts the date and time columns into a Python <code>datetime</code> so that the entries can all be correctly sorted before before written to the output files. The <code>glob</code> library is used to just return a list of <code>.csv</code> files for a given folder. If sub-folders are needed, this would need to be converted to use <code>os.walk()</code>. The <code>csv</code> library is used to automatically read the csv files into lists.</p>

<p>Tested using Python 2.7.12</p>

python windows csv scripting