topic Re: csv.DictWriter Help convert epoch dates in Python Questions

csv.DictWriter Help convert epoch dates

JamesCrandall — Sun, 12 Dec 2021 05:46:57 GMT

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?

Re: csv.DictWriter Help convert epoch dates

RandyBurton — Sun, 12 Dec 2021 05:47:00 GMT

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,
        ...
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: csv.DictWriter Help convert epoch dates

JamesCrandall — Thu, 22 Feb 2018 19:24:35 GMT

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!

Re: csv.DictWriter Help convert epoch dates

RandyBurton — Thu, 22 Feb 2018 19:36:05 GMT

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.

Re: csv.DictWriter Help convert epoch dates

JamesCrandall — Thu, 22 Feb 2018 19:53:53 GMT

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/48935816#48935816

Re: csv.DictWriter Help convert epoch dates

RandyBurton — Thu, 22 Feb 2018 20:17:34 GMT

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

Re: csv.DictWriter Help convert epoch dates

JamesCrandall — Thu, 22 Feb 2018 20:34:30 GMT

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.

Re: csv.DictWriter Help convert epoch dates

RandyBurton — Sun, 12 Dec 2021 05:47:03 GMT

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: csv.DictWriter Help convert epoch dates

JamesCrandall — Sun, 12 Dec 2021 05:47:05 GMT

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']
    )‍‍‍‍‍‍‍

Re: csv.DictWriter Help convert epoch dates

RandyBurton — Sun, 12 Dec 2021 05:47:08 GMT

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'] }
‍‍‍‍‍‍‍

Re: csv.DictWriter Help convert epoch dates

JamesCrandall — Fri, 23 Feb 2018 02:15:51 GMT

Good stuff. Thanks again!