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?
1 Answer
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:
-
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?– TulaSep 15, 2015 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 toPython
.– PaulSep 15, 2015 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.– TulaSep 15, 2015 at 21:00
-