Dates to categories

Question

I have an Excel spreadsheet I'm preparing to migrate to Access and the date column has entries in multiple formats such as: 1963 to 1969, Aug. 1968 to Sept. 1968, 1972, Mar-73, 24-Jul, Oct. 2, 1980, Aug 29, 1980, July 1946, etc. and 'undated'. I'm pulling the column that will be the key (map number) and date column into a csv and writing back to a csv. I can strip out years that are 4 digit, but not ranges. And I'm stumped how to extract days and 2 digit years short of re-formatting by hand. My code isn't very elegant and probably not best practice:

import csv, xlwt, re

# create new Excel document and add sheet
# from tempfile import TemporaryFile
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')

# populate first row with header
sheet1.write(0,0,"Year")
sheet1.write(0,1,"Map")
sheet1.write(0,2,"As Entered")

# count variable for populating sheet
rowCount=0

# open csv file and read
with open('C:\dateTestMSDOs.csv', 'rb') as f:
    reader=csv.reader(f)
    for row in reader:

        map = row[0]  # first row is map number
        dateRaw = row[1] # second row is raw date as entered

        # write undated and blank entries
        if dateRaw == 'undated':
            yearStr = '0000'
            rowCount +=1
            sheet1.write(rowCount, 0, yearStr)
            sheet1.write(rowCount, 1, map)
            sheet1.write(rowCount, 2, dateRaw)
            #print rowCount, yearStr, map, dateRaw, '\n'
            yearStr=''

        if dateRaw == '':
            yearStr = 'NoEntry'
            rowCount +=1
            sheet1.write(rowCount, 0, yearStr)
            sheet1.write(rowCount, 1, map)
            sheet1.write(rowCount, 2, dateRaw)
            #print rowCount, yearStr, map, dateRaw, '\n'
            yearStr=''

        # search and write instances of four consecutive digits
        try:
            year = re.search(r'\d\d\d\d', dateRaw)
            yearStr= year.group()
            #print yearStr, map, dateRaw
            rowCount +=1
            sheet1.write(rowCount, 0, yearStr)
            sheet1.write(rowCount, 1, map)
            sheet1.write(rowCount, 2, dateRaw)
            #print rowCount, yearStr, map, dateRaw, '\n'
            yearStr=''

        # if none exist flag for cleaning spreadsheet and print
        except:
            #print 'Nope', map, dateRaw
            rowCount +=1
            yearStr='Format'
            sheet1.write(rowCount, 0, yearStr)
            sheet1.write(rowCount, 1, map)
            sheet1.write(rowCount, 2, dateRaw)
            #print rowCount, yearStr, map, dateRaw, '\n'
            yearStr=''
yearStr=''
dateRaw=''

book.save('D:\dateProperty.xls')
print "Done!"

I would like to write day and month to an additional column as well as pull the second 4 digit date of range entries.

Answer 1

Thank you for the innovative suggestions. After consideration we decided to remove day and month from what would be searchable in our database, since only a relatively small amount of our data had that level of detail. Here is the code I use to extract and generate the data I needed from a long and messy list.

import csv, xlwt, re
# create new Excel document and add sheet
from xlwt import Workbook
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')

# populate first row with header
sheet1.write(0,0,"MapYear_(Parsed)")
sheet1.write(0,1,"Map_Number")
sheet1.write(0,2,"As_Entered")

# count variable for populating sheet
rowCount=0

