2

I have date data that I'm separating into three separate fields (all fields are string type, including the input field) by day, month, and year. Unfortunately, the date field is raw data and isn't consistent; there are eight variations for how the data in this field is organized (d/m/yy; d/m/yyyy; d/mm/yy; d/mm/yyyy; dd/m/yy; dd/m/yyyy; dd/mm/yy; dd/mm/yyyy). I figured out how to separate the day and the year into separate columns, but I'm having difficulty separating the month. I've tried the VB Script Mid() function by initially separating the different formats and performing the function, but that was extremely time-consuming, and I have close to 100,000 data points that I need to go through. Is there a python code that I can use, or a combination of different VB scripts, that can select the numbers between the slashes (/) for all of these? Or am I doomed?

  • Is the input field actually a date field or just a string/text field? – Evil Genius Sep 15 '15 at 16:34
  • 1
    The input field is also a string - I'll edit that into the question! – Tula Sep 15 '15 at 16:46
4

In field calculator, you'll have to run 3 separate Calculate Field operations:

In pseudo code:

day_field = !str_field!.split("/")[0]
month_field = !str_field!.split("/")[1]
year_field = !str_field!.split("/")[2]

With cursors, this is a little simpler (and it should be faster) as it can be reduced to only iterating over str_field once:

fields = [str_field, day_field, month_field, year_field]
with arcpy.da.UpdateCursor(FC, fields) as rows:
     for row in rows:
         #only use the first 3 in case there is a 4th slash for some reason
         row[1:] = row[0].split("/")[0:3] 
         rows.updateRow(row)

Here's what it looks like in Field Calculator:

enter image description here

  • I've tried running this, but it's not working. Is the !str_field! going to be the original field from which the date data is in? – Tula Sep 15 '15 at 17:02
  • @Tula, correct. You'll need to use the field.split("/")[x] code for each DMY field you are interested in. Make sure the parser is set to Python. – Paul Sep 15 '15 at 17:38
  • Thanks for your help. I've tried every combination for the code, but I seem to be inept with Python and the field calculator today. I keep getting error codes that state that the multi-lined code I entered into the code block was switched to my "month = " code box for the Python parser. That and general geoprocessing errors. Just to clarify: I use one of the pseudo codes (i.e. month_field) and insert the field the data is coming from (i.e. !date!). Then, do I replace anything else (other than the str_field) in the latter half? Thanks. – Tula Sep 15 '15 at 21:00
  • @Tula, added a pic; hope it helps! – Paul Sep 15 '15 at 21:11

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.