Exporting a CSV file from QGIS into several CSV files based on a field attributes

Question

I have loaded a CSV table (no coordinates, just data) into QGIS. The table consists of data with a field containing timestamps.

Is there a way to export the same table to CSV and creating separate files containing the same timestamp? Each filename would be the unique timestamp.

I have tried the Export ---> Save feature as..., I can export the entire table back into a single file but I cannot separate into several files containing the same timestamp.

I can use FME to do that using the "fanout" technique, but I need to use QGIS if possible.

Answer 1

Im sure there are other ways but this is working and the code should be adaptable to suite many different problems.

My test data have a date field called 'Start' with datetimes in two different formats:

idfield,scientificName,Start
1,Pisidium,2001-03-21 00:00:00
2,Rosa dumalis subsp. subcanina,9/1/1881 12:00:00 AM
...

Which complicate things a bit. Im extracting by unique years:

from datetime import datetime
from collections import defaultdict #https://docs.python.org/2/library/collections.html#defaultdict-examples
import os

datefield = 'Start'
idfield = 'idfield'
outfolder = r'/home/bera/GIS/test/testout/'

layer = iface.activeLayer()
d = defaultdict(list)
for f in layer.getFeatures():
    try:
        #Most of my dates are in format '9/1/1881 12:00:00 AM'. See: https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior
        data = datetime.strptime(f[datefield], '%m/%d/%Y %I:%M:%S %p').year
        d[data].append(f[idfield])
    except ValueError:
        #But some are in this '2002-05-20 00:00:00'
        data = datetime.strptime(f[datefield], '%Y-%m-%d %H:%M:%S').year
        d[data].append(f[idfield])

#d will now look like:
#defaultdict(<class 'list'>, {2001: [1], 1881: [2], 2012: [3, 9, 42, 43, 45], 2017: [4, 5, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 48], 2018: [6, 44], 2015: [7, 46], 2009: [8], 2013: [10, 11, 41], 2014: [12, 39], 2016: [13, 14, 15, 16, 17, 18], 2011: [40], 2002: [47], 2006: [49]})
#Each year as key and all ids corresponding to that year as a value list

for year, ids in d.items():
    if len(ids)==1:
        ids = ids+ids
    exp = '"{0}" IN{1}'.format(idfield, tuple(ids))
    processing.run("native:extractbyexpression", 
        {'INPUT':layer,'EXPRESSION':exp,
        'OUTPUT':os.path.join(outfolder, str(year)+'.csv')}) 

You can also use pandas module which is more flexible/smart when it comes to date handling and no need for QGIS:

import pandas as pd
import os

file = '/home/bera/GIS/Species_data.csv'
datefield = 'Start'
outfolder = '/home/bera/GIS/test/testout/pandas/'

df = pd.read_csv(file)
df[datefield] = df[datefield].astype('datetime64[ns]') #This will handle the different date formats automatically.

for y in df[datefield].apply(lambda x: x.year).unique():
    df[df[datefield].dt.year == y].to_csv(os.path.join(outfolder, 'Extracted_{0}.csv'.format(y)))