# open csv file and read
yearStr = ''
with open('C:\mapsDateFix.csv', 'rb') as f:
    reader=csv.reader(f)
    for row in reader:

        map = row[0]  # first row is map number
        dateRaw = row[1] # second row is raw date as entered

        # write undated and blank entries
        if dateRaw == 'undated':
            yearStr = 'undated'
            rowCount +=1
            sheet1.write(rowCount, 0, yearStr)
            sheet1.write(rowCount, 1, map)
            sheet1.write(rowCount, 2, dateRaw)
            #print rowCount, yearStr, map, dateRaw, '\n'
            #yearStr=''

        if yearStr != 'undated':
            if dateRaw == '':
                yearStr = 'NoEntry'
                rowCount +=1
                sheet1.write(rowCount, 0, yearStr)
                sheet1.write(rowCount, 1, map)
                sheet1.write(rowCount, 2, dateRaw)
                #print rowCount, yearStr, map, dateRaw, '\n'
                #yearStr=''

        # search and write instances of four consecutive digits
        if yearStr != dateRaw:
            try:
                year = re.search(r'\d\d\d\d', dateRaw)
                yearStr= year.group()
                #print yearStr, map, dateRaw
                rowCount +=1
                sheet1.write(rowCount, 0, yearStr)
                sheet1.write(rowCount, 1, map)
                sheet1.write(rowCount, 2, dateRaw)
                #print rowCount, yearStr, map, dateRaw, '\n'
                yearStr=''

             # if none exist flag for cleaning spreadsheet and print
            except:
                #print 'Nope', map, dateRaw
                rowCount +=1
                yearStr='Format'
                sheet1.write(rowCount, 0, yearStr)
                sheet1.write(rowCount, 1, map)
                sheet1.write(rowCount, 2, dateRaw)
                #print rowCount, yearStr, map, dateRaw, '\n'
                yearStr=''
yearStr=''
dateRaw=''

book.save('D:\dateProperty.xls')
print "Done!"

Answer 2

You could define all the possible cases of dates using regex, something like:

import re
s = ['1963 to 1969', 'Aug. 1968 to Sept. 1968',
     '1972', 'Mar-73', '03-Jun', '24-Jul', 'Oct. 2, 1980', 'Oct. 26, 1980',
     'Aug 29 1980', 'July 1946']


def get_year(date):
    mm = re.findall("\d{4}", date)
    if mm:
        return mm
    mm = re.search("\w+-(\d{2})", date)
    if mm:
        return [mm.group(1)]

def get_month(date):
    mm = re.findall("[A-Z][a-z]+", date)
    if mm:
        return mm

def get_day(date):
    d_expr = ["(\d|\d{2})\-[A-Z][a-z]+","[A-Z][a-z]+[\. ]+(\d|\d{2}),"]
    for expr in d_expr:
        mm = re.search(expr, date)
        if mm:
            return [mm.group(1)]

d = {}
m = {}
y = {}

for idx, date in enumerate(s):
    d[idx] = get_day(date)
    m[idx] = get_month(date)
    y[idx] = get_year(date)

print "Year Dict: ", y
print "Month Dict: ", m
print "Day Dict: ", d

As result you get dictionaries of days, month, and years. They could be used to populate the rows.

Output:

Year Dict:  {0: ['1963', '1969'], 1: ['1968', '1968'], 2: ['1972'], 3: ['73'], 4: None, 5: None, 6: ['1980'], 7: ['1980'], 8: ['1980'], 9: ['1946']}
Month Dict:  {0: None, 1: ['Aug', 'Sept'], 2: None, 3: ['Mar'], 4: ['Jun'], 5: ['Jul'], 6: ['Oct'], 7: ['Oct'], 8: ['Aug'], 9: ['July']}
Day Dict:  {0: None, 1: None, 2: None, 3: None, 4: ['03'], 5: ['24'], 6: ['2'], 7: ['26'], 8: None, 9: None}

Answer 3

Not entirely sure if this is what you were going for or not but I just used a "simple" regex search and then traversed through the sets of groups that matched, applying the given function defined. If a match is found then the function that is called (found in the regex_groups variable) should return a dictionary with the following keys: start_day, start_month, start_year, end_day, end_month, end_year

Then you can do whatever you'd like with those values. Definitely not the cleanest solution but it works, as far as I can tell.

#!/usr/local/bin/python2.7

import re

# Crazy regex
regex_pattern = '(?:(\d{4}) to (\d{4}))|(?:(\w+)\. (\d{4}) to (\w+)\. (\d{4}))|(?:(\w+)-(\d{2}))|(?:(\d{2})-(\w+))|(?:(\w+)\. (\d+), (\d{4}))|(?:(\w+) (\d+), (\d{4}))|(?:(\w+) (\d{4}))|(?:(\d{4}))'

date_strings = [
  '1963 to 1969',
  'Aug. 1968 to Sept. 1968',
  '1972',
  'Mar-73',
  '24-Jul',
  'Oct. 2, 1980',
  'Aug 29, 1980',
  'July 1946',
]

