0

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.

  • Can you add a sample of the csv (as text) to your question? – BERA Mar 28 at 6:47
  • You could use select by expression and then copy&pasta the selections into excel, then save as csv? – Erik Mar 28 at 7:01
4

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')}) 

example

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)))
| improve this answer | |
  • Thanks @BERA I will take a look at your suggestions and give you a feedback. – MyGIS Mar 28 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.