csv.DictWriter Help convert epoch dates

[JamesCrandall]

I'm looking for assistance to writing a dictionary to a .csv file.  First, set a "gdata2" variable:

taburl =  hostedFeatureService + '/{}/query/0'
where = '1=1'
query = "?where={}&outFields={}&returnGeometry=true&f=json&token={}".format(where, fieldsM, token)
queryfsURL = taburl + query
            
greq = urllib2.Request(queryfsURL)
gresp = urllib2.urlopen(greq)
gdata = json.load(gresp)
gdata2 = gdata['features']
print gdata2‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This codeblock gives me the gdata2 variable result as:

[{u'geometry': {u'y': 26.67525376665167, u'x': -80.09297701660482}, u'attributes': {u'Ingress1Arrive': 1519133580000L, u'SurveyDate': 1519102800000L, u'globalid': u'fdd28a97-9a28-400d-8284-5ce91868f26e', u'objectid': 3}}, {u'geometry': {u'y': 26.67525546250736, u'x': -80.0929301851895}, u'attributes': {u'Ingress1Arrive': 1519136280000L, u'SurveyDate': 1519102800000L, u'globalid': u'cc83f1b4-6335-4659-8463-9d6d50d82bd7', u'objectid': 4}}, {u'geometry': {u'y': 26.675340745164966, u'x': -80.09289801628141}, u'attributes': {u'Ingress1Arrive': 1519227780000L, u'SurveyDate': 1519189200000L, u'globalid': u'99ed6b91-6823-4702-868a-2bb4dbd32cbf', u'objectid': 5}}]‍‍‍

From this I am attempting to write this result to a .csv file.  It actually works great however I am having an issue with date values (Ingress1Arrive and SurveyDate) as they are epoch formatted values because this source is a hosted feature service.

The DictWriter proc:

with open('C:temp\{}.csv'.format(sheetname), 'wb') as outf:
           dw = csv.DictWriter(outf,  delimiter=",", quotechar="|", fieldnames=['objectid','globalid','SurveyDate','Ingress1Arrive'])
                headers = {}
           for n in dw.fieldnames:
               headers[n] = n
           dw.writerow(headers)
           for row in gdata2:
               dw.writerow(row['attributes'])‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Any recommendations on how/where to perform a conversion prior to writing the final .csv output so that the date format is something that makes sense?

[RandyBurton · Accepted solution]

I haven't played around much with DictWriter.  I usually use the following when I processing my AGOL data; it does show how I process the date/time data when given an epoch date.   Code is just snippets, but it shows the basics.

f = open('certification.xls','w')

jsonResponse = urllib.urlopen(URL, urllib.urlencode(query_dict))
features = json.loads(jsonResponse.read().decode("utf-8-sig").encode("utf-8"),
                      object_pairs_hook=collections.OrderedDict)[u'features']

# write header
f.write("{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\n".format(
    "OBJECTID",
    ...
    "Date Certified",
    ...
    "EditDate",
    ...

for feature in features:
    # AGOL uses GMT/UTC - convert to local time
    if feature['attributes']['CertDate'] is not None:
        certTime = time.strftime('%c', time.localtime(feature['attributes']['CertDate']/1000))
    else : certTime = None
    editTime = time.strftime('%c', time.localtime(feature['attributes']['EditDate']/1000))

    f.write("{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\t{}\n".format(
        feature['attributes']['OBJECTID'],
     ...
        certTime if certTime is not None else "",
     ...
        editTime,
        ...
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JamesCrandall]

Thanks Randy.  I wasn't sure about if/how to use the .format() so this helps.  ugh, this isn't going to be very nice with FS's with tons of columns!

[RandyBurton]

I can see how DictWriter would be helpful with lots of columns.  I suppose you could loop through gdata2 and edit 

u'SurveyDate': 1519102800000L

so that it contains the time/date data in the desired format before passing it to DictWriter.

[JamesCrandall]

Randy,

Here's an alternative method I was able to get from StackOveflow!  Pretty slick way to just update the dictionary and then write header/row over the rest.

https://stackoverflow.com/questions/48935250/csv-dictwriter-convert-epoch-values-before-writing/4893...

[RandyBurton]

Looks like this is best answer. You should mark it as correct.

[JamesCrandall]

I'll leave yours marked as the answer for this post and maybe do an alternate discussion thread or something like that on this topic. 

Thanks again for your assist.

[RandyBurton]

Here's a version of the stackoverflow code that will include the geometry.  (Also changed it to tab delimited.)

import csv
from datetime import datetime

def timestamp_to_date(t):
    return datetime.fromtimestamp(t / 1e3).strftime('%Y-%m-%d %H:%M')

sheetname = 'test'
with open('{}.csv'.format(sheetname), 'wb') as outf:
    dw = csv.DictWriter(
        outf,
        delimiter="\t", # for tab delimited; omit for csv
        quotechar="|",
        fieldnames=['objectid', 'globalid', 'SurveyDate', 'Ingress1Arrive', 'PointX', 'PointY']
    )
    dw.writeheader()
    for row in gdata2:
        row['attributes']['PointX'] = row['geometry']['x']
        row['attributes']['PointY'] = row['geometry']['y']
        current = row['attributes']
        times = {
            'Ingress1Arrive': timestamp_to_date(current['Ingress1Arrive']),
            'SurveyDate': timestamp_to_date(current['SurveyDate'])
        }
        current.update(times)
        dw.writerow(current)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JamesCrandall]

Thanks Randy --- this is definitely much more manageable when dealing with hosted feature services that are Survey123 sources as they can have tons and tons of columns.  Also slight change to the setup of the DictWriter dialect attribute helps to validate things such as commas (since we're writing out to csv files, there can be issues if users are adding commas to values).

 dw = csv.DictWriter(
     outf,
     delimiter="\t", # for tab delimited; omit for csv
     quotechar='"',
     dialect='excel', # seems to handle commas in values
     fieldnames=['objectid', 'globalid', 'SurveyDate', 'Ingress1Arrive', 'PointX', 'PointY']
    )‍‍‍‍‍‍‍

[RandyBurton]

And in my URL request (way back in my first code block)  I like to add "outSR" : "4326" so that I get the geometry in latitude and longitude.  I find this is more meaningful than meters when working with Collector and Survey123 data.

query_dict = {
    "where" : "EditDate >= DATE '2017-04-29 09:00:00'",
    "outFields" : "*",
    "orderByFields" : "EditDate",
    "returnGeometry" : "true",
    "outSR" : "4326", # get geometry in lat/lon
    "f" : "json", "token" : token['token'] }
‍‍‍‍‍‍‍