csv.DictWriter Help convert epoch dates

950
10
Jump to solution
02-22-2018 08:44 AM
JamesCrandall
MVP Frequent Contributor

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?

0 Kudos
1 Solution

Accepted Solutions
RandyBurton
MVP Regular Contributor

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

View solution in original post

10 Replies
RandyBurton
MVP Regular Contributor

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
MVP Frequent Contributor

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!

0 Kudos
RandyBurton
MVP Regular Contributor

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
MVP Frequent Contributor

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
MVP Regular Contributor

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

0 Kudos
JamesCrandall
MVP Frequent Contributor

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.

0 Kudos
RandyBurton
MVP Regular Contributor

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
MVP Frequent Contributor

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
MVP Regular Contributor

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