# Here you set the group matching functions that will be called for a matching group
regex_groups = {
  (1,2):      lambda group_matches: {
    'start_day': '', 'start_month': '', 'start_year': group_matches[0], 
    'end_day': '', 'end_month': '', 'end_year': group_matches[1]
  },
  (3,4,5,6):  lambda group_matches: {
    'start_day': '', 'start_month': group_matches[0], 'start_year': group_matches[1], 
    'end_day': '', 'end_month': group_matches[2], 'end_year': group_matches[3]
  },
  (7,8):      lambda group_matches: {
    'start_day': '', 'start_month': group_matches[0], 'start_year': group_matches[1], 
    'end_day': '', 'end_month': '', 'end_year': ''
  },
  (9,10):     lambda group_matches: {
    'start_day': group_matches[1], 'start_month': '', 'start_year': group_matches[0],
    'end_day': '', 'end_month': '', 'end_year': ''
  },
  (11,12,13): lambda group_matches: {
    'start_day': group_matches[1], 'start_month': group_matches[0], 'start_year': group_matches[2],
    'end_day': '', 'end_month': '', 'end_year': ''
  },
  (14,15,16): lambda group_matches: {
    'start_day': group_matches[1], 'start_month': group_matches[0], 'start_year': group_matches[2],
    'end_day': '', 'end_month': '', 'end_year': ''
  },
  (17,18):    lambda group_matches: {
    'start_day': '', 'start_month': group_matches[0], 'start_year': group_matches[1], 
    'end_day': '', 'end_month': '', 'end_year': ''
  },
  (19,):      lambda group_matches: {
    'start_day': '', 'start_month': '', 'start_year': group_matches[0], 
    'end_day': '', 'end_month': '', 'end_year': ''
  },
}

for ds in date_strings:
  matches = re.search(regex_pattern, ds)
  start_month = ''
  start_year = ''
  end_month = ''
  end_year = ''

  for regex_group, group_func in regex_groups.items():
    group_matches = [matches.group(sub_group_num) for sub_group_num in regex_group]
    if all(group_matches):
      match_data = group_func(group_matches)
      print
      print 'Matched:', ds
      print '%s to %s' % ('-'.join([match_data['start_day'], match_data['start_month'], match_data['start_year']]), '-'.join([match_data['end_day'], match_data['end_month'], match_data['end_year']]))

      # match_data is a dictionary with keys:
      #   * start_day
      #   * start_month
      #   * start_year
      #   * end_day
      #   * end_month
      #   * end_year
      # If a group doesn't contain one of those items, then it is set to a blank string

Outputs:

Matched: 1963 to 1969
--1963 to --1969

Matched: Aug. 1968 to Sept. 1968
-Aug-1968 to -Sept-1968

Matched: 1972
--1972 to --

Matched: Mar-73
-Mar-73 to --

Matched: 24-Jul
Jul--24 to --

Matched: Oct. 2, 1980
2-Oct-1980 to --

Matched: Aug 29, 1980
29-Aug-1980 to --

Matched: July 1946
-July-1946 to --

Answer 4

You can try using dateutil for this. I think you'd still need to deal with some of the more difficult formats in a different way though. See a sample implementation below:

Code:

import dateutil.parser as dateparser

date_list = ['1963 to 1969', 
             'Aug. 1968 to Sept. 1968', 
             'Mar-73', 
             '24-Jul', 
             'Oct. 2 1980', 
             'Aug 29, 1980', 
             'July 1946', 
             'undated']          

for d in date_list:
    if 'to' in d:
        a, b = d.split('to')
        # Get the higher number. Use min to get lower of two.
        print max(dateparser.parse(a.strip()).year, dateparser.parse(b.strip()).year)
    elif d == 'undated':
        print '0000'
    else:
        yr = dateparser.parse(d).year
        print yr

Result:

1969
1968
1973
2014
1980
1980
1946
0000
[Finished in 0.4s]

Only glaring issue I can see is that 24-Jul returns a date of 2014 because the parser assumes the current day, month, or year in place of missing component, ie. Mar-73 will become 1973-03-20 if today is the 20th of the month, etc.