3

I currently have a Date field in the format of MM/DD/YYYY.

My goal here is to convert the Dates into 1 of 4 quarters, along with the Year. The field to be created will be called QTR_YR.

My first question is what data type do I need to make the QTR_YR field? Should it be Integer (Short or Long), or Date, or Double?

I am old school so I'd prefer VBA but I can use Python.

4
  • 1
    Is it a date field, not text with a date in it? You have only four options so a cascading if/else statement sounds like the go .. if (month < 4) qtr = 1 else if (month < 7) qtr = 2... let's work with what you've got. Can you go to the field properties in catalog and confirm what it's currently stored as and edit your question, include a screen shot of the attribute table and we will be able to give some more specific advice. Python code works much better in the field calculator than VBS but either will work. Sep 3, 2015 at 0:31
  • VBA would actually be slightly easier in this case (maybe the only time!). You can use the DatePart function, which has an option for returning the quarter and the year. And beware using date fields with Python in field calculator, they do not get evaluated as datetime objects. They do if you use an arcpy.da cursor though... Sep 3, 2015 at 11:27
  • @MichaelMiles-Stimson: The datatype for the current Date field is DATE. But like I was saying earlier, the 1st part of the 2 questions is to ascertain WHAT datatype to use BEFORE I create the new field to accommodate the result of the QUARTER field calculation. Attached IMGUR link below: Imgur
    – Phil
    Sep 3, 2015 at 12:20
  • @EvilGenius: good to know that the VBA would be easier and am looking forward to seeing the code for this field calculation.
    – Phil
    Sep 3, 2015 at 12:29

2 Answers 2

3

The query below in VBA should return a string that looks like "Q2 1990" for each record. All credits go to @EvilGenius for this one.

VBA Sample

"Q" & DatePart ( "q", [MyDate] ) & " " & DatePart ( "yyyy", [MyDate] )

Just make sure your quarter field "QTR_YR" is a Text/String type. If the format "Q2 1990" is not to your taste, you can tweak the format. Concatenation in VBA is done by the ampersand "&" not "+" like most other languages.

Let us know if this works.


Update: As requested, the solution in ArcPy with the UpdateCursor would be:

import arcpy
import datetime

def GetQuarter(month):
    """
    Define a function to return the quarter of a month (Jan=1, Dec=12).
    Where:
        0-3 = Q1
        4-6 = Q2
        7-9 = Q3
        10-12 = Q4
    """
    if 0 <= int(month) <= 3:
        return 1
    elif 4 <= int(month) <= 6:
        return 2
    elif 7 <= int(month) <= 9:
        return 3
    elif 10 <= int(month) <= 12:
        return 4
    else:
        raise ValueError, "Month needs to be between 0-12"

fc = r"C:\Users\MyUsername\Desktop\Working.gdb\TestPoints" # Change to path to your feature class.
with arcpy.da.UpdateCursor(fc, ["MyDate", "MyQuarter"]) as cursor: # Change "MyDate" and "MyQuarter" to your respective field names.
    for row in cursor:
        date = row[0] # A datetime object
        quarter = "Q{0} {1}".format(GetQuarter(date.month), date.year) # The quarter string will look like "Q2 1990".
        row[1] = quarter # Assign quarter value to current row.
        cursor.updateRow(row) # Update (save) row.
del cursor
4
  • John, that did the trick! Props also to @EvilGenius. I would like to add that if anyone would like to put in the equivalent code/formula in ArcPy that would be welcomed.
    – Phil
    Sep 3, 2015 at 14:18
  • Hi @Phil. I've updated the answer to include the equivalent in ArcPy.
    – John
    Sep 3, 2015 at 15:15
  • Hi @John, thanks for the ArcPy code as well. To be honest, I think it's remarkable that the previous VBS code (provided by you - from EvilGenius) can (which was only one line) could work as well as the ArcPy code you've provided above.
    – Phil
    Sep 3, 2015 at 19:56
  • John, you've used a with statement so you don't need to del cursor, it's only the older arcpy.UpdateCursor that isn't compatible with with that needs to be deleted... only a minor point in an otherwise excellent answer. Sep 3, 2015 at 21:44
3

At your request, here's the equivalent in Python, that can be run within calculate field. You'll need to use the codeblock as well as the expression.

Code block:

def yr_qtr(dt):
    date_str = dt.split(' ')[0] # some dates may have a time component
    date_parts = dt.split('/')  # are they always in this format, 
                                # or is it regional specific?
    yr = date_parts[2]
    mnth = int(date_parts[0])
    qrtr = ((mnth-1)/3)  + 1
    return 'Q{0} {1}'.format(qrtr, yr)

Expression:

yr_qtr(!DateField!)

As I noted in my comment, the date value comes over as a string, so you have to convert/parse it. You could use datetime.datetime to do that but I've found that the formatting isn't consistent enough for that.

2
  • Great formula for qrtr - more concise than several if statements.
    – John
    Sep 3, 2015 at 18:56
  • Thanks @EvilGenius for the Python code above. When I get some time I'm going to try it out as well as the ArcPy code provided by John above.
    – Phil
    Sep 3, 2015 